Build an object database, Part 2: Object storage backend

Implement relational database storage for Java objects

Mapping objects into relational databases and vice versa is almost always a hassle. A general solution is difficult because of the inherent differences in the relational and object-oriented approaches to data modeling.

Tools do exist to help with this process, usually at a "generous fee" (with respects to the late Curtis Mayfield). Sometimes the tools are necessary, especially when mapping from an existing relational data model to an object model. However, when the relational model is not set in stone, it is possible to let the object model drive the relational model, allowing storage options to surface closer to home.


TEXTBOX_HEAD: Build an object database: Read the whole series!


The most obvious solution: serialize each Java object using the object streams and slap the result into a database as a binary blob. While this is certainly a valid option and the JDBC explicitly supports it, blobs cannot be readily manipulated (or even read) by anything other than other Java applications. So interoperability, along with human readability, go out the door.

The relational storage backend we'll build alleviates this data- interoperability problem. Our backend actually creates relational tables for each class and maps each instance's variables into them as columns, creating a sort of poor man's object-relational mapping, if you will. (To download this article's complete source code, go to Resources.)

Framework overview revisited

The object-storing framework introduced in the January Java Step by Step time divides the work of persisting Java objects into two tasks:

  1. Frontend: Scatter the object into its fields, preserving its type information -- the responsibility of an ObjectStorer implementation.

  2. Backend: Store the values of the fields along with type information -- the responsibility of an ObjectStorage implementation.
Figure 1. The object storage architecture

The object-storing framework enables object storers and object storage implementations to vary independently. The object storer doesn't care how the object storage implements its storage behavior, and the object storage doesn't know that the object storer exists. This is as it should be.

The ObjectStorer interfaces look like this:

 public interface ObjectStorer {
  public void put (Object key, Object object) throws IOException;
  public Object get (Object key) throws IOException,

In the January Java Step by Step, Merlin provides implementations of this interface -- most notably a SerializationStorer.

In this article, we'll complete the framework by implementing the ObjectStorage interface, as seen below:

 public interface ObjectStorage {
  public void put (Object key, StorageFields object) throws IOException;
  public RetrievalFields get (Object key) throws IOException;

The ObjectStorage interface uses the StorageFields and the RetrievalFields classes to pass information about fields that are stored and retrieved, as we'll see next.

Class StorageFields

As you'll no doubt recall, the StorageFields is a collection of the scattered fields, as well as the stored Java object's type information. The ObjectStorer implementation hands an instance of this class to the ObjectStorage implementation as a parameter to the put() method.

Class RetrievalFields

The RetrievalFields class is a collection of fields returned by the ObjectStorage implementation in response to a get() call. It is practically the same as the StorageFields class, except that there is no need to explicitly state type information since it is represented by the class of the object being returned.

We'll call our ObjectStorage implementation class SQLObjectStorage.

SQLObjectStorage's data model and constraints

Before we delve into the code, let's outline how our SQLObjectStorage's relational data model will work.

Figure 2. SQLObjectStorage's data model

The main concept behind this extremely simple data model is: SQLObjectStorage creates a relational table for each class to be stored in the database. SQLObjectStorage then stores instances of a given class in the table created to correspond to that class.

Each table includes a set of columns that correspond to the fields defined in the class. SQLObjectStorage dynamically creates tables and columns using a database-specific mapping from Java types to column types supported by the database.

A bit of overhead information -- the mapping from each key to its respective instance in the database -- is kept in the database in addition to the stored instances. To achieve this end, SQLObjectStorage creates a key column in each table to key the instances therein. It also creates a special key table in the database to store every key and the table where it resides.

In addition, SQLObjectStorage imposes the following constraints:

  • Each database's keys are unique: A key uniquely identifies an object in a specific database. If a key/value pair exists in the database and another value is entered with the same key, the new value replaces the old value.
  • There are no duplicate entries per key: Each key will store against only one value.
  • One table exists per primary class type (classname): Each class is stored as an instance of its primary class, with all of its fields as well as superclass fields flattened out into the columns of the table that bears its name.
  • Class and field names are escaped: In order to avoid illegal database characters, encode class names with escape characters to get table names. The same process applies to mapping fields to column names.
  • Values are escaped: Field values are escaped to avoid problems with SQL statement syntax.

Abstract class SQLObjectStorage

So now we come to the ObjectStorage implementation. We'll look at just the important methods here. (For the complete source code, see Resources.)

First, we look at the ObjectStorage-interface method implementations, starting with put().

The put() implementation takes a key and a StorageFields object as arguments. It then removes any existing entries in the database under the key. If there is an object to store, it creates a table and stores the object's field values to it as follows:

  public synchronized void put (Object key, StorageFields object) throws 
IOException {
    if (key == null) return;
    if (DEBUG) System.out.println (this + "::put: key is '" + key + "', object 
is " + object);  
    if (DEBUG_2) System.out.println (object);
    String keyString = key.toString ();
    try {
      removeEntries (keyString); // always remove old entries under key, if 
they exist
      if (object != null) {
        createTable (object);
        storeValuesToTable (keyString, object);
    } catch (SQLException ex) {
      if (DEBUG) {
        System.err.println (this + "::put: caught exception... ");
        System.err.println ("SQLException: " + ex.getMessage ());
        System.err.println ("SQLState:     " + ex.getSQLState ());
        System.err.println ("VendorError:  " + ex.getErrorCode ());
      throw new IOException (ex.getMessage ());

Next, the get() method returns a RetrievalFields. The first step is to get the class table for the key. Then the get() method obtains a RetrievalFields for the key in the correct class table:

  public synchronized RetrievalFields get (Object key) throws IOException {
    if (key == null) return null;
    if (DEBUG) System.out.println (this + "::get: key is '" + key + "'");
    RetrievalFields fields = null;
    try {
      String keyString = key.toString ();
      String classTable = getClassTableForKey (keyString);
      fields = getEntryForKeyInTable (keyString, classTable);
    } catch (SQLException ex) {
      if (DEBUG) {
        System.err.println (this + "::get: caught exception... ");
        System.err.println ("SQLException: " + ex.getMessage ());
        System.err.println ("SQLState:     " + ex.getSQLState ());
        System.err.println ("VendorError:  " + ex.getErrorCode ());
      throw new IOException (ex.getMessage ());
    return fields;

Moving on, the getConnection(), getTypeString(), and getObjectFromResultSet() methods are deferred to subclasses. The getConnection() method knows how to make a connection to the specific database a subclass represents. getTypeString() and getObjectFromResultSet() mapped type information from the database column types to Java types and vice versa:

  // subclasses define driver-specific connection tasks and set the connection 
  protected abstract void getConnection () throws SQLException;
  // subclasses define database-specific type mapping
  protected abstract String getTypeString (Class type);
  // subclasses define type information when reconstructing objects and 
sqlDecode () strings
  protected abstract Object getObjectFromResultSet (ResultSet rs, 
                                                    int colNum,
                                                    String colName,
                                                    int jdbcType) throws 

Next is an example of the flavor of the methods that SQLObjectStorage uses to store objects in a generic SQL database:

  protected void createTable (StorageFields object) throws SQLException {
    Statement statement = null;
    try {
      StringBuffer sqlBuffer = new StringBuffer ();
      String tableName = sqlEncode (object.getClassName ()); // to get rid of . 
in classname
      if (doesTableExist (tableName)) return;
      sqlBuffer.append ("CREATE TABLE " +
                        tableName + " (" +
                        KEY_COLUMN_NAME +
                        " VARCHAR(" +
                        getMaxKeyLength () +
                        "), "); 
      Iterator fieldNames = object.getFieldNames ();   
      while (fieldNames.hasNext ()) {
        String fieldName = (String) ();
        Class type = object.getType (fieldName);
        String typeString = getTypeString (type);
        sqlBuffer.append (sqlEncode (fieldName) + " " + typeString);
        if (fieldNames.hasNext ()) sqlBuffer.append (", ");
      sqlBuffer.append (")");
      if (DEBUG) System.out.println (this + "::createTable: sending: " + 
      statement = connection.createStatement ();
      statement.executeUpdate (sqlBuffer.toString ());
    } finally {
      if (statement != null) {
        try {
          statement.close ();
        } catch (SQLException ignored) {

The createTable() method above creates a StringBuffer which it uses to build an SQL statement, starting with CREATE TABLE, followed by the name of the key column that is added to each table as it is defined.

Of course, subclasses can override these methods if absolutely necessary, although the SQL used should work with drivers for standard databases.

Note that everything that goes to the database is encoded (KEY_COLUMN_NAME and similar constants are pre-encoded). This ensures that no errors will be raised because of attempts to create column names containing . and such. This would be of concern in any class that is in a package other than the default package -- java.lang.Integer for example, or practically any real-world class for that matter.

Next, we see a (typically) lengthy method used by the get() method. The getEntryForKeyInTable() method populates a RetrievalFields object based on the fields and values obtained from the row in table classTable, which itself corresponds to key:

1 2 Page 1
Page 1 of 2