This part of the project is to be implemented individually.

4. Part II: Object storage using JDBC and MySQL

In this part of the project you will implement methods for objects that store and retrieve the object data using JDBC and the MySQL database.  In the previous part of the project, you used the Serializable interface to store Java objects to disk.  One of the significant limitations of this is that objects cannot be retrieved if changes are made to the class definitions.  Another problem is that stored data are opaque: they cannot be used or shared across other applications that do not use the same object class definitions.  Finally, storing objects as disk files is not a DBMS, there is no access control, concurrency control, recovery, etc.

1. The SQL interface


One way to address some of the problems with lightweight persistence is to use an SQL database to store objects.  This offers the advantages of using a relational  database (transparency, control, data sharing, etc.) while preserving the object model.  To do this, you will define a set of methods that will be implemented for every object that you will store in the database:

•  public void readSQL(int objID, Connection db, HashMap hm)

 throws NotInDBException

     objID  Is the object ID of the object to read in.
     db     Is the handle to the opened SQL database.
     hm     Is the HashMap of all objects read in.  This is how we keep track of which objects have been created already.
     NotInDBException Is thrown when the object is not found in the database.

The readSQL method reads data from the database for a particular object, identified by that object's objID. Data are read from one or more relations through the JDBC interface. The data are put into the appropriate fields of the object.   If the object contains references to other objects, those objects must be created and initialized from the database through their readSQL methods.  The HashMap implements (objID,object)  pairs to keep track of which objects have already been created and initialized.  Re-initializing an already-initialized object from the DB is not a problem, but only one object must be created for each objID.
 

• public void writeSQL(Connection db)


     db     Is the handle to the opened SQL database.

The writeSQL method writes data from the object out to the database.  Data are inserted or replaced in tables through the JDBC interface.  If the object contains references to other objects, those objects are written out as well through their writeSQL methods (it should behave like lightweight persistence).
 

• public void removeSQL(Connection db, HashMap hm)
       throws NotInDBException
 
     db     Is the handle to the opened SQL database.
     hm     Is the HashMap of all objects read in.  We want to remove object ref from here, too.
     NotInDBException Is thrown when the object is not found in the database.

The removeSQL method removes the persistent object data from the MySQL database.  All tables in which this object's objID appears as part of the primary key must be updated.  This method does not enforce cleanup of references to this object from other database entries, responsibility for that lies within the application.
 

Here is the example code and jar file:
• SqlTest.java
• mysql.jar (this file must be in your CLASSPATH)
• fix_sqltest


Before compiling, you must edit SqlTest.java and change the following line to read:

• String DBUrl = "jdbc:mysql://bell.rutgers.edu/dbname?user=username&password=password";

Where dbname is your assigned MySQL database, username is your MySQL userid, and password is the MySQL password (see class newsgroup for details).

The fix_sqltest script will make this change for you if you run it with your email userid in the same directory as SqlTest.java:

• fix_sqltest emailuserid


Do all of the following:

 
1. For each of the object classes in the previous assignment (Customer and Database), implement the readSQL method to initialize the objects from values stored in a MySQL database.  Be sure that you correctly handle forward object references by keeping track of objects in a hashmap, and that you understand how to retrieve multi-valued attributes and relationships in SQL relations.
2. For each of the object classes in the previous assignment (Customer and Database), implement the writeSQL method to save values from the objects in a MySQL database.  Be sure that you understand how to store multi-valued attributes and relationships into SQL relations.
3. For each of the object classes in the previous assignment (Customer and Database), implement the removeSQL method to remove the persistent object data from the MySQL database.
4. In your MySQL database, define all of the tables required for the methods above.  Manually insert the data from the previous assignment into these tables.
5. Write a test program that creates and initializes all the objects in the database and then prints them out in a form that shows their values and relationships.  Then have the program add some new objects and delete some existing objects.
6. Using the MySQL client, print out the contents of all of your database tables showing the changes.

 

2002-28-02