Data AccessCore JavaApp FrameworksViewTestingBuildingDeploymentDev ToolsSecurityOpinions

Saturday, December 5, 2009

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.

10 comments:

  1. This is very cheap shot.
    1) You did not present any ORM code. Some juicy hibernate config file is even worse to read.
    2) Who uses pure JDBC? Try Spring JDBC Templates or Ibatis.

    ReplyDelete
  2. Not sure how this is a cheap shot. It's just meant to illustrate that writing JDBC code is a low level of database interaction and those that still use the methodology above, better have a good reason then the avoidance of learning an ORM like Hibernate.

    ReplyDelete
  3. I'm back to this nice article, Thanks for sharing and keep sharing.
    erp in chennai

    ReplyDelete
  4. In the event that your obligation is to a great degree vast, you might need to contact an expert credit directing administration to take a gander at conceivable choices for obligation settlement, solidification choices or as a last outcome,Cash Advance

    ReplyDelete
  5. You've seen them in the city corner, however have you seen all the diverse sites that give data on the most proficient method to set up your own particular car title credit business?Check Cashing

    ReplyDelete
  6. In the event that something doesn't appear to be correct, run with your gut and pick an alternate payday credit organization. The loan specialist you acquire from ought to in advance with you and in this manner answer the majority of your inquiries. Check Cashing

    ReplyDelete
  7. Hello friends
    Today I would like to mention the top app gadgets in 2018. Android app store. IOS Store application. Sam Sung Store Application. You can refer and leave comments for my items. Visit my page to add an app extension here: https://iplaystoredownloadfree.com/

    You can install and follow the instructions below as the top 3 download stores:

    - How to fix Play Store errors
    - Play Store for PC
    - Play Store for iOS

    Your feedback will help me improve the app store. thank you!

    ReplyDelete
  8. Regardless of whether the business has made a deal or not. In any case, on the off chance that you pick a shipper loan, installments are ascertained as a level of Visa deals, check cashing near me open now san diego

    ReplyDelete
  9. I hope to hear more updates from you. Thank you for the nice article.
    gmail email login

    ReplyDelete