EmbeddedSQL.java

/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science & Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL
/*
 * MODIFIED BY JUSTIN KAHAL AND THOMAS DESMOND
 * SSID:860892022
 *
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science &amp; Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


//FUCK THIS TESTING
// My newest CHANGE

importjava.sql.DriverManager;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.io.File;
importjava.io.FileReader;
importjava.io.BufferedReader;
importjava.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
publicclassEmbeddedSQL {


publicstaticStringcurrentUser;	
// reference to physical database connection.
privateConnection_connection = null;

// handling the keyboard inputs through a BufferedReader
// This variable can be global for convenience.
staticBufferedReaderin = newBufferedReader(
newInputStreamReader(System.in));

/**
 * Creates a new instance of EmbeddedSQL
 *
 * @param hostname the MySQL or PostgreSQL server hostname
 * @param database the name of the database
 * @param username the user name used to login to the database
 * @param password the user login password
 * @throws java.sql.SQLException when failed to make a connection.
 */
publicEmbeddedSQL (Stringdbname, Stringdbport, Stringuser, Stringpasswd) throwsSQLException {

System.out.print("Connecting to database...");
try{
// constructs the connection URL
Stringurl = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
System.out.println ("Connection URL: " + url + "\n");

// obtain a physical connection
this._connection = DriverManager.getConnection(url, user, passwd);
System.out.println("Done");
 }catch (Exceptione){
System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
System.out.println("Make sure you started postgres on this machine");
System.exit(-1);
 }//end catch
 }//end EmbeddedSQL

/**
 * Method to execute an update SQL statement. Update SQL instructions
 * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
 *
 * @param sql the input SQL string
 * @throws java.sql.SQLException when update failed
 */
publicvoidexecuteUpdate (Stringsql) throwsSQLException {
// creates a statement object
Statementstmt = this._connection.createStatement ();

// issues the update instruction
stmt.executeUpdate (sql);

// close the instruction
stmt.close ();
 }//end executeUpdate

publicStringexecuteLoginQuery (Stringquery) throwsSQLException{
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
rs.next();
Stringresultset = rs.getString(numCol);
System.out.println(resultset);

stmt.close();
returnresultset;
// iterates through the result set and output them to standard out.
/* boolean outputHeader = true;
 while (rs.next()){
 if(outputHeader){
 for(int i = 1; i <= numCol; i++){
 System.out.print(rsmd.getColumnName(i) + "\t");
 }
 System.out.println();
 outputHeader = false;
 }
 for (int i=1; i<=numCol; ++i)
 System.out.print (rs.getString (i) + "\t");
 System.out.println ();
 ++rowCount;
 }//end while
 stmt.close ();
 return rowCount;
 */
 }

/**
 * Method to execute an input query SQL instruction (i.e. SELECT). This
 * method issues the query to the DBMS and outputs the results to
 * standard out.
 *
 * @param query the input query string
 * @return the number of rows returned
 * @throws java.sql.SQLException when failed to execute the query
 */
publicintexecuteQuery (Stringquery) throwsSQLException {
// creates a statement objectv
Statementstmt = this._connection.createStatement ();

// issues the query instruction
ResultSetrs = stmt.executeQuery (query);

/*
 ** obtains the metadata object for the returned result set. The metadata
 ** contains row and column info.
 */
ResultSetMetaDatarsmd = rs.getMetaData ();
intnumCol = rsmd.getColumnCount ();
introwCount = 0;

// iterates through the result set and output them to standard out.
booleanoutputHeader = true;
while (rs.next()){
if(outputHeader){
for(inti = 1; i <= numCol; i++){
System.out.print(rsmd.getColumnName(i) + "\t");
 }
System.out.println();
outputHeader = false;
 }
for (inti=1; i<=numCol; ++i)
System.out.print (rs.getString (i) + "\t");
System.out.println ();
 ++rowCount;
 }//end while
stmt.close ();
returnrowCount;
 }//end executeQuery

/**
 * Method to close the physical connection if it is open.
 */
publicvoidcleanup(){
try{
if (this._connection != null){
this._connection.close ();
 }//end if
 }catch (SQLExceptione){
// ignored.
 }//end try
 }//end cleanup

/**
 * The main execution method
 *
 * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
 */
publicstaticvoidmain (String[] args) {
if (args.length != 4) {
System.err.println (
"Usage: " +
"java [-classpath <classpath>] " +
EmbeddedSQL.class.getName () +
" <dbname> <port> <user> <passwd>");
return;
 }//end if

Greeting();
EmbeddedSQLesql = null;
try{
// use postgres JDBC driver.
Class.forName ("org.postgresql.Driver").newInstance ();
// instantiate the EmbeddedSQL object and creates a physical
// connection.
Stringdbname = args[0];
Stringdbport = args[1];
Stringuser = args[2];
Stringpasswd = args[3];
esql = newEmbeddedSQL (dbname, dbport, user, passwd);

booleankeepon = true;
while(keepon) {
// These are sample SQL statements
System.out.println("WELCOME! Are you an existing user?");
System.out.println("---------");
System.out.println("0. Yes I am!");
System.out.println("1. No, I need to register!");
System.out.println("9. < EXIT (Stop the program)");

switch (readChoice()){
case0: LogInQuery(esql); break;
case1: RegisterQuery(esql); break;
case9: keepon = false; break;
default : System.out.println("Unrecognized choice!"); break;
 }//end switch
 }//end while
 }catch(Exceptione) {
System.err.println (e.getMessage ());
 }finally{
// make sure to cleanup the created table and close the connection.
try{
if(esql != null) {
System.out.print("Disconnecting from database...");
esql.cleanup ();
System.out.println("Done\n\nBye !");
 }//end if
 }catch (Exceptione) {
// ignored.
 }//end try
 }//end try
 }//end main

publicstaticvoidGreeting(){
System.out.println(
"\n\n*******************************************************\n" +
" User Interface \n" +
"*******************************************************\n");
 }//end Greeting

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
publicstaticintreadChoice() {
intinput;
// returns only if a correct value is given.
do {
System.out.print("Please make your choice: ");
try { // read the integer, parse it and break.
input = Integer.parseInt(in.readLine());
break;
 }catch (Exceptione) {
System.out.println("Your input is invalid!");
continue;
 }//end try
 }while (true);
returninput;
 }//end readChoice

publicstaticvoidLogInQuery(EmbeddedSQLesql){
try{
System.out.println("You are trying to Login as an existing user, enter your Username (Make sure it is only 9 chars long)");
while( true ){
System.out.println("Please specify your username:");
Stringinput = in.readLine();
Stringquery = "SELECT COUNT(user_id) FROM users WHERE user_id='";
query += input + "'";

Stringoutput = esql.executeLoginQuery (query);
if( output != "1" ) {
System.out.println("Sorry, that username isn't in our records. Please try again.");
continue;
 }
elseif( output == "1" ){
currentUser = input;
System.out.println("Please specify your password:");
input = in.readLine();
query = "SELECT COUNT(user_id) FROM users WHERE user_id='" + currentUser + "' AND password='" + input + "'";
Stringoutput2 = esql.executeLoginQuery(query);
if( output2 != "1" ){
System.out.println("Sorry, the username and password don't match up. Please try again.");
continue;
 }
elseif( output2 == "1" ){
System.out.println("Successfully logged in!");
break;
 }
 }
 }


//System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end QueryExample

publicstaticvoidRegisterQuery(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);
 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query1

publicstaticvoidQuery2(EmbeddedSQLesql){
try{
Stringquery = "SELECT C.sid, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE C.sid=S.sid GROUP BY C.sid HAVING COUNT(C.pid)>=3;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query2

publicstaticvoidQuery3(EmbeddedSQLesql){
try{
Stringquery = "SELECT sname, COUNT(C.pid) FROM Suppliers S, Catalog C "
 + "WHERE pid IN (SELECT pid FROM Parts WHERE color='Green') AND S.sid=C.sid "
 + "GROUP BY sname";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query3

publicstaticvoidQuery4(EmbeddedSQLesql){
try{
Stringquery = "SELECT P.pname, MAX(C.cost) FROM Parts P, Catalog C "
 + "WHERE C.sid IN (SELECT temp.sid "
 + "FROM (SELECT sid, pid FROM Catalog WHERE pid IN (SELECT pid FROM Parts WHERE color='Red')) as temp, "
 + "(SELECT sid, pid FROM catalog WHERE PID IN (SELECT pid FROM Parts WHERE color='Green')) as temp2 "
 + "WHERE temp.sid = temp2.sid) "
 + "GROUP BY P.pname;";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query4

publicstaticvoidQuery5(EmbeddedSQLesql){
try{
Stringquery = "SELECT DISTINCT pname FROM Parts "
 + "WHERE pid IN (SELECT pid FROM Catalog WHERE cost<";

System.out.print("\tEnter cost: $");
Stringinput = in.readLine();
query+=input;
query += ");";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query5

publicstaticvoidQuery6(EmbeddedSQLesql){
try{
Stringquery = "SELECT address FROM Suppliers WHERE sid IN ("
 + "SELECT sid FROM Catalog WHERE pid IN ("
 + "SELECT pid FROM Parts WHERE pname='";

System.out.print("\tEnter name of part: ");
Stringinput = in.readLine();
query += input;
query += "'));";

introwCount = esql.executeQuery (query);
System.out.println ("total row(s): " + rowCount);

 }catch(Exceptione){
System.err.println (e.getMessage ());
 }
 }//end Query6

}//end EmbeddedSQL