Sanjay, If you want only one query to accomplish this, I doubt this is possible with JPA. You might expreriment with eager collection loading or fetch groups but I expect it to issue multiple SQL queries.
Regards, Milosz > 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. > > -sanjay > > -----Original Message----- > From: Miłosz Tylenda [mailto:mtyle...@o2.pl] > Sent: Thursday, July 30, 2009 11:02 AM > To: users@openjpa.apache.org > 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 layer. > > > > 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. > > Cheers, > Milosz > >
