Limiting Query Results With JPA and Spring Data JPA

1. Introduction

In this tutorial, we’re going to learn about limiting query results with JPA and Spring Data JPA.

First, we’ll take a look at the table we want to query, as well as the SQL query we want to reproduce.

Then we’ll dive right into how to achieve that with JPA and Spring Data JPA.

Let’s get started!

2. The Test Data

Below we have the table that we’ll be querying throughout this article.

The question we want to answer is, “What is the first occupied seat and who is occupying it?”

3. The SQL

With SQL, we might write a query that looks something like this:

SELECT firstName, lastName, seatNumber FROM passengers ORDER BY seatNumber LIMIT 1;

4. JPA Setup

With JPA, we need an Entity first to map our table:

@Entity
class Passenger {

    @Id
    @GeneratedValue
    @Column(nullable = false)
    private Long id;

    @Basic(optional = false)
    @Column(nullable = false)
    private String firstName;

    @Basic(optional = false)
    @Column(nullable = false)
    private String lastName;

    @Basic(optional = false)
    @Column(nullable = false)
    private int seatNumber;

    // constructor, getters etc.
}

Next, we need a method which encapsulates our query code, implemented here as PassengerRepositoryImpl#findOrderedBySeatNumberLimitedTo(int limit):

@Repository
class PassengerRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Passenger> findOrderedBySeatNumberLimitedTo(int limit) {
        return entityManager.createQuery("SELECT p FROM Passenger p ORDER BY p.seatNumber",
          Passenger.class).setMaxResults(limit).getResultList();
    }
}

In our repository method, we use the EntityManager to create a Query on which we call the setMaxResults() method.

This call to Query#setMaxResults will eventually result in the limit statement appended to the generated SQL:

select
  passenger0_.id as id1_15_,
  passenger0_.first_name as first_nam2_15_,
  passenger0_.last_name as last_nam3_15_,
  passenger0_.seat_number as seat_num4_15_
from passenger passenger0_ order by passenger0_.seat_number limit ?

5. With Spring Data JPA

We can also generate our SQL using Spring Data JPA.

5.1. first or top

One way we could approach this is by using method name derivation with the keywords first or top.

We can, optionally, specify a number as the maximum result size that will be returned. If we omit it, Spring Data JPA assumes a result size of 1.

Since we want to know which was the first seat to be occupied and who is occupying it, we can get it by omitting the number in these two ways:

Passenger findFirstByOrderBySeatNumberAsc();
Passenger findTopByOrderBySeatNumberAsc();

If we limit to one instance result, as above, then we can also wrap the result using Optional:

Optional<Passenger> findFirstByOrderBySeatNumberAsc();
Optional<Passenger> findTopByOrderBySeatNumberAsc();

5.2. Pageable

Alternatively, we can use a Pageable object:

Page<Passenger> page = repository.findAll(
  PageRequest.of(0, 1, Sort.by(Sort.Direction.ASC, "seatNumber")));

If we take a look at the default implementation of JpaRepository, the SimpleJpaRepository, we can see that it also calls Query#setMaxResults:

protected <S extends T > Page < S > readPage(TypedQuery < S > query, 
  Class < S > domainClass, Pageable pageable,
  @Nullable Specification < S > spec) {
    if (pageable.isPaged()) {
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
    }

    return PageableExecutionUtils.getPage(query.getResultList(), pageable, () -> {
        return executeCountQuery(this.getCountQuery(spec, domainClass));
    });
}

5.3. Using Limit

Spring Data JPA version 3.2 provides a new type named Limit to limit the number of results returned from a query. It has two static methods – of() and unlimited().

The of() method accepts an integer as an argument.  It limits query results to the given maximum size. This allows fetching only the specified number of results.

Next, the unlimited() method returns all the matching results from a query. it doesn’t restrict the number of results.

Let’s use the of() method from Limit to limit the query result to 1:

List<Passenger> passenger = repository.findByOrderBySeatNumberAsc(Limit.of(1));

Here, we invoke the static method of() on Limit to return 1 result based on our specification.

5.4. Comparison

These alternatives produce the SQL that we’re after, with first and top favoring convention, Pageable favoring configuration, and Limit providing a simple method call:

select
  passenger0_.id as id1_15_,
  passenger0_.first_name as first_nam2_15_,
  passenger0_.last_name as last_nam3_15_,
  passenger0_.seat_number as seat_num4_15_ 
from passenger passenger0_ order by passenger0_.seat_number asc limit ?

6. Conclusion

Limiting query results in JPA is slightly different from SQL; we don’t include the limit keyword directly into our JPQL.

Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. Also, with Spring Data JPA version 3.2 and above, we can use the Limit interface to limit query results by calling its static of() method to specify the maximum size.