jboss - Hibernate SQLQuery bypasses hibernate session cache

Question

I'm "transactionalizing" some extensive database manipulation and I came across this issue where if I run sql queries through hibernate but not using the MQL approach, the view of the database doesn't appear correct. Specifically the code uses the hibernate in the more appropriate manner in most cases but there are places where someone decided to just execute sql. I don't like that they did this but at this point "it is what it is".

I found an explanation that seems to explain it but all the examples are wrt actually getting and managing the transaction in the code. We are using the @TransactionAttribute annotation on the entire class to change this code and are finding a lot of places where this behavior happens but I'm not entirely convinced the explanation applies to code that is simply wrapped in the annotation--I was assuming that anything using the hibernate manager would rely on the object cache in the session.

Apologies in advance if I am referring to concepts in hibernate by incorrect terminology, etc.

Answer 1

Actually the "explanation" blog by Chris Landry misses 3 important API methods of SQLQuery and thats in fact why he has those problems. Specifically, (1) addSynchronizedQuerySpace, (2) addSynchronizedEntityName and (3) addSynchronizedEntityClass

As partenon points out, just based on the SQL query string itself Hibernate has no way to know what tables and/or entities are queried in the query. Therefore, it has no idea what changes queued up in the Session need to be flushed to the database. In the blog Chris does point out that you can perform a flush() call on your own prior to running the SQL query. However, what I am describing is Hibernate's auto-flush capability. It actually does the same thing for HQL and Criteria queries. Only there it knows the tables being affected. Anyway, this process of auto-flushing does a "minimal flush" flushing only things that affect the query. Thats where these methods come into play.

As an example, Chirs's SQL query is

session.createSqlQuery("select name from user where name = :userName")

Really all he needed to do was to say...

session.createSqlQuery("select name from user where name = :userName")
        .addSynchronizedQuerySpace( "user" )

The addSynchronizedQuerySpace( "user" ) is telling Hibernate that the query uses a table named "user". Now Hibernate can auto flush all changes pending for entities mapped to that user table.