Why you should look into ORM, if you haven't already?

In my last posting, I got clobbered by the blogosphere concerning my posting "Data Access: Plain JDBC vs. ORM".

I feel that the title was misleading, however, I do think that I do make some valid points about the shortcomings of authoring a data tier without the assistance of some type of ORM.

It is my opinion that, yes, there are many applications out there that still use raw JDBC as the primary method of accessing a data source in their data tier.

With that being said, I was targeting this article to those programmers that still write data tiers using raw JDBC, and was thinking of following up with some simple posts and tutorials on ORM's like Spring JDBC and Hibernate.

Without further ado, I am going to repost my thoughts and concerns.

Why you should look into ORM, if you haven't already?

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 contained within the code above:

• The call to getConnection() on the javax.sql.DataSource object can be problematic because it obtains its own database connection. For example, suppose we wrote all our data access methods like the one above, even for inserts and updates. Imagine attempting to persist multiple records, perhaps on the completion of an order, something with an invoice, shipping details and billing details. If one of those transactions fail for some unexpected reason, how would you rollback the transactions? Obtaining a connection from the pool will only rollback the last transaction, leaving you to hand implement the rollback. It becomes even worse if the set of transactions spans multiple data sources.
• 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.

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 effectively handle the leakage of data access details into the view tier. In addition, they support:

• Transaction Demarcation - a mechanism to declare when transactions start and end, and support rollback.
• 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.

The benefits of ORM's are too many for you not to have looked heavily into their proposed benefits.

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.