SER322 Database Management Systems Lab: JDBC programming and XML

This lab asks you to write simple JDBC programs, and execute an XPath query.

What you will need:

- A MySQL server installation
- A MySQL JDBC driver
- Load the movies_simple.sql script in MySQL

30 points. Activity 1: Construct a JDBC program according to the following specifications:

1. (7.5p) Write a method named query1 that uses JDBC to execute a SELECT query to List all film_id, film
title, length, film release year together with their Language (the name of the language, not just the id)

Example of output:

film_id title length release_year language

1 ACADEMY DINOSAUR 86 2006 English
2 ACE GOLDFINGER 48 2006 Spanish
3 ADAPTATION HOLES 50 2006 English
4 AFFAIR PREJUDICE 117 2006 English
5 AFRICAN EGG 130 2006 Spanish
6 AGENT TRUMAN 169 2006 English
7 AIRPLANE SIERRA 62 2006 Japanese
8 AIRPORT POLLOCK 54 2006 English
9 ALABAMA DEVIL 114 2006 Japanese
10 ALADDIN CALENDAR 63 2006 Spanish

a. Use a regular JDBC Statement object for this.

b. Pretty-print the results to the console (One line per row in readable aligned columns)
c. This would be invoked on the command line as
java ser322.JdbcLab <url> <user> <pwd> <driver> query1

2. (7.5p) Write a method named query2 that uses JDBC to execute a SELECT query to List, for a provided
Language, the movies for that language (ID, Title, Release Date) together with the Number of actors that
act on each of them. Note if a movie has 0 actors, it should appear with a 0 (For example, film with id 5, in
Spanish, has not actors)

Example of output for the parameter ‘Spanish’:

film_id title release_year Number of actors

2 ACE GOLDFINGER 2006 2
5 AFRICAN EGG 2006 0
10 ALADDIN CALENDAR 2006 3

a.
Use a PreparedStatement with a parameter for the LanguageName and filter the query results to
only return rows tied to that Language.
b. Pretty-print the results to the console (One line per row in readable aligned columns)
c. This would be invoked on the command line as
java ser322.JdbcLab <url> <user> <pwd> <driver> query2 <language_name>

3. (7.5p) Write a method named updateFilm that uses JDBC to update the length of a Film in the database
a. Use a PreparedStatement with parameter for the value to UPDATE
b. Be sure to commit your results appropriately!
c. If successful, indicate by printing out SUCCESS
d. This would be invoked on the command line as
java ser322.JdbcLab <url> <user> <pwd> <driver> updateFilm
<film_id> <new_length>
































































 Note: you can run query1 to check on updateFilm, as the results should change.

4. (7.5p) Write a method named addActor that uses JDBC to add an existing actor to a movie providing the
actor_id and the film_id
a. Use a PreparedStatement with parameters for the 2 values to INSERT
b. Be sure to commit your results appropriately!
c. If successful, indicate by printing out SUCCESS
d. It should display a nice error message if an actor is already in a movie.
e. This would be invoked on the command line as
java ser322.JdbcLab <url> <user> <pwd> <driver> addActor
<actor_id> <film_id>

Note: you can run query2 to check on addActor, as the results should change if there is a new actor in the
movie!

20 points Activity 2: XML-ize the database and execute XPath queries

Using the same database instance, do the following:
1. (10p) Modify your JDBC program from Activity 1 to export the entire database to an XML file (note, if you
plan to do the extra credit activity, read it first to have it in mind when doing this)
a. This would be invoked on the command line as
java ser322.JdbcLab <url> <user> <pwd> <driver> export <filename>
2. (10p) Construct a separate Java program to display the results of an XPath expression on an exported
XML file that returns all the films with a given Language (on this case, to use the numeric language_id is
ok).
a. This would be invoked on the command line as
java ser322.JdbcLab2 <language_id>

Extra Credit: (15p)

For extra credit,
1. (5p) Create an XML schema for your database export, name it JdbcLab2.xsd
2. (10p) Write a standalone program to import an XML file that conforms to the schema to the database.
a. The database will be prepared in MySQL running only the part of the script that create the tables,
but not the inserts, so the database will have all the tables ready but without any row.
b. The script will read the xml file and will insert the data in the prepared database
c. This would be invoked on the command line as
java ser322.JdbcLabEC <url> <user> <pwd> <driver> <filename>

Make sure of the following for this lab:

1. You never leak database resources!
2. Fully handle all error situations. By “fully” we mean give directed feedback as to what is incorrect about
the user’s query, or what the error executing the query is.
3. Your programs should use the package ser322. Note Activity 2, #2 changes the program name slightly.
4. Your code should be readable and documented, and exhibit coding practices appropriate for an upper-
division programming course in Software Engineering.
5. The command-line examples above do not show the use of the -classpath (-cp) option to java. We will add
this as per our grading environment.

Submission Instructions:
You should submit a zipfile with the following contents:
o A readme.txt that explains how to build the source code. Given these are small standalone
programs, a simple javac command is fine.

o All source code in a subdirectory src followed by package (e.g. src/ser322).

o You do not have to include the JDBC driver or your SQL database dump.
o Do not include hardcoded paths in any scripts or code.