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

/** JDBC Driver documentation can be fount at:
 *  jdbc.postgresql.org/documentation/92/index.html **/

public class DBConnection {
	
	public java.sql.Connection conn;
	public java.sql.PreparedStatement st;
	public java.sql.ResultSet rs;

	public static void main(String[] args){
		
		String hostname  = "<andrew>-db.qatar.cmu.local";
		String DBname    = "Recitation3";
		String username  = "postgres";
		String password  = "*******";	
		
		DBConnection newConn = new DBConnection();
		
		/* load the postgreSQL driver */
		
		try {
			newConn.loadDriver();
		} 
		catch (ClassNotFoundException e) {			
			e.printStackTrace();
		}

		/* Connect to a database */
		
		try {
			newConn.connectToDatabase(hostname, DBname, username, password);
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		/* Execute a SQL query */
		
		try {
			
			String query = "select * from student where " + 
							"standing = ? and major = ?  ";
			
			newConn.executeSelect(query, new ArrayList<String>(
						Arrays.asList("JR","Computer Science")));
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		/* Display the result of the query */
		
		try {
			
			newConn.printResult();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public DBConnection() {
		
			this.conn = null;
			this.st = null;
			this.rs = null;	
	}
	
	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 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 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 printResult() throws SQLException {
		
		ResultSetMetaData md = this.rs.getMetaData();
		int numCols = md.getColumnCount();
	
		while(rs.next()) 
		{
			for(int i = 1; i <= numCols; i++)
			{
				print(rs.getObject(i));
			
				if(i != numCols)
					print(" | ");
			}
			System.out.println();
		}	
	}
	
	public void clean() throws SQLException {
		
		st.close();
		rs.close();
	}
	
	public static void println(Object msg)
	{
		System.out.println(msg.toString());
	}
	
	public static void print(Object msg)
	{
		System.out.print(msg.toString());
	}
}