Phone screen tech talent in 5 minutes with Induckt Use invite code BETA15 for a free trial through July 31

ResultSets vs. Hibernate

by timvasil 2/1/2008 8:14:00 PM

When writing Java code requiring object/relational mapping to an underlying relational database, I've always relied on Hibernate.  Recently, though, I tackled a small project dealing with only a few objects and primarily read-only access to the data.  I thought it'd be the perfect opportunity to use JDBC directly--to keep the project simple.  The code ended up looking pretty clean.  Here's a sample data access method to log in a user and provide some information related to the user, namely the Customer object associated with the user and all the "Sites" associated with that customer:

    final NamedParameterStatement stmt = new NamedParameterStatement(getConnection(),
            "SELECT u.*, c.*, cs.*, us.CustomerSiteId AS UserSiteId FROM tbl_users u" +
            " INNER JOIN tbl_customers c ON u.CustomerId = c.CustomerId" +
            " INNER JOIN tbl_customer_sites cs ON cs.CustomerId = c.CustomerId" +
            " LEFT OUTER JOIN tbl_user_sites us ON us.CustomerSiteId = cs.CustomerSiteId" +
            " WHERE LOWER(Username) = :username AND (:password IS NULL OR u.Password = :password)");
    stmt.setString("username", StringUtils.nonNullify(username).toLowerCase());
    stmt.setString("password", password);
    final ResultSet results = stmt.executeQuery();
    if (!results.first())
        // No user found
        return null;

    // Hydrate customer
    final Customer customer = HydrationUtils.hydrateCustomer(results);
    // Hydrate user
    final User user = HydrationUtils.hydrateUser(results);
    // Hydrate customer sites and determine user accessible sites
    while (
        final Site site = HydrationUtils.hydrateSite(results);
        if (results.getObject("UserSiteId") != null)
    if (updateLastLogonTime)
        final NamedParameterStatement updateStmt = new NamedParameterStatement(getConnection(),
                "UPDATE tbl_users SET LastLogon = NOW() WHERE UserId = :userId;");
        updateStmt.setInt("userId", user.getId());
    return user;
catch (Exception e)
    throw new RuntimeException(e);

There are a number of things to notice about this code:

  • Java doesn't support named parameters.  I had to write my own NamedParameterStatement class so I could perform parameter substitution using named parameters.
  • Though you don't see its implementation, my getConnection() method pulls a connection from a c3p0 connection pool.  Without Hibernate, I have to manage connection strings and the connection pool.
  • The "hydration" process (aka relational to object mapping) is handled by the HydrationUtils class.  This is a bit clunky and an annoyance to write, with a bunch of setter calls and data conversions (e.g. from java.sql.Date to java.util.Date).
  • The work to ensure there aren't multiple objects referring to the same row in a table is handled explicitly, i.e. the sites in the user's access list is pulled from the sites in the customer's list.  Hibernate would have handled this for me.
  • The underlying DDL script has to be written by me, and the embedded SQL limits portability.

Yes, there's a bit of work going on here.  Some of it is brittle.  For example, additions to the object model will not cause compile time errors, yet the HydrationUtils methods would need updating.  Such issues would likely not be caught until runtime.

As the application grew more complex and I found myself struggling to manage unique indexes and foreign keys in an ever-growing DDL script, I decided to switch to Hibernate.  Now, take a look at the same user logon code written Hibernate style:

return new TransactionRunner<User>() {
    protected User doWork(Session session) throws Exception
        // Prepare the user-fetching query--eagerly fetching associated customer and the customer's associated sites
        final Query userQuery = session.createQuery("from User as u " +
          " inner join fetch u.customer c" +
          " inner join fetch c.sites" +
          " left join fetch u.accessibleSites" +
          " where lower(u.username) = :username and (:password is null or u.password = :password)");
        userQuery.setParameter("username", username);
        userQuery.setParameter("password", password);
        // Fetch the user
        final User user = (User)userQuery.uniqueResult();
        // Update the user's last logon time (if so desired)
        if (updateLastLogonTime)
            user.setLastLogon(new Date());
        // Replace persistent sets with regular sets for serialization
        user.setAccessibleSites(new HashSet(user.getAccessibleSites()));
        user.getCustomer().setSites(new HashSet(user.getCustomer().getSites()));
        return user;

See the difference?

  • I wrote HQL instead of SQL to ensure portability.  No more NamedParameterStatement; Hibernate understands named parameters.  It also understands objects, so the query has an object-oriented feel to it.  I'm joining objects, not IDs.
  • There's no hydration code.  Hibernate does this for me.
  • Updating an object is as simply as changing its properties; I didn't have to write any SQL.
  • I didn't have to manage any databsae connection object explicitly.
  • I did write a TransactionRunner class to wrap the code in a transaction, so the begin/commit/rollback could happen at the appropriate time.  Writing data access methods within anonymous classes is a bit clunkly.  The alternative of using annotations with a pointcut-enabling component such as Spring crossed my mind, but I don't really have any need for most of its features at this point so I'll live with the callback.
  • I have to jump through a few hoops near the end of the method so I can get rid of Hibernate's persistent collections in favor of Java's built-in collections.  Even though Hibernate's collections implement the standard Java List, Map, and Set interfaces, they don't play nice with GWT, and I needed to serialize the object graph to a web client.

Overall I feel more comfortable with the Hibernate code.  I think it's cleaner and easier to maintain.  And next time I'm working on a simple little project, I won't be so quick to discount an O/R mapping tool; I believe it does save time, even for the small projects.


Java | Hibernate | GWT

Comments are closed



«  October 2016  »

View posts in large calendar

Recent comments