Spring Data JPA @Query – Spring Data JPA @Query

最后修改: 2018年 5月 2日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

Spring Data provides many ways to define a query that we can execute. One of these is the @Query annotation.

Spring Data提供了许多方法来定义我们可以执行的查询。其中之一就是@Query注解。

In this tutorial, we’ll demonstrate how to use the @Query annotation in Spring Data JPA to execute both JPQL and native SQL queries.

在本教程中，我们将演示如何在Spring Data JPA中使用@Query注解来执行JPQL和本地SQL查询。

We’ll also show how to build a dynamic query when the @Query annotation is not enough.

我们还将展示当@Query注解不够用时，如何建立一个动态查询。

2. Select Query

2.选择查询

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute.

为了定义要为Spring Data存储库方法执行的SQL，我们可以用@Query注解来标注方法–其value属性包含要执行的JPQL或SQL。

The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.

@Query注解优先于命名的查询，命名的查询是用@NamedQuery注解的，或者在orm.xml文件中定义。

It’s a good approach to place a query definition just above the method inside the repository rather than inside our domain model as named queries. The repository is responsible for persistence, so it’s a better place to store these definitions.

把查询定义放在资源库内的方法上面，而不是放在我们的领域模型内作为命名的查询，这是一个好办法。存储库负责持久化，所以它是存储这些定义的一个更好的地方。

2.1. JPQL

2.1. JPQL

By default, the query definition uses JPQL.

默认情况下，查询定义使用JPQL.。

Let’s look at a simple repository method that returns active User entities from the database:

让我们看看一个简单的资源库方法，它从数据库中返回活动的User实体。

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

2.2. Native

2.2.本土的

We can use also native SQL to define our query. All we have to do is set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation:

我们也可以使用本地SQL来定义我们的查询。我们要做的就是将nativeQuery属性的值设置为true并在注释的value属性中定义本地SQL查询。

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection<User> findAllActiveUsersNative();

3. Define Order in a Query

3.在查询中定义顺序

We can pass an additional parameter of type Sort to a Spring Data method declaration that has the @Query annotation. It’ll be translated into the ORDER BY clause that gets passed to the database.

我们可以向具有@Query注解的Spring Data方法声明传递一个Sort类型的额外参数。它将被翻译成ORDER BY子句，被传递给数据库。

3.1. Sorting for JPA Provided and Derived Methods

3.1.对JPA提供的和派生的方法进行排序

For the methods we get out of the box such as findAll(Sort) or the ones that are generated by parsing method signatures, we can only use object properties to define our sort:

对于我们开箱即得的方法，如findAll(Sort)或通过解析方法签名生成的方法，我们只能使用对象属性来定义我们的排序。

userRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));

Now imagine that we want to sort by the length of a name property:

现在想象一下，我们想通过一个名字属性的长度来排序。

userRepository.findAll(Sort.by("LENGTH(name)"));

When we execute the above code, we’ll receive an exception:

当我们执行上述代码时，我们会收到一个异常。

org.springframework.data.mapping.PropertyReferenceException: No property LENGTH(name) found for type User!

3.2. JPQL

3.2. JPQL

When we use JPQL for a query definition, then Spring Data can handle sorting without any problem — all we have to do is add a method parameter of type Sort:

当我们使用JPQL进行查询定义时，那么Spring Data可以毫无问题地处理排序问题–我们所要做的就是添加一个类型为Sort的方法参数。

@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort);

We can call this method and pass a Sort parameter, which will order the result by the name property of the User object:

我们可以调用这个方法并传递一个Sort参数，它将按照User对象的name属性对结果排序。

userRepository.findAllUsers(Sort.by("name"));

And because we used the @Query annotation, we can use the same method to get the sorted list of Users by the length of their names:

因为我们使用了@Query注解，所以我们可以使用同样的方法来获得按姓名长度排序的Users列表。

userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));

It’s crucial that we use JpaSort.unsafe() to create a Sort object instance.

我们使用JpaSort.unsafe()来创建一个Sort对象实例，这一点至关重要。

When we use:

当我们使用。

Sort.by("LENGTH(name)");

then we’ll receive exactly the same exception as we saw above for the findAll() method.

那么我们将收到与我们在上面看到的findAll()方法完全相同的异常。

When Spring Data discovers the unsafe Sort order for a method that uses the @Query annotation, then it just appends the sort clause to the query — it skips checking whether the property to sort by belongs to the domain model.

当Spring Data发现使用@Query注解的方法有不安全的Sort顺序时，它只是将排序子句附加到查询中–它跳过检查要排序的属性是否属于领域模型。

3.3. Native

3.3.本土的

When the @Query annotation uses native SQL, then it’s not possible to define a Sort.

当@Query注解使用本地SQL时，就不可能定义Sort。

If we do, we’ll receive an exception:

如果我们这样做，我们会收到一个例外。

org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination

As the exception says, the sort isn’t supported for native queries. The error message gives us a hint that pagination will cause an exception too.

正如异常所言，该排序不支持本地查询。错误信息给了我们一个提示：分页也会引起异常。

However, there is a workaround that enables pagination, and we’ll cover it in the next section.

然而，有一种变通方法可以实现分页，我们将在下一节介绍。

4. Pagination

4.分页法

Pagination allows us to return just a subset of a whole result in a Page. This is useful, for example, when navigating through several pages of data on a web page.

分页允许我们在一个Page中只返回整个结果的一个子集。例如，在浏览一个网页上的几页数据时，这很有用。

Another advantage of pagination is that the amount of data sent from server to client is minimized. By sending smaller pieces of data, we can generally see an improvement in performance.

分页的另一个好处是，从服务器发送至客户端的数据量被最小化。通过发送较小的数据片断，我们通常可以看到性能的改善。

4.1. JPQL

4.1. JPQL

Using pagination in the JPQL query definition is straightforward:

在JPQL查询定义中使用分页是很直接的。

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);

We can pass a PageRequest parameter to get a page of data.

我们可以通过一个PageRequest参数来获得一个页面的数据。

Pagination is also supported for native queries but requires a little bit of additional work.

分页也支持本地查询，但需要一点额外的工作。

4.2. Native

4.2.本土的

We can enable pagination for native queries by declaring an additional attribute countQuery.

我们可以通过声明一个额外的属性countQuery来为本地查询启用分页功能。

This defines the SQL to execute to count the number of rows in the whole result:

这定义了要执行的SQL，以计算整个结果中的行数。

@Query(
  value = "SELECT * FROM Users ORDER BY id", 
  countQuery = "SELECT count(*) FROM Users", 
  nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

4.3. Spring Data JPA Versions Prior to 2.0.4

4.3.2.0.4之前的Spring Data JPA版本

The above solution for native queries works fine for Spring Data JPA versions 2.0.4 and later.

上述针对本地查询的解决方案在Spring Data JPA 2.0.4及以后的版本中运行良好。

Prior to that version, when we try to execute such a query, we’ll receive the same exception we described in the previous section on sorting.

在这个版本之前，当我们试图执行这样的查询时，我们会收到我们在前面关于排序的章节中描述的同样的异常。

We can overcome this by adding an additional parameter for pagination inside our query:

我们可以通过在查询中添加一个额外的分页参数来克服这个问题。

@Query(
  value = "SELECT * FROM Users ORDER BY id \n-- #pageable\n",
  countQuery = "SELECT count(*) FROM Users",
  nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

In the above example, we add

在上面的例子中，我们添加了

\n-- #pageable\n

as the placeholder for the pagination parameter. This tells Spring Data JPA how to parse the query and inject the pageable parameter. This solution works for the H2 database.

作为分页参数的占位符。这告诉Spring Data JPA如何解析查询并注入可分页参数。这个解决方案适用于H2数据库。

We’ve covered how to create simple select queries via JPQL and native SQL. Next, we’ll show how to define additional parameters.

我们已经介绍了如何通过JPQL和本地SQL创建简单的选择查询。接下来，我们将展示如何定义额外的参数。

5. Indexed Query Parameters

5.索引查询参数

There are two possible ways that we can pass method parameters to our query: indexed and named parameters.

有两种可能的方式，我们可以将方法参数传递给我们的查询：索引的和命名的参数。

In this section, we’ll cover indexed parameters.

在本节中，我们将介绍索引参数。

5.1. JPQL

5.1. JPQL

For indexed parameters in JPQL, Spring Data will pass method parameters to the query in the same order they appear in the method declaration:

对于JPQL中的索引参数，Spring Data将按照方法声明中出现的相同顺序将方法参数传递给查询。

@Query("SELECT u FROM User u WHERE u.status = ?1")
User findUserByStatus(Integer status);

@Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);

For the above queries, the status method parameter will be assigned to the query parameter with index 1, and the name method parameter will be assigned to the query parameter with index 2.

对于上述查询，status方法参数将被分配给索引1,的查询参数，name方法参数将被分配给索引2的查询参数。

5.2. Native

5.2.本土的

Indexed parameters for the native queries work exactly in the same way as for JPQL:

本地查询的索引参数与JPQL的工作方式完全相同。

@Query(
  value = "SELECT * FROM Users u WHERE u.status = ?1", 
  nativeQuery = true)
User findUserByStatusNative(Integer status);

In the next section, we’ll show a different approach: passing parameters via name.

在下一节，我们将展示一种不同的方法：通过名字传递参数。

6. Named Parameters

6.命名的参数

We can also pass method parameters to the query using named parameters. We define these using the @Param annotation inside our repository method declaration.

我们还可以使用命名参数将方法参数传递给查询。我们在存储库方法声明中使用@Param注解定义这些参数。

Each parameter annotated with @Param must have a value string matching the corresponding JPQL or SQL query parameter name. A query with named parameters is easier to read and is less error-prone in case the query needs to be refactored.

每个用@Param注释的参数必须有一个与相应的JPQL或SQL查询参数名称相匹配的值字符串。带有命名参数的查询更容易阅读，而且在查询需要重构的情况下，错误率更低。

6.1. JPQL

6.1. JPQL

As mentioned above, we use the @Param annotation in the method declaration to match parameters defined by name in JPQL with parameters from the method declaration:

如上所述，我们在方法声明中使用@Param注解来匹配JPQL中以名称定义的参数与方法声明中的参数。

@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByStatusAndNameNamedParams(
  @Param("status") Integer status, 
  @Param("name") String name);

Note that in the above example, we defined our SQL query and method parameters to have the same names, but it’s not required as long as the value strings are the same:

请注意，在上面的例子中，我们定义了我们的SQL查询和方法参数具有相同的名称，但这并不是必须的，只要值字符串是相同的。

@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByUserStatusAndUserName(@Param("status") Integer userStatus, 
  @Param("name") String userName);

6.2. Native

6.2.本土的

For the native query definition, there is no difference in how we pass a parameter via the name to the query in comparison to JPQL — we use the @Param annotation:

对于本地查询定义，与JPQL相比，我们通过名称向查询传递参数的方式没有区别–我们使用@Param注释。

@Query(value = "SELECT * FROM Users u WHERE u.status = :status and u.name = :name", 
  nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(
  @Param("status") Integer status, @Param("name") String name);

7. Collection Parameter

7.收集参数

Let’s consider the case when the where clause of our JPQL or SQL query contains the IN (or NOT IN) keyword:

让我们考虑当我们的JPQL或SQL查询的where子句包含IN（或NOT IN）关键字的情况。

SELECT u FROM User u WHERE u.name IN :names

In this case, we can define a query method that takes Collection as a parameter:

在这种情况下，我们可以定义一个以Collection为参数的查询方法。

@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
List<User> findUserByNameList(@Param("names") Collection<String> names);

As the parameter is a Collection, it can be used with List, HashSet, etc.

由于参数是一个Collection，它可以与List、HashSet等一起使用。

Next, we’ll show how to modify data with the @Modifying annotation.

接下来，我们将展示如何用@Modifying注解来修改数据。

8. Update Queries With @Modifying

8.用@Modifying更新查询

We can use the @Query annotation to modify the state of the database by also adding the @Modifying annotation to the repository method.

我们可以使用@Query注解来修改数据库的状态，也可以在存储库方法中加入@Modifying注解。

8.1. JPQL

8.1. JPQL

The repository method that modifies the data has two differences in comparison to the select query — it has the @Modifying annotation and, of course, the JPQL query uses update instead of select:

与select查询相比，修改数据的存储库方法有两个不同之处–它有@Modifying注释，当然，JPQL查询使用update而不是select。

@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

The return value defines how many rows are updated by the execution of the query. Both indexed and named parameters can be used inside update queries.

返回值定义了执行该查询时有多少行被更新。索引参数和命名参数都可以在更新查询中使用。

8.2. Native

8.2.本土的

We can modify the state of the database also with a native query. We just need to add the @Modifying annotation:

我们也可以通过一个本地查询来修改数据库的状态。我们只需要添加@Modifying注释。

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?", 
  nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

8.3. Inserts

8.3.插件

To perform an insert operation, we have to both apply @Modifying and use a native query since INSERT is not a part of the JPA interface:

要执行插入操作，我们必须同时应用@Modifying和使用本地查询，因为INSERT不是JPA接口的一部分。

@Modifying
@Query(
  value = 
    "insert into Users (name, age, email, status) values (:name, :age, :email, :status)",
  nativeQuery = true)
void insertUser(@Param("name") String name, @Param("age") Integer age, 
  @Param("status") Integer status, @Param("email") String email);

9. Dynamic Query

9.动态查询

Often, we’ll encounter the need for building SQL statements based on conditions or data sets whose values are only known at runtime. And in those cases, we can’t just use a static query.

通常情况下，我们会遇到需要根据条件或数据集建立SQL语句，而这些数据集的值只有在运行时才知道。而在这些情况下，我们不能只使用静态查询。

9.1. Example of a Dynamic Query

9.1.动态查询的例子

For example, let’s imagine a situation where we need to select all the users whose email is LIKE one from a set defined at runtime — email1, email2, …, emailn:

例如，让我们设想这样一种情况：我们需要从运行时定义的集合–email1, email2, …, emailn中选择所有电子邮件为LIKE的用户。

SELECT u FROM User u WHERE u.email LIKE '%email1%' 
    or  u.email LIKE '%email2%'
    ... 
    or  u.email LIKE '%emailn%'

Since the set is dynamically constructed, we can’t know at compile-time how many LIKE clauses to add.

由于这个集合是动态构建的，我们无法在编译时知道要添加多少个LIKE子。

In this case, we can’t just use the @Query annotation since we can’t provide a static SQL statement.

在这种情况下，我们不能仅仅使用@Query注解，因为我们不能提供静态SQL语句。

Instead, by implementing a custom composite repository, we can extend the base JpaRepository functionality and provide our own logic for building a dynamic query. Let’s take a look at how to do this.

相反，通过实现一个自定义的复合仓库，我们可以扩展基础的JpaRepository功能，并提供我们自己的逻辑来构建一个动态查询。让我们来看看如何做到这一点。

9.2. Custom Repositories and the JPA Criteria API

9.2.自定义存储库和JPA标准API

Luckily for us, Spring provides a way for extending the base repository through the use of custom fragment interfaces. We can then link them together to create a composite repository.

幸运的是，Spring提供了一种通过使用自定义片段接口来扩展基础资源库的方法。然后我们可以将它们连接起来，创建一个复合资源库。

We’ll start by creating a custom fragment interface:

我们将从创建一个自定义的片段接口开始。

public interface UserRepositoryCustom {
    List<User> findUserByEmails(Set<String> emails);
}

And then we’ll implement it:

然后我们将实施它。

public class UserRepositoryCustomImpl implements UserRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<User> findUserByEmails(Set<String> emails) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> user = query.from(User.class);

        Path<String> emailPath = user.get("email");

        List<Predicate> predicates = new ArrayList<>();
        for (String email : emails) {
            predicates.add(cb.like(emailPath, email));
        }
        query.select(user)
            .where(cb.or(predicates.toArray(new Predicate[predicates.size()])));

        return entityManager.createQuery(query)
            .getResultList();
    }
}

As shown above, we leveraged the JPA Criteria API to build our dynamic query.

如上所示，我们利用JPA Criteria API来构建我们的动态查询。。

Also, we need to make sure to include the Impl postfix in the class name. Spring will search the UserRepositoryCustom implementation as UserRepositoryCustomImpl. Since fragments are not repositories by themselves, Spring relies on this mechanism to find the fragment implementation.

另外，我们需要确保在类名中包含Impl后缀。Spring将搜索UserRepositoryCustom实现为UserRepositoryCustomImpl。由于片段本身不是存储库，Spring依靠这种机制来寻找片段的实现。

9.3. Extending the Existing Repository

9.3.扩展现有的存储库

Notice that all the query methods from section 2 through section 7 are in the UserRepository.

注意，从第2节到第7节的所有查询方法都在UserRepository。

So, now we’ll integrate our fragment by extending the new interface in the UserRepository:

所以，现在我们将通过扩展UserRepository中的新接口来整合我们的片段。

public interface UserRepository extends JpaRepository<User, Integer>, UserRepositoryCustom {
    //  query methods from section 2 - section 7
}

9.4. Using the Repository

9.4.使用存储库

And finally, we can call our dynamic query method:

最后，我们可以调用我们的动态查询方法。

Set<String> emails = new HashSet<>();
// filling the set with any number of items

userRepository.findUserByEmails(emails);

We’ve successfully created a composite repository and called our custom method.

我们已经成功创建了一个复合存储库，并调用了我们的自定义方法。

10. Conclusion

10.结论

In this article, we covered several ways of defining queries in Spring Data JPA repository methods using the @Query annotation.

在这篇文章中，我们介绍了使用@Query注解在Spring Data JPA资源库方法中定义查询的几种方法。

We also learned how to implement a custom repository and create a dynamic query.

我们还学习了如何实现自定义资源库和创建动态查询。

As always, the complete code examples used in this article are available over on GitHub.

一如既往，本文中使用的完整代码示例可在GitHub上获得over 。