In this assignment, you will use MySQL and JDBC | Use JDBC to connect your Java program to your database

In this assignment, you will use MySQL and JDBC:

·        Use mysql command line utility:

o   Connect to your database

o   Create the Flight, Customer, and Reservation tables in your database using SQL scripts.

o   Create an additional index on the Reservation table.

o   Populate each of those tables using SQL scripts

·        Use JDBC to connect your Java program to your database and perform a variety of select, insert, update, and delete operations

 

MySql User Id, Password, and Database

Your user id:  abc123

Your password:   lastThreeDigitsBannerIdpw

Your database name:   abc123db

mysql server address: 10.100.1.81

mysql serve name: db01

 

Source Directory

·        You should notice that it created a "courseNum" directory which should contain:

MySqlUtility.java         java code which can print the result set and meta data.  You should not have to change this code.

P3Main.java        contains a static main method for invoking your P3Program passing your ID and password.  You need to change those.

P3Program.java  contains your java source code which uses jdbc.  Initially, it will have some constants.  This is where you will do the bulk of your work.

Note that this directory contains the java package named courseNum which is why the directory is named that.

·        You should notice there is a "Script" directory which should contain:

createFlight.sql    SQL script to drop Flight if it exists and create the Fight table.

createCustomer.sql       SQL script to drop Customer if it exists and create the Customer table.

createReservation.sql    SQL script to drop Reservation if it exists and create the Reservation table.

insertFlight.sql     SQL script to insert rows into the Flight table.

insertCustomer.sql        SQL script to insert rows into the Customer table.

insertReservation.sql    SQL script to insert rows into the Reservation table.

 

 

Using MySQL to connect to abc123db

 

1.     Login to a linux computer

 

2.     From the directory above the Script directory, launch the mysql command line utility (use your abc123 ID and password

 

$ mysql –h db01 –u abc123 –p

 

·        It will prompt you to enter a password.

·        It should then give you the mysql> prompt.

 

3.     Tell mysql that you want to use your database:

mysql> use abc123db;

·        It should state "Database changed".

 

4.     Your database doesn't have any tables.

 

5.     Use the mysql source command to run each of the sql scripts to create your tables and then populate them.  That is six scripts. Replace filename.sql with each of the 6 sql scripts.

mysql> source Script/filename.sql;

Note that the create scripts first do a drop table if exists which will show a warning if it doesn't already exist.  That is expected.

 

6.     Confirm that those tables are populated:

mysql> select * from Flight;

mysql> select * from Customer;

mysql> select * from Reservation;

 

7.     Within the mysql utility, you must add an index to the Reservation Table.  The Reservation Table has a primary key index on `custNr` then `flightId`.  Execute an SQL statement to create another unique index by `flightId` then `custNr` on that table.  You can name that index `flightIdx`.

 

8.     To exit the mysql command line utility:

mysql> exit;

 

Java Programs

On a linux computer, you will create a Java program.  Before proceeding, make certain you set the CLASSPATH in your .cshrc file as stated in the course notes.

 

P3Main.java

Change it to use your id and password.

 

P3Program.java

1.     Change the constructor method to use your database.

2.     Create a printCustomers(title, resultSet) instance method which prints the title, a column heading, and the data for each tuple.  Make certain you check for null values where appropriate and print "---".  This must be coded in a manner similar to the printSections method in the MySQLandJDBC notes; however, you should indent the column headings and column data four spaces to improve readability.  The output must match the output shown below.

3.     Create a runProgram method which does the following:

a.     Use statement to execute this select statement:

select c.* from Customer c

b.     Use printCustomers("Beginning Customers", resultSet) to print those customers.

c.      Use statement to execute this select statement:

select f.* from Flight f

d.     Use MySqlUtility.printUtility("Beginning Flights", resultSet) to print those flights.

e.      Use statement.executeUpdate to insert a row into Customer which uses:

·  1999 for the custNr

·  your name for the name

·  your favorite airline for the airline

·  your birthdate (or use NULL if you would rather not provide it) for birthdate

·  your gender

Note: place the executeUpdate in a special try… catch to print an error if there was a duplicate as was done in our MySQL and JDBC notes Example #14.  Your code must not terminate because of a duplicate key error.

f.       Use statement to get the customers (as was done in step a.) and printCustomers("Customers after I was added", resultSet).

g.     Print the index information from the MYSQL catalog for the Reservation table.

·  Use statement to get the index column information for this query:

select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
     , SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY
  from INFORMATION_SCHEMA.STATISTICS
 where TABLE_SCHEMA = "abc123db"
   and TABLE_NAME = "Reservation"
order by INDEX_NAME, SEQ_IN_INDEX;

·  Since that SQL statement is fairly long, you should break it into multiple literal pieces and concatenate them together, making certain you have appropriate spacing between the pieces.

·  Use the MySqlUtility.printUtility("My Reservation Indexes", resultSet).

h.     Create a prepareStatement which does an insert into Reservation using subsitution parameters for each attribute.  This should be done outside the for statement of the next step.

i.       Use a Java for statement to iterate through the strFlightIdM array until a value of "END" is encountered.  Set the substitution parameters as follows:

·        Parameter 1 to 1999.

·        Parameter 2 to strFlightIdM[i].  Assuming i is your counter variable in the for.

·        Parameter 3 to reqSeatQty value which is 1 for the first reservation.  Increase that by 1 for each of the reservations (i.e, use 1, 2, 3).

Execute the insertion using preparedStatement.executeUpdate(). 
Note: place the executeUpdate in a special try… catch to print an error if there was a duplicate as was done in our notes Example #14.

j.       Create a prepareStatement to select reservations (all columns) for a custNr that is provided as a subsitution parameter.  Provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it.  Print the result using MySqlUtility.printUtility("My reservations", resultSet).

k.     Use statement to execute a select statement which returns the flightId, custNr, customer name, and reqSeatQty for customers who are on flights that customer 1999 is on.  Do not include 1999 in the returned rows.  Print them using
MySqlUtility.printUtility("Other customers on my flights", resultSet).

l.       Use statement.executeUpdate to update the reservations for custNr 1999 by doubling them.  (i.e., set reqSeatQty = reqSeatQty * 2).

m.  Use statement to execute this select statement:

select r.* from Reservation r

n.     Use MySqlUtility.printUtility("Modified Reservations", resultSet) to print those flights.

o.     Use statement to execute a select statement which returns flightId and count(*) for flights having more than two reservations.  (See example #16 in the SQL DML notes.)  Print the results using
MySqlUtility.printUtility("Flights Having more than 2 reservations", resultSet).

p.    Delete the reservations for customer 1999 from the Reservation Table using statement.

q.     Reusing the preparedStatement from step j, again provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it.  Print the result using MySqlUtility.printUtility
("My Reservations after Deleting", resultSet).

r.      Repeat step k, but use this for the title "Other customers on my flights after mine were deleted".  Of course, no results are printed.

 

To compile your code from the directory above courseNum (package directory):

$ javac courseNum/MySqlUtility.java        # should only do this once

$ javac courseNum/P3Main.java                # should only do this once

$ javac courseNum/P3Program.java

 

To execute your main:

$ java courseNum/P3Main

 

Before submitting:

·        Make certain your code works on a linux computer.

·        Place output it in a file named courseNum/p3Out.txt

 

Sample Partial Output:

Beginning Customers

CustNr Name                           Preferred Airline    Birth Dt   Gender

111    Perry Noid                     Spirit               2000-04-01 M

222    Melba Toast                    American             1990-09-11 F

333    Pete Moss                      Clampett             1992-03-03 M

444    Bill Board                     American             1985-04-04 M

480    Anita Vacay                    Spirit               1975-06-01 F

555    Jerry Tall                     PoDunk               1927-04-15 M

666    Tom E Gunn                     Clampett             1976-06-06 M

777    Bob Wire                       PoDunk               1973-07-07 M

888    Ron DeVoux                     American             1990-08-08 M

890    Ann T Freeze                   ---                  ---        F

901    Peg Board                      Delta                1987-04-04 F

902    Al B Tross                     American             1957-07-12 M

903    B B Gunn                       PoDunk               1976-09-09 F

904    Sally Mander                   Delta                1995-09-04 F

999    Marcus Absent                  Delta                1999-09-09 M

1999   your name                      Southwest            1957-12-04 M

Beginning Flights

flightId.... flightNm airline............. orig dest schedDt... depTm..... maxSeats numSeatsSold

331          PD001    PoDunk               SAT  MCO  2020-03-03 10:00:00   30       30

341          PD001    PoDunk               SAT  MCO  2020-03-04 10:00:00   54       30

411          AH100    American             HOU  CDG  2020-04-01 08:00:00   130      25

412          AH200    American             HOU  FLL  2020-04-01 06:00:00   130      125

441          SH100    Spirit               IHC  FLL  2020-04-04 07:00:00   130      5

442          SH110    Spirit               IHC  FLL  2020-04-04 15:00:00   130      0

460          SF200    Spirit               FLL  IHC  2020-04-11 12:00:00   130      0

462          SF200    Spirit               FLL  IHC  2020-04-12 12:00:00   130      50

510          AS300    American             SAT  MCO  2020-05-10 10:30:00   130      5

519          F333     PoDunk               SAT  MCO  2020-05-19 16:00:00   30       50

532          PD002    PoDunk               HOU  SEG  2020-05-03 11:00:00   30       5

604          AH200    American             HOU  FLL  2020-06-04 06:00:00   130      30

705          CL123    Clampett             SAT  MCO  2020-07-05 04:00:00   30       0

706          PD002    PoDunk               HOU  SEG  2020-07-06 11:00:00   42       0

707          FXXXX    PoDunk               SAT  HOU  2020-05-03 11:00:00   30       5

Answer Preview
public class DbConnector {
    private static Connection conn;
    public final static String pass = "lastThreeDigitsBannerIdpw";
    public final static String dbName = "abc123db";
    public static String user = "abc123";
    /**
     *
     * @return
     */
    public static Connection dbconnect() {
        try {
            forName("com.mysql.jdbc.Driver").newInstance();
            conn = getConnection("jdbc:mysql://10.100.1.81:3306/" + dbName, user, pass);
            return conn;
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
            ErrorLogger.write(e, new Exception().getStackTrace()[0], "Error connecting to database!");
            Platform.exit();
            System.exit(0);
            return null;
        }
    }
    private DbConnector() {
    }
}
This is only a preview of the solution. Please use the purchase button to see the entire solution