Home

ISys 403: MP3 DB

MP3 Database Assignment

Information Systems 403

Background

This week we'll explore JDBC and Derby. JDBC (Java Database Connectivity) is Java's way of accessing databases. Fortunately for you and me, it was designed very well and is easy to work with. We'll explore the standard Java classes:

  • Connection
  • Statement
  • ResultSet
  • (plus a few others)

Following are links that should be helpful in completing this assignment:

  1. JDBC Basics: http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html

Design

Your first step to completing this assignment is to get Derby working on your computer. NetBeans comes ready to integrate with the embedded Derby database. It's not something you'd use in a production system, but it's great for development, embedded projects, and 403 assignments. :) You can develop with it, then go to Oracle or MS SQL at production time. Please open NetBeans and go to Tools | Update Center. Select the Apache Derby database module. Once you install, you're ready to go!

Next, add the derbyclient.jar file to your project. Once you install the Apache Derby module, you'll find it somewhere beneath the NetBeans directory. Search for it and include it in your project by going to Project Properties, Libraries, Add JAR file.

Your assignment is to modify your MP3 program to work with a database rather than with XML or saved files. You will need to fork your code for this assignment. A fork is a new line of development that breaks compatability with the older development. Forking the code is necessary because the database needs to take over the saving and loading of your table code. Rather than saving to a file of the user's choosing, the program will always save to the Derby database.

Your program should automatically load the list of lists using SELECT calls from the database upon startup. As the user works with the table, run SQL statements in the background to immediately reflect changes in the database. This should work as follows:

  • The user adds a row to the table. Your code generates an "INSERT" statement to insert a row into the table. For simplicity, only allow the user to append a row to the end of the table, not to the middle.
  • The user deletes a row from the table. Your code generates a "DELETE FROM" statement to remove the record from the database.
  • The user modifies values in one or more cells of a record. When the user moves off the record (NOT cell by cell), generate an "UPDATE" statement to update the appropriate record in the database. The user might update several fields in a record, so don't run the UPDATE statement until the user moves to another record.
  • When the application closes, the user may have modified the current record (without moving off of it). Be sure to catch the "window closing" event to check for this.

When you create statements, be sure to use transactions. You will lose points if you fail to use transactions. To use transactions, you need to set the connections setAutoCommit to false when you open the connection. This will prevent the driver from automatically committing changes. To commit changes, call Connection.commit(). To roll back (on error), call Connection.rollback().

Upon startup, your application should check to ensure the correct table(s) exist in the database. If they do not, it should create them on the fly. In other words, you should not create your tables before running the application. Your CREATE TABLE code should be run at the beginning of your program. This will facilitate easier grading, and it will ensure the correct tables always exist. The Connection.getMetaData() method is an excellent way to find out if a table exists or not.

You can hard code your JDBC connection string into your program. One nice addition to the assignment (that would fulfill your "one extra thing" task) would be to move the connection string to a java.util.Properties file.

Your table structure should match the XML structure you are currently using. For example, you'll probably have fields for "composer", "song name", "artist", and so forth. In addition, you'll need a primary key field. Don't forget the primary key field! You need to use your GUID code from earlier this semester to generate a primary key for each record in the table.

When the user moves off of a record, you need to check to see if the record was modified. In other words, don't save every time the user moves -- keep a boolean variable (a.k.a. a flag) that tells your event handler whether the user has made changes in the record and whether it needs to run an UPDATE statement or not. In other words, you'll have to attach an event that fires when the user selects a new row. This can be done by attaching a ListSelectionListener implementation to your table's column model (table.getColumnModel).

For your JTable, modify your AbstractTableModel extension class to work with databases instead of XML. That way you know when the user sets data and can run SQL statements on the fly.

Finally, use PreparedStatements and not regular Statements. PreparedStatements are much more resistant to database injections and other security breaches.

Grading

See ISys 403: Grading Policy for information on grading.