Limiting Query Results with JPA and Spring Data JPA – 用JPA和Spring Data JPA限制查询结果

最后修改: 2018年 11月 10日

1. Introduction


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

在本教程中,我们将学习使用JPASpring 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.

然后我们将直接探讨如何通过JPA和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:


class Passenger {

    @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):

接下来我们需要一个方法来封装我们的查询代码,在这里实现为PassengerRepositoryImpl#findOrderedBySeatNumberLimitedTo(int limit)

class PassengerRepositoryImpl {

    private EntityManager entityManager;

    public List<Passenger> findOrderedBySeatNumberLimitedTo(int limit) {
        return entityManager.createQuery("SELECT p FROM Passenger p ORDER BY p.seatNumber",

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

5.使用Spring Data JPA

We can also generate our SQL using Spring Data JPA.

我们也可以使用Spring Data JPA生成我们的SQL。

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.

我们可以选择性地指定一个数字作为将被返回的最大结果大小。如果我们省略它,Spring Data JPA会假定结果大小为1。

Since we want to know which was the first seat to be occupied 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

5.2 可页式。

Alternatively, we can use a Pageable object:


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

If we take a look at the default implementation of JpaRepository, the SimpleJpaRepositorywe 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());

    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’re after, with first and top favoring convention and Pageable favoring configuration:


select 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 ?

6. Conclusion


Limiting query results in JPA is slightly different to 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.

相反,我们只需对Query#maxResults进行一次方法调用,或者在我们的Spring Data JPA方法名称中包括关键字firsttop

As always, the code is available over on GitHub.