In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already...

Transcribed Image Text:

In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already done it): o Connect to your database o Create the property, Customer, and Rental tables in your database using SQL scripts. Create an additional index on the Rental 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 o Download your empty database with right click and save as using the following link. Source Directory Download the compressed file under the name "Pgm3.zip". You should notice that it has already coded the following classes. Two of them are partially coded: MySqlUtility.java P3Main.java java code which can print the result set and meta data. You should not have to change this code. contains a static main method for invoking your P3Program connecting to the database that you should have filled as explained below. You should not have to change this code. 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. P3Program.java You should notice that it created a "Script" directory which should contain: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql SQL script to drop Property if it exists and create the Property table. SQL script to drop Customer if it exists and create the Customer table. SQL script to drop Rental if it exists and create the Rental table. SQL script to insert rows into the Property table. SQL script to insert rows into the Customer table. SQL script to insert rows into the Rental table. Using SQLiteStudio and adding your database 1. Launch the SQLiteStudio tool and connect to the database (as explained in class) 2. Open the "propertyDB.db" database that is located in the root folder of your Java project. 3. Your database doesn't have any tables. If not empty, you could drop the existing tables. 4. Open and execute the sql files that you can download from Blackboard to create and fill in the database tables in the following order: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql You may open your files in any other editor (Notepad/Notes/Gedit) and paste the contents of those files. 5. Confirm that those tables are populated running the following queries in a new Query tab: select from Property; select from Customer; select from Rental; 6. Make sure you exit from the SQLiteStudio app to avoid that the database file has only access. Java Programs Download the zip file attached in the folder of the problem statement. Open the project in Intellij (or you can make it work in another environment by importing the different classes). Please, remember that this is a Maven project, which, as explained in class, will be in charge of obtaining all dependencies. P3Program.java 1. 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. 2. Create a runProgram method which does the following: a. Test printCustomers() method Use statement to execute this select statement: b. Use printUtility() method select c.* from Customer c Use printCustomers("Beginning Customers", resultSet) to print those customers. c. Use statement.executeUpdate to insert a row into Customer which uses: • 1999 for the custNr • your name for the name • your state of birth (or TX if you were born outside of the USA) for the baseLoc your statt Use statement to execute this select statement: select m.* from Property mi Use MySqlUtility.print Utility("Beginning Properties", resultSet) to print those properties. e. • your birthdate (or use NULL if you would rather not provide it) for birthdate your birthd • 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 notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + not pos customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. d. Display updated values . Use statement to get the customers (as was done in step a.) and printCustomers("Customers after I was added", resultSet). Create a prepareStatement which does an insert into Rental using substitution parameters for each attribute. This should be done outside the for statement of the next step. f. Use a Java for statement to iterate through the strPropertyIdMarray until a value of "END" is encountered. Set the substitution parameters as follows: Parameter 1 to 1999. Parameter 2 to strPropertyldM[i]. Assuming i is your counter variable in the for. Parameter 3 to "2019-12-14". (You will need to use String and setString, instead of Date). Parameter 4 to a totalCost using setDouble. The first rental should use 200.00. Increase the amount by 10.00 for each of the rentals (i.e., use 200, 210, 220). g. 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. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. Create a prepareStatement to select rentals (all columns) for a custNr that is provided as a substitution parameter. Provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it. Print the result using MySqlUtility.printUtility("My rentals", resultSet). h. Use statement to execute a select statement which returns the propid, custNr, customer name, and totalCost for customers who rented properties that customer 1999 coacomer ameyang meny cono also rented. Do not include 1999 in the returned rows. Print them using aporanc CON MySqlUtility.printUtility("Other customers renting my properties", resultSet). i. Use statement.executeUpdate to update your rentals decreasing the totalCost by 10% (.e., set totalcost-g (i.e., set totalCost = 0.90* totalCost). totalcos j. Repeat step g, but use "My Cheaper Rentals" as the title. k. Use statement to execute a select statement which returns propld and count(*) for properties having more than two rentals. (See example # 15 in the SQL DML notes.) Prope mams mor Print the results using MySqlUtility.printUtility ("Properties Having more than 2 rentals", resultSet). 1. Delete the rentals for customer 1999 from the Rental Table using statement. m. Repeat step h, but use this for the title "Other customers renting my properties after my rentals were deleted". Of course, no results are printed. To compile your code (either through the teminal or Maven interface): mvn compile To execute your main: mvn package exec: java What to submit via BlackBoard? Submit a zip file named LastnameFirstName.zip containing: • Your P3Program.java • Your propertyDB.db Your output generated by your program. Place it in a file named p3Out.txt Do not include any directories. • Sample Partial Output: Beginning Customers CustNr Name 111 Ava Kashun 222 Cam Aruh 333 444 480 555 601 Pete Moss Bill Board Anita Vacay Perry Scope E Race State Birth Dt Gender 2000-04-01 M 1990-09-11 M ARANY TX CA FL CA TX OK OK 1992-03-03 M 1985-04-04 M 1975-06-01 F 1927-04-15 M 1986-10-01 M Rubric: 666 755 30 points Tom E Gunn Ray Mee Bob Wire T Doh 777 855 888 890 901 Peg Board 902 Al B Tross 903 B B Gunn 984 999 1999 Penny Lane Anita Break Sally Mander Marcus Absent Larry Beginning Properties Forest View propId... locDesc.... BEANSBD Friole Heaven FIREHHH HOMEJJJ MTNDDD NYCCC Times Square OILAAA Oil Slick QUAKELLL Rock N Roll RVBBB Home Alone Mountain View SAEEE SNOWFFF SNOWNNN STARIII TENTGGG Kamp Komfort VACAYKKK Griswold Paradise WESTMMM Desert View 15 Rows Water Logged Camper River Shores Snowcap Ridge Snowy View Star View M 1965-04-01 M 1973-07-07 M CA 1975-04-15 F CA 1990-08-08 F CA NY CA 1992-08-09 F 1987-04-04 F 138 333 23:EER OK 1957-07-12 M F F 1999-09-09 M TX 1957-12-04 M NY --- NY state propType rating TX CA CO CO NY TX CA FL TX CO co CO CO CO TX T T T T T T M M T T T с 1x^^*=*==³*16= с T с NULL + 5 points for performing all 6 operations in SQLite studio correctly (Database) + 5 points for showing the results of printCustomers method as explained and shown above - 2 points if nulls are not shown correctly - 1 points if the output does not match the examples shown above with the corresponding spaces. +2 points for functionality 2a including the method call + 1 points for functionality 2b correctly and as specified + 2 points for functionality 2c correctly + 1 point for functionality 2d correctly + 5 points for showing the correct inserted values following steps 2e to 2g. + 5 points for showing the correct updated values following steps 2i to 2j. + 2 points for showing the correct output of query of 2k. + 2 points for deleting and showing the correct output of 21 to 2m. In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already done it): o Connect to your database o Create the property, Customer, and Rental tables in your database using SQL scripts. Create an additional index on the Rental 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 o Download your empty database with right click and save as using the following link. Source Directory Download the compressed file under the name "Pgm3.zip". You should notice that it has already coded the following classes. Two of them are partially coded: MySqlUtility.java P3Main.java java code which can print the result set and meta data. You should not have to change this code. contains a static main method for invoking your P3Program connecting to the database that you should have filled as explained below. You should not have to change this code. 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. P3Program.java You should notice that it created a "Script" directory which should contain: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql SQL script to drop Property if it exists and create the Property table. SQL script to drop Customer if it exists and create the Customer table. SQL script to drop Rental if it exists and create the Rental table. SQL script to insert rows into the Property table. SQL script to insert rows into the Customer table. SQL script to insert rows into the Rental table. Using SQLiteStudio and adding your database 1. Launch the SQLiteStudio tool and connect to the database (as explained in class) 2. Open the "propertyDB.db" database that is located in the root folder of your Java project. 3. Your database doesn't have any tables. If not empty, you could drop the existing tables. 4. Open and execute the sql files that you can download from Blackboard to create and fill in the database tables in the following order: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql You may open your files in any other editor (Notepad/Notes/Gedit) and paste the contents of those files. 5. Confirm that those tables are populated running the following queries in a new Query tab: select from Property; select from Customer; select from Rental; 6. Make sure you exit from the SQLiteStudio app to avoid that the database file has only access. Java Programs Download the zip file attached in the folder of the problem statement. Open the project in Intellij (or you can make it work in another environment by importing the different classes). Please, remember that this is a Maven project, which, as explained in class, will be in charge of obtaining all dependencies. P3Program.java 1. 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. 2. Create a runProgram method which does the following: a. Test printCustomers() method Use statement to execute this select statement: b. Use printUtility() method select c.* from Customer c Use printCustomers("Beginning Customers", resultSet) to print those customers. c. Use statement.executeUpdate to insert a row into Customer which uses: • 1999 for the custNr • your name for the name • your state of birth (or TX if you were born outside of the USA) for the baseLoc your statt Use statement to execute this select statement: select m.* from Property mi Use MySqlUtility.print Utility("Beginning Properties", resultSet) to print those properties. e. • your birthdate (or use NULL if you would rather not provide it) for birthdate your birthd • 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 notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + not pos customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. d. Display updated values . Use statement to get the customers (as was done in step a.) and printCustomers("Customers after I was added", resultSet). Create a prepareStatement which does an insert into Rental using substitution parameters for each attribute. This should be done outside the for statement of the next step. f. Use a Java for statement to iterate through the strPropertyIdMarray until a value of "END" is encountered. Set the substitution parameters as follows: Parameter 1 to 1999. Parameter 2 to strPropertyldM[i]. Assuming i is your counter variable in the for. Parameter 3 to "2019-12-14". (You will need to use String and setString, instead of Date). Parameter 4 to a totalCost using setDouble. The first rental should use 200.00. Increase the amount by 10.00 for each of the rentals (i.e., use 200, 210, 220). g. 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. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. Create a prepareStatement to select rentals (all columns) for a custNr that is provided as a substitution parameter. Provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it. Print the result using MySqlUtility.printUtility("My rentals", resultSet). h. Use statement to execute a select statement which returns the propid, custNr, customer name, and totalCost for customers who rented properties that customer 1999 coacomer ameyang meny cono also rented. Do not include 1999 in the returned rows. Print them using aporanc CON MySqlUtility.printUtility("Other customers renting my properties", resultSet). i. Use statement.executeUpdate to update your rentals decreasing the totalCost by 10% (.e., set totalcost-g (i.e., set totalCost = 0.90* totalCost). totalcos j. Repeat step g, but use "My Cheaper Rentals" as the title. k. Use statement to execute a select statement which returns propld and count(*) for properties having more than two rentals. (See example # 15 in the SQL DML notes.) Prope mams mor Print the results using MySqlUtility.printUtility ("Properties Having more than 2 rentals", resultSet). 1. Delete the rentals for customer 1999 from the Rental Table using statement. m. Repeat step h, but use this for the title "Other customers renting my properties after my rentals were deleted". Of course, no results are printed. To compile your code (either through the teminal or Maven interface): mvn compile To execute your main: mvn package exec: java What to submit via BlackBoard? Submit a zip file named LastnameFirstName.zip containing: • Your P3Program.java • Your propertyDB.db Your output generated by your program. Place it in a file named p3Out.txt Do not include any directories. • Sample Partial Output: Beginning Customers CustNr Name 111 Ava Kashun 222 Cam Aruh 333 444 480 555 601 Pete Moss Bill Board Anita Vacay Perry Scope E Race State Birth Dt Gender 2000-04-01 M 1990-09-11 M ARANY TX CA FL CA TX OK OK 1992-03-03 M 1985-04-04 M 1975-06-01 F 1927-04-15 M 1986-10-01 M Rubric: 666 755 30 points Tom E Gunn Ray Mee Bob Wire T Doh 777 855 888 890 901 Peg Board 902 Al B Tross 903 B B Gunn 984 999 1999 Penny Lane Anita Break Sally Mander Marcus Absent Larry Beginning Properties Forest View propId... locDesc.... BEANSBD Friole Heaven FIREHHH HOMEJJJ MTNDDD NYCCC Times Square OILAAA Oil Slick QUAKELLL Rock N Roll RVBBB Home Alone Mountain View SAEEE SNOWFFF SNOWNNN STARIII TENTGGG Kamp Komfort VACAYKKK Griswold Paradise WESTMMM Desert View 15 Rows Water Logged Camper River Shores Snowcap Ridge Snowy View Star View M 1965-04-01 M 1973-07-07 M CA 1975-04-15 F CA 1990-08-08 F CA NY CA 1992-08-09 F 1987-04-04 F 138 333 23:EER OK 1957-07-12 M F F 1999-09-09 M TX 1957-12-04 M NY --- NY state propType rating TX CA CO CO NY TX CA FL TX CO co CO CO CO TX T T T T T T M M T T T с 1x^^*=*==³*16= с T с NULL + 5 points for performing all 6 operations in SQLite studio correctly (Database) + 5 points for showing the results of printCustomers method as explained and shown above - 2 points if nulls are not shown correctly - 1 points if the output does not match the examples shown above with the corresponding spaces. +2 points for functionality 2a including the method call + 1 points for functionality 2b correctly and as specified + 2 points for functionality 2c correctly + 1 point for functionality 2d correctly + 5 points for showing the correct inserted values following steps 2e to 2g. + 5 points for showing the correct updated values following steps 2i to 2j. + 2 points for showing the correct output of query of 2k. + 2 points for deleting and showing the correct output of 21 to 2m.