Hi,

I have been using JPA 2.0 with hibernate as the vendor for my web application. For one of the database queries, I am using the JPA criteria builder API to construct the dynamic queries . I have been able to get it to work as expected.

The query has a fetch join between two tables A & B. A has a OneToMany relationship with B.
Join<A,B> ordLeg = ord.joinSet("bLegs", JoinType.INNER);
ord.fetch("bLegs",JoinType.INNER);

I also have used the setMaxResults to restrict the no. of records returned to the user
typedQuery.setMaxResults(10);

FYI, there are around 400 records in the database.

When I see the result of the query in the logs, I see that it loads all the 400 records in the memory. However, it returns only 20 records to the user since Maxresults is set.

When analyzed further, found out that hibernate has hints that can be given to fetch records in batches rather than loading all the 400 records in the memory.
TypedQuery<A> typedQuery = entityManager.createQuery(query);
typedQuery.setHint("org.hibernate.fetchSize", 5);
List<A> orderList = (List<A>) typedQuery.getResultList();

But it does not seem to take the hint at all. I am still seeing that it loads all 400 records in the memory and no batch fetching is done. I am expecting it to fetch the 10 (maxResults ) records in 2 batches of 5. Am i missing something ?\

It will be a severe performance hit if there are more than 1000 records in the DB for the specified query.

I even tried setting the following property in the persistence.xml level but in vain.
<properties>
<property name="hibernate.jdbc.batch_size" value="5"/>
<property name="hibernate.jdbc.fetch_size" value="5">
</properties>

Can you please help me out in the right direction ?

Hibernate Version: I have tried 3.6.4.Final, 4.0.0.Final & 4.2.1.Final

Appreciate your help.

Thanks,
Santhosh