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

1. Introduction

1.绪论

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

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

首先，我们要看一下我们要查询的表，以及我们要复制的SQL查询。

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

2.测试数据

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

3.SQL的作用

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

用SQL，我们可以写一个类似这样的查询。

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

4. JPA Setup

4.JPA设置

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

在JPA中，我们首先需要一个实体来映射我们的表。

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

接下来我们需要一个方法来封装我们的查询代码，在这里实现为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.

在我们的存储库方法中，我们使用EntityManager创建一个Query，在此基础上我们调用setMaxResults（）方法。

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

这种对Query#setMaxResults的调用最终将导致限制语句附加到生成的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

5.使用Spring Data JPA

We can also generate our SQL using Spring Data JPA.

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

5.1. first or top

5.1.第一或顶部

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

我们可以采取的一种方法是使用方法名称派生，关键词为first或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来包装结果。

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

5.2. Pageable

5.2 可页式

Alternatively, we can use a Pageable object:

另外，我们可以使用一个Pageable对象。

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:

如果我们看一下JpaRepository,的默认实现SimpleJpaRepository，我们可以看到，它也调用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

5.3.比较

Both of these alternatives will produce the SQL that we’re after, with first and top favoring convention and Pageable favoring configuration:

这两种方法都会产生我们想要的SQL，其中first和top倾向于惯例，而Pageable倾向于配置。

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 ?

6. Conclusion

6.结论

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

在JPA中限制查询结果与SQL稍有不同；我们没有在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方法名称中包括关键字first或top。

As always, the code is available over on GitHub.

像往常一样，代码可在GitHub上获得。