Limiting Query Results with JPA and Spring Data JPA

Table of Contents

1. Introduction
2. The Test Data
3. The SQL
4. JPA Setup
5. With Spring Data JPA
6. Conclusion

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?”.

First Name	Last Name	Seat Number
Jill	Smith	50
Eve	Jackson	94
Fred	Bloggs	22
Ricki	Bobbie	36
Siya	Kolisi	85

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 fistName;

    @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_.fist_name as fist_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.

Remembering that we want to know what is the first occupied seat and who is occupying it, we can get it 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. Comparison

Both of these alternatives will produce the SQL that we are after:

select
  passenger0_.id as id1_15_,
  passenger0_.fist_name as fist_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 ?

With first and top favoring convention and Pageable favoring configuration.