/**
 * Test how mysql works with the help of Jeremy Dreese of ECST.<p>
 * This has been tested on Solaris. Need to try it out on linux.<p>
 */
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.io.*;
import java.sql.*;
import java.net.*;

class mySqlQuery {

    public static void main(String argv[])  {

	System.out.println("Hello world!");
	loadDriver();
	System.out.println("calling connectDB ...");
	Connection db = connectDB();
	if (db != null)
	    System.out.println("db connected ...");
	else
	    System.out.println("db connection failed ...");
	//createDB(db, "xmJavaTestTbl");  // create called once!
	//insertToDB(db, "xmJavaTestTbl");
	//deleteTbl(db, "xmJavaTestTbl");
	testQuery(db, "xmJavaTestTbl");
    }

    public static void loadDriver() {

	try {
	    Class.forName("com.mysql.jdbc.Driver").newInstance(); 
	    //Class.forName("org.postgresql.Driver");
	    System.out.println("mySQL driver loaded okay...");
	}
	catch (ClassNotFoundException e) {
	    System.out.println("class not found, exiting ...");
	    System.exit(1);
	}
	catch (Exception e) {
	    System.out.println("new instance exception");
	    System.exit(1);
	}
    }

    public static String readLine()  {

	String localStr = "";
	try {
	    BufferedReader in = 
		new BufferedReader(new InputStreamReader (System.in) );
	    localStr = in.readLine();
	}
	catch (IOException e) {
	    System.out.println("IO exception, exiting ...");
	    System.exit(1);
	}
	return localStr;
    }

  /**
   * Give a fixed user name, enter a password from standard input
   * and test the db.<p>
   */
    public static Connection connectDB()  {

	String user;
	String pass;
	String dbName = "xmeng";
	String hostName = "db.eg.bucknell.edu";
	String protocol = "jdbc:mysql";
	String url = protocol + "://" + hostName + "/" + dbName;
	Connection db = null;

	String info = readLine();

	int split = info.indexOf(' ');
	user = info.substring(0, split);
	pass = info.substring(split+1);

	try {
	    //	    System.out.println(url+" "+user+" "+pass);
	    //	    DriverManager.getConnection("jdbc:mysql://db.eg.bucknell.edu/test?user=xmeng&password=yyy");
	    //	    DriverManager.getConnection("jdbc:mysql://db.eg.bucknell.edu/test?user=xmeng&password=yy"); // wrong password, should fail

	    db = DriverManager.getConnection(url, user, pass);
	    System.out.println("connected okay");
	}
	catch (SQLException e) {
	    System.out.println("SQLException, exiting ...");
	    System.exit(1);
	}
	return db;
    }


    public static void createDB(Connection db, String tblName)  {

	try {
	    Statement s = db.createStatement();
	    s.executeUpdate("create table " + tblName +
			    "(name char(10), age integer, salary real)");
	    System.out.println("create table successfully ...");
	}
	catch (SQLException e) {
	    System.out.println("create table failed, exiting ...");
	    System.exit(1);
	}
    }

    public static void insertToDB(Connection db, String tblName) {

	try {
	    Statement s = db.createStatement();
	    /*
	    s.executeUpdate("insert into " + tblName +
			    " values ('first_name', 23, 123.45)");
	    */
	    s.executeUpdate("insert into " + tblName +
			    " values ('second_nam', 21, 423.45)");
	    s.executeUpdate("insert into " + tblName +
			    " values ('last_name_', 19, 23.45)");
	    s.executeUpdate("insert into " + tblName +
			    " values ('third_name', 20, 723.45)");
	    System.out.println("insert table successfully ...");
	}
	catch (SQLException e) {
	    System.out.println("create table failed, exiting ...");
	    System.exit(1);
	}
    }


    public static void testQuery(Connection db, String tblName)  {

	try {
	    Statement s = db.createStatement();

	    ResultSet rs = s.executeQuery("select * from " + tblName);

	    while (rs.next()) {
		    String name = rs.getString(1);  // first field is name
		    int    age  = rs.getInt(2);     // second field is age
		    double salary = rs.getDouble(3); // third field is salary

		    System.out.println(name + " " + age + " " + salary);
		}
	}
	catch (SQLException e) {
	    System.out.println("query failed, exiting ...");
	    System.exit(1);
	}
    }

    public static void deleteTbl(Connection db, String tblName) {

	try {
	    Statement s = db.createStatement();
	    s.executeUpdate("delete from " + tblName);
	    System.out.println("deleted table successfully ...");
	}
	catch (SQLException e) {
	    System.out.println("delete table failed, exiting ...");
	    System.exit(1);
	}
    }

}