If I use session.getObject (MyClass.class, myid), then hibernate will use the 2nd level cache with no hit to the database.

However, if I use a HQL query "from MyClass where id in (:idList)" then it will always hit the database.

Is there a way with an idList to have the ability for hibernate to use the 2nd level cache instead of hitting the database all the time?

It also raises another question: why isn't there a method called

getObjects (Class clazzz, List<Serializable> idList) in the Session interface?

Dino

Do you use the query cache?

In hibernate.cfg.xml:
<property name="cache.use_query_cache">true</property>

And then for the query you wish to cache set it as cacheable:
session.createQuery("from MyClass where id in (:idList)").setCacheable(true)

I don't want to cache the actual query since the idList may vary. This makes caching the query impractical since the combination of the idList may be huge.[/quote]

HQL queries are not really "understood" by Hibernate at that level. The only thing that Hibernate can do is to convert the HQL to SQL and then let the database return the result. What you can do to improve performance a bit to use Query.iterate(). This will make Hibernate to issue a query that only selects the primary key in the main query. Then, if the entity is found in the second-level cache it is fully loaded from there. This strategy is fine as long as most of the entities can be found in the second-level cache. If not, Hibernate will issue additional select:s against the database to load the missing entities.

By the way... if you are sure that most entities are already in the second-level cache it may be even faster to simply iterate over the list and use Session.get() or Session.load() for each id. Session.load() is probably very fast if you have enabled proxies for MyClass since it will simply create a new proxy for it (never hitting the database). Which method that works out the best probably also depends on what you are going to do with the objects later on. For example, if you expect the proxies to become initialized or not.

[quote="nordborg"]HQL queries are not really "understood" by Hibernate at that level. The only thing that Hibernate can do is to convert the HQL to SQL and then let the database return the result. What you can do to improve performance a bit to use Query.iterate(). This will make Hibernate to issue a query that only selects the primary key in the main query. Then, if the entity is found in the second-level cache it is fully loaded from there. This strategy is fine as long as most of the entities can be found in the second-level cache. If not, Hibernate will issue additional select:s against the database to load the missing entities.

By the way... if you are sure that most entities are already in the second-level cache it may be even faster to simply iterate over the list and use Session.get() or Session.load() for each id. Session.load() is probably very fast if you have enabled proxies for MyClass since it will simply create a new proxy for it (never hitting the database). Which method that works out the best probably also depends on what you are going to do with the objects later on. For example, if you expect the proxies to become initialized or not.[/quote]

Ideally, there should be a session.getObjects(classzz, idList) method. This method should figure out what's in the cache or not and builts another idList to hit the database to fetch all entities not in the cache with a single select. This leads me to my original question: why isn't there a session.getObjects?