import java.sql.*;
import java.util.Arrays;
import java.util.Properties;

public class DBConnection {

	public Connection conn;
	public PreparedStatement st;
	public ResultSet rs;

	public void loadDriver() throws ClassNotFoundException {
		String driver = "org.postgresql.Driver";
		Class.forName(driver);
		println("JDBC driver loaded!");
	}

	public void connectToDatabase(String hostname, String DB, String username, String password) throws SQLException {
		String url = "jdbc:postgresql://" + hostname + "/" + DB;
		
		Properties props = new Properties();
		props.setProperty("user", username);
		props.setProperty("password", password);

		conn = DriverManager.getConnection(url, props);
		println("Connection established to database " + DB + "!");
	}

	public static void main(String[] a) {

		String hostname = "tjabban2-db.qatar.cmu.local";
		String DBname = "Recitation3";
		String username = "postgres";
		String password = Credentials.password;

		DBConnection newConn = new DBConnection();

		try {
			newConn.loadDriver();
		} catch (ClassNotFoundException e) { e.printStackTrace();}

		try {
			/* Connect to the database */
			newConn.connectToDatabase(hostname, DBname, username, password);

			/* Execute non-generic select */
			newConn.executeSelect("JR", "Computer Science");
			
			/* Print result set*/
			newConn.printResult();
				
			/* Execute generic select */
			String query = "select * from student where " + 
								"standing = ? and major = ?  ";
			newConn.executeSelect(query, new ArrayList<String>
							(Arrays.asList("SO", "Computer Science")));
			
			/* Print result set*/
			newConn.printResult();

			/*	 Clean 	  */
			newConn.clean();

		} catch (SQLException e) { e.printStackTrace(); }

	}

	private void executeSelect(String query, ArrayList<String> args) throws SQLException {
		st = conn.prepareStatement(query);
		for (int i = 0; i < args.size(); i++)
			st.setString(i+1, args.get(i));
		rs = st.executeQuery();
		println("Executed SQL query: " + st.toString());
	}

	public void executeSelect(String query) throws SQLException {
		st = conn.prepareStatement(query);
		st.execute();
		rs = st.executeQuery();
	}

	public void executeSelect(String standing, String major) throws SQLException {
		String query = "select * from student where standing = ? and major = ?";
		st = conn.prepareStatement(query);
		st.setString(1, standing);
		st.setString(2, major);
		rs = st.executeQuery();
		println("Executed SQL query: " + st.toString());
	}

	public void printResult() throws SQLException {
		ResultSetMetaData md = rs.getMetaData();
		int numCols = md.getColumnCount();
		while (rs.next()) {
			for (int i = 1; i <= numCols; i++) {
				print(rs.getObject(i));
				if (i != numCols)
					print("  |  ");
			}
			println("");
		}
	}

	public static void println(Object msg) {
		System.out.println(msg.toString());
	}

	public static void print(Object msg) {
		System.out.print(msg.toString());
	}
	
	public void clean() throws SQLException {
		this.conn.close();
		this.st.close();
		this.rs.close();
	}

}