Returning large sets of data from your queries is an issue for many applications. It is virtually impossible to display a huge entire result in a single page so applications should be able to display a range of a
result set and provide users with the ability to control the range of data that they are viewing.
The most common way to solve this issue is to paginate the data and use a Client interface to
navigate through the results, also known as pagination.
When using plain JDBC, there is the concept of Scrollable result sets, which can be navigated forward and backward as required:
Statement stmt=con.createStatement ( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); ResultSet rs=stmt.executeQuery (“select * from Customers”);
In Entity EJB, the Query and TypedQuery interfaces provide support for pagination via the setFirstResult() and setMaxResults() methods. These methods can be used specify the first result to be received and the maximum number of results to return relative to that point.
WARNING: The setFirstResult() and setMaxResults() methods should not be used with queries that join across collection relationships (one-to-many and many-to-many) because these queries may return duplicate values. The duplicate values in the result set make it impossible to use a logical result position.
Here is an example, supposing you want to retrieve only the first “page” of 100 Users:
List <Users> tasklist = em.createNamedQuery("findUsersByRole") .setParameter("role", role) .setMaxResults(100) .setFirstResult(0) .getResultList();
You could further optimize the query with “setFetchSize” which sets a fetch size for the underlying JDBC query.