We're presently evaluating choices for moving from hands-written persistence layer to ORM.

You will find there's couple of legacy persistent objects (~200), that implement simple interface such as this:

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

When retrieve() is known as, object populates itself by giving handwritten SQL queries towards the connection provided while using ID it received within the setter (this usually may be the only parameter towards the query). It handles its claims, result sets, etc itself. A few of the objects have particular tastes of retrive() method, like retrieveByName(), within this situation another SQL is released.

Queries might be quite complex, we frequently join several tables to populate the sets representing relations with other objects, sometimes join queries are released on-demand within the specific getter (lazy loading). So essentially, we now have implemented the majority of the ORM's functionality by hand.

The main reason for your was performance. We now have quite strong needs for speed, and in 2005 (if this code was written) performance tests has proven that none of mainstream ORMs were that fast as hands-written SQL.

The issues we're facing since make us think about ORM are:

  • The majority of the pathways within this code are very well-examined and therefore are stable. However, some rarely-used code is vulnerable to result set and connection leaks which are very difficult to identify
  • We're presently compressing some additional performance with the addition of caching to the persistence layer and it is a huge discomfort to keep the cached objects by hand within this setup
  • Support of the code when DB schema changes is really a large problem.

I'm searching for a guidance on what is the very best alternative for all of us. So far as I understand, ORMs has advanced in last five years, so it may be that now there's one which provides an acceptable performance. When I check this out problem, we have to address individuals points:

  • Find a way to reuse a minimum of a few of the written SQL to convey mappings
  • Potentially have to problem native SQL queries without the call to by hand decompose their results (i.e. avoid manual rs.getInt(42) because they are very responsive to schema changes)
  • Give a non-intrusive caching layer
  • Keep your performance figures.

Can there be any ORM framework you can recommend in relation to that?

UPDATE To provide a sense of what type of performance figures we're speaking about:

  • The after sales database is TimesTen, in-memory database that works on the same machine because the JVM
  • We discovered that altering rs.getInt("column1") to rs.getInt(42) brings the performance increase we consider significant.

If you prefer a standard persistence layer that allows you problem native SQL queries, think about using iBATIS. It is a fairly thin mapping involving the objects and SQL. http://ibatis.apache.org/

For caching and lazy joins, Hibernate may well be a better option. I've not used iBATIS of these reasons.

Hibernate provides lots of versatility in permitting you to definitely specify certain defaults for lazy loading while you traverse your object graph, yet also pre-fetch data with SQL or HQL queries for your heart's content when you really need better-known load occasions. However, the conversion effort is going to be complicated for you personally as it features a fairly high bar to entry when it comes to learning and configuration. Annotations chose to make this simpler for me personally.

Two benefits you did not mention about switching to some standard framework: (1) running lower bugs becomes simpler if you have an abundance of sites and forums available to aid you. (2) new employs are less costly, simpler and faster.

Best of luck in addressing your speed and agility and usability issues. The tradeoffs you explain are extremely common. Sorry basically evangelized.