Build an object database, Part 2: Object storage backend

Implement relational database storage for Java objects

1 2 Page 2
Page 2 of 2
  protected RetrievalFields getEntryForKeyInTable (String keyString,
                                                   String classTable) throws 
SQLException {
    if (classTable == null) return null;
    // find first entry for key in class table (there should be only one...)
    Statement statement = null;
    ResultSet rs = null;
    try {
      String className = sqlDecode (classTable);
      RetrievalFields fields = new RetrievalFields (className);
      if (DEBUG) System.out.println (this + "::getEntryForKeyInTable: class 
name: " + className);
      StringBuffer sqlBuffer = new StringBuffer ();
      sqlBuffer.append ("SELECT * FROM " +
                        classTable +
                        " WHERE " +
                        KEY_COLUMN_NAME + " = '" +
                        sqlEncode (keyString) + "'");
      if (DEBUG) System.out.println (this + "::getEntryForKeyInTable: sending: 
" + sqlBuffer);
      statement = connection.createStatement ();
      rs = statement.executeQuery (sqlBuffer.toString ());
      rs.next (); // advance to the first entry
      ResultSetMetaData meta = rs.getMetaData ();
      int n = meta.getColumnCount ();      
      for (int colNum = 1; colNum <= n; ++ colNum) { 
        String colName = meta.getColumnLabel (colNum);
        int jdbcType = meta.getColumnType (colNum);
        if (DEBUG) System.out.println (this + "::getEntryForKeyInTable: found 
colName: '" + colName + "' of JDBC type: " + jdbcType);
        if (!colName.equals (KEY_COLUMN_NAME)) {
          Object object = getObjectFromResultSet (rs, colNum, colName, 
jdbcType);
          String field = sqlDecode (colName); 
          if (DEBUG) System.out.println (this + "::getEntryForKeyInTable: field 
'" + field + "' value is: " + object);
          fields.addField (field, object);
        }
      }
      return fields;
    } finally {
      if (rs != null) {
        try {
          rs.close ();
        } catch (SQLException ignored) {
        }
      }
      if (statement != null) {
        try {
          statement.close ();
        } catch (SQLException ignored) {
        }
      }
    }
  }

Note the use of metadata to figure out the columns that the table contains, as well as their respective types. Metadata proves useful for this sort of thing; otherwise we would have no way to get properties of the database itself.

getObjectFromResultSet (rs, colNum, colName, jdbcType) is one of the deferred superclass methods that a database-specific subclass implements to return an object of the correct type for the field.

Class MySQLObjectStorage

The MySQLObjectStorage concrete class specializes SQLObjectStorage for the MySQL database. A counterpart to MySQLObjectStorage must be defined for every database that is to be used with the framework:

  protected void getConnection () throws SQLException {
    String connectionString = "jdbc:" +
                               driverIDString +
                               "://" +
                               hostname +
                               (port.equals ("") ? "" : ":") +
                               port +
                               "/" +
                               databaseName +
                               "?user=" +
                               username +
                               "&password=" +
                               password;
    connection = DriverManager.getConnection (connectionString);
  }

getConnection() serves as an example of one of the methods that specialize the superclass for use with the MySQL database. Because of getConnection(), MySQLObjectStorage knows how to make a connection to the MySQL database using a MySQL-specific connection string.

Using the framework

With the code safely implemented, we can turn our attention to how to use it -- if we use the MySQL database. If you're not using MySQL, you'll have to make your own subclass of SQLObjectStorage first. (The complete MySQLObjectStorage class (for reference purposes) is provided with the code in Resources.)

You'll also need to obtain the JDBC driver for your database. Look at Resources for Sun's listing of JDBC drivers, or contact your vendor directly.

With that out of the way, let's proceed to a discussion of how a containing class will use the framework. The containing class will use your subclass and the framework in a manner similar to the following:

 ObjectStorage storage = new MySQLObjectStorage (DRIVER_CLASS_NAME,
                                                 DRIVER_ID_STRING,
                                                 PORT,
                                                 HOSTNAME,
                                                 USER_NAME,
                                                 PASSWD,
                                                 DATABASE_NAME);
    ObjectStorer storer = new SerializationStorer (storage);
    storer.put ("Helms", new RepublicanSenator ("Helms", 16000000000L));
    storer.put ("Clinton", new Senator ("Clinton", 52, true));
    storer.put ("Nobody", new LibertarianSenator ("Nobody", 0, 0.00001F)); 
    LibertarianSenator lib = (LibertarianSenator) storer.get ("Nobody");

Simple enough, but when using the code, keep the following in mind:

  • The user USER_NAME must have permission to read to, write from, and create and drop tables in the database.
  • Keys should be strings or return useful toString() representations.
  • Remember that in this version of the framework, object fields that are reference types (that is, graphs of objects) aren't stored as reference types. The framework supports simple types only.

Furthermore, when you run your code you will need to incorporate the JDBC driver class files for your database. If these are contained in a JAR file, you might issue something like the following command (under Linux):

java -cp mysql_2_comp.jar:. DBStorageTest

Enhancements

As always, there are a number of things you can do to the supplied code to add features and increase robustness:

  • Identify and escape SQL reserved words in class and variable names.
  • Implement a Connection pool, or alternatively a database bridge that exposes only needed methods such as getMetadata() and createStatement(), but encapsulates failure recovery and multiple Connections.
  • Implement some other kind of Connection failure-recovery mechanism.

Conclusion

We have reached the end of our little trip ... and what a long, strange trip it's been. We now have a fairly functional object-storage framework that persists objects to a standard relational database using standard database types and column structures.

In the next edition of Java Step By Step, Merlin will return to iterate on the framework. Meanwhile, please send in comments about the framework -- loves, hates, errors, omissions, and/or improvements.

Until next time, bon appétit!

Here's my bumper sticker idea for this election year: Don't blame me -- I voted for NOTA! http://www.nota.org

Learn more about this topic

1 2 Page 2
Page 2 of 2