Spring JPA By Example With Oracle Database Free: Paging, Sorting and Filtering

Spring JPA’s abstractions for paging, sorting, and filtering enable developers to write methods that simplify querying the persistence layer. These features are essential for efficiently managing large datasets and improving application performance.

Paging breaks down large result sets into smaller, manageable chunks, while sorting enables ordering data based on specific criteria. Filtering helps narrow down results based on specific conditions.

In this article, we’ll walk through examples using Oracle Database Free that combine, mix, and match these strategies to facilitate efficient data retrieval and presentation using Spring JPA.

Want to jump right to the code? Check out my spring-jpa module on GitHub.

Defining a schema for paging, sorting and filtering

We’ll use the books schema to test paging, sorting, and filtering with Spring JPA. Although simple, this schema’s JPA entities and relationships provide a solid foundation for the examples we’ll explore later.

If you’d like a primer on how JPA manages entities and relationships, I suggest reading my prior post, Learn Spring JPA by Example: Entity Relationships.

JPA Entities

Below are the JPA entities we’ll be using — You can find the full entity definitions here.

// author
@Entity
@Table(name = "author")
@Getter
@Setter
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "author_id")
    private Long authorId;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(name = "birth_year")
    private Integer birthYear;

    private String bio;

    @OneToMany(
            mappedBy = "author",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Book> books;
}

// book
@Entity
@Table(name = "book")
@Getter
@Setter
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "book_id")
    private Long bookId;

    @Column(nullable = false, length = 200)
    private String title;

    private Integer pages;

    @Column(name = "published_year")
    private Integer publishedYear;

    // Lazy Loading is used to only retrieve authors when requested
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id", nullable = false)
    private Author author;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "genre_id")
    private BookGenre genre;
}

// book genre
@Entity
@Table(name = "book_genre")
@Getter
@Setter
public class BookGenre {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "genre_id")
    private Long genreId;

    @Column(name = "genre_name", nullable = false, unique = true, length = 50)
    private String genreName;
}        

You can find the corresponding JPA repositories for these entities on GitHub. We’ll cover the repository definitions and their methods in detail as we work through each example.

Trying things out

The PagingSortingFilteringTest class contains several examples that demonstrate paging, sorting, and filtering. The test uses Testcontainers to instantiate a throwaway Oracle Database Free container.

You can run the test using Maven from the root of the spring-jpa module, provided you have a Docker-compatible environment:

mvn test -Dtest=PagingSortingFilteringTest

The spring-jpa module sets the spring.jpa.show-sql property to true, which prints out JPA-generated queries to the console. I find this useful when debugging, but you should disable this property in production to avoid excessive or unwanted logging.

Paging and PageRequest

The pagingExample test case demonstrates how to query books using the PageRequest class, which is used as a parameter in repository methods. PageRequest’s various static factory methods allow the configuration of page offset, size, and other parameters.

@Test
void pagingExample() {
    // Request the first page of the dataset, with two records per page.
    // As the query page is iterated through, hibernate issues one query per page until
    // the record set has been fully traversed.
    Pageable prFirstPage = PageRequest.of(0, 2);
    Page<Book> books = bookRepository.findAll(prFirstPage);
    // 16 books in the sample dataset, should be 8 pages
    assertThat(books.getTotalPages()).isEqualTo(books.getTotalElements() / 2);
    // Should be on the first page (0-indexed)
    assertThat(books.getNumber()).isEqualTo(0);

    // Request the second page, using the same page size.
    Pageable prSecondPage = PageRequest.of(1, 2);
    books = bookRepository.findAll(prSecondPage);
    // Should be on the second page
    assertThat(books.getNumber()).isEqualTo(1);

    // What happens if we access non-existent page?
    Pageable prOutOfBounds = PageRequest.of(9, 5);
    books = bookRepository.findAll(prOutOfBounds);
    // Empty page is returned
    assertThat(books.getNumberOfElements()).isEqualTo(0);
}        

The generated SQL query for paging will look something like this, and should be visible in the test output:

select b1_0.book_id,b1_0.author_id,b1_0.genre_id,b1_0.pages,b1_0.published_year,b1_0.title 
from book b1_0 offset ? rows 
fetch first ? rows only        

Sorting with PageRequest

Entity field-level sorting can also be done with PageRequests. The sortingExample test case shows how to sort books by one or more fields.

When books are sorted by author name, a join on the author table is required. Because the book-author relationship is lazily loaded, we apply the @Transactional annotation on the test method for a persistent database session.

@Test
// For Lazy Loading on joined Author when sorting by author name
@Transactional
void sortingExample() {
    // Sort by book title in ascending order
    PageRequest prSortedByTitle = PageRequest.of(
            0,
            10,
            Sort.by(Sort.Order.asc("title"))
    );
    Page<Book> sortedByTitle = bookRepository.findAll(prSortedByTitle);

    // Verify sorted according to title
    assertThat(sortedByTitle.getContent()).isSortedAccordingTo(
            Comparator.comparing(Book::getTitle)
    );

    // Sort by multiple properties
    PageRequest pageRequest = PageRequest.of(0, 10, Sort.by(
            Sort.Order.asc("author.name"),
            Sort.Order.desc("publishedYear")
    ));
    Page<Book> books = bookRepository.findAll(pageRequest);

    // Verify sorted by author name and descending published year
    assertThat(books.getContent()).isSortedAccordingTo(
            Comparator.comparing((Book b) -> b.getAuthor().getName())
                    .thenComparing(Book::getPublishedYear, Comparator.reverseOrder())
    );
}        

The respective, generated SQL queries that sort by title and author name + publishedYear look like so:

-- Sort by title
select b1_0.book_id,b1_0.author_id,b1_0.genre_id,b1_0.pages,b1_0.published_year,b1_0.title
from book b1_0
order by b1_0.title
offset ? rows
fetch first ? rows only

-- Sort by author name and published year
select b1_0.book_id,b1_0.author_id,b1_0.genre_id,b1_0.pages,b1_0.published_year,b1_0.title
from book b1_0
left join author a1_0 on a1_0.author_id=b1_0.author_id
order by a1_0.name,b1_0.published_year desc
offset ? rows
fetch first ? rows only        

JPA Query Methods

Spring JPA allows the definition of repository Query Methods where the SQL query is derived from the method name. In the book repository, we can retrieve the first book ordered by title using the following method:

public interface BookRepository extends JpaRepository<Book, Long> {
    Optional<Book> findFirstByOrderByTitleAsc();
}        

Let’s take a look at using query methods in the methodQueryExample test case. We use the previous method query to find the first book sorted by title:

@Test
void methodQueryExample() {
    // Use a method name query generator to get the first book sorted by title
    Optional<Book> book = bookRepository.findFirstByOrderByTitleAsc();

    assertTrue(book.isPresent());
    // Equal Rites is the first book in alphabetical order from the sample dataset
    assertThat(book.get().getTitle()).isEqualTo("Equal Rites");
}        

The corresponding query is run against the database:

select b1_0.book_id,b1_0.author_id,b1_0.genre_id,b1_0.pages,b1_0.published_year,b1_0.title
from book b1_0
order by b1_0.title
fetch first ? rows only        

Method queries are helpful for quickly writing simple queries, but are cumbersome for longer queries — Method names can become excessively long and unreadable when queries involve multiple predicates.

JPA @Query Annotation

When query methods don’t meet your needs, you can use the @Query annotation as an alternative. Applying this annotation to repository methods allows developers to write queries SQL queries without relying exclusively on JPA query generation.

The Author repository provides a few examples of this of varying styles:

// Note the use of the Author entity and the JPA size() function
@Query("select a from Author a where size(a.books) > :bookCount")
List<Author> findAuthorsWithMoreThanXBooksJPASyntax(@Param("bookCount") int bookCount);

// Identical results to the previous query, but using a more literal SQL syntax
@Query("""
    select distinct a from Author a
    join Book b on b.author.authorId = a.authorId
    group by a
    having count(b) > :bookCount
""")
List<Author> findAuthorsWithMoreThanXBooksSQLSyntax(@Param("bookCount") int bookCount);

// A more complex query, combining paging, sorting, and filtering
@Query("""
    select distinct a from Author a
    join Book b on b.author.authorId = a.authorId
    where a.name like 'T%'
    group by a
    having count(b) > :bookCount
    order by a.birthYear
""")
Page<Author> moreThanXBooksNameStartsWithTSortedByBirthYear(
        @Param("bookCount") int bookCount,
        Pageable pageable);        

Let’s try it out the first two in the jpaSQLQueryExample test case. The results to both queries should be the same:

@Test
void jpaSQLQueryExample() {
    List<Author> authors = authorRepository.findAuthorsWithMoreThanXBooksJPASyntax(4);
    assertThat(authors).hasSize(2);

    authors = authorRepository.findAuthorsWithMoreThanXBooksSQLSyntax(4);
    assertThat(authors).hasSize(2);
}        

Running the test case, we see the following SQL queries were generated, which align with our @Query annotated repository methods:

-- findAuthorsWithMoreThanXBooksJPASyntax
-- In this case, the JPA size() function translates to a subquery.
select a1_0.author_id,a1_0.bio,a1_0.birth_year,a1_0.name
from author a1_0
where (
  select count(*)
  from book b1_0
  where a1_0.author_id=b1_0.author_id
) > ?

-- findAuthorsWithMoreThanXBooksSQLSyntax
-- After the entity conversions, the @Query SQL is directly applied.
select distinct a1_0.author_id,a1_0.bio,a1_0.birth_year,a1_0.name
from author a1_0
join book b1_0 on b1_0.author_id=a1_0.author_id
group by a1_0.author_id,a1_0.bio,a1_0.birth_year,a1_0.name
having count(b1_0.book_id) > ?        

The @Query annotation provides a powerful method for developers to customize repository methods with a SQL syntax when query methods aren’t applicable.

@Query annotated methods have one major drawback — lack of portability. If your application targets different databases (e.g., Oracle and MySQL), the @Query annotation may not suitable.

Specification Queries

Perhaps the most dynamic way to implement queries is through specifications with the criteria API, introduced in JPA 2.0. Specifications allow you to dynamically build queries programmatically, typically beginning with a static specification factory.

To use specifications, your repository should extend the JpaSpecificationExecutor<T> interface:

public interface AuthorRepository extends JpaRepository<Author, Long>, JpaSpecificationExecutor<Author> {
  // methods
}        

Next, you can begin writing specifications. We’ll implement an author specification that finds all authors with X or more books. Initially, the Criteria API can be challenging to grasp, but SQL concepts like joins, groupings, and predicates are rapidly applied to the Java API:

import com.example.paging.model.Author;
import com.example.paging.model.Book;
import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.JoinType;
import org.springframework.data.jpa.domain.Specification;

public final class AuthorSpecifications {
    public static Specification<Author> hasBookCountGreaterThanX(Long countThreshold) {
        return (root, query, criteriaBuilder) -> {
            Join<Author, Book> bookJoin = root.join("books", JoinType.LEFT);
            query.groupBy(root);
            query.having(criteriaBuilder.gt(criteriaBuilder.count(bookJoin), countThreshold));

            return query.getRestriction();
        };
    }
}        

The specificationQueryExample test case shows how to run specifications with the author repository. Note that the specification factory method returns dynamic specification which we may update with additional predicates — in this case, authors whose name begins with ‘T’.

@Test
@Transactional
void specificationQueryExample() {
    // Specification query for authors with 4 or more books
    List<Author> authors = authorRepository.findAll(hasBookCountGreaterThanX(4L));
    assertThat(authors).hasSize(2);

    // We may take an existing specification query and modify it with additional criteria,
    // combining paging, sorting, and filtering.
    // The authors with 2 or more books are fetched with the following conditions:
    // - at most 5 authors per page
    // - authors are sorted by birth year
    // - authors are filtered by name beginning with 'T'
    Pageable pageable = PageRequest.of(0, 5, Sort.by("birthYear"));
    Page<Author> authorsSortedByBirthYearNameStartingWithT = authorRepository.findAll(
            hasBookCountGreaterThanX(2L).and((root, query, cb) ->
                    cb.like(root.get("name"), "T%")),
            pageable
    );

    // Verify the predicates from the specification query
    assertThat(authorsSortedByBirthYearNameStartingWithT.getContent()).allMatch(author ->
            author.getBooks().size() > 2 && author.getName().startsWith("T"));
    assertThat(authorsSortedByBirthYearNameStartingWithT.getContent())
            .isSortedAccordingTo(Comparator.comparing(Author::getName));
}        

Specifications in JPA offer great flexibility for creating dynamic queries, allowing you to build and modify queries programmatically based on various inputs, making them ideal for complex, user-driven functionality like search filters in REST APIs.

Specifications comes with increased complexity due to the use of the criteria API. For simpler scenarios, method queries may be easier to write and understand, making them a better choice when your querying needs are straightforward and don’t require the dynamic capabilities of specifications.

Use specifications when you need highly flexible, dynamic querying. Opt for method queries or @Query annotated methods when your requirements are simpler or static.