Data Access: Plain JDBC vs. ORM

I start this discussion, to try and organize some thoughts and comparisons on data access code, written in plain old JDBC (or Java Database Connectivity) vs. utilizing an ORM Framework (or Object Relational Management Framework), such as Hibernate or iBatis.

A developer's goal when writing data access code is to properly contain the responsibilities of all data access operations. Leaking data access operations into the application is referred to as leakage of data-access details and has some adverse affects when the application has to adapt to changes on the data side.

Let's take a look at a method that uses raw JDBC:

public class UserRegistrationJDBC {

   private javax.sql.DataSource dataSource;

   public int getUserRegistrationCount() throws MyDataAccessException {

      java.sql.Connection connection = null;
      java.sql.Statement statement = null;
      java.sql.ResultSet resultSet = null;
      try{
         connection = dataSource.getConnection();
         statement = connection.createStatement();
         resultSet = statement.executeQuery(
            "SELECT COUNT(*) FROM USER_REGISTRATION");

         rs.next();
         return rs.getInt(1);
      }catch(java.sql.SQLException sqle){
         throw new MyDataAccessException(e);
      }finally{
         if(resultSet != null){ 
            try{ resultSet.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
         if(statement != null){ 
            try{ statement.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
         if(connection != null){ 
            try{ connection.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
      }
   }
}

Let's look at some technical details of this data access code:

• The call to getConnection() on the javax.sql.DataSource object can be problematic because it obtains its own database connection. There may be an instance when this call depends entirely on the underlying DataSource object. Not too bad, but it does restrict the application on how it can organize database transactions.
• The call to createStatement() is problematic as well. All sql should be rendered using a java.sql.PreparedStatement() for improved performance via caching. While this is more of a practice and learned through experience, using raw JDBC leaves the programmer open to these types of mistakes.
• Catching a java.sql.SQLException is required by the JDBC API as it is it's only exception type and typically reveals little about the root cause of your error. Wrapping it with the unchecked exception MyDataAccessException, adds little value, only that your calling code does not have to catch it. Your application code now is restricted in how it can deal with specific data access errors.
• The finally block contains the closing of all resources used in the method, which makes it impossible for other methods to reuse it.

Some of the concerns also exist when using ORM tools, however, are often less visible.

Poor handling of the concerns above can lead to:

• Resource exhaustion - loss of connection availability and loss of memory from improperly closed result sets and statements
• Poor performance - due to Statement vs. PreparedStatement as mentioned above.
• Inappropriate connection life cycles - any operation where more than one SQL statement is executed can reuse connections and other resources for more appropriate life cycles. Even with connection pools, we should be careful not to spend CPU cycles and memory on obtaining and releasing connections.

To elaborate even further the inappropriate connection life cycles point, consider where a user registration is added with a payment for the registration. We would want to ensure that all the data access operations occurred or none at all. In a more appropriate world, we want to demarcate these operations at a transaction level, enabling the reuse of all related resources.

The goal of many ORM's are to handle the concerns above with:

• Transaction Demarcation - a mechanism to declare when transactions start and end.
• Transaction Management API - an API to keep contracts clear and concise on transactions

It should be obvious, that writing a Transaction Management API is out of scope for most developers and only an adequately defined API can guarantee that applications remain flexible and adaptive.

In my next article, I'll present ways on how Spring provides a constructive and flexible programming model which addresses the concerns above and much more.