Version 1.0 - initial release

Version 1.1 - fixed name of java file and queries

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.

Preparation Steps

1. The latest path for MySQL Connector/J is

   /usr/share/java/mysql-connector-java.jar 
   

   Include 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 
   

2. Login into one of the state capital machines (denver, austin, springfield, etc).
   
   
3. Download the example code Lab4_ex.java. 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");
   

4. 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.
   
   

Description of the Task for Lab 4

In 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.

Activities

1. Create and populate the tables from the initial data from lab3.

2. 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.

3. 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.

4. 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.

5. Once you have finished loading the data for the day's activities, run the following queries.
   1. Print the contents of the Borrowed_by table.
   2. 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.

When you are satisfied that your Java program works correctly, run it once again to produce an output file.

You need to submit both your Lab4.java file, your output file, and a README file (preferable, with instructions on how to run your program).

Submitting Your Assignment

Your database should be restored to initial state (end of L3) so your programs can be graded.

Submit

• 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.

References

http://www.kitebird.com/articles/jdbc.html#TOC_4.

http://www.developer.com/java/data/article.php/3417381.

Late Policy

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.