import java.io.*;
import java.sql.*;
import java.net.*;

class PQQuery   {

    public static void main(String argv[])    {

	System.out.println("Hello world!");
	loadDriver();
	Connection db = connectDB();
	//	createDB(db, "xmJavaTestTbl");  // create called once!
	//	insertToDB(db, "xmJavaTestTbl");
	testQuery(db, "xmJavaTestTbl");
    }

    public static void loadDriver()   {

	try {
	    //	    Class.forName("org.postgresql.Driver");
	    Class.forName("com.mysql.jdbc.Driver");
	    System.out.println("PostgresQL driver loaded okay...");
	}  catch (ClassNotFoundException e) {
	    System.out.println("class not found, exiting ...");
	    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;
    }

    public static Connection connectDB()   {

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

	System.out.print("enter user name and password in one line, separated by a space : ");
	String info = readLine();

	int split = info.indexOf(' ');
	user = info.substring(0, split);
	pass = info.substring(split+1);
	try  {
	    //	    System.out.println(url+" "+user+" "+pass);
	    System.out.println(url+"  user_name pass_word");
	    db = DriverManager.getConnection(url, user, pass);
	}  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("insert 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);
	}
    }
}