Raible Designs

Persistence Options with existing SQL

At my new gig, it's not an option to use Hibernate. Their data model is too complex, and they've already written a bunch of code and it's corresponding SQL to get the information they need (think lots of inner joins, stored procedures and selects in where clauses). It was my task last week to port all the JDBC from one project to a more general framework to be used by all the websites we're building. The existing code is in the following form:

PreparedStatement pstmt = 
    conn.prepareStatement("select * from table where id=?");
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
MyBean bean = new MyBean();
if (rs.next) {
    bean.set(...);
    ...
    bean.set(...);
}

After doing all my persistence with Hibernate for the last year, it made me cringe to have to resort to this archaic (though tried and true) way of populating my objects. So I pinged the struts-user mailing list and asked what my options where for populating an object from a ResultSet. Basically, I was looking for a 1-2 line solution that didn't affect performance too much. After jossling back and forth for a while, I came up with 2 options:

• Use a ResultSetUtils utility class, such as the one from Ted Husted.
• Use iBATIS.

I did some performance testing and the ResultSetUtils class had the same performance numbers as rs.next() { set, set, set }, so it was definitely a viable solution. The downside? You have to name your resultset columns the same as your object's properties - and it's case sensitive. iBATIS was also a slick solution - as soon as I added <settings useBeansMetaClasses="false"/> to my sql-map-config.xml file, the performance was comparable to the ResultSet options (I turn it on when deploying, off for unit tests).

My Point: We're using iBATIS for our Persistence framework, and I dig it. It allows us to keep the complex SQL (externalized in XML files) that has already been written and it took me about a 1/2 hour to setup. I'd recommend Hibernate if you're starting a DB from scratch, but iBATIS seems to be a great solution when the SQL is already in place.

Will I add an iBATIS option to any of projects? Naahhh, then I'd have to work with Hibernate to export the SQL for each call, and I'd have to update my XML file's SQL everytime I change something in the DDL (currenly Hibernate and XDoclet perform this magic).