In one session, I have 3 loops and inside each loop is called 2 times a méthod containing a certain SQL Query on a certain entity (there is some other treatment, but the 2 calls are not separated by others SQL Queries).
I expected that the 2nd call of the method should use the cache, not send the query to the database.
The cache works fine for the first loop but for the next 2 loops it doesn't: the second call (in those loops) sends the query to the database.
I've put sqlQuery.setCacheable(true), and in the hibernate.cfg.xml file I specify the cache usage (see it below).

I become even more confused if I use another way to test the usage of the cache: calling "mrkOrderContainer = sqlQuery.uniqueResult();" more than one time successively; the cache is not used, even in the first loop:
....
mrkOrderContainer = sqlQuery.uniqueResult();
mrkOrderContainer = sqlQuery.uniqueResult();//no cache usage. Why?
mrkOrderContainer = sqlQuery.uniqueResult();//no cache usage. Why?

Each call to uniqueResult() leads to a query like this:
select * from (select internalKey, version, ..etc.., order__id from TmrkOrderContainerOPP where order__id=9000000000020454) where rownum <= 1

As you see the query is quite simple (trying to get the columns of a unique table), no join at all, so the entities related to this entity aren't implied.

Hibernate version:
3.1.3
Mapping documents:
<hibernate-mapping>
<class name="com.stuff.mrkOrderContainer" table="TmrkOrderContainerOPP" >
<id name="internalKey" >
<generator class="sequence">
<param name="sequence">seq_tmrkordercontaineropp</param>
</generator>
</id>
<version name="versionCounter" column="version" type="int" />
<property name="creationDate" column="creationDate" update="false">
</property>
...some other properties...
<set name="mrkOrders" inverse="true" batch-size="200" access="field" cascade="all" >
<key column="mrkOrderContainer_id" foreign-key="FK_MRKORDER2"/>
<one-to-many class="com.stuff.MrkOrder" />
</set>
<many-to-one name="order" class="com.stuff.Order" column="order__id"
not-null="true" foreign-key="FK_MRKORDERCONTAINER2">
</many-to-one>
</class>
</hibernate-mapping>


In the hibernate.cfg.xml file I specify the usage of the cache:
<class-cache class="com.stuff.MrkOrderContainer"
usage="read-write"></class-cache>


Code between sessionFactory.openSession() and session.close():
The session is in a longer usage (is closed after the treatement of all loops).

public MrkOrderContainer getMrkOrderContainer(Order pOrder) {
Object mrkOrderContainer = null;

if(pOrder != null && pOrder.getInternalKey() != null) {
Session session = HibernateFactory.getCurrentSession();
try {
String sqlStmt = "select {mrkOrderContainer.*} from TmrkOrderContainerOPP mrkOrderContainer where mrkOrderContainer.order__id = ?";

SQLQuery sqlQuery = session.createSQLQuery(sqlStmt);
sqlQuery.addEntity("mrkOrderContainer", MrkOrderContainer.class);
sqlQuery.setLong(0,pOrder.getInternalKey());
sqlQuery.setCacheable(true);
sqlQuery.setMaxResults(1);
mrkOrderContainer = sqlQuery.uniqueResult();
mrkOrderContainer = sqlQuery.uniqueResult();
mrkOrderContainer = sqlQuery.uniqueResult();
}
catch(Exception e) {
HibernateFactory.setSessionToRollback();
Trace.severe("Exception", e);
}
}
return (MrkOrderContainer) mrkOrderContainer;
}

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle10
The generated SQL (show_sql=true):
For each loop, the following query, twice:
select * from (select internalKey, version, ..., order__id from TmrkOrderContainerOPP where order__id=9000000000020454) where rownum <= 1

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html