5.3.4. 使用@Query

5.3.4. 使用@Query

5.3.4. Using @Query

使用命名查询来为实体声明查询,对于小数据量的查询来说个不错的选择。查询本身会绑定被执行的Java方法,你可以使用Spring Data JPA @Query注解,而不是注解在域类上,来绑定对应方法。这种做法将域类从特定持久化信息中释放出来,并协同定位查询所属的库接口。

Using named queries to declare queries for entities is a valid approach and works fine for a small number of queries. As the queries themselves are tied to the Java method that executes them, you can actually bind them directly by using the Spring Data JPA @Query annotation rather than annotating them to the domain class. This frees the domain class from persistence specific information and co-locates the query to the repository interface.

注解在方法上的查询优先级高于使用@NamedQuery定义的查询和在orm.xml声明的命名查询。

Queries annotated to the query method take precedence over queries defined using @NamedQuery or named queries declared in orm.xml.

下面例子展示了一个使用@Query注解创建的查询:

The following example shows a query created with the @Query annotation:

例56. 在查询方法上使用@Query声明查询

Example 56. Declare query at the query method using @Query

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.emailAddress = ?1")
  User findByEmailAddress(String emailAddress);
}

使用高级LIKE表达式

Using Advanced LIKE Expressions

使用@Query创建的手动定义的查询执行机制允许在查询定义的内部使用高级LIKE表达式定义,如下例:

The query execution mechanism for manually defined queries created with @Query allows the definition of advanced LIKE expressions inside the query definition, as shown in the following example:

例57. 在@Query中的高级like表达式

Example 57. Advanced like expressions in @Query

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname like %?1")
  List<User> findByFirstnameEndsWith(String firstname);
}

在前面的例子中,LIKE的分隔符(%)被识别出来,并且查询被转换为合法的JPQL查询(替换掉%)。当查询执行时,在方法调用中传递的参数会补充之前识别的LIKE模式。

In the preceding example, the LIKE delimiter character (%) is recognized, and the query is transformed into a valid JPQL query (removing the %). Upon query execution, the parameter passed to the method call gets augmented with the previously recognized LIKE pattern.

原生查询

Native Queries

设置@Query注解的nativeQuery标记为true,可以允许运行原生查询,如下例:

The @Query annotation allows for running native queries by setting the nativeQuery flag to true, as shown in the following example:

例58. 在查询方法上使用@Query声明一个原生查询

Example 58. Declare a native query at the query method using @Query

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
}

Spring Data JPA目前不支持原生查询的动态排序,因为需要操作声明的实际查询,在原生SQL下可能无法支持。但你可以通过指定统计查询来使用原生的分页查询,如下例:

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL. You can, however, use native queries for pagination by specifying the count query yourself, as shown in the following example:

例59. 在查询方法上使用@Query为分页声明原生统计查询

Example 59. Declare native count queries for pagination at the query method by using @Query

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

另一个类似的方法是,在你查询的一个复制中添加.count后缀。你可能需要注册一个结果集来匹配统计查询。

A similar approach also works with named native queries, by adding the .count suffix to a copy of your query. You probably need to register a result set mapping for your count query, though.

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

发表评论