openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Patel, Sanjay " <sapa...@NEMOURS.ORG>
Subject RE: setMaxResults does not return specified number of records
Date Thu, 30 Jul 2009 17:08:06 GMT
Thanks for reply Milosz,

how my query will be so it will return 25 records from TABLE_A with collection_of_b populated
in each record.


-----Original Message-----
From: MiƂosz Tylenda [] 
Sent: Thursday, July 30, 2009 11:02 AM
Subject: Re: setMaxResults does not return specified number of records

Hi Sanjay,

> my query is like this.
> String queryString = "SELECT a FROM Table_A a LEFT JOIN FETCH
> a.collection_of_b WHERE my_condition
> Query q = em.createQuery(queryString);
> 		q.setFirstResult(0);
> 		q.setMaxResults(25);
> q.getResultList()
> above query does not return 25 records but it returns random number of
> records.

Unfortunately you can't rely on using setFirstResult/setMaxResults with fetch joins. The JPA
1 spec indicates it:

"The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over
collections is undefined."

The reason might become clear when you look at the generated SQL. It would be difficult (or
even impossible) to apply correct row limiting SQL syntax to such a query as the number of
rows in SQL terms does not easily translate to number or rows you would expect in the JPA

> If I remove FETCH and just do LEFT JOIN, instead of returning
> collection_of_b, it returns 25 but duplicate records.
That's most probably correct. This works the same as SQL: if you join table A with table B
and return only A columns, you will get as many A rows as there are rows in B. You might want
to use DISTINCT in this case.


View raw message