Simple classes for JDBC

Avoid the tedium of JDBC programming

Call me a troublemaker. I know what you are thinking. Yet another tool/wizard/API/framework to complicate your life. I can understand your hesitation: the problem of object-relational mapping has been kicked around for years. Everyone has a solution that is easier, better, faster, cheaper. Why do we need more code that probably does what someone has already done somewhere else? Two reasons: Most of the solutions appear more complicated than necessary, and they require a significant commitment. Once you buy in, it's hard to get out.

I like things simple. A colleague recently shared a nice quote:

"Simplicity leads to ubiquity, complexity leads to obscurity."

And we've all heard the Einstein quote:

"A scientific theory should be as simple as possible, but no simpler."

Here's one from James Gosling himself:

"Complexity is in many ways just evil. Complexity makes things harder to understand, harder to build, harder to debug, harder to evolve, harder to just do about everything."

I could go on and on. There are many examples of complexity, too many. In the case of database access, you can use entity beans, or an object relational mapping tool, or the latest open source framework. I don't have a formal complaint against any of those tools; I'm certain their authors had a great vision in mind when developing them. For me, today, I want something simple.

Charting the latitudes

Let's step back a bit. Most of the time, an application needs to take information from a user and create/edit/delete/view it as rows in a database. One important detail is that information to and from the user must be in the form of a string. This means that validation/parsing has to be done on the way in and formatting, on the way out. This is not hard, but it's repetitive and involves numerous annoying null checks. Another important feature that users have come to expect are the so-called VCR controls: the ability to page through a large set of results or jump to the end, not easy to do with a ResultSet alone.

One popular solution is to manually or automatically map SQL results and table schemas to objects and classes. At the outset, this appears reasonable, if not natural. In general, database modelers and object modelers try to accomplish the same thing: manage business information in a logical and extensible manner.

Unfortunately, the honeymoon ends there. Object modelers prefer to isolate themselves from the sundry details of how data goes in and out. To some, the database is just a big hashmap with more than one key. This might be an accurate assessment, but there is a tricky SQL animal known as the table join. Database experts argue that joins give relational databases their power. Object modelers might look at a join as an untamed beast that just causes more trouble than it's worth. Regardless of your opinions, objects and databases are not likely to change significantly in the near future. Applications and business logic will continue to use objects, and data persistence will continue to take the form of a relational database.

Cultural barriers aside, significant differences exist between objects/classes and rows/tables. Objects can include behavior, which distinguishes them from structures and other aggregate types. Classes offer inheritance and polymorphism, which makes them reusable and extensible. But databases are about efficient storage and quick searching. That cannot be underestimated when you consider the potential of tens of thousands, if not millions, of rows of information. This information, and its efficient access, proves just as important as the code that manages it.

Another important detail is that classes are easy to change and code is often refactored when convenient and helpful. Changing a database's structure is a much bigger deal. Changing column names or even creating new tables and reloading data is not in itself challenging, but adjusting all of the code that relies on that structure can prove problematic. For that reason, typically, database tables change little after they are populated.

All of these differences have led to the so-called impedance mismatch, a term borrowed from the electrical-engineering principle of maximum power transfer. Even though we can get data in and out of a database easily and efficiently, the objects that use the data might not fit well. The typical result is lots of boilerplate code that looks like manual object serialization. This is tedious and error prone.

To avoid the tedium, many tools have evolved over the years. The J2EE specification offers a model in the form of container-managed entity beans and EJB-QL (Enterprise JavaBean Query Language). In the open source community, Hibernate has become a popular solution. These solutions depend on a query language like SQL, but not exactly. The result is that you might have to adjust your queries to fit the tool, rather than use what the database is capable of doing by itself.

These can be effective solutions for a broad range of issues, but they do not yet meet my arbitrary standard of simplicity. I stress arbitrary because many will find these solutions meet their requirements; it's just a difference of opinion and nothing more.

The Chase

Many developers who I have worked with are not object modelers or database modelers; they represent a third category of programmer. They are equally comfortable with objects and SQL; they are PowerBuilder developers. PowerBuilder has its good and bad qualities, like any product, but in my opinion, it has one jewel: the data window. Instead of mapping every table or potential result set into a distinct class, PowerBuilder has one class: the data window. Simply put, the data window is like an updatable ResultSet. To use it, simply issue a query, any query, no matter how complex. Any results returned could be easily formatted, sorted, and navigated in any order. Further, any of the returned data could be modified and committed back to the database in one method call. The data window handles all primary key management and transactions. The data window reduces the standing wave ratio (SWR) of the impedance mismatch from 10:1 down to something like 1.5:1. It's not perfect, but it does the job.

The Chase, Version 1

Having convinced myself that the data window can be an effective solution, I started writing something similar in Java on top of JDBC (Java Database Connectivity). I had a fairly complete solution, but there were problems. One of the beauties of the data window is that it automatically manages primary keys and column data types. This significantly reduces the burden on the developer. The problem with my implementation was the availability of high quality database drivers. The implementation depends heavily on result set metadata. This metadata, like any other metadata, such as Java Reflection and XML Schema, is powerful when accurate. The problem is that many drivers, even high quality drivers, do not return all of the necessary metadata. Nothing in the JDBC specification requires drivers to do so. The methods exist, but they don't necessarily work for all combinations of drivers and databases.

From what I am told, PowerBuilder also runs into metadata problems when using ODBC (Open Database Connectivity) and native drivers. The solution is to implement custom database drivers for each database supported to guarantee robust functionality. Foolishly, I wasted a lot of time investigating why some drivers just didn't provide all of the metadata. The answer is simple: there are lots of databases, lots of drivers, and a few people developing them. Most likely, perfect metadata is not high on the list of priorities; 100 percent support is not likely to be achieved in the near future.

The Chase, Version 2

I attempted to scale back the metadata requirements in the hopes of supporting the most popular database drivers and databases. This proved to be better, but less convenient than the original implementation, and it would still leave some out in the cold.

I concluded that maybe I was too bold. Like others, I tried to solve too large a problem. I retreated to something more manageable, something simple. I decided that what I liked best were the navigation facilities, formatting, parsing, and validation. I scrapped the idea of managing primary keys and left that to the developer. I concluded that in the end, primary keys are not that hard to manage, a fair compromise in my opinion.

The end result is a scaled-back solution that tolerates limitations of available database drivers, but is still relatively easy to use.

Nuts and bolts

Enough background—it's time to see the implementation and some examples. We'll see how the package can be used to perform basic select/insert/update/delete operations and also format and validate data.

Here's a simplified UML diagram of this article's package, which can be downloaded from Resources:

UML diagram of key classes

The Database class does two things: maintains a map of formatters and implements methods to select, insert, update, and delete rows. There's also a validate() method, which I will talk about shortly. Formatter is an abstract class that defines three methods: parse(), format(), and validate(). As mentioned earlier, the users' data view is in the form of strings, so the formatters are responsible for converting database types to and from strings. When entering data, invalid data is always possible, e.g., nonnumeric characters in a numeric field, dates incorrectly formatted, or strings that don't match a pattern. For each of the expected column data types, there exists a Formatter subclass.

Let's look at a simple example of selecting data:

Connection con = getConnection();
myDB = new Database(false);
myDB.loadDefaultFormatters(con, "roles");
String[] params = {"", "99"};
Results rs =, "select id,name,role from roles where id = ?", params, 10);
System.out.println("Name is " + rs.getString(0, 1));
System.out.println("Password is " + rs.getString(0, "roles.role"));

The first line gets a database connection. I assume you have means for getting the connection, most applications do. Usually it comes from a predefined data source or a simple call to DriverManager. The constructor to Database takes a Boolean argument that tells the instance to automatically close connections after an operation. As you probably know, closing connections is critical and error prone, so it's automated if you like. The loadDefaultFormatters() method is a cheap way of loading the appropriate formatters for a given table. This relies on the getColumnClass() method in ResultSetMetaData. It's possible this might not work, but in my experience, this method appears to work for all the databases I tested, including Access, SQL Server, MySQL, Oracle, and DB2. Your mileage may vary. Alternatively, you can set the formatter for specific columns by name. You will probably want to do this in production anyway, as the default formatters don't have many validation restrictions.

This example is a simple select. Notice the SQL is nothing more than what you might supply to PreparedStatement. In fact, under the hood, that's exactly what it is, allowing you to use any SQL that your database will execute. Also, you don't need to learn another query language. The params argument is an array of Strings containing column name and value pairs. The params array is like an ordered HashMap, but easier to create and initialize. The column name is needed, so the parameter values can be parsed correctly. There is an overloaded form of select() that takes a string array of columnNames if your database driver does not properly return that information for each column. Most databases do, but some don't. Cases also exist where no table or column name is available because a SQL function or constant might be used in the select.

The select() method returns a Results object, which is like a ResultSet, but formats results based on column name. If the column name is not available, it will try to use the column type. Values for a given row and column can be retrieved in any order. Note that row and column index are zero based, deviating from ResultSet behavior. Also note that the column name or the column number can be specified. The select() method will throw SQLException if something goes wrong.

Inserts are done like this:

String[] values = {
   "", "99",
   "", "ahab",
   "roles.role", "captain"
String[] errors = myDB.validate(values);
if (errors != null) {
   for (int i=0; i<errors.length; i+=1) {
      System.out.println(values[2*i] + ": " + errors[i]);
} else {
   myDB.insertRow(con, values);

This example shows how validation can be used. The Database class has a validate() method that takes an array of values to be validated and returns error messages, if any. Each value is checked against its corresponding formatter. If any value is not valid, the array will contain corresponding error messages. Values with no errors contain an empty (non-null) string. A null return value indicates no validation errors.

The insertRow() method accepts a connection and the values to insert. The values are formatted as an array of Strings containing column name and value pairs. SQLException will be thrown if something goes wrong.

An update example:

String[] values = {
   "", "99",
   "", "ahab",
   "roles.role", "fool"
String[] params = {"", "99"};
int n = myDB.updateRows(con, values, "id=?", params);
1 2 Page 1
Page 1 of 2