JQuery DataTables server-side pagination, sorting, search with Spring MVC + JPA

Question

I'm working on the porting of an old .NET web application to Java + Spring MVC + JPA.

And an old problem is raised up again. In this question JQuery DataTables server-side pagination I was looking for a .NET solution. And finally I found it.

Now I need to know if something similar exists for Java applications. I googled a bit, but I didn't find nothing realy complete...

Answer 1

I finally solved implementing an ad hoc Java library: DataTables Pagination.

Here you find my project jar: https://bitbucket.org/davioooh/datatablepager/

---

UPDATE (01 Sept. 2016): I recently updated my project to support also v 1.10.x

---

UPDATE (26 May 2017): I moved the project repo to GitHub.

Answer 2

There is a simple way of doing pagination using JPA. If you look at the javax.persistence.Query interface, you'll see that it has setFirstResult() and setMaxResult() methods. Using this in combination allows you to retrieve paged results for your query.

Some code to illustrate:

Query q = entityManager.createQuery("select f from Foo");
q.setFirstResult(10).setMaxResults(10);
List<Foo> foos = q.getResultList();

This will retrieve 10 results from the query starting at the 10th result.

For sorting you have a couple of approaches. You can either change the query dependent upon the field that needs to be sorted e.g

entityManager.createQuery("select f from Foo order by f.field1 asc").setFirstResult(10).setMaxResults(10).getResultList();

or

entityManager.createQuery("select f from Foo order by f.field2 asc").setFirstResult(10).setMaxResults(10).getResultList();

Alternatively you could look at the javax.persistence.CriteriaQuery API which might make things a bit more maintainable:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> query = cb.createQuery(Foo.class);
Root<Foo> root = query.from(Foo.class);
query.select(root).orderBy(cb.asc(root.get("theFieldToOrderBy")));
entityManager.createQuery(query).setFirstResult(10).setMaxResults(10).getResultList();

Search is a bit more difficult. There isn't a native way to search across all fields of an entity using JPA. You may need to take a look at something like Hibernate Search to help you with that. It should integrate fairly cleanly with the JPA API assuming you are using Hibernate as your persistence provider of choice.