Pagination and Sorting Spring JPA @NamedQueries

Since we added our nifty Like Buttons to NixMash Spring Posts we want to add the ability for users to view a listing of their Liked Posts. And we want that list to support paging and sorting.

We talked about our Spring JPA @NamedQuery in performing a SQL “SELECT IN” from a Secondary Table in an earlier post. Here's that query, which supports neither paging or sorting.

A Word on @NamedQuery Pagination

If we were going to use a standard JPA @Query approach to this issue we'd add the statement to our Post Repository.

Then we'd create a PageRequest in our Service Layer which includes our Sorting logic and pass that to the repository, along with our List of postIds.

We really want to use our JPA @NamedQuery, but there seems to be a tiny issue with using PageRequest objects with @NamedQueries: JPA NAMED QUERIES DO NOT SUPPORT PAGING OR SORTING.

If we dig into the Spring Data JPA NamedQuery source code we'll find exception messages like this when paging and sorting parameters are used.

That's cool, and certainly not a problem. You'll see how we add both paging and sorting with very little fuss.

Sorting

For sorting, we're going to add that to our @NamedQuery statement with ORDER BY.

Done! Now onto Paging.

Pagination

We're going to add pagination to our query results with two simple lines of code, and what's more we'll be able to call our Post Service Method with the same PageNumber and PageSize parameters as if using a JPA PageRequest object. Notice the setFirstResult() and setMaxResults() TypedQuery methods? Whoop, there it is!

Testing

To prove that we're pulling back paged results, here's our test. We confirm that we retrieve 2 records in our page size and that our dates are in descending order.

Source Code Notes for this Post

All source code discussed in this post can be found in my NixMash Spring GitHub repo and viewed online here.

Posted July 22, 2016 03:42 PM EDT

More Like This Post