Subversion Repositories programming

/* Written by Ira Snyder
 * Started on 03-17-2005
 * CS435 Homework #3
 */
import java.io.*;
import java.sql.*;

class Homework03 {
        public static void main ( String [] args ) throws Exception {
                
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));
                
                boolean quit=false;
                String inStr;
                int input;
                
                while( quit == false ) { //the menu loop
                        printmenu();
                        inStr = kb.readLine();
                        input = Integer.parseInt(inStr);
                
                        switch( input ) {
                                case 1: userAddDriver(); break;
                                case 2: userEditDriver(); break;
                                case 3: displayAllDriver(); break;
                                case 4: userAddBus(); break;
                                case 5: userEditBus(); break;
                                case 6: displayAllBus(); break;
                                case 7: userAddTrip(); break;
                                case 8: userEditTrip(); break;
                                case 9: displayAllTrip(); break;
                                case 10: userAddStop(); break;
                                case 11: userEditStop(); break;
                                case 12: displayAllStop(); break;
                                case 13: userDisplayTrip(); break;
                                case 14: userAddTripOffering(); break;
                                case 15: userEditTripOffering(); break;
                                case 16: userDisplayMonthlySched(); break;
                                case 17: quit=true; break;
                                default: userBadChoice(); break;
                        }
                }
        }

        public static void userBadChoice() { 
                System.out.println("Bad Entry -- Try Again"); 
        }
        
        public static void printmenu() {
                System.out.println("1.  Add Driver");
                System.out.println("2.  Edit Driver");
                System.out.println("3.  Display All Drivers");
                System.out.println("4.  Add Bus");
                System.out.println("5.  Edit Bus");
                System.out.println("6.  Display All Busses");
                System.out.println("7.  Add Trip");
                System.out.println("8.  Edit Trip");
                System.out.println("9.  Display All Trips");
                System.out.println("10. Add Stop");
                System.out.println("11. Edit Stop");
                System.out.println("12. Display All Stops");
                System.out.println("13. Display All Stops on a Trip");
                System.out.println("14. Add to Monthly Schedule");
                System.out.println("15. Edit Monthly Schedule");
                System.out.println("16. Display Monthly schedule");
                System.out.println("17. QUIT");
                System.out.print("make a choice: ");
        }       
        
        public static void userAddDriver() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();

                System.out.print("Enter name: ");
                String name=kb.readLine();

                System.out.print("Enter address: ");
                String address=kb.readLine();

                System.out.print("Enter telnum: ");
                String telnum=kb.readLine();

                addDriver(id,name,address,telnum);
        }
        
        public static void addDriver( String id,
                                      String name,
                                      String address,
                                      String telnum ) throws Exception {
                String values=id+","+quote(name)+","+quote(address)+","+quote(telnum);
                SQLDriver.runInsert("driver","driverid,name,address,telnum",values);
        }

        public static void userEditDriver() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();

                System.out.print("Enter attribute to change: ");
                String item=kb.readLine();

                System.out.print("Enter the new value: ");
                String newval=kb.readLine();

                editDriver(id,item,newval);
        }

        public static void editDriver( String id,
                                       String item,
                                       String newval ) throws Exception {
                
                String cond="driverid="+id;
                SQLDriver.runUpdate("driver",item,quote(newval),cond);
        }

        public static void userAddBus() throws Exception {

                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();

                System.out.print("Enter plate: ");
                String plate=kb.readLine();

                System.out.print("Enter year: ");
                String year=kb.readLine();

                System.out.print("Enter make: ");
                String make=kb.readLine();

                addBus(id,plate,year,make);
        }

        public static void addBus( String id,
                                   String plate,
                                   String year,
                                   String make ) throws Exception {
                String items="busid,plate,year,make";
                String values=id+","+quote(plate)+","+quote(year)
                              +","+quote(make);

                SQLDriver.runInsert("bus",items,values);
        }
        
        public static void userEditBus() throws Exception {
                
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();

                System.out.print("Enter attribute to change: ");
                String item=kb.readLine();

                System.out.print("Enter the new value: ");
                String newval=kb.readLine();

                editBus(id,item,newval);
        }
        
        public static void editBus( String id,
                                    String item,
                                    String newval ) throws Exception {
                
                String cond="busid="+id;
                SQLDriver.runUpdate("bus",item,quote(newval),cond);
        }

        public static void userAddTrip() throws Exception {
                BufferedReader kb=new BufferedReader(
                                      new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();
                
                System.out.print("Enter start location: ");
                String startloc=kb.readLine();

                System.out.print("Enter end location: ");
                String endloc=kb.readLine();

                addTrip(id,startloc,endloc);
        }
        
        public static void addTrip( String id,
                                    String startloc,
                                    String endloc ) throws Exception {
                String items="tripid, startloc, endloc";
                String values=id+","+quote(startloc)+","+quote(endloc);

                SQLDriver.runInsert("trip",items,values);
        }
        
        public static void userEditTrip() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter id: ");
                String id=kb.readLine();

                System.out.print("Enter attribute to change: ");
                String item=kb.readLine();

                System.out.print("Enter the new value: ");
                String newval=kb.readLine();

                editTrip(id,item,newval);
        }
        
        public static void editTrip( String id,
                                     String item,
                                     String newval ) throws Exception {
                String cond="tripid="+id;
                SQLDriver.runUpdate("trip",item,quote(newval),cond);
        }

        public static void userAddStop() throws Exception {
                BufferedReader kb=new BufferedReader(
                                      new InputStreamReader( System.in ));

                System.out.print("Enter stopid: ");
                String stopid=kb.readLine();
                
                System.out.print("Enter tripid: ");
                String tripid=kb.readLine();
                
                System.out.print("Enter sequencenum: ");
                String sequencenum=kb.readLine();
                
                System.out.print("Enter arrival time: ");
                String arrivaltime=kb.readLine();
                
                System.out.print("Enter departure time: ");
                String departtime=kb.readLine();

                addStop(stopid,tripid,sequencenum,arrivaltime,departtime);
        }
        
        public static void addStop( String stopid,
                                    String tripid,
                                    String sequencenum,
                                    String arrivaltime,
                                    String departtime ) throws Exception {
                
                String items="stopid,tripid,sequencenum,arrivaltime,departtime";
                String values=stopid+","+tripid+","+sequencenum+","
                              +quote(arrivaltime)+","+quote(departtime);

                SQLDriver.runInsert("stops",items,values);
        }

        public static void userEditStop() throws Exception {
                BufferedReader kb=new BufferedReader(
                                      new InputStreamReader( System.in ));

                System.out.print("Enter stopid: ");
                String stopid=kb.readLine();
                
                System.out.print("Enter attribute to change: ");
                String item=kb.readLine();
                
                System.out.print("Enter new value: ");
                String newval=kb.readLine();
                
                editStop(stopid,item,newval);
        }

        public static void editStop( String stopid,
                                     String item,
                                     String newval ) throws Exception {
                
                String cond="stopid="+stopid;
                SQLDriver.runUpdate("stops",item,quote(newval),cond);
        }
        
        public static void userAddTripOffering() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter date (day only): ");
                String date=kb.readLine();
                
                System.out.print("Enter start time: ");
                String starttime=kb.readLine();
                
                System.out.print("Enter tripid: ");
                String tripid=kb.readLine();
                
                System.out.print("Enter time of arrival at destination: ");
                String desttime=kb.readLine();
                
                System.out.print("Enter driver id: ");
                String driverid=kb.readLine();
                
                System.out.print("Enter bus id: ");
                String busid=kb.readLine();
                
                System.out.print("Enter month: ");
                String month=kb.readLine();
                
                System.out.print("Enter year: ");
                String year=kb.readLine();

                addTripOffering(date,starttime,tripid,desttime,driverid,
                                busid,month,year);
        }
        
        public static void addTripOffering( String date,
                                            String starttime,
                                            String tripid,
                                            String desttime,
                                            String driverid,
                                            String busid,
                                            String month,
                                            String year ) throws Exception {
                String items="date,starttime,tripid,desttime,driverid,busid,month,year";
                String values= quote(date)+","+quote(starttime)+","+tripid+","
                              +quote(desttime)+","+driverid+","+busid+","+quote(month)+","
                              +quote(year);

                SQLDriver.runInsert("tripoffering",items,values);
        }
        
        public static void userEditTripOffering() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter tripid: ");
                String tripid=kb.readLine();

                System.out.print("Enter attribute to change: ");
                String item=kb.readLine();

                System.out.print("Enter new value: ");
                String newval=kb.readLine();

                editTripOffering(tripid,item,newval);
        }
        
        public static void editTripOffering( String tripid,
                                             String item,
                                             String newval ) throws Exception {

                String cond="tripid="+tripid;
                SQLDriver.runUpdate("tripoffering",item,quote(newval),cond);
        }
        
        public static void userDisplayTrip() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));

                System.out.print("Enter tripid: ");
                String tripid=kb.readLine();
                displayTrip(tripid);
        }
        
        public static void displayTrip( String tripid ) throws Exception {

                String query =  "SELECT stopid,sequencenum,arrivaltime,departtime "+
                                "FROM trip t,stops s "+
                                "WHERE t.tripid=s.tripid "+
                                "AND s.tripid="+tripid;
                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();
                
                //print header
                System.out.printf("%16s | %16s | %16s | %16s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3),
                                  rsMeta.getColumnName(4));
                System.out.println(seperator(80));
                
                //print data
                while( rs.next() ) {
                        System.out.printf("%16s | %16s | %16s | %16s\n",
                                          rs.getString(1),
                                          rs.getString(2),
                                          rs.getString(3),
                                          rs.getString(4));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }

        public static void displayAllStop() throws Exception {
                String query = "SELECT * FROM stops";

                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();

                System.out.printf("%12s | %12s | %12s | %12s | %12s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3),
                                  rsMeta.getColumnName(4),
                                  rsMeta.getColumnName(5));
                System.out.println(seperator(80));

                while( rs.next() ) {
                        System.out.printf("%12s | %12s | %12s | %12s | %12s\n",
                                          rs.getString(1),
                                          rs.getString(2),
                                          rs.getString(3),
                                          rs.getString(4),
                                          rs.getString(5));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }

        public static void displayAllDriver() throws Exception {
                String query = "SELECT * FROM driver";

                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();

                System.out.printf("%18s|%18s|%18s|%18s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3),
                                  rsMeta.getColumnName(4));
                System.out.println(seperator(80));

                while( rs.next() ) {
                        System.out.printf("%18s|%18s|%18s|%18s\n",
                                        rs.getString(1),
                                        rs.getString(2),
                                        rs.getString(3),
                                        rs.getString(4));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }
        
        public static void displayAllBus() throws Exception {
                String query = "SELECT * FROM bus";

                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();

                System.out.printf("%18s|%18s|%18s|%18s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3),
                                  rsMeta.getColumnName(4));
                System.out.println(seperator(80));

                while( rs.next() ) {
                        System.out.printf("%18s|%18s|%18s|%18s\n",
                                        rs.getString(1),
                                        rs.getString(2),
                                        rs.getString(3),
                                        rs.getString(4));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }

        public static void displayAllTrip() throws Exception {
                String query = "SELECT * FROM trip";

                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();

                System.out.printf("%20s|%20s|%20s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3));
                System.out.println(seperator(80));

                while( rs.next() ) {
                        System.out.printf("%20s|%20s|%20s\n",
                                        rs.getString(1),
                                        rs.getString(2),
                                        rs.getString(3));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }

        public static void userDisplayMonthlySched() throws Exception {
                BufferedReader kb = new BufferedReader(
                                        new InputStreamReader( System.in ));
                
                System.out.print("Enter the month: ");
                String month=kb.readLine();
                
                System.out.print("Enter the year: ");
                String year=kb.readLine();

                displayMonthlySched(month,year);
        }

        public static void displayMonthlySched(String month, 
                                               String year) throws Exception {
                String query =  "SELECT month,date,year,tripid,driverid,"
                               +"busid,starttime,desttime FROM tripoffering "
                               +"WHERE month="+quote(month)+" "
                               +"AND year="+quote(year);
                ResultSet rs = SQLDriver.runSelect(query);
                ResultSetMetaData rsMeta = rs.getMetaData();

                System.out.printf("%9s|%9s|%9s|%9s|%9s|%9s|%9s|%9s\n",
                                  rsMeta.getColumnName(1),
                                  rsMeta.getColumnName(2),
                                  rsMeta.getColumnName(3),
                                  rsMeta.getColumnName(4),
                                  rsMeta.getColumnName(5),
                                  rsMeta.getColumnName(6),
                                  rsMeta.getColumnName(7),
                                  rsMeta.getColumnName(8));
                System.out.println(seperator(80));

                while( rs.next() ) {
                        System.out.printf("%9s|%9s|%9s|%9s|%9s|%9s|%9s|%9s\n",
                                          rs.getString(1),
                                          rs.getString(2),
                                          rs.getString(3),
                                          rs.getString(4),
                                          rs.getString(5),
                                          rs.getString(6),
                                          rs.getString(7),
                                          rs.getString(8));
                }
                System.out.printf("%s\n\n", seperator(80));
                rs.close();
        }
        
        public static String quote( String s ) { return "'"+s+"'"; }
        public static String seperator( int len ) {
                String s="";
                for( int i=0; i<len; i++ ) { s = s+"-"; }
                return s;
        }
}

/* SQLDriver is a nice little class to automate some sql statements for
 * it's user. The methods have no error checking, so be careful.
 */
class SQLDriver {

        /* a generic SELECT statment */
        public static ResultSet runSelect( String query ) throws Exception {
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(query);
                return rs;
        }
        
        /* run a SELECT statement. SELECT items FROM table */
        public static ResultSet runSelect( String items,
                                           String table ) throws Exception {
                //start the driver
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                
                //connect to my database
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                
                //create a new statement
                Statement stmt = conn.createStatement();
                
                //run the statement
                String sqlStatement = "SELECT " + items + " FROM " + table;
                ResultSet rs = stmt.executeQuery(sqlStatement);
                
                return rs;
        }

        /* run a SELECT statement. SELECT items FROM table WHERE value=cond */
        public static ResultSet runSelect( String items,
                                           String table,
                                           String value,
                                           String cond ) throws Exception {
                //start the driver
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                
                //connect to my database
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                
                //create a new statement
                Statement stmt = conn.createStatement();
                
                //run the statement
                String sqlStatement = "SELECT " + items + " FROM " + table
                                      + " WHERE " + value + "=" + cond;
                ResultSet rs = stmt.executeQuery(sqlStatement);
                //ResultSetMetaData rsMeta = rs.getMetaData();
                
                return rs;
        }
        
        /* run an INSERT statement. INSERT INTO table (items) VALUES (values) */
        public static int runInsert( String table, 
                                     String items, 
                                     String values ) throws Exception {
                //start the driver
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                
                //connect to my database
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                
                //create a new statement
                Statement stmt = conn.createStatement();
                String sqlStatement = "INSERT INTO " + table + " (" + items +
                                      ") VALUES (" + values + ")";
                
                //run the statement
                int result = stmt.executeUpdate(sqlStatement);
                
                return result;
        }
        
        /* run an UPDATE statement. UPDATE table SET item=value WHERE cond */
        public static int runUpdate( String table,
                                     String item,
                                     String value,
                                     String cond ) throws Exception {
                //start the driver
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                
                //connect to my database
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                
                //create a new statement
                Statement stmt = conn.createStatement();
                String sqlStatement = "UPDATE " + table + " SET " + item +
                                      "=" + value + " WHERE " + cond;
                
                //run the statement
                int result = stmt.executeUpdate(sqlStatement);
                
                return result;
        }

        /* run a DELETE statement. DELETE FROM table WHERE item=value */
        public static int runDelete( String table,
                                     String item,
                                     String value ) throws Exception {

                //start the driver
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                
                //connect to my database
                String connString = "jdbc:mysql://localhost/cs435?user=cs435";
                Connection conn = DriverManager.getConnection(connString);
                
                //create a new statement
                Statement stmt = conn.createStatement();
                String sqlStatement = "DELETE FROM " + table + " WHERE "
                                      + item + "=" + value;
                
                //run the statement
                int result = stmt.executeUpdate(sqlStatement);
                
                return result;
        }
}