Hi community,

We are currently evaluating options for migrating from hand-written persistence layer to ORM.

We have a bunch of legacy persistent objects (~200), that implement simple interface like this:

interface JDBC {
    public long getId();
    public void setId(long id);
    public void retrieve();
    public void setDataSource(DataSource ds);
}

When retrieve() is called, object populates itself by issuing handwritten SQL queries to the connection provided using the ID it received in the setter (this usually is the only parameter to the query). It manages its statements, result sets, etc itself. Some of the objects have special flavors of retrive() method, like retrieveByName(), in this case a different SQL is issued.

Queries could be quite complex, we often join several tables to populate the sets representing relations to other objects, sometimes join queries are issued on-demand in the specific getter (lazy loading). So basically, we have implemented most of the ORM's functionality manually.

The reason for that was performance. We have very strong requirements for speed, and back in 2005 (when this code was written) performance tests has shown that none of mainstream ORMs were that fast as hand-written SQL.

The problems we are facing now that make us think of ORM are:

• Most of the paths in this code are well-tested and are stable. However, some rarely-used code is prone to result set and connection leaks that are very hard to detect
• We are currently squeezing some additional performance by adding caching to our persistence layer and it's a huge pain to maintain the cached objects manually in this setup
• Support of this code when DB schema changes is a big problem.

I am looking for an advice on what could be the best alternative for us. As far as I know, ORMs has advanced in last 5 years, so it might be that now there's one that offers an acceptable performance. As I see this issue, we need to address those points:

• Find some way to reuse at least some of the written SQL to express mappings
• Have the possibility to issue native SQL queries without the necessity to manually decompose their results (i.e. avoid manual rs.getInt(42) as they are very sensitive to schema changes)
• Add a non-intrusive caching layer
• Keep the performance figures.

Is there any ORM framework you could recommend with regards to that?