Hi,
Is this the right and efficient way to use pagination for a select query? I've seen in the generated query (by logger) that the query doesn't use ROWNUM, or any trick like that. The generated query is simply a SELECT statement, so I think that all the data is transfered to the Java side and only a portion of that is returned through getResultList().
EntityManager manager = ...
Query query = manager.createQuery("SELECT mo FROM MyObject mo");
query.setFirstResult(startPosition);
query.setMaxResults(pageSize);
return query.getResultList();
I didn't test it against Hibernate, but it claims (http://www.hibernate.org/hib_docs/entitymanager/reference/en/html_single/#d0e759) to translate the following piece of code to efficient native paged query:
Query q = em.createQuery("select cat from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.getResultList(); //return cats from the 20th position to 29th
Hibernate knows how to translate this limit query into the native SQL of your DBMS.