Lightweight Database Abstraction

Although there are object-relational mapping tools like Hibernate for persisting Java objects, there are still situations where you need or want to access the database directly using JDBC.

One thing that has always bothered me about JDBC is the disconnect between the SQL command strings and the surrounding Java code. Consider the following example (which prints a list of robots that were “born” before 1980):

    public void printClassicRobots() {
        Calendar dobThreshold =
            new GregorianCalendar(1980, 0, 1);
        PreparedStatement statement = null;
        try {
            Connection connection = getConnection();
            statement = connection.prepareStatement(
                    "SELECT ID, Name" +
                    " FROM Robots" +
                    " WHERE DateOfBirth < ?");
            statement.setDate(1, new Date(
                    dobThreshold.getTimeInMillis()));

            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                System.out.format("%08d: %s\n",
                        rs.getInt(1),
                        rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cleanup(statement);
        }
    }

What I don’t like about this kind of code:

  1. Since the SQL command is just another string, the compiler does not know anything about its meaning and cannot check if its syntax is correct or if the number and types of columns in its result set are consistent with the subsequent method calls that extract data from the result set.
  2. Different database systems use different variants of SQL, making it quite challenging to write portable SQL statements in all but the simplest cases.
  3. Refactoring the database schema can be very tedious and error-prone if you have to resort to a text search to find all the places where a certain table or column is used in your code.

In C# 3.0, all of these issues have been addressed through the introduction of LINQ to SQL. But Java developers are out of luck and probably will be for some time, since adding something comparable to Java would require similarly wide-ranging language changes as were necessary to make this work in C#. So while I can’t provide a complete solution to these problems in Java, there are some simple steps I found useful in addressing at least some of them.

Making table references explicit

As a first step, let’s add a class to represent the Robots table, with a string constant for the table name and one for each column name:


public class Robots {

    public static final String TABLE = "Robots";

    public static final String ID = "ID";

    public static final String NAME = "Name";

    public static final String DOB = "DateOfBirth";
}

We can then change the query method from above as follows:

    public void printClassicRobots() {
        Calendar dobThreshold =
            new GregorianCalendar(1980, 0, 1);
        PreparedStatement statement = null;
        try {
            Connection connection = getConnection();
            statement = connection.prepareStatement(
                    "SELECT " + Robots.ID +
                    ", " + Robots.NAME +
                    " FROM " + Robots.TABLE +
                    " WHERE " + Robots.DOB + " < ?");
            statement.setDate(1, new Date(
                    dobThreshold.getTimeInMillis()));

            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                System.out.format("%08d: %s\n",
                        rs.getInt(1),
                        rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cleanup(statement);
        }
    }

The main advantage of this approach is that it makes table references explicit. If string constants are defined in this way for all table and column names and used consistently in all SQL statements, then it becomes easy to find all the places in your code where a given table or column is used, simply by using the “Find References” feature of your IDE. This can be helpful when refactoring the database schema.

Another advantage of representing database artifacts as explicit Java artifacts is that these Java artifacts provide a good place for documenting the database schema. Each table’s or column’s meaning can be documented by attaching a JavaDoc comment to the corresponding class or field declaration.

But wait, there’s more!

Adding table definitions

Now you might say that using string constants is pretty obvious and really no big deal, and I would agree with you. So let’s go a step further. If we’re going to create a separate class for each table, we might as well use that class for a little more than just to hold a couple of string constants. In addition to declaring the names of tables and columns that are referenced in the program, we can include the definitions of these tables and columns.

So I created two annotations, @Table and @Column, to hold some meta-data about tables and columns, for example the data type and size of a column. The class for the Robots table now looks like this:

@Table(name = "Robots")
public class Robots {

    @Column(name = "ID", index = 0,
            type = Types.INTEGER, primaryKey = true)
    public final String id;

    @Column(name = "Name", index = 1,
            type = Types.VARCHAR, size = 100)
    public final String name;

    @Column(name = "DateOfBirth", index = 2,
            type = Types.DATE)
    public final String dob;

    public Robots(String name, String[] columns) {
        this.tableName = name;
        this.id = columns[0];
        this.name = columns[1];
        this.dob = columns[2];
    }

    @Override
    public String toString() {
        return tableName;
    }

    private final String tableName;
}

I am not going to go into details about the individual elements in these annotations, but you get the idea. The point is that we now have a class that encapsulates the definition of a specific table. The class contains the information necessary for creating this table in a database. It is actually pretty straightforward to write some general code that takes this kind of class as input, uses reflection to read the annotations from it and then calls DdlUtils to create the actual database table.

Another thing I changed from the first version of the Robots class is that the fields for the columns are no longer static. In this way, these fields can be initialized at run time, for example to account for naming differences between multiple existing databases, or to automatically quote the names if they conflict with reserved words in the database.

Using this new version of the Robots class, the query method looks like this:

    public void printClassicRobots() {
        Calendar dobThreshold =
            new GregorianCalendar(1980, 0, 1);
        PreparedStatement statement = null;
        try {
            Connection connection = getConnection();
            Robots robots = getRobotsTable();
            statement = connection.prepareStatement(
                    "SELECT " + robots.id +
                    ", " + robots.name +
                    " FROM " + robots +
                    " WHERE " + robots.dob + " < ?");
            statement.setDate(1, new Date(
                    dobThreshold.getTimeInMillis()));

            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                System.out.format("%08d: %s\n",
                        rs.getInt(1),
                        rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            cleanup(statement);
        }
    }

The method now requires an instance of the Robots class, since its fields are no longer static. This instance could be created as part of some initialization code when the database connection is established. At this point it could also be checked whether the existing tables in the connected database are consistent with the definitions in the corresponding Java table classes, and if necessary, tables could be created or altered automatically using tools like DdlUtils as mentioned above.

I like to think of table classes like the Robots class as defining the interface between the Java code and the database. Here you can define explicitly the assumptions you make troughout your program about the database schema. These assumptions can be verified or enforced early in your program’s execution (by checking and/or creating the database schema), and from that point on you shouldn’t have to worry about getting exceptions at random locations in your code because some column doesn’t exist (unless someone changes the schema at runtime).

I hope you enjoyed my first real blog post, and I am looking forward to your comments. I would be interested in your experiences in this area, and in any ideas on how to solve the remaining problems that I mentioned in the beginning.

Bonus

In case you were wondering, this is what the above method prints on my example database:

00000027: R2-D2
00000018: C-3PO
00000011: Maria

You can read more about these and other famous robots at the Robot Hall of Fame.

About these ads

One Response to Lightweight Database Abstraction

  1. [...] SQL Interfaces for Java I previously blogged about the shortcomings of JDBC and its way of passing SQL statements as strings without any [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: