Lab/Program Assignment 4
Version 1.0 - initial release
Version 1.1 - fixed name of java file and queries
Due Date: 11:59 PM, Sunday, July 16th
Late Period : 11:59 PM, Tuesday July 18th
Lab Assignment 4: Updating a Database in SQL using JDBC with XML Input
- The purpose of this assignment is for you:
- To get beyond the initial hurdles involved in using a higher level language interface with SQL;
- To be able to read XML data and insert it into your SQL databases;
- To write JDBC programs to manipulate the data stored in the MySQL database.
- The latest path for MySQL Connector/J is
/usr/share/java/mysql-connector-java.jarInclude it in your classpath. Below is an example of adding a line to your .cshrc file to do so. It can also be done by typing it in each session, adding it to your Eclipse, etc.
setenv CLASSPATH ./:/usr/share/java/mysql-connector-java.jar
- Login into one of the state capital machines
(denver, austin, springfield, etc).
- Download the example code
The database name and the password are included in the
following lines in Lab4_ex.java.
Please change the database
name and password to your own database name and password.
//Define URL of database server for // database named "user" on the faure. String url = "jdbc:mysql://faure/user"; //Get a connection to the database for a // user named "user" with the password // "123456789". con = DriverManager.getConnection( url,"user", "123456789");
- Compile and run the example code.
If you have a table called Author and an attribute in it called
AuthorID, you should get a list of the authors in it. If you want
to connect to faure from home, click here
for instructions on how to do port forwarding from a windows machine,
or here for instructions on
a Linux box.
- Create and populate the tables from the initial data from lab3.
- In JDBC program Lab4.java,
read in and parse the activity file
in XML format. This file records checkins and checkouts.
If the transaction is a checkin, simply update the corresponding
record appropriately. If the transaction is a checkout, a new
record is created.
Prepare and execute SQL statements inside your java program that insert this data into the appropriate relation tables.
Note: You should be able to find some Java code for parsing XML data online.
You are welcome to use this, as long as it works correctly. Here is an example of one.
Everytime your program adds or modifies a record in a table, it should
print an message to the screen to that effect. If an error
is encountered, you should print a message to the screen to
the specifics of the error and continue processing.
You will need to implement code to check that the book being
checked out exists in a library, and that a book being checked
in has a corresponding checkout record.
Once you have finished loading the data for the day's activities,
run the following queries.
- Print the contents of the Borrowed_by table.
- For each member that has a book checked out (Last name, first name, member id), print a list of the book titles currently checked out.
Description of the Task for Lab 4In this part of the lab, you are going to add data to your existing data as if from another system that has created it. The data is passed in XML format.
Submitting Your Assignment
- Your database should be restored to initial state (end of L3) so your
programs can be graded.
- your Lab4.java file
- your query file
- your output file generated by a run of your compiled Lab4 program
- your output file generated by your queries.
- your README, if any
using the Canvas CS 430 DL Assignment Submission icon
shown on the Canvas CS 430 DL Assignments page.
- This is Lab Assignment 4.
- Late programs are subject to a 20% late penalty.
For more information on SQL
- Refer to http://dev.mysql.com/doc/mysql/en/ for the online MySQL Reference Manual, as well as information on downloading MySQL onto your own machine.
Please let us know if there are any obvious errors in this writeup.