/**
 * A simple example of working with PostgresSQL database.
 * The syntax to connect to PostgreSQL and mySQL are identical with the 
 * exception of Class.forName() call. See the function loadDriver()
 *
 * For PostgreSQL, see the following
 * https://www.tutorialspoint.com/postgresql/postgresql_java.htm
 *
 * For mySQL, see the following
 * https://www.javatpoint.com/example-to-connect-to-the-mysql-database
 *
 * 2019-09-26
 */
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("Driver loaded okay...");
		}  catch (ClassNotFoundException e) {
			System.err.println("Driver class not found, exiting ...");
			System.exit(1);
		}  catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			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()   {

		// If mysql database is installed on a local host, do this
		// Otherwise see the rest of the function
		/*
		c = DriverManager
			.getConnection("jdbc:mysql://localhost:5432/testdb",
						   "mysql", "123");
		*/

		String user, pass;
		String dbName = "csci479_xmeng";
		String hostName = "db.eg.bucknell.edu";
		String protocol = "jdbc:mysql"; // or "jdbc:post
		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);
		}
    }
}