I am trying to come up with the best strategy for handling reference data. We have several small read-only tables (10-100) records that are frequently accessed by the application. We need to be able to query those tables sometimes joined with other tables for sorting purposes, so the data has to be in the database. However, often times we just need to get a reference record based on a column value in the reference table, and it would be inefficient to go to the database every time.

I thought that second level cache would be the right solutoin for this. I made the reference objects immutable and specified cache usage="read-only". This worked as expected for cases where the object is looked up by it's primary key (the database was not accessed). However, when I run a query against the reference table as show below, Hibernate still goes to the database and seems to create a new object from the result set.

Can someone recommend the best approach? Obviously I can just preload all records from the reference table at startup and iterate them in memory instead of the database, but that's coding something that I feel I should be able to get out of Hibernate caching.

In the example below Institution object has a primary key id, and a property station number, which I'm trying to query.

Hibernate version: 3.0.3

Mapping documents:
<class
name="Institution"
table="INSTITUTIONS"
mutable="false"
>
<cache usage="read-only"/>
...

Code between sessionFactory.openSession() and session.close():
System.out.println("Testing query");
getSession().clear();
query = getSession().createQuery("from Institution i where i.stationNumber = :number");
query.setParameter("number", institution.getStationNumber());
secondInstitution = (Institution)query.list().get(0);
assertNotNull(secondInstitution);
System.out.println("Got institution through a query with name: " + secondInstitution.getName());


Name and version of the database you are using:
Oracle 9i

Debug level Hibernate log excerpt:
17:18:11,673 DEBUG org.hibernate.hql.ast.QueryTranslatorImpl:46 - HQL: from Institution i where i.stationNumber = :number
17:18:11,673 DEBUG org.hibernate.hql.ast.QueryTranslatorImpl:46 - SQL: select institutio0_.INSTITUTION_ID as INSTITUT1_, institutio0_.VERSION as VERSION3_, institutio0_.NAME as NAME3_, institutio0_.VISTA_NAME as VISTA4_3_, institutio0_.STATION_NUMBER as STATION5_3_, institutio0_.CREATED_DATE as CREATED6_3_, institutio0_.MODIFIED_DATE as MODIFIED7_3_, institutio0_.ARCHIVED_DATE as ARCHIVED8_3_, institutio0_.PARENT_INSTITUTION_ID as PARENT9_3_ from INSTITUTIONS institutio0_ where institutio0_.STATION_NUMBER=?
17:18:11,673 DEBUG org.hibernate.hql.ast.ErrorCounter:46 - throwQueryException() : no errors
17:18:11,673 DEBUG org.hibernate.jdbc.AbstractBatcher:46 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
Hibernate: select institutio0_.INSTITUTION_ID as INSTITUT1_, institutio0_.VERSION as VERSION3_, institutio0_.NAME as NAME3_, institutio0_.VISTA_NAME as VISTA4_3_, institutio0_.STATION_NUMBER as STATION5_3_, institutio0_.CREATED_DATE as CREATED6_3_, institutio0_.MODIFIED_DATE as MODIFIED7_3_, institutio0_.ARCHIVED_DATE as ARCHIVED8_3_, institutio0_.PARENT_INSTITUTION_ID as PARENT9_3_ from INSTITUTIONS institutio0_ where institutio0_.STATION_NUMBER=?
17:18:11,673 DEBUG org.hibernate.jdbc.AbstractBatcher:38 - preparing statement
17:18:11,683 DEBUG org.hibernate.loader.hql.QueryLoader:46 - bindNamedParameters() 503 -> number [1]
17:18:11,703 DEBUG org.hibernate.jdbc.AbstractBatcher:46 - about to open ResultSet (open ResultSets: 0, globally: 0)
17:18:11,703 DEBUG org.hibernate.loader.Loader:38 - processing result set
17:18:11,703 DEBUG org.hibernate.loader.Loader:46 - result set row: 0
17:18:11,703 DEBUG org.hibernate.loader.Loader:46 - result row: EntityKey[gov.va.med.domain.model.Institution#1000110]
17:18:11,703 DEBUG org.hibernate.loader.Loader:38 - Initializing object from ResultSet: [gov.va.med.domain.model.Institution#1000110]
17:18:11,713 DEBUG org.hibernate.persister.entity.BasicEntityPersister:38 - Hydrating entity: [gov.va.med.domain.model.Institution#1000110]
17:18:11,713 DEBUG org.hibernate.engine.TwoPhaseLoad:38 - Version: 0
17:18:11,713 DEBUG org.hibernate.loader.Loader:38 - done processing result set (1 rows)
17:18:11,713 DEBUG org.hibernate.jdbc.AbstractBatcher:46 - about to close ResultSet (open ResultSets: 1, globally: 1)
17:18:11,713 DEBUG org.hibernate.jdbc.AbstractBatcher:46 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
17:18:11,783 DEBUG org.hibernate.jdbc.AbstractBatcher:38 - closing statement
17:18:11,783 DEBUG org.hibernate.loader.Loader:38 - total objects hydrated: 1