Jpa paging with numbers and next, previous

Question

I apologize for asking this question if someone already asked it and for asking a perhaps dumb question but I am new to this and you are experts and I would love to learn from your expert advice and experience.

I want to add paging to an application displaying 200 records per page. The bottom of the page should have numbers. If there are 1050, the first page will display 100 and the bottom of the page will show numbers 1,2,3,4,5 & 6.

What is the general logic to accomplish this? I know that the database must select 200 every time and I must keep track of the first record.

1. Is there a way to know how many records will be returned total so that I can know how many numbers to display on the bottom of the page? Does it require selecting a count() statement or something else?
2. For the 1050 records, The numbers 1,2,3,4,5 & 6 will display and clicking each one requires a call to the server. Is there a way to know how many records will be returned in the next call to the server? Does it require selecting a count() statement or something else?

Presa

Thanks everyone for taking the time to answer my question. I will update my code and will let you know if I have further questions about this. – Presa

Answer 1

You can use the JPA Criteria API to accomplish this. Assuming TypedQuery, you would use setFirstResult and setLastResult to limit the records returned from the database. Of course the values for these methods will be dependent on what page was requested, and how many records are getting displayed per page.

first = (page - 1) * page size;
last = (page * size) - 1;

Note: This assumes the first page is 1 (as opposed to zero).

To get a record count you execute a standard Criteria query. As an example:

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
countQuery.select(builder.count(countQuery.from(MyEntity.class)));
final Long count = entityManager.createQuery(countQuery)
        .getSingleResult();

You can customize the above code to perform the count relative to another query as well. Finally, you need some way of communicating the total count back to the client. One way of doing this is wrapping the result set of your query in another object that contains an attribute for the count, or returns said object as a result of your DAO call. Or alternatively, you could store the count attribute in an object in your request scope.

public class ResultListWrapper<T> {
    private Long count;
    private Collection<T> results;

    // constructor, getters, setters
}

Heliotherapy

Answer 2

You will usually perform the same query except using count int the select list instead of the columns prior running the actual query to find how many pages there are. Getting a specific page in hibernate can then be done with something like:

int maxRecords = // page size ...
int startRow = // page number * page size
Query query = session.createQuery('...');
query.setMaxResults(maxRecords);
query.setFirstResult(startRow);

If performing the extra query is too expensive an operation then you could consider just providing next/previous links or alternatively providing extra pages as needed (e.g. As the last of the loaded data comes into view) via ajax.

For displaying paged results including showing page number links I would suggest (assuming you are using jsp pages to display this data) using the display tag JSP tag library. Display tag handles paging and sorting display of tabular data assuming you can get it to the JSP.

Jayjaycee

Answer 3

Yes, you have to do a select count before getting the rows themselves.

Ephialtes

Answer 4

Have a look at Spring Data JPA. It's basically working how Perception suggested earlier, just that you don't have to do it, the framework does that for you. You get paging and sorting by default (you still need to do filtering yourself if you don't want to use Query DSL).

They have a 1h video on the main page that's showing a quick intro, but you'll only need to watch about 15 mins to get an idea of what id does.

Classieclassification