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.
For sorting, we’re going to add that to our @NamedQuery statement with ORDER BY.
Done! Now onto Paging.
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!
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.