/*
 *      DBTest.java
 *
 *      Copyright 2008 Robert Ketteringham <robket@robket-desktop>
 *
 *      This program is free software; you can redistribute it and/or modify
 *      it under the terms of the GNU General Public License as published by
 *      the Free Software Foundation; either version 2 of the License, or
 *      (at your option) any later version.
 *
 *      This program is distributed in the hope that it will be useful,
 *      but WITHOUT ANY WARRANTY; without even the implied warranty of
 *      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *      GNU General Public License for more details.
 *
 *      You should have received a copy of the GNU General Public License
 *      along with this program; if not, write to the Free Software
 *      Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 *      MA 02110-1301, USA.
 */

//export CLASSPATH=/usr/share/java/mysql-connector-java-5.1.5.jar:$CLASSPATH
//export CLASSPATH=/usr/share/java/mysql-connector-java.jar:$CLASSPATH

//Installed the database connectivity by installing the package libmysql-java...
// and then copying the connecter to the jre directory (running this command)
// sudo cp /usr/share/java/mysql-connector-java-5.1.5.jar /usr/lib/jvm/java-6-sun-1.6.0.03/jre/lib/ext

import java.sql.*;
import java.awt.*;
import javax.swing.*;
public class DBTest {
    public static void runMe (String host, String database, String user, String password) throws Exception
    {

        /* run driverTest method shown below */
        driverTest();

        /* make the connection to the database */
        Connection conMe = makeCon (host, database, user, password);

        /* now run a select query of the intended database */
        exeUpdate(conMe, "INSERT INTO testTB VALUES (1234,'Oohh') ; INSERT INTO testTB VALUES (1235,'Dear')");
        exeQuery (conMe, "SELECT * FROM testTB");

        /* close the database */
        conMe.close();
    }

    protected static void driverTest () throws java.lang.ClassNotFoundException
    {
        try
        {
            Class.forName("org.gjt.mm.mysql.Driver");
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("MySQL Driver Found");
        } catch (java.lang.ClassNotFoundException e) {
            System.err.println("MySQL JDBC Driver not found ... ");
            throw (e);
        }
    }

   protected static Connection makeCon (String host, String database, String user, String password) throws java.sql.SQLException
   {
        String url = "";
        try
        {
            url = "jdbc:mysql://" + host + ":3306/" + database;
            Connection con = DriverManager.getConnection(url, user, password);
            System.out.println("Connection established to " + url + "...");
            return con;
        }
        catch (java.sql.SQLException e)
        {
            System.out.println("Connection couldn't be established to " + url);
            throw (e);
        }
    }

    protected static void exeQuery(Connection con, String sqlStatement)
          throws SQLException {

                try {
                        Statement cs = con.createStatement();
                        ResultSet sqls = cs.executeQuery(sqlStatement);

                        /*while (sqls.next()) {
                                String id = (sqls.getObject("IDNumber").toString());
                                String data = (sqls.getObject("Name").toString());
                                System.out.println(id + " " + data);
                        }*/

                            TableModel dataModel = new AbstractTableModel() {
                              public int getColumnCount() { return 10; }
                              public int getRowCount() { return 10;}
                              public Object getValueAt(int row, int col) {
                                sqls.absolute(row);
                                return sqls.getObject(col); }
                          };
                          JTable table = new JTable(dataModel);
                          JScrollPane scrollpane = new JScrollPane(table);

                        sqls.close();

                } catch (SQLException e) {
                        System.out.println ("Error executing sql statement");
                        throw (e);
                }
    }

       protected static void exeUpdate(Connection con, String sqlStatement)
          throws SQLException {

                try {
                        Statement cs = con.createStatement();
                        cs.executeUpdate(sqlStatement);

                } catch (SQLException e) {
                        System.out.println ("Error executing sql statement");
                        throw (e);
                }
    }
    public static void main (String args[]) throws Exception
    {
        runMe("localhost","polobob", "bob","");
    }
}