Query Methods

大多数您通常在存储库上触发的数据库访问操作会导致针对数据库运行查询。定义此类查询是声明存储库接口方法的问题，如下例所示：

Most of the data access operations you usually trigger on a repository result in a query being run against the databases. Defining such a query is a matter of declaring a method on the repository interface, as the following example shows: .PersonRepository with query methods

interface ReactivePersonRepository extends ReactiveSortingRepository<Person, Long> {

  Flux<Person> findByFirstname(String firstname);                                   1

  Flux<Person> findByFirstname(Publisher<String> firstname);                        2

  Flux<Person> findByFirstnameOrderByLastname(String firstname, Pageable pageable); 3

  Mono<Person> findByFirstnameAndLastname(String firstname, String lastname);       4

  Mono<Person> findFirstByLastname(String lastname);                                5

  @Query("SELECT * FROM person WHERE lastname = :lastname")
  Flux<Person> findByLastname(String lastname);                                     6

  @Query("SELECT firstname, lastname FROM person WHERE lastname = $1")
  Mono<Person> findFirstByLastname(String lastname);                                7
}

1	The method shows a query for all people with the given `firstname`. The query is derived by parsing the method name for constraints that can be concatenated with `And` and `Or`. Thus, the method name results in a query expression of `SELECT … FROM person WHERE firstname = :firstname`.
2	The method shows a query for all people with the given `firstname` once the `firstname` is emitted by the given `Publisher`.
3	Use `Pageable` to pass offset and sorting parameters to the database.
4	Find a single entity for the given criteria. It completes with `IncorrectResultSizeDataAccessException` on non-unique results.
5	Unless <4>, the first entity is always emitted even if the query yields more result rows.
6	The `findByLastname` method shows a query for all people with the given last name.
7	A query for a single `Person` entity projecting only `firstname` and `lastname` columns. The annotated query uses native bind markers, which are Postgres bind markers in this example.

请注意，在 @Query 注释中使用的选择语句的列必须与 NamingStrategy 为相应属性生成的名称匹配。如果选择语句不包含匹配的列，则不设置该属性。如果持久化构造函数需要该属性，则提供空值或（对于基本类型）默认值。

Note that the columns of a select statement used in a @Query annotation must match the names generated by the NamingStrategy for the respective property. If a select statement does not include a matching column, that property is not set. If that property is required by the persistence constructor, either null or (for primitive types) the default value is provided.

下表显示了查询方法支持的关键字：

The following table shows the keywords that are supported for query methods:

Table 1. Supported keywords for query methods
Keyword	Sample	Logical result
`After`	`findByBirthdateAfter(Date date)`	`birthdate > date`
`GreaterThan`	`findByAgeGreaterThan(int age)`	`age > age`
`GreaterThanEqual`	`findByAgeGreaterThanEqual(int age)`	`age >= age`
`Before`	`findByBirthdateBefore(Date date)`	`birthdate < date`
`LessThan`	`findByAgeLessThan(int age)`	`age < age`
`LessThanEqual`	`findByAgeLessThanEqual(int age)`	`age ⇐ age`
`Between`	`findByAgeBetween(int from, int to)`	`age BETWEEN from AND to`
`NotBetween`	`findByAgeNotBetween(int from, int to)`	`age NOT BETWEEN from AND to`
`In`	`findByAgeIn(Collection<Integer> ages)`	`age IN (age1, age2, ageN)`
`NotIn`	`findByAgeNotIn(Collection ages)`	`age NOT IN (age1, age2, ageN)`
`IsNotNull`, `NotNull`	`findByFirstnameNotNull()`	`firstname IS NOT NULL`
`IsNull`, `Null`	`findByFirstnameNull()`	`firstname IS NULL`
`Like`, `StartingWith`, `EndingWith`	`findByFirstnameLike(String name)`	`firstname LIKE name`
`NotLike`, `IsNotLike`	`findByFirstnameNotLike(String name)`	`firstname NOT LIKE name`
`Containing` on String	`findByFirstnameContaining(String name)`	`firstname LIKE '%' + name +'%'`
`NotContaining` on String	`findByFirstnameNotContaining(String name)`	`firstname NOT LIKE '%' + name +'%'`
`(No keyword)`	`findByFirstname(String name)`	`firstname = name`
`Not`	`findByFirstnameNot(String name)`	`firstname != name`
`IsTrue`, `True`	`findByActiveIsTrue()`	`active IS TRUE`
`IsFalse`, `False`	`findByActiveIsFalse()`	`active IS FALSE`

Modifying Queries

前面的章节描述了如何声明查询来访问给定的实体或实体集合。可以将前一个表中的关键字与 delete…By 或 remove…By 结合使用，以创建派生的查询来删除匹配的行。

The previous sections describe how to declare queries to access a given entity or collection of entities. Using keywords from the preceding table can be used in conjunction with delete…By or remove…By to create derived queries that delete matching rows.

Example 1. Delete…By Query

interface ReactivePersonRepository extends ReactiveSortingRepository<Person, String> {

  Mono<Integer> deleteByLastname(String lastname);            1

  Mono<Void> deletePersonByLastname(String lastname);         2

  Mono<Boolean> deletePersonByLastname(String lastname);      3
}

1	Using a return type of `Mono<Integer>` returns the number of affected rows.
2	Using `Void` just reports whether the rows were successfully deleted without emitting a result value.
3	Using `Boolean` reports whether at least one row was removed.

由于这种方法对于全面的自定义功能是可行的，因此您可以修改只需参数绑定的查询，方法是使用 @Modifying 注释查询方法，如下例所示：

As this approach is feasible for comprehensive custom functionality, you can modify queries that only need parameter binding by annotating the query method with @Modifying, as shown in the following example:

Unresolved include directive in modules/ROOT/pages/r2dbc/query-methods.adoc - include::example$r2dbc/PersonRepository.java[]

修改查询的结果可以是：

The result of a modifying query can be:

Void (or Kotlin Unit) to discard update count and await completion.
Integer or another numeric type emitting the affected rows count.
Boolean to emit whether at least one row was updated.

@Modifying 注释仅与 @Query 注释相结合时才相关。派生的自定义方法不需要此注释。

The @Modifying annotation is only relevant in combination with the @Query annotation. Derived custom methods do not require this annotation.

修改查询直接针对数据库执行。不会调用事件或回调。因此，如果审计注释中的字段不在带注释的查询中更新，则它们也不会更新。

Modifying queries are executed directly against the database. No events or callbacks get called. Therefore also fields with auditing annotations do not get updated if they don’t get updated in the annotated query.

或者，你可以使用 Custom Implementations for Spring Data Repositories中描述的功能添加自定义修改行为。

Alternatively, you can add custom modifying behavior by using the facilities described in Custom Implementations for Spring Data Repositories.

Using `@Query`

以下示例展示如何使用 @Query 声明查询方法：

The following example shows how to use @Query to declare a query method:

Declare a query method by using @Query

interface UserRepository extends ReactiveCrudRepository<User, Long> {

  @Query("select firstName, lastName from User u where u.emailAddress = :email")
  Flux<User> findByEmailAddress(@Param("email") String email);
}

请注意，基于字符串的查询不支持分页，也不接受 Sort、PageRequest 和 Limit 作为查询参数，因为对于这些查询，需要重写查询。如果您想应用限制，请使用 SQL 表达这种意图，并自己将适当的参数绑定到查询。

Note that String-based queries do not support pagination nor accept Sort, PageRequest, and Limit as a query parameter as for these queries the query would be required to be rewritten. If you want to apply limiting, please express this intent using SQL and bind the appropriate parameters to the query yourself.

Spring 完全支持基于 -parameters 编译器标志的 Java 8 的参数名称发现。在您的构建中将这个标志作为调试信息的替代，您可以省略带命名参数的 @Param 注释。

Spring fully supports Java 8’s parameter name discovery based on the -parameters compiler flag. By using this flag in your build as an alternative to debug information, you can omit the @Param annotation for named parameters.

Queries with SpEL Expressions

查询字符串定义可与 SpEL 表达式一起使用，以在运行时创建动态查询。SpEL 表达式可以提供谓词值，这些值在运行查询正文之前进行评估。

Query string definitions can be used together with SpEL expressions to create dynamic queries at runtime. SpEL expressions can provide predicate values which are evaluated right before running the query.

表达式通过包含所有参数的数组公开方法参数。以下查询使用 `[0]`声明 `lastname`的谓词值（这等同于 `:lastname`参数绑定）：

Expressions expose method arguments through an array that contains all the arguments. The following query uses [0] to declare the predicate value for lastname (which is equivalent to the :lastname parameter binding):

Unresolved include directive in modules/ROOT/pages/r2dbc/query-methods.adoc - include::example$r2dbc/PersonRepository.java[]

查询字符串中的 SpEL 可以增强查询功能。但是，它们也可以接受各种不受欢迎的参数。在将字符串传递到查询之前，您应该确保对其进行清理，以避免对查询进行不必要的更改。

SpEL in query strings can be a powerful way to enhance queries. However, they can also accept a broad range of unwanted arguments. You should make sure to sanitize strings before passing them to the query to avoid unwanted changes to your query.

表达式支持可以通过查询 SPI 扩展：org.springframework.data.spel.spi.EvaluationContextExtension。查询 SPI 可以提供属性和函数，并且可以自定义根对象。在构建查询时，会在 SpEL 评估时从应用程序上下文中检索扩展。

Expression support is extensible through the Query SPI: org.springframework.data.spel.spi.EvaluationContextExtension. The Query SPI can contribute properties and functions and can customize the root object. Extensions are retrieved from the application context at the time of SpEL evaluation when the query is built.

在将 SpEL 表达式与普通参数结合使用时，请使用命名参数表示法，而不是本地绑定标记，以确保正确的绑定顺序。

When using SpEL expressions in combination with plain parameters, use named parameter notation instead of native bind markers to ensure a proper binding order.

Query Methods

Modifying Queries

Using @Query

Queries with SpEL Expressions

Using `@Query`