Query Methods

Spring Data JDBC 简化了基于 JDBC 的数据库访问，提供了声明式查询方法，支持分页、排序、流式处理和自定义查询。它从方法名称派生查询，解析约束并将其转换为 WHERE 子句，并允许使用注解和属性文件进行查询自定义。此外，它提供了流式结果和自定义行映射器等高级功能，以优化性能和灵活性。

本节提供了有关 Spring Data JDBC 的实现和使用的一些具体信息。

This section offers some specific information about the implementation and use of Spring Data JDBC.

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

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 PersonRepository extends PagingAndSortingRepository<Person, String> {

  List<Person> findByFirstname(String firstname);                                   1

  List<Person> findByFirstnameOrderByLastname(String firstname, Pageable pageable); 2

  Slice<Person> findByLastname(String lastname, Pageable pageable);                 3

  Page<Person> findByLastname(String lastname, Pageable pageable);                  4

  Person findByFirstnameAndLastname(String firstname, String lastname);             5

  Person findFirstByLastname(String lastname);                                      6

  @Query("SELECT * FROM person WHERE lastname = :lastname")
  List<Person> findByLastname(String lastname);                                     7
  @Query("SELECT * FROM person WHERE lastname = :lastname")
  Stream<Person> streamByLastname(String lastname);                                     8

  @Query("SELECT * FROM person WHERE username = :#{ principal?.username }")
  Person findActiveUser();															9
}

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	Use `Pageable` to pass offset and sorting parameters to the database.
3	Return a `Slice<Person>`.Selects `LIMIT+1` rows to determine whether there’s more data to consume. `ResultSetExtractor` customization is not supported.
4	Run a paginated query returning `Page<Person>`.Selects only data within the given page bounds and potentially a count query to determine the total count. `ResultSetExtractor` customization is not supported.
5	Find a single entity for the given criteria. It completes with `IncorrectResultSizeDataAccessException` on non-unique results.
6	In contrast to <3>, the first entity is always emitted even if the query yields more result documents.
7	The `findByLastname` method shows a query for all people with the given `lastname`.
8	The `streamByLastname` method returns a `Stream`, which makes values possible as soon as they are returned from the database.
9	You can use the Spring Expression Language to dynamically resolve parameters. In the sample, Spring Security is used to resolve the username of the current user.

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

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`

查询派生仅限于可以在 WHERE 子句中使用的属性，而不使用联接。

Query derivation is limited to properties that can be used in a WHERE clause without using joins.

Query Lookup Strategies

JDBC 模块支持在 @Query 注释中以字符串手动定义查询，或在属性文件中的命名查询中定义查询。

The JDBC module supports defining a query manually as a String in a @Query annotation or as named query in a property file.

从方法名称派生查询目前仅限于简单的属性，这意味着直接存在于聚合根中的属性。此外，此方法仅支持查询选择。

Deriving a query from the name of the method is is currently limited to simple properties, that means properties present in the aggregate root directly. Also, only select queries are supported by this approach.

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 CrudRepository<User, Long> {

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

用于将查询结果转换为实体的 RowMapper 与 Spring Data JDBC 为其自身生成的查询相同。您提供的查询必须与 RowMapper 预期的格式匹配。必须提供用于实体构造函数中的所有属性的列。通过 setter、wither 或字段访问设置的属性的列是可选的。不匹配结果中的列的属性不会被设置。查询用于填充聚合根、嵌入实体和一对一关系，包括存储和加载为 SQL 数组类型的基本类型数组。为实体映射、列表、集合和数组生成了单独的查询。

For converting the query result into entities the same RowMapper is used by default as for the queries Spring Data JDBC generates itself. The query you provide must match the format the RowMapper expects. Columns for all properties that are used in the constructor of an entity must be provided. Columns for properties that get set via setter, wither or field access are optional. Properties that don’t have a matching column in the result will not be set. The query is used for populating the aggregate root, embedded entities and one-to-one relationships including arrays of primitive types which get stored and loaded as SQL-array-types. Separate queries are generated for maps, lists, sets and arrays of entities.

请注意，基于字符串的查询不支持分页，也不接受 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.

查询可能包含允许绑定变量的 SpEL 表达式。此类 SpEL 表达式将被绑定变量替换，并且变量将绑定到 SpEL 表达式的结果。

Queries may contain SpEL expressions where bind variables are allowed. Such a SpEL expression will get replaced with a bind variable and the variable gets bound to the result of the SpEL expression.

Use a SpEL in a query

@Query("SELECT * FROM person WHERE id = :#{person.id}")
Person findWithSpEL(PersonRef person);

这可用于访问参数的成员，如上面示例中所示。对于更复杂的用例，可以在应用程序上下文中提供一个 EvaluationContextExtension，它反过来还可以向 SpEL 提供任何对象。

This can be used to access members of a parameter, as demonstrated in the example above. For more involved use cases an EvaluationContextExtension can be made available in the application context, which in turn can make any object available in to the SpEL.

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.

Spring Data JDBC 仅支持命名参数。

Spring Data JDBC supports only named parameters.

Named Queries

如果没有按照上一部分的说明在注释中提供查询，Spring Data JDBC 将尝试查找一个命名查询。有两种方法可确定查询名称。默认做法是获取查询的域类，即存储库的聚合根，获取其简单名称并附加方法名称，用“.”分隔。或者，@Query 注释具有 name 属性，可用于指定要查找的查询的名称。

If no query is given in an annotation as described in the previous section Spring Data JDBC will try to locate a named query. There are two ways how the name of the query can be determined. The default is to take the domain class of the query, i.e. the aggregate root of the repository, take its simple name and append the name of the method separated by a .. Alternatively the @Query annotation has a name attribute which can be used to specify the name of a query to be looked up.

命名查询应该提供给 classpath 中的属性文件 META-INF/jdbc-named-queries.properties。

Named queries are expected to be provided in the property file META-INF/jdbc-named-queries.properties on the classpath.

可以通过向 @EnableJdbcRepositories.namedQueriesLocation 设置值来更改该文件的位置。

The location of that file may be changed by setting a value to @EnableJdbcRepositories.namedQueriesLocation.

命名查询的处理方式与注释提供的查询相同。

Named queries are handled in the same way as queries provided by annotation.

Customizing Query Methods

Streaming Results

当您指定 Stream 作为查询方法的返回类型时，Spring Data JDBC 会在元素可用时立即返回元素。在处理海量数据时，这适合于降低延迟和内存需求。

When you specify Stream as the return type of a query method, Spring Data JDBC returns elements as soon as they become available. When dealing with large amounts of data this is suitable for reducing latency and memory requirements.

流包含与数据库的打开连接。为了避免内存泄漏，最终需要通过关闭流来关闭该连接。推荐的方法是使用 try-with-resource 子句。这也意味着，一旦与数据库的连接关闭，流就无法获取更多元素并可能引发异常。

The stream contains an open connection to the database. To avoid memory leaks, that connection needs to be closed eventually, by closing the stream. The recommended way to do that is a try-with-resource clause. It also means that, once the connection to the database is closed, the stream cannot obtain further elements and likely throws an exception.

Custom `RowMapper` or `ResultSetExtractor`

@Query 注释允许您指定要使用的自定义 RowMapper 或 ResultSetExtractor。属性 rowMapperClass 和 resultSetExtractorClass 允许您指定要使用的类，它们将使用默认构造函数进行实例化。或者，您可以将 rowMapperClassRef 或 resultSetExtractorClassRef 设置为 Spring 应用程序上下文中中的 bean 名称。

The @Query annotation allows you to specify a custom RowMapper or ResultSetExtractor to use. The attributes rowMapperClass and resultSetExtractorClass allow you to specify classes to use, which will get instantiated using a default constructor. Alternatively you may set rowMapperClassRef or resultSetExtractorClassRef to a bean name from your Spring application context.

如果您想对所有具有自定义查询并返回特定类型的自定义方法使用特定 RowMapper，而不仅仅是单一方法，则可以注册一个 RowMapperMap bean 并为每个方法返回类型注册一个 RowMapper。以下示例显示了如何注册 DefaultQueryMappingConfiguration：

If you want to use a certain RowMapper not just for a single method but for all methods with custom queries returning a certain type, you may register a RowMapperMap bean and registering a RowMapper per method return type. The following example shows how to register DefaultQueryMappingConfiguration:

@Bean
QueryMappingConfiguration rowMappers() {
  return new DefaultQueryMappingConfiguration()
    .register(Person.class, new PersonRowMapper())
    .register(Address.class, new AddressRowMapper());
}

在确定为某方法使用哪个 RowMapper 时，会基于该方法的返回类型执行以下步骤：

When determining which RowMapper to use for a method, the following steps are followed, based on the return type of the method:

If the type is a simple type, no RowMapper is used.[.iokays-translated-efcc9544004b911a945beadd6e42aeec] 相反，预期查询返回单行单列，并且会向该值应用对返回类型的转换。

Instead, the query is expected to return a single row with a single column, and a conversion to the return type is applied to that value. . The entity classes in the QueryMappingConfiguration are iterated until one is found that is a superclass or interface of the return type in question. The RowMapper registered for that class is used.[.iokays-translated-9bbf4e8123fcc2386774208005a5599d] 迭代按照注册顺序进行，因此请确保在特定类型后面注册更通用的类型。

Iterating happens in the order of registration, so make sure to register more general types after specific ones.

在适用的情况下，集合或 Optional 等包装类型会被解包。因此，Optional<Person> 返回类型在前面的过程中使用了 Person 类型。

If applicable, wrapper types such as collections or Optional are unwrapped. Thus, a return type of Optional<Person> uses the Person type in the preceding process.

通过 QueryMappingConfiguration、@Query(rowMapperClass=…) 或自定义 ResultSetExtractor 使用自定义 RowMapper 会禁用实体回调和生命周期事件，因为结果映射可在需要时发布自己的事件/回调。

Using a custom RowMapper through QueryMappingConfiguration, @Query(rowMapperClass=…), or a custom ResultSetExtractor disables Entity Callbacks and Lifecycle Events as the result mapping can issue its own events/callbacks if needed.

Modifying Query

您可以使用查询方法上的 @Modifying 将查询标记为修改查询，如下面的示例所示：

You can mark a query as being a modifying query by using the @Modifying on query method, as the following example shows:

@Modifying
@Query("UPDATE DUMMYENTITY SET name = :name WHERE id = :id")
boolean updateName(@Param("id") Long id, @Param("name") String name);

您可以指定以下返回类型：

You can specify the following return types:

void
int (updated record count)
boolean(whether a record was updated)

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

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.