ORIGINAL DRAFT

There are several ways to map objects onto database records. The easiest of these is probably to use an object database, but reporting tools are somewhat limited and IT professionals tend to prefer relational database systems in the enterprise. Enterprise JavaBeans provide a nice solution. Though there are still some inconsistencies between vendors, that’s something that’ll get shaken out as the technology matures. Another option is to put something together yourself. Depending on your needs, this offers an interesting alternative.

In this article, we’re going to implement a simple object/relational infrastructure that uses introspection to make things as transparent as possible. While this may not be the ultimate solution, it allows us to explore important concepts in a pragmatic manner. This is not the only way to approach the problem. The best approach to object/relational mapping is still subject to plenty of debate and I don’t profess to offer any final words. In general, however, I think you’ll find the information interesting enough to make it worth your time.

Guidelines

Before we dive into writing code, we need to take a quick look at the basics of object/relational modeling. Here are a few simple guidelines we want to apply in our efforts. These guidelines highlight what it means to bridge the object/class and relational database worlds.

• Each persistent class maps directly onto a database table.
• Object fields with primitive data types map directly onto table columns.
• Each object instance maps directly onto a row in the corresponding table.
• Many-to-many relationships require a join object and a corresponding join table.
• Subclasses are modeled by using a one-to-one relationship with the parent table.

Let’s take a look at these guidelines individually.

Each persistent class maps directly onto a database table. There are certainly cases where you want a class to represent more than a single table, but it’s generally unwise to create a data model that doesn’t already reflect this in the database. If the database is well designed, there is already a one-to-one mapping between tables and objects at the conceptual level. If not, you’re life will quickly get complicated, so its typically better to review the database structure if you can.

Object fields with primitive data types map directly onto table columns. This is simple enough. If you have bytes, shorts, integers, longs, floats, doubles, strings, or characters, this goes without saying. In Java, this also applies to Date, Time, Timestamp, BigDecimal, and byte arrays, given that they are primitives as far as the database is concerned. Non-primitives are compound objects that map directly onto other database tables.

Each object instance maps directly onto a row in the corresponding table. This highlights the fact that each record maps directly onto an object instance. Where we think of the class as a template for object instances, the table is also a template for each of the rows it contains. Put another way, a class maps onto a table and each row is an instance of a table description. Reading a row, therefore, results in a new object instance. Writing a row requires the existence of an object instance for that table class.

Many-to-many relationships require a join object and a corresponding join table. This one is a bit less obvious. To create many-to-many relationships in a database, we typically use a join table that stores rows of references to the records being joined together. The same approach maps onto the object model in that an intermediary object is required to connect tables together. Such an object is consistent with the mapping scheme described before, but this rule helps clarify the way these relationships are modeled.

Subclasses are modeled by using a one-to-one relationship with the parent table. If you have a class that stores an address, for example, and a subclass that stores additional phone number information, you would map the address object onto the address table and use a join operation to create an object that extends the address class with the added information. The extended table information need only store the information that is not part of an address record, along with a reference to the specific parent record that contains the relevant address information.

Architecture

The main target of our effort is a DBObject class we can use to transparently map objects and relational database tables. To work with it, you need only implement a subclass that contains relevant instance variables and call the inherited methods to operate on the database. The implementation effectively maps suitable variables to database columns and each object instance represents a row in the database.

Figure 1: DBObject Class Relationships.

Figure 1 shows the classes we’ll need to develop. DBObject is an abstract class that implements our introspection-based object-relational mapping. The DBManager class provides a vehicle for generating queries through the DBQuery object. We keep these loosely coupled so that alternate implementations are possible. The DBField, DBRelation and DBValue objects are used to support a better internal representation of SQL elements. This keeps us from having to parse SQL strings and defers SQL statement generation to when they are needed.

The DBPrimaryKey and DBForeignKey objects map integer values onto respective roles for convenience. This has the added benefit of providing a useful level of type safety. My original intent was to provide transparent mapping of foreign keys, but there are numerous issues related to graph traversal that are not easily resolved. These have to do with whether you always read or write all subobjects or some portion of the containment graph, or not. Since we don’t have the time or space to provide a comprehensive solution, this part of our solution remains more open ended.

When you use the DBObject implementation with foreign keys, you’ll need to read and write contained objects explicitly. The DBPrimaryKey and DBForeignKey objects provide a safe way to manage this complexity. While the implementation of foreign key to object mappings is not completely transparent, these classes provide a way to work with them as painlessly as possible.

Implementation

One of our objectives is to automate SQL statement generation using the Reflections API. To do this, we’re going to implement a few classes designed to represent statement elements. To start with, we’ll create a DBType object to map between relational and object data types. A DBType has an integer identifier, taken from the java.sql.Types, a name string and a type, represented as a Java class.

Listing 1 shows the DBType source code. There’s a single constructor that requires all three arguments and a set of read-only accessors. In addition, you’ll see that constants are used to define a DBType instance for each of the data types supported by JDBC. We’ll also provide a static fromType method that returns a suitable DBType object for a given Java class. We’ll use this to determine what a each instance variable maps to in a relational database record.

To create a table, we need to describe the fields in enough detail to execute a SQL statement. Rather than work directly with strings, which would have to be parsed at intermediate stages, we’ll use DBField arrays. A DBField object encapsulates a field name, DBType, maximum length and a primary key flag. Primary keys are assumed to be integers and use the auto increment flag supported by most database management systems. The string for auto increment is intentionally stored as a static, non-final String value so that you can change it to suit your needs if you have to.

Listing 2 shows the DBField class. We provide a few constructor variants for convenience and read-only accessors for each of the instance variables. There’s also a toString method that actually formats SQL syntax for each DBField elements. The formatted output helps construct elements of a more complete SQL statements that we’ll need to create new records. When a compound SQL statement needs to be constructed, the toString method handles the DBField-specific element at the local level. We’ll use the same technique in the other SQL element classes.

Finding a record requires a SQL statement with a WHERE clause that describes appropriate constraints. These are effectively relations, so we’ll use a class called DBRelation to describe each element. We’ll support seven (7) actual relations, corresponding to "equal", "greater than or equal to", "less than or equal to", "not equal", "greater than", "less than" and "like" operators.

Listing 3 shows the DBRelation source code. A DBRelation has three instance variables containing a field name, an operator and a value. We’ll implement a set of read-only accessors for each of the variables and a toString method that serves the same purpose as it did in the DBField class, formatting output for use in SQL statements.

There is a special case of a relation when we are interested in assignment rather than comparison. These element show up in different parts of an SQL statement and deserve to be differentiated. As such, we implement a subclass of DBRelation, called DBValue, which does nothing more than assume the equality operator in a DBRelation. This might seem like a waste of time but it helps preserve type safety and avoid trivial coding mistakes in our implementation. The DBValue class extends DBRelation and merely exposes a simplified constructor.

Because we want the introspection API to handle finding what ever we want to know about an object mapping, we also need to implement DBPrimaryKey and DBForeignKey classes. The primary key stores an integer identifier and provides a single read-only accessor method to get it. The DBForeignKey is not much more interesting. It merely extends the DBPrimaryKey class so that we can distinguish the difference and pass in a DBForeignKey anywhere we’re expecting a DBPrimaryKey. You can find the full source code online at www.java-pro.com.

To use the DBForeignKey effectively, you need to understand the design. A foreign key is a reference to a primary key in another table. The DBPrimaryKey and DBForeignKey types help our introspection process distinguish between these but they are both represented as integers values in the table rows. You’ll often need to read or write the table row associated with the foreign key. The DBForeignKey class is meant to make this easier but its up to you to decide when reading or writing is appropriate.

Introspection

A DBObject is expected to be composed of a DBPrimaryKey, zero or more primitive DBType-compatible Java types, and zero or more DBForeignKey objects. Extending DBObjects and declaring your instance variables is all you need to do to gain the functionality you want to map straight on to a relational database system.

Listing 4 shows the source code for the DBObject class. This is an abstract class because it should never be directly instantiated. We do this primarily because our introspection will pick up all the instance variables in the subclass and we don’t want to end up with exceptions because of the super class. Any suitable class that extends DBObject automatically becomes object/relational database-capable.

For convenience, we implement a number of utility classes that collect information through the Introspection API. The getFields and getQualifiedFields methods collect all public fields. They differ only in that the getQualifiedFields method makes sure the table prefix is available for SQL queries that include multiple tables.

The getSchema method constructs a DBField array from the public fields in our object. The getRecord method collects a DBValue array from the actual field and respectively assigned values. I’ll point out that this method dislikes unpopulated fields. This infrastructure does nothing to implement referential integrity or completeness other than give you a vehicle to make life easier. It’s up to you to make sure your data is valid and accounted for. This is an application-specific issue that can’t be handled generically, other than to provide warnings. I decided a NullPointerException was warning enough.

The makeWhere method constructs a DBRelation array to be used in a WHERE clause. The WHERE clause we are interested in is one that describes an object instance without ambiguity, so we construct a single equality relation, mapping the DBPrimaryKey name and a primary key value. To support this, we need to use two methods called getKeyName and getKeyValue, which both walk the introspection Field list and return a suitable result when they finds a DBPrimaryKey instance.

The rest of the methods let us create SQL statements appropriate for each of the important cases and delegate their work to a DBQuery. These are named, respectively, createTable, createIndex, dropTable, dropIndex, selectRecord, deleteRecord, insertRecord and updateRecord. To understand these better, let’s take a look at the DBQuery code in Listing 5.

To keep the code as loosely coupled as possible, we use the java.text.MessageFormat class to create SQL query templates that we’ll populate with arguments at runtime. There are several support methods that glue DBField, DBRelation and DBValue objects together to form more complete statements. These are called formatWhere, getFields, getValues, and list. They are fairly-self explanatory. Both formatWhere and list use a StringBuffer to create an output string. The getFields and getValues methods are used to pull out the field names and field values from a Value array to support the syntax required by SQL to insert a record.

Each of the factory methods produces a SQL statement, customized to manipulate a given DBObject class. Each takes some argument(s) and constructs the clauses necessary to build up a usable query. The methods operate either on the database (createTable, createIndex, dropTable and dropIndex), on multiple records (selectRecords, deleteRecords, updateRecords), or deal with a single record at a time (updateRecord).

Keep in mind that, while we support queries that return multiple records, our assumption is that there is a unique primary key associated with a record and that this is used to select, delete and update an object-record. The DBQuery implementation is intentionally more general, but the DBObject implementation is necessarily constrained for this application.

Management

High-level control over all these operations can become complex. To make things easier, the DBManager makes it possible to handle database operations in various pragmatic ways. DBManager, for example, is responsible for delegating DBQuery calls and managing SQL statements. The constructor expects a Statement instance and uses this instance for all SQL queries. If you work with a customized connection pool of some kind, you can create as many DBManager instances as you like.

Listing 6 shows the code for the DBManager. The interface supports a debug mode which is useful to verifying SQL statement generation. SQL strings can be written to the console. In addition, you can use a null Statement if you want to test DBManager without using a database. This is a good way to verify the SQL output without messing up a database, but it is potentially dangerous if you accidentally pass in a null value. If things don’t make it into the database, this might be a good suspect.

The internal methods that interest us are createTable, createIndex, dropTable, dropIndex, insertRecord, selectRecord, updateRecord and deleteRecord. By passing a DBObject to any of these methods, we can operate on both the table and its records. The insertRecord, selectRecord, updateRecord and deleteRecord methods are largely self-explanatory. Selecting, deleting or updating assumes a suitable primary key value for the DBObject instance.

The high-level, public methods are createClass, destroyClass, writeObject, readObject, createObject and deleteObject. Each of these relies on the earlier protected calls to accomplish its goals. Calling createClass creates a new table and index for the table describing the DBObject class you are working with. Calling destroyClass removes the index and table completely. Be careful using that one, given how dangerous it is to your data.

The createObject method will create a new table record from the object. You should call this in your constructor. It will return a DBObject instance with the primary key automatically updated. The deleteObject call removes the record associated with the PrimaryKey identifier. The readObject method will populate the instance variables in a DBObject with the values in the row pointed to by the primary key. The write object method writes the data out to the database.

It’s worth noting that inserting an object creates a record in the database and needs to get the primary key value from the auto-increment counter before it’s done. In a production system, I’d recommend you use transactions to make sure these operations are atomic. Unlike Enterprise JavaBeans, we don’t support finding records based on non-primary keys, at least not automatically.

You are, naturally, free to extend your objects to perform these kinds of queries and may want to make use of some of the support capabilities provided by the DBManager implementation. My predilection is to make internal methods protected for just this reason. If you need to do so, just subclass DBManager and add your own operations to extend the behavior.

Usage

The best way to see how all this works together is to present a simple example. We’ll keep things fairly straight forward. Let’s look at something with a master class and a contained class, each of which map onto relational database tables, a simple user access system. The two classes we’ll need are called ExampleUser and ExamplePermission.

The master class is an ExampleUser object that maps onto a table with username and password fields. It also contains a DBForeignKey that will point to an ExamplePermission object. The ExamplePermission class contains an Integer value that identifies a useful permission level. Remember that this is not necessarily a practical example, but rather meant to demonstrate the DBObject in action. Both these classes extend the DBObject class and automatically become database-enabled.

public class ExampleUser extends DBObject
{
  public DBPrimaryKey userKey;
  public String username;
  public String password;
  public DBForeignKey permissions;
}

Notice that we have to declare the instance variables public for introspection to work. This may seem like pure blasphemy to you but I’ll refer you to the Jini/JavaSpaces specification for the reasons behind doing things this way and sidestep the issue altogether. You should still work with accessor methods and restrict access to these fields using delegates or other methods. The need for public variables is an introspection issue.

Here’s what the ExamplePermission declaration looks like:

public class ExamplePermission
  extends DBObject
{
  public DBPrimaryKey permissionKey;
  public Integer accessLevel;
}

I’ve only listed the instance variable declarations. The rest of these classes are available online and include accessor methods for each of the instance variables, along with a toString method that makes it easy to see what’s going on. It’s also important to have a no-argument constructor or you’ll see an instantiation exception. When the object is read back from the database, we use this constructor to create a new object to be populated with the values from the database.

Listing 7 shows the ExampleTest class, which is nothing more than a main method that watches for three command line arguments. You can use "-c" to create a new set of tables. Use "-d" to delete them, and "-t" to run a test of createObject, readObject and writeObject for the two example classes above.

Listing 8 shows the output from these runs against mySQL on my system. I have not tested this against other databases, so you mileage may vary. I would expect the AUTO_INCREMENT and LAST_INSERT_ID to be potentially incompatible with another database but they are both localized in the DBQuery class and should be easy to change. My testing was also limited with regards to each of the data types and so this is not code I would take into production without investing more effort.

Developing this code lead me to a few insights, along with renewed my respect for the Enterprise JavaBeans model. The use of introspection to manage object-relation mapping is a powerful tool. Trying to make the process completely transparent is difficult and the resulting code can be brittle if left as is. This is nothing that good exception handling could not improve but a truly usable implementation would need to address these requirements more effectively. As well, the matter of transparently mapping foreign keys needs to be better addressed, and String mappings need better control over field length. Finally, the distinction between static and instance variables is not made during introspection and future versions of this code would have deal with this more effectively.

The strengths of object-relational systems is your ability to use a multitude of existing tools to report and manipulate the database and to develop applications using object-oriented metaphors. I hope you’ve gained your own insights into this process and that this article will help you better understand object-relational mapping issues so you can make better informed decisions when you use this type of technology in your projects.

Listing 1

import java.sql.*;
import java.math.*;

public class DBType
{
  public static final DBType BIT =
    new DBType(Types.BIT, "BIT", Boolean.class);

  // Numerical
  public static final DBType TINYINT =
    new DBType(Types.TINYINT, "TINYINT", Byte.class);
  public static final DBType SMALLINT =
    new DBType(Types.SMALLINT, "SMALLINT", Short.class);
  public static final DBType INTEGER =
    new DBType(Types.INTEGER, "INT", Integer.class);
  public static final DBType BIGINT =
    new DBType(Types.BIGINT, "BIGINT", Long.class);
  public static final DBType REAL =
    new DBType(Types.REAL, "REAL", Float.class);
  public static final DBType FLOAT =
    new DBType(Types.FLOAT, "FLOAT", Double.class);
  public static final DBType DOUBLE =
    new DBType(Types.DOUBLE, "DOUBLE", Double.class);
  public static final DBType NUMERIC =
    new DBType(Types.NUMERIC, "NUMERIC", BigDecimal.class);
  public static final DBType DECIMAL =
    new DBType(Types.DECIMAL, "DECIMAL", BigDecimal.class);

  // Character
  public static final DBType CHAR =
    new DBType(Types.CHAR, "CHAR", String.class);
  // !!! Mapping VARCHAR onto VARCHAR(255) is not ideal !!!
  public static final DBType VARCHAR =
    new DBType(Types.VARCHAR, "VARCHAR(255)", String.class);
  
  public static final DBType LONGVARCHAR =
    new DBType(Types.LONGVARCHAR, "LONGVARCHAR", String.class);

  // Binary
  public static final DBType BINARY =
    new DBType(Types.BINARY, "BINARY", byte[].class);
  public static final DBType VARBINARY =
    new DBType(Types.VARBINARY, "VARBINARY", byte[].class);
  public static final DBType LONGVARBINARY =
    new DBType(Types.LONGVARBINARY, "LONGVARBINARY", byte[].class);

  // Temporal
  public static final DBType DATE =
    new DBType(Types.DATE, "DATE", Date.class);
  public static final DBType TIME =
    new DBType(Types.TIME, "TIME", Time.class);
  public static final DBType TIMESTAMP =
    new DBType(Types.TIMESTAMP, "TIMESTAMP", Timestamp.class);

  protected int id;
  protected String name;
  protected Class type;
  
  public DBType(int id, String name, Class type)
  {
    this.id = id;
    this.name = name;
    this.type = type;
  }

  public int getID()
  {
    return id;
  }
  
  public String getName()
  {
    return name;
  }
  
  public Class getType()
  {
    return type;
  }
  
  public static DBType fromType(Class type)
  {
    if (type == DBPrimaryKey.class) return INTEGER;
    if (type == DBForeignKey.class) return INTEGER;
    if (type == Boolean.class) return BIT;
    if (type == Byte.class) return TINYINT;
    if (type == Short.class) return SMALLINT;
    if (type == Integer.class) return INTEGER;
    if (type == Long.class) return BIGINT;
    if (type == Float.class) return REAL;
    if (type == Double.class) return DOUBLE;
    if (type == BigDecimal.class) return DECIMAL;
    if (type == String.class) return VARCHAR;
    if (type == byte[].class) return BINARY;
    if (type == Date.class) return DATE;
    if (type == Time.class) return TIME;
    if (type == Timestamp.class) return TIMESTAMP;
    return null;
  }
}

Listing 2

public class DBField
{
  protected String fieldName;
  protected DBType dataType;
  protected boolean primaryKey;
  protected int maxLength;
  
  public DBField(String fieldName)
  {
    this(fieldName, DBType.INTEGER, 0, true);
  }
  
  public DBField(String fieldName, DBType dataType)
  {
    this(fieldName, dataType, 0, false);
  }
  
  public DBField(String fieldName,
    DBType dataType, int maxLength)
  {
    this(fieldName, dataType, maxLength, false);
  }
  
  public DBField(String fieldName,
    DBType dataType, boolean primaryKey)
  {
    this(fieldName, dataType, 0, primaryKey);
  }
  
  public DBField(String fieldName,
    DBType dataType, int maxLength, boolean primaryKey)
  {
    this.fieldName = fieldName;
    this.dataType = dataType;
    this.primaryKey = primaryKey;
    this.maxLength = maxLength;
  }
  
  public String getFieldName()
  {
    return fieldName;
  }
  
  public DBType getDataType()
  {
    return dataType;
  }
  
  public boolean isPrimaryKey()
  {
    return primaryKey;
  }
  
  public String toString()
  {
    String text = fieldName + " " + dataType.getName() +
      (maxLength > 0 ? "(" + maxLength + ")" : "");
    return text + (primaryKey ?
      " NOT NULL " + DBQuery.AUTO_INCREMENT +
      " PRIMARY KEY" : "");
  }
}

Listing 3

public class DBRelation
{
  public static final String OP_EQ = "=";
  public static final String OP_GT = ">";
  public static final String OP_GE = ">=";
  public static final String OP_LT = "<";
  public static final String OP_LE = "<=";
  public static final String OP_NE = "";
  public static final String OP_LIKE = "LIKE";
  
  protected String field;
  protected String operator;
  protected Object value;
  
  public DBRelation(String field, Object value)
  {
    this(field, OP_EQ, value);
  }
  
  public DBRelation(String field, String operator, Object value)
  {
    this.field = field;
    this.operator = operator;
    this.value = value;
  }
  
  public String getField()
  {
    return field;
  }
  
  public String getOperator()
  {
    return operator;
  }
  
  public Object getValue()
  {
    return value;
  }
  
  public String toString()
  {
    return field + " " + operator +
      " '" + value.toString() + "'";
  }
}

Listing 4

import java.sql.*;
import java.lang.reflect.*;

public abstract class DBObject
{
// ------------------------------------------------------------------
// INTROSPECTION METHODS
// ------------------------------------------------------------------
  
  public String getName()
  {
    return getClass().getName();
  }
  
  public Field[] getFields()
  {
    return getClass().getFields();
  }
  
  public String[] getFieldNames()
  {
    Field[] fields = getFields();
    int size = fields.length;
    String[] list = new String[size];
    for (int i = 0; i < size; i++)
    {
      list[i] = fields[i].getName();
    }
    return list;
  }

  public String[] getQualifiedFields()
  {
    Field[] fields = getFields();
    int size = fields.length;
    String[] list = new String[size];
    for (int i = 0; i < size; i++)
    {
      list[i] = getName() + "." + fields[i].getName();
    }
    return list;
  }

  public DBField[] getSchema()
  {
    Field[] fields = getFields();
    int size = fields.length;
    DBField[] schema = new DBField[size];
    for (int i = 0; i < size; i++)
    {
      String name = fields[i].getName();
      Class type = fields[i].getType();
      if (type == DBPrimaryKey.class)
        schema[i] = new DBField(name);
      else
        schema[i] = new DBField(name,
          DBType.fromType(type));
    }
    return schema;
  }

  public DBValue[] getRecord(boolean includePK)
    throws IllegalAccessException
  {
    Field[] fields = getFields();
    int size = fields.length;
    int max = includePK ? size : size - 1;
    DBValue[] list = new DBValue[max];
    int count = 0;
    for (int i = 0; i < size; i++)
    {
      String name =	fields[i].getName();
      Object value = fields[i].get(this);
      Class type = fields[i].getType();
      if (includePK || type != DBPrimaryKey.class)
      {
        list[count] = new DBValue(name, value);
        count++;
      }
    }
    return list;
  }

  protected String getKeyName()
  {
    Field[] fields = getFields();
    int size = fields.length;
    for (int i = 0; i < size; i++)
    {
      if (fields[i].getType() == DBPrimaryKey.class)
        return fields[i].getName();
    }
    throw new IllegalArgumentException(
      "No PrimaryKey object in this class");
  }
  
  protected DBPrimaryKey getKeyValue()
  {
    Field[] fields = getFields();
    int size = fields.length;
    for (int i = 0; i < size; i++)
    {
      if (fields[i].getType() == DBPrimaryKey.class)
      {
        try
        {
          return (DBPrimaryKey)fields[i].get(this);
        }
        catch (IllegalAccessException e)
        {
          throw new IllegalArgumentException(
            "Security problem IllegalAccessException");					
        }
      }
    }
    throw new IllegalArgumentException(
      "No PrimaryKey object in this class");
  }
  
  public void setPrimaryKey(DBPrimaryKey key)
  {
    Field[] fields = getFields();
    int size = fields.length;
    for (int i = 0; i < size; i++)
    {
      if (fields[i].getType() == DBPrimaryKey.class)
      {
        try
        {
          fields[i].set(this, key);
        }
        catch (IllegalAccessException e)
        {
          throw new IllegalArgumentException(
            "Security problem IllegalAccessException");					
        }
      }
    }
  }

  public Field[] getForeignKeyFields()
    throws IllegalAccessException
  {
    Field[] fields = getFields();
    int size = fields.length;
    int count = 0;
    for (int i = 0; i < size; i++)
    {
      Object value = fields[i].get(this);
      if (value instanceof DBForeignKey)
      {
        count++;
      }
    }
    Field[] list = new Field[count];
    count = 0;
    for (int i = 0; i < size; i++)
    {
      Object value = fields[i].get(this);
      if (value instanceof DBForeignKey)
      {
        list[count] = fields[i];
        count++;
      }
    }
    return list;
  }

// ------------------------------------------------------------------
// DATABASE METHODS
// ------------------------------------------------------------------
  
  public void createClass(DBManager manager)
    throws SQLException
  {
    manager.createClass(this);
  }

  public void destroyClass(DBManager manager)
    throws SQLException
  {
    manager.destroyClass(this);
  }

  public DBObject readObject(DBManager manager)
    throws SQLException, IllegalAccessException,
      ClassNotFoundException, InstantiationException
  {
    return manager.readObject(this);
  }
  
  public DBObject readObject(
    DBManager manager, DBPrimaryKey key)
      throws SQLException, IllegalAccessException,
        ClassNotFoundException, InstantiationException
  {
    return manager.readObject(this, key);
  }
  
  public DBObject createObject(DBManager manager)
    throws SQLException, IllegalAccessException,
      ClassNotFoundException, InstantiationException
  {
    return manager.createObject(this);
  }
  
  public void writeObject(DBManager manager)
    throws SQLException, IllegalAccessException
  {
    manager.writeObject(this);
  }
  
  public void deleteObject(DBManager manager)
    throws SQLException, IllegalAccessException
  {
    manager.deleteObject(this);
  }

  public void deleteObject(DBManager manager, DBPrimaryKey key)
    throws SQLException, IllegalAccessException
  {
    manager.deleteObject(this, key);
  }
}

Listing 5

import java.text.*;

public class DBQuery
{
  protected static String AUTO_INCREMENT =
    "AUTO_INCREMENT";

  private static MessageFormat CREATE_TABLE =
    new MessageFormat("CREATE TABLE {0} ({1})");
  private static MessageFormat DROP_TABLE =
    new MessageFormat("DROP TABLE {0}");
  private static MessageFormat CREATE_INDEX =
    new MessageFormat("CREATE INDEX {0} ON {1} ({2})");
  private static MessageFormat DROP_INDEX =
    new MessageFormat("DROP INDEX {0}");
  
  private static MessageFormat GET_LAST_INSERT_ID =
    new MessageFormat("SELECT LAST_INSERT_ID() FROM {0}");
  private static MessageFormat INSERT_RECORD =
    new MessageFormat("INSERT INTO {0} ({1}) VALUES ({2})");
  private static MessageFormat UPDATE_RECORD =
    new MessageFormat("UPDATE {0} SET {1}");
  private static MessageFormat SELECT_RECORD =
    new MessageFormat("SELECT {1} FROM {0}");
  private static MessageFormat DELETE_RECORD =
    new MessageFormat("DELETE FROM {0}");
  
  private static MessageFormat WHERE_CLAUSE =
    new MessageFormat("WHERE {0}");
  private static MessageFormat FROM_CLAUSE =
    new MessageFormat("FROM {0}");

// --------------------------------------------------------
// TABLE METHODS
// --------------------------------------------------------
  
  public String createTable(String table, DBField[] schema)
  { 
    Object[] arguments = {table, list(schema)};
    return CREATE_TABLE.format(arguments);
  }
  
  public String createIndex(String table, String index, String[] fields)
  { 
    Object[] arguments = {index, table, list(fields)};
    return CREATE_INDEX.format(arguments);
  }

  public String dropTable(String table)
  { 
    Object[] arguments = {table};
    return DROP_TABLE.format(arguments);
  }
  
  public String dropIndex(String index)
  { 
    Object[] arguments = {index};
    return DROP_INDEX.format(arguments);
  }

// --------------------------------------------------------
// SINGLE RECORD METHODS
// --------------------------------------------------------
  
  public String getLastInsertID(String table)
  {
    Object[] arguments = {table};
    return GET_LAST_INSERT_ID.format(arguments);
  }
  
  public String insertRecord(String table, DBValue[] record)
  { 
    String[] fields = getFields(record);
    Object[] values = getValues(record);
    Object[] arguments = {table, list(fields), list(values, true)};
    return INSERT_RECORD.format(arguments);
  }

// --------------------------------------------------------
// MULTI-RECORD METHODS
// --------------------------------------------------------
  
  public String selectRecords(
    String[] tables, String[] fields, DBRelation[] where)
  {
    return selectRecords(tables, fields) + formatWhere(where);
  }
  
  public String selectRecords(String[] tables, String[] fields)
  { 
    Object[] arguments = {list(tables), list(fields)};
    return SELECT_RECORD.format(arguments);
  }

  public String updateRecords(
    String[] tables, DBValue[] record, DBRelation[] where)
  { 
    Object[] arguments = {list(tables), list(record)};
    return UPDATE_RECORD.format(arguments) + formatWhere(where);
  }
  
  public String deleteRecords(String[] tables, DBRelation[] where)
  { 
    Object[] arguments = {list(tables)};
    return DELETE_RECORD.format(arguments) + formatWhere(where);
  }
  
// --------------------------------------------------------
// SUPPORT METHODS
// --------------------------------------------------------

  private String formatWhere(DBRelation[] where)
  {
    if (where == null) return "";
    Object[] arguments = {list(where)};
    return " " + WHERE_CLAUSE.format(arguments);
  }
  
  private String[] getFields(DBValue[] record)
  {
    int size = record.length;
    String[] fields = new String[size];
    for (int i = 0; i < size; i++)
    {
      fields[i] = record[i].getField();
      if (fields[i] == null)
        System.out.println("Null field");
    }
    return fields;
  }
  
  private Object[] getValues(DBValue[] record)
  {
    int size = record.length;
    Object[] values = new Object[size];
    for (int i = 0; i < size; i++)
    {
      values[i] = record[i].getValue();
      if (values[i] == null)
        System.out.println("Null value for " +
          record[i].getField());
    }
    return values;
  }
  
  private String list(Object[] list)
  {
    return list(list, false);
  }
  
  private String list(Object[] list, boolean quotes)
  {
    StringBuffer buffer = new StringBuffer();
    for (int i = 0; i < list.length; i++)
    {
      if (i > 0) buffer.append(", ");
      if (quotes) buffer.append("'");
      buffer.append(list[i].toString());
      if (quotes) buffer.append("'");
    }
    return buffer.toString();
  }
 }

Listing 6

import java.sql.*;
import java.lang.reflect.*;

public class DBManager
{
  protected Statement statement;
  protected DBQuery query = new DBQuery();
  protected boolean debug = false;

  public DBManager(Statement statement)
  {
    this.statement = statement;
  }
  
  public boolean isDebug()
  {
    return debug;
  }
  
  public void setDebug(boolean debug)
  {
    this.debug = debug;
  }
  
  public DBQuery getQuery()
  {
    return query;
  }
  
  public void setQuery(DBQuery query)
  {
    this.query = query;
  }
  
// ------------------------------------------------------------------
// UTILITY METHODS
// ------------------------------------------------------------------
  
  protected DBRelation[] makeWhere(
    DBObject object, DBPrimaryKey key)
      throws IllegalAccessException
  {
    DBRelation[] where =
    {
      new DBRelation(object.getKeyName(), key)
    };
    return where;
  }
  
  protected DBRelation[] makeWhere(DBObject object)
    throws IllegalAccessException
  {
    return makeWhere(object, object.getKeyValue());
  }
  
  protected DBValue[] getResult(ResultSet result)
    throws SQLException
  {
    if (result == null) return null;
    boolean more = result.next();
    if (!more) return null;
    ResultSetMetaData meta = result.getMetaData();
    int count = meta.getColumnCount();
    DBValue[] record = new DBValue[count];
    for (int i = 0; i < count; i++)
    {
      String name = meta.getColumnName(i + 1);
      Object value = result.getObject(i + 1);
      record[i] = new DBValue(name, value);
    }
    return record;
  }
  
  protected DBObject createInstance(
    Class objectType, ResultSet result)
      throws SQLException, InstantiationException,
        IllegalAccessException
  {
    if (result == null) return null;
    DBValue[] record = getResult(result);
    DBObject object = (DBObject)objectType.newInstance();
    Field[] fields = object.getFields();
    for (int i = 0; i < record.length; i++)
    {
      for (int j = 0; j < fields.length; j++)
      {
        if (fields[j].getName().equalsIgnoreCase(record[i].getField()))
        {
          Object value = record[i].getValue();
          if (fields[j].getType() == DBPrimaryKey.class)
            value = new DBPrimaryKey(
              ((Integer)record[i].getValue()).intValue());
          if (fields[j].getType() == DBForeignKey.class)
            value = new DBForeignKey(
              ((Integer)record[i].getValue()).intValue());
          fields[j].set(object, value);
        }
      }
    }
    return object;
  }
  
  protected DBPrimaryKey getInsertedPrimaryKey(ResultSet result)
    throws SQLException
  {
    if (result == null) return new DBPrimaryKey(0);
    DBValue[] record = getResult(result);
    int id = ((Long)record[0].getValue()).intValue();
    return new DBPrimaryKey(id);
  }
  

  protected DBPrimaryKey getNewKey(DBObject object)
    throws SQLException
  {
    ResultSet idResult = getLastInsertID(object);
    if (statement == null) return new DBPrimaryKey(0);
    ResultSetMetaData meta = idResult.getMetaData();
    return getInsertedPrimaryKey(idResult);
  }

// ------------------------------------------------------------------
// ACCESS METHODS
// ------------------------------------------------------------------
  
  protected int createTable(DBObject object)
    throws SQLException
  {
    String sql = query.createTable(
      object.getName(), object.getSchema());
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }
  
  protected int createIndex(DBObject object)
    throws SQLException
  {
    String table = object.getName();
    String[] keyFields = {object.getKeyName()};
    String sql = query.createIndex(
      table, table + "Index", keyFields);
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }
  
  protected int dropTable(DBObject object)
    throws SQLException
  {
    String table = object.getName();
    String sql = query.dropTable(table);
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }
  
  protected int dropIndex(DBObject object)
    throws SQLException
  {
    String table = object.getName();
    String sql = query.dropIndex(table + "Index");
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }

  protected ResultSet selectRecord(DBObject object)
    throws SQLException, IllegalAccessException
  {
    String[] tables = {object.getName()};
    String sql = query.selectRecords(
      tables, object.getFieldNames(), makeWhere(object));
    if (debug) System.out.println(sql);
    if (statement == null) return null;
    return statement.executeQuery(sql);
  }
  
  protected ResultSet selectRecord(
    DBObject object, DBPrimaryKey key)
      throws SQLException, IllegalAccessException
  {
    String[] tables = {object.getName()};
    String sql = query.selectRecords(
      tables, object.getFieldNames(),
      makeWhere(object, key));
    if (debug) System.out.println(sql);
    if (statement == null) return null;
    return statement.executeQuery(sql);
  }
  
  protected ResultSet getLastInsertID(DBObject object)
    throws SQLException
  {
    String sql = query.getLastInsertID(object.getName());
    if (debug) System.out.println(sql);
    if (statement == null) return null;
    return statement.executeQuery(sql);
  }

  protected int deleteRecord(DBObject object)
    throws SQLException, IllegalAccessException
  {
    String[] tables = {object.getName()};
    String sql = query.deleteRecords(
      tables, makeWhere(object));
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }

  protected int deleteRecord(DBObject object, DBPrimaryKey key)
    throws SQLException, IllegalAccessException
  {
    String[] tables = {object.getName()};
    String sql = query.deleteRecords(
      tables, makeWhere(object, key));
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }

  protected int insertRecord(DBObject object)
    throws SQLException, IllegalAccessException
  {
    String sql = query.insertRecord(
      object.getName(), object.getRecord(false));
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }

  protected int updateRecord(DBObject object)
    throws SQLException, IllegalAccessException
  {
    String[] tables = {object.getName()};
    String sql = query.updateRecords(
      tables, object.getRecord(true), makeWhere(object));
    if (debug) System.out.println(sql);
    if (statement == null) return -1;
    return statement.executeUpdate(sql);
  }

// ------------------------------------------------------------------
// OBJECT METHODS
// ------------------------------------------------------------------

  public void createClass(DBObject object)
    throws SQLException
  {
    createTable(object);
    createIndex(object);
  }
  
  public void destroyClass(DBObject object)
    throws SQLException
  {
    dropIndex(object);
    dropTable(object);
  }
  
  public void writeObject(DBObject object)
    throws SQLException, IllegalAccessException
  {
    updateRecord(object);
  }
  
  public DBObject readObject(DBObject object)
    throws SQLException, IllegalAccessException,
      ClassNotFoundException, InstantiationException
  {
    ResultSet result = selectRecord(object);
    if (statement == null) return object;
    Class objectType = Class.forName(object.getName());
    return createInstance(objectType, result);
  }

  public DBObject readObject(DBObject object, DBPrimaryKey key)
    throws SQLException, IllegalAccessException,
      ClassNotFoundException, InstantiationException
  {
    ResultSet result = selectRecord(object, key);
    if (statement == null) return object;
    Class objectType = Class.forName(object.getName());
    return createInstance(objectType, result);
  }

  public DBObject createObject(DBObject object)
    throws SQLException, IllegalAccessException,
      ClassNotFoundException, InstantiationException
  {
    int count = insertRecord(object);
    object.setPrimaryKey(getNewKey(object));
    ResultSet result = selectRecord(object);
    if (statement == null) return null;
    Class objectType = Class.forName(object.getName());
    return createInstance(objectType, result);
  }
  
  public void deleteObject(DBObject object)
    throws SQLException, IllegalAccessException
  {
    deleteRecord(object);
  }
  
  public void deleteObject(DBObject object, DBPrimaryKey key)
    throws SQLException, IllegalAccessException
  {
    deleteRecord(object, key);
  }
}

Listing 7

import java.sql.*;

public class ExampleTest
{
  public static void main(String[] args)
    throws Exception
  {
    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/test"); 
    Statement statement = connection.createStatement();
    
    DBManager manager = new DBManager(statement);
    manager.setDebug(true);
    
    ExampleUser user = new ExampleUser("user", "password");
    ExamplePermission permission = new ExamplePermission(
      ExampleConstants.ACCESS_READWRITE);
    
    if (args[0].equalsIgnoreCase("-c"))
    {
      System.out.println("Create tables");
      manager.createClass(user);
      manager.createClass(permission);
    }
    if (args[0].equalsIgnoreCase("-d"))
    {
      System.out.println("Destroy tables");
      manager.destroyClass(user);
      manager.destroyClass(permission);
    }
    if (args[0].equalsIgnoreCase("-t"))
    {
      System.out.println("Create new objects");
      manager.createObject(permission);
      //System.out.println(permission.getKeyValue());
      user.setPermission(permission);
      manager.createObject(user);
      //System.out.println(user.getKeyValue());

      System.out.println("Change password");
      user = (ExampleUser)manager.readObject(user);
      user.setPassword("newpassword");
      manager.writeObject(user);
      
      System.out.println("Change access level");
      permission = (ExamplePermission)
        manager.readObject(permission);
      permission.setAccessLevel(
        ExampleConstants.ACCESS_READONLY);
      manager.writeObject(permission);
    }
  }
}

Listing 8

C:\projects\java\dbobjects\code >java ExampleTest -c
Create tables
CREATE TABLE ExampleUser (userKey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255), password VARCHAR(255), permission INT)
CREATE INDEX ExampleUserIndex ON ExampleUser (userKey)
CREATE TABLE ExamplePermission (permissionKey INT NOT NULL AUTO_INCREMENT PRIMARY KEY, accessLevel INT)
CREATE INDEX ExamplePermissionIndex ON ExamplePermission (permissionKey)

C:\projects\java\dbobjects\code >java ExampleTest -t
Create new objects
INSERT INTO ExamplePermission (accessLevel) VALUES ('2')
SELECT LAST_INSERT_ID() FROM ExamplePermission
SELECT permissionKey, accessLevel FROM ExamplePermission WHERE permissionKey = '1'
INSERT INTO ExampleUser (username, password, permission) VALUES ('user', 'password', '1')
SELECT LAST_INSERT_ID() FROM ExampleUser
SELECT userKey, username, password, permission FROM ExampleUser WHERE userKey = '1'
Change password
SELECT userKey, username, password, permission FROM ExampleUser WHERE userKey = '1'
UPDATE ExampleUser SET userKey = '1', username = 'user', password = 'newpassword', permission = '1' WHERE userKey = '1'
Change access level
SELECT permissionKey, accessLevel FROM ExamplePermission WHERE permissionKey = '1'
UPDATE ExamplePermission SET permissionKey = '1', accessLevel = '1' WHERE permissionKey = '1'

C:\projects\java\dbobjects\code >java ExampleTest -d
Destroy tables
DROP INDEX ExampleUserIndex
DROP TABLE ExampleUser
DROP INDEX ExamplePermissionIndex
DROP TABLE ExamplePermission