3.4 定义查询方法

3.4 定义查询方法

3.4. Defining query methods

库代理有两种方法从方法名中提取指定存储的查询。一是可以直接从方法名中提取查询,二是使用手动定义的查询。使用哪一个取决于实际的存储。但必须有一种策略来决定实际创建的查询。接下来看看这两种方法。

The repository proxy has two ways to derive a store-specific query from the method name. It can derive the query from the method name directly, or by using a manually defined query. Available options depend on the actual store. However, there’s got to be a strategy that decides what actual query is created. Let’s have a look at the available options.

3.4.1. 查询策略

3.4.1. Query lookup strategies

库基础设施有下列策略来解决查询问题。你可以通过XML配置中的query-lookup-strategy属性,或者在Java config中的Enable${store}Repositories注解通过queryLookupStrategy属性,在命名空间下配置策略。其中某些策略可能不支持特定的数据库。

The following strategies are available for the repository infrastructure to resolve the query. You can configure the strategy at the namespace through the query-lookup-strategy attribute in case of XML configuration or via the queryLookupStrategy attribute of the Enable${store}Repositories annotation in case of Java config. Some strategies may not be supported for particular datastores.

  • CREATE会从查询方法名尝试构建一个指定存储的查询。通常方法是从方法名中去除一个指定系列的前缀,并解析其他部分。可在查询的创建中阅读更多关于查询构建的信息。
  • USE_DECLARED_QUERY会尝试找到一个已声明的查询,如果找不到会抛出一个异常。查询可以通过注解定义或者其他方法声明。可以查阅对应数据库的文档来找到对应方法。如果库基础设置在启动时无法为方法找到一个已声明的查询,将启动失败。
  • CREATE_IF_NOT_FOUND(默认)为CREATEUSE_DECLARED_QUERY的组合。首先将搜索一个已声明的查询,如果没有找到,将创建一个基于方法名称的自定义查询。这是默认的寻找策略,因此你没有配置的话就会按此策略。此策略允许通过方法名快速定义查询,但也可以通过根据需要引入已声明的查询来自定义调节这些查询。
  • CREATE attempts to construct a store-specific query from the query method name. The general approach is to remove a given set of well-known prefixes from the method name and parse the rest of the method. Read more about query construction in Query creation.
  • USE_DECLARED_QUERY tries to find a declared query and will throw an exception in case it can’t find one. The query can be defined by an annotation somewhere or declared by other means. Consult the documentation of the specific store to find available options for that store. If the repository infrastructure does not find a declared query for the method at bootstrap time, it fails.
  • CREATE_IF_NOT_FOUND (default) combines CREATE and USE_DECLARED_QUERY. It looks up a declared query first, and if no declared query is found, it creates a custom method name-based query. This is the default lookup strategy and thus will be used if you do not configure anything explicitly. It allows quick query definition by method names but also custom-tuning of these queries by introducing declared queries as needed.

3.4.2. 查询的创建

3.4.2. Query creation

基于Spring Data库基础设施的查询构建机制,是用于通过库的实体来构建约束查询。这种机制会从方法中裁剪掉find…By, read…By, query…By, count…Byget…By等前缀,然后开始解析剩下的部分。说明语法还可以包含其他的解释,例如Distinct。第一个By是作为分隔符来识别查询条件的开始。每个基于实体属性的基本条件使用AndOr来连接。

The query builder mechanism built into Spring Data repository infrastructure is useful for building constraining queries over entities of the repository. The mechanism strips the prefixes find…By, read…By, query…By, count…By, and get…By from the method and starts parsing the rest of it. The introducing clause can contain further expressions such as a Distinct to set a distinct flag on the query to be created. However, the first By acts as delimiter to indicate the start of the actual criteria. At a very basic level you can define conditions on entity properties and concatenate them with And and Or.

例16. 从方法名创建查询

Example 16. Query creation from method names

interface PersonRepository extends Repository<User, Long> {

  List<Person> findByEmailAddressAndLastname(EmailAddress emailAddress, String lastname);

  // 在查询中启用distinct标记
  List<Person> findDistinctPeopleByLastnameOrFirstname(String lastname, String firstname);
  List<Person> findPeopleDistinctByLastnameOrFirstname(String lastname, String firstname);

  // 为单一属性启用忽略大小写
  List<Person> findByLastnameIgnoreCase(String lastname);
  // 为所有适合的属性启用忽略大小写
  List<Person> findByLastnameAndFirstnameAllIgnoreCase(String lastname, String firstname);

  // 为查询启用静态ORDER BY
  List<Person> findByLastnameOrderByFirstnameAsc(String lastname);
  List<Person> findByLastnameOrderByFirstnameDesc(String lastname);
}

实际解析方法的结果取决于你创建查询的持久化存储。因此需要注意以下事项。

The actual result of parsing the method depends on the persistence store for which you create the query. However, there are some general things to notice.

  • 遍历属性的表达式通常通过可连接的分隔符来结合。你可以使用ANDOR来组合属性表达式。同样还支持Between, LessThan, GreaterThan, Like等操作符。不同的数据库支持不同的操作符,请查阅相关参考文档。
  • 方法解析其支持为每个独立属性设置一个IgnoreCase标记(例如findByLastnameIgnoreCase(…)),或者为所有支持忽略大小写的属性(通常为String实例,例如findByLastnameAndFirstnameAllIgnoreCase(…))设置标记。是否支持大小写忽略取决于数据库,所以请查看参考文档的相关章节。
  • 你可以通过添加OrderBy语法来为查询方法应用一个静态排序,还可以提供一个排序书顺序(Asc或者Desc)。创建一个支持动态排序的查询方法,请参考特殊参数处理
  • The expressions are usually property traversals combined with operators that can be concatenated. You can combine property expressions with AND and OR. You also get support for operators such as Between, LessThan, GreaterThan, Like for the property expressions. The supported operators can vary by datastore, so consult the appropriate part of your reference documentation.
  • The method parser supports setting an IgnoreCase flag for individual properties (for example, findByLastnameIgnoreCase(…)) or for all properties of a type that support ignoring case (usually String instances, for example, findByLastnameAndFirstnameAllIgnoreCase(…)). Whether ignoring cases is supported may vary by store, so consult the relevant sections in the reference documentation for the store-specific query method.
  • You can apply static ordering by appending an OrderBy clause to the query method that references a property and by providing a sorting direction (Asc or Desc). To create a query method that supports dynamic sorting, see Special parameter handling.

3.4.3. 属性表达式

3.4.3. Property expressions

正如前面的例子一样,属性表达式只能与管理的实体的直系属性对应。在查询创建时需要确保解析的属性是托管域类的属性之一。你还可以通过遍历嵌套属性来定义约束。假设Person有一个包含ZipCodeAddress。有一个方法名如下

Property expressions can refer only to a direct property of the managed entity, as shown in the preceding example. At query creation time you already make sure that the parsed property is a property of the managed domain class. However, you can also define constraints by traversing nested properties. Assume a Person has an Address with a ZipCode. In that case a method name of

List<Person> findByAddressZipCode(ZipCode zipCode);

创建了一个属性遍历x.address.zipCode。算法首先需要分解整个属性部分(AddressZipCode),并检查域类中是否有此属性名(未大写)。如果算法成功,就会使用那个属性。如果失败,算法会从右边开始以驼峰来分割为两部分,并再寻找对应的属性,在此例中为AddressZipCode。如果算法找到了一个属性匹配第一个名称,则继续向下构建。如果没有找到,则算法会向左移动分割点(Address, ZipCode)并继续处理。

creates the property traversal x.address.zipCode. The resolution algorithm starts with interpreting the entire part (AddressZipCode) as the property and checks the domain class for a property with that name (uncapitalized). If the algorithm succeeds it uses that property. If not, the algorithm splits up the source at the camel case parts from the right side into a head and a tail and tries to find the corresponding property, in our example, AddressZip and Code. If the algorithm finds a property with that head it takes the tail and continue building the tree down from there, splitting the tail up in the way just described. If the first split does not match, the algorithm move the split point to the left (Address, ZipCode) and continues.

此算法适用于大多数场景,但算法也有可能选择错误的属性。假设Person类有还有一个addressZip属性。算法将匹配第一次分割,然后选择错误的属性并最终导致失败(因为addressZip属性的类型没有code属性)。

Although this should work for most cases, it is possible for the algorithm to select the wrong property. Suppose the Person class has an addressZip property as well. The algorithm would match in the first split round already and essentially choose the wrong property and finally fail (as the type of addressZip probably has no code property).

为了解决这种模棱两可的情况,可以在方法名中使用\_来手动定义遍历点。所以方法名应如下:

To resolve this ambiguity you can use \_ inside your method name to manually define traversal points. So our method name would end up like so:

List<Person> findByAddress_ZipCode(ZipCode zipCode);

因为我们把下划线作为保留字符,所以强烈建议遵循标准Java命名规范(例如属性名使用驼峰命名而不是用下划线)。

As we treat underscore as a reserved character we strongly advise to follow standard Java naming conventions (i.e. not using underscores in property names but camel case instead).

3.4.4. 特殊参数处理

3.4.4. Special parameter handling

为了在查询中处理参数,可以像上述例子中一样定义方法参数。在那基础之上还可以识别常见类型如PageableSort,用来在你的动态查询中应用分页和排序。

To handle parameters in your query you simply define method parameters as already seen in the examples above. Besides that the infrastructure will recognize certain specific types like Pageable and Sort to apply pagination and sorting to your queries dynamically.

例17. 在查询方法中使用Pageable, Slice和Sort

Example 17. Using Pageable, Slice and Sort in query methods

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

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

List<User> findByLastname(String lastname, Sort sort);

List<User> findByLastname(String lastname, Pageable pageable);

第一个方法允许你在查询方法中传递一个org.springframework.data.domain.Pageable实例,在你静态定义的查询中动态地添加分页方法。一个Page包含元素的总数和可用的页数。基础库将触发一个计数的查询来计算总数。根据不同的数据库,返回也可以用Slice。一个Slice只知道是否有一个备选的Slice来进入一个更大的结果集合。

The first method allows you to pass an org.springframework.data.domain.Pageable instance to the query method to dynamically add paging to your statically defined query. A Page knows about the total number of elements and pages available. It does so by the infrastructure triggering a count query to calculate the overall number. As this might be expensive depending on the store used, Slice can be used as return instead. A Slice only knows about whether there’s a next Slice available which might be just sufficient when walking through a larger result set.

可使用Pageable实例来处理排序选项。如果需要排序,只需要在方法中增加一个org.springframework.data.domain.Sort参数。如你所见,返回是一个List。在此例中,需要用来构建Page实例的额外的元数据并没有被创建(意味着并不是必须的附加计数查询没有被发布)只是简单的限制了查询来搜索指定范围的实体。

Sorting options are handled through the Pageable instance too. If you only need sorting, simply add an org.springframework.data.domain.Sort parameter to your method. As you also can see, simply returning a List is possible as well. In this case the additional metadata required to build the actual Page instance will not be created (which in turn means that the additional count query that would have been necessary not being issued) but rather simply restricts the query to look up only the given range of entities.

3.4.5. 限制查询结果

3.4.5. Limiting query results

可以通过关键词firsttop来限制查询结果,两者都可互换。可在top/first加一个可选的数字值来指定返回的最大结果数量。如果没有数字,则默认结果数量为1。

The results of query methods can be limited via the keywords first or top, which can be used interchangeably. An optional numeric value can be appended to top/first to specify the maximum result size to be returned. If the number is left out, a result size of 1 is assumed.

例18. 使用TopFirst限制查询结果数量

Example 18. Limiting the result size of a query with Top and First

User findFirstByOrderByLastnameAsc();

User findTopByOrderByAgeDesc();

Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

Slice<User> findTop3ByLastname(String lastname, Pageable pageable);

List<User> findFirst10ByLastname(String lastname, Sort sort);

List<User> findTop10ByLastname(String lastname, Pageable pageable);

限制表达式同样支持Distinct关键词。所以对于一个实例的来限制查询结果集,也支持将结果封装为一个Optional

The limiting expressions also support the Distinct keyword. Also, for the queries limiting the result set to one instance, wrapping the result into an Optional is supported.

如果在限制查询分页中(计算分页数量)应用了分页或切片,则也会应用到限制的结果中。

If pagination or slicing is applied to a limiting query pagination (and the calculation of the number of pages available) then it is applied within the limited result.

3.4.6. 流式化查询结果

3.4.6. Streaming query results

使用Java 8的Stream<T>作为返回类型,可以逐步处理查询方法的结果。我们使用了特定存储方法来执行流式化,而不是在一个Stream数据中简单封装查询结果。

The results of query methods can be processed incrementally by using a Java 8 Stream<T> as return type. Instead of simply wrapping the query results in a Stream data store specific methods are used to perform the streaming.

例19. 使用Java 8 Stream<T>流式化查询结果

Example 19. Stream the result of a query with Java 8 Stream<T>

@Query("select u from User u")
Stream<User> findAllByCustomQueryAndStream();

Stream<User> readAllByFirstnameNotNull();

@Query("select u from User u")
Stream<User> streamAllPaged(Pageable pageable);

例20. 在try-with-resources块中使用Stream<T>结果

Example 20. Working with a Stream<T> result in a try-with-resources block

try (Stream<User> stream = repository.findAllByCustomQueryAndStream()) {
  stream.forEach(…);
}

翻译部分版权归YahaCode团队所有。仅供学习研究之用,任何组织或个人不得私自以此用于任何形式的商业目的

发表评论