Caution using SQL Queries with Hibernate
As someone fairly new to Hibernate, I was pleasantly surprised to see that it supported the execution of an SQL query through the createSQLQuery() method within the Hibernate Session.
Initially, it seemed beneficial to use plain old SQL to generate my DAO queries. After all I already knew SQL, why waste my time learning HQL (Hibernate’s query language) or Hibernate’s Criteria API. In addition, with SQL I could easily test my queries in DB editing tool like DBVisualizer and in the “rare” event that I might need it, a DBA unfamiliar with Hibernate could easily enhance and maintain my queries.
It seemed like the solution.
However, on further analysis I have changed my tune.
There are many reasons from a purely OO point of view to avoid straight SQL. However, many other people make a far more compelling argument on this point than I can here, so I’ll leave that to them.
The main point I would like to focus on here is:
The Hibernate SQLQuery bypasses the Hibernate Session cache and queries ONLY against the database.
This means that if you perform a SQL query on the database in the same transaction that you’ve just performed a save and/or update, the saved / updated objects in the Hibernate Session Cache will not be included in the SQL result.
By the same token, HQL and the Hibernate Criteria queries check the Session cache before executing the query. If there are objects that the HQL query may execute against hibernate will flush the cache to the database.
This essentially means that unlike the SQLQuery, HQL and Hibernate Criteria queries will ALWAYS include the objects in the Session Cache automatically.
To illustrate how this works here’s an example:
1) As noted on the SQLQuery diagram, you can actually manually force Hibernate to flush the cache by executing session.flush(). This would require you to execute this line before each SQLQuery you execute. For instance:
session.flush(); List result = session.createSqlQuery("select name from user where name = :userName") .setParameter("userName", user.getName()) .list();
However, this has two major disadvantages:
- You might accidentally forget to do this, leaving your application in a poor state.
- session.flush() is an expensive operation. Using HQL and Criterias Hibernate can decide whether or not it needs to flush. Thereby avoiding unnecessary flushing.
2) Junit testing.
It’s worth mentioning that the code in the example diagrams are Junit tests. By utilizing hibernate’s transaction mechanism and the session cache we are able to test our dao code without actually changing the state of our database. At the end of the test we can just rollback the transaction and undo any updates that we made.