Are native SQL queries cached by default when Hibernate query cache is turned on?

Question

Using Hibernate 3.3.0 and ehcache 1.2.3 with 2nd level caching and query cache turned on, I realised that the following code was returning the same sequence number across multiple calls which resulted in insertion failure.

        HibernateCallback callback = new HibernateCallback()
        {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                StringBuilder strQuery = new StringBuilder();
                strQuery.append("SELECT ");
                strQuery.append(sequenceName);
                strQuery.append(".nextval as nextSequence FROM dual d");

                Query query = session.createSQLQuery(strQuery.toString()).addScalar("nextSequence", Hibernate.STRING);


                return query.uniqueResult();
            }
        };
        return this.hibernateTemplate.execute(callback).toString();

The code works properly if I turn off the query cache or add the following line before executing the query.

query.setCacheable(false);

This is confusing, since Hibernate docs clearly state

Most queries do not benefit from caching, so by default queries are not cached. To enable caching, call Query.setCacheable(true). This call allows the query to look for existing cache results or add its results to the cache when it is executed.

In this case, is this abnormal behavior and can I still make the assumption that queries are not cached by default?

Answer 1

Query cache is a really simple mechanism which stores results for certain key. In case of native query that key will be your query itself and of all of the params.

So, for example, the key might be:

*----------------------------------------------------------------------------------------*
|                                    Query Cache                                         |
|----------------------------------------------------------------------------------------|
| ["select * from table as t where t.id=? and p.column=?", [ 1 , "value"] ] -> [  2 ] ]  |
*----------------------------------------------------------------------------------------*

From that perspective every query can be cached - in certain scenarios. Of course the query have to be dealt with by Hibernate classes, not directly via JDBC connection.

And, BTW, it's easy to find out whether your query will use the query cache or not! It's all in a log files under org.hibernate.cache.

And there is a big catch in all of these - if you run your native query, it will evict all the entities and records of the second level cache! At least it did till the last version I was using! So you can use native queries all right, but since Hibernate can not decide what they do, it will clear the cache to avoid data changes done by this query not reflected in the cache objects.

So there is a whole lot of issues with query cache and you should consider if you really want to use that feature! Take a look at this article or this one. I'm trying to avoid using query caches in my work, I only use SLC for entities...