When can/should you go whole hog with the ORM approach?

Question

It seems to me that introducing an ORM tool is supposed to make your architecture cleaner, but for efficiency I've found myself bypassing it and iterating over a JDBC Result Set on occasion. This leads to an uncoordinated tangle of artifacts instead of a cleaner architecture.

Is this because I'm applying the tool in an invalid Context, or is it deeper than that?

When can/should you go whole hog with the ORM approach?

Any insight would be greatly appreciated.

---

A little of background:

In my environment I have about 50 client computers and 1 reasonably powerful SQL Server.

I have a desktop application in which all 50 clients are accessing the data at all times.

The project's Data Model has gone through a number of reorganizations for various reasons including clarity, efficiency, etc.

My Data Model's history

1. JDBC calls directly
2. DAO + POJO without relations between Pojos (basically wrapping the JDBC).
3. Added Relations between POJOs implementing Lazy Loading, but just hiding the inter-DAO calls
4. Jumped onto the Hibernate bandwagon after seeing how "simple" it made data access (it made inter POJO relations trivial) and because it could decrease the number of round trips to the database when working with many related entities.
5. Since it was a desktop application keeping Sessions open long term was a nightmare so it ended up causing a whole lot of issues
6. Stepped back to a partial DAO/Hibernate approach that allows me to make direct JDBC calls behind the DAO curtain while at the same time using Hibernate.

Answer 1

See http://stackoverflow.com/questions/18655/why-do-we-need-entity-objects for some related discussion.

Answer 2

Hibernate makes more sense when your application works on object graphs, which are persisted in the RDBMS. Instead, if your application logic works on a 2-D matrix of data, fetching those via direct JDBC works better. Although Hibernate is written on top of JDBC, it has capabilities which might be non-trivial to implement in JDBC. For eg:

1. Say, the user views a row in the UI and changes some of the values and you want to fire an update query for only those columns that did indeed change.
2. To avoid getting into deadlocks you need to maintain a global order for SQLs in a transaction. Getting this right JDBC might not be easy
3. Easily setting up optimistic locking. When you use JDBC, you need to remember to have this in every update query.
4. Batch updates, lazy materialization of collections etc might also be non-trivial to implement in JDBC.

(I say "might be non-trivial", because it of course can be done - and you might be a super hacker:)

Hibernate lets you fire your own SQL queries also, in case you need to.

Hope this helps you to decide.

PS: Keeping the Session open on a remote desktop client and running into trouble is really not Hibernate's problem - you would run into the same issue if you keep the Connection to the DB open for long.