In our application we have a search screen with a couple of filter fields. When you click the search button a query is executed that fetches a couple of results and lists them in a grid.
There can be however a lot of data so we want to limit the amount of returned results to a maximum of 1000. We are using OpenJPA to map the return records back to our model classes and SQL server as the database engine. For a while I have limited the amount of returned results using this code:
return entityManager.createQuery(query).setMaxResults(1000).getResultList();
This worked great until I recently added a couple of joins to the original query.
Let's say for example that you would like to get 10 "Contacts". Each contact has 5 addresses. When I perform the following query:
select c from Contact c left join fetch c.address
The actual query that is executed selects 50 records (5 rows per contact, 1 for each address). And this for the 10 contacts.
and limit it to 10 results and check the query that is executed against the database it is translated to "SELECT TOP 10 FROM ...".
This translates to 10 records, not 10 entities. So in my example the returned list of contacts would contain 2 contacts with their 5 addresses.
So that brings me to my question: What can I do to ensure the complete data of 10 entities is returned instead of the first 10 records?
I'll assume that a contact may not have exactly 5 addresses (cause if that were true the answer would be trivial).
You do not have a query construct in either SQL (as far as I know, though you should check your vendor's documentation) or JPQL that can make a query to do what you want. Your only option thus would be to fetch a page from the DB, like you're doing now, then count the number of returned contacts, and then fetch the next page if you need more, and so on until you have enough contacts pulled. To do that you would use setFirstResult as well as setMaxResults.