Query Hints in Spring Data JPA – Spring Data JPA 中的查询提示

最后修改: 2024年 3月 5日

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

1. Introduction

1.导言

In this tutorial, we’ll explore the fundamentals of query hints in Spring Data JPA. These hints help to optimize database queries and potentially improve application performance by influencing the decision-making process of the optimizer. We’ll also discuss their functionality and how to apply them effectively.

在本教程中，我们将探讨 Spring Data JPA 中查询提示的基本原理。这些提示有助于优化数据库查询，并通过影响优化器的决策过程来潜在地提高应用程序性能。我们还将讨论它们的功能以及如何有效地应用它们。

2. Understanding Query Hints

2.理解查询提示

Query hints in Spring Data JPA are a powerful tool that can help optimize database queries and improve application performance. Unlike directly controlling execution, they influence the decision-making process of the optimizer.

Spring Data JPA 中的查询提示是一种强大的工具，可帮助优化数据库查询并提高应用程序性能。与直接控制执行不同，查询提示会影响优化器的决策过程。

In Spring Data JPA, we find these hints in the org.hibernate.annotations package, alongside various annotations and classes associated with Hibernate, a prevalent persistence provider. It’s crucial to note that the interpretation and execution of these hints often depend on the underlying persistence provider, such as Hibernate or EclipseLink, making them vendor-specific.

在 Spring Data JPA 中，我们可以在org.hibernate.annotations包中找到这些提示，以及与 Hibernate（一种流行的持久化提供程序）相关的各种注解和类。需要注意的是，这些提示的解释和执行通常取决于底层持久化提供商（如 Hibernate 或 EclipseLink），因此它们是特定于供应商的。

3. Using Query Hints

3.使用查询提示

Spring Data JPA offers various ways to leverage query hints for optimizing the database queries. Let’s explore the common approaches.

Spring Data JPA 提供了多种利用查询提示优化数据库查询的方法。让我们来探讨一下常见的方法。

3.1. Annotation-Based Configuration

3.1.基于注释的配置

Spring Data JPA provides an easy way to add query hints to the JPA queries using annotations. The @QueryHints annotation enables the specification of an array of JPA @QueryHint hints intended for application to the generated SQL query.

Spring Data JPA 提供了一种使用注解向 JPA 查询添加查询提示的简便方法。@QueryHints注解可指定用于生成的 SQL 查询的 JPA @QueryHint 提示数组。

Let’s consider the following example, where we set the JDBC fetch size hint to limit the result return size:

让我们看看下面的示例，在这个示例中，我们设置了 JDBC 抓取大小提示，以限制结果返回的大小：

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @QueryHints(value = { @QueryHint(name = "org.hibernate.fetchSize", value = "50") })
    List<Employee> findByGender(String gender);
}

In this example, we’ve added the @QueryHints annotation to the findByGender() method of the EmployeeRepository interface to control the number of entities fetched at once. Furthermore, we can apply the @QueryHints annotation at the repository level to impact all queries within the repository:

在本示例中，我们在 EmployeeRepository 接口的 findByGender() 方法中添加了 @QueryHints 注解，以控制一次获取的实体数量。此外，我们还可以在存储库级别应用 @QueryHints 注解，以影响存储库中的所有查询：

@Repository
@QueryHints(value = { @QueryHint(name = "org.hibernate.fetchSize", value = "50") })
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    // Repository methods...
}

This action ensures that the specified query hint applies to all queries within the EmployeeRepository interface, hence promoting consistency across the repository’s queries.

此操作可确保指定的查询提示适用于 EmployeeRepository 接口中的所有查询，从而促进整个存储库查询的一致性。

3.2. Configuring Query Hints Programmatically

3.2.以编程方式配置查询提示

In addition to annotation-based and dynamic approaches, we can configure query hints programmatically using the EntityManager object. This method offers granular control over query hint configuration. Below is an example of programmatically setting a custom SQL comment hint:

除了基于注解和动态的方法外，我们还可以使用 EntityManager 对象以编程方式配置查询提示。该方法提供了对查询提示配置的细粒度控制。下面是一个以编程方式设置自定义 SQL 注释提示的示例：

@PersistenceContext
private EntityManager entityManager;

@Override
List<Employee> findRecentEmployees(int limit, boolean readOnly) {
    Query query = entityManager.createQuery("SELECT e FROM Employee e ORDER BY e.joinDate DESC", Employee.class)
      .setMaxResults(limit)
      .setHint("org.hibernate.readOnly", readOnly);
    return query.getResultList();
}

In this example, we pass a boolean flag as an argument to indicate whether the hint should be set to true or false. This flexibility allows us to adapt the query behavior based on runtime conditions.

在此示例中，我们传递了一个 boolean 标志作为参数，用于指示提示应设置为 true 还是 false. 这种灵活性允许我们根据运行时条件调整查询行为。

3.3. Define the Named Query in the Entity

3.3.在实体中定义命名查询

Query hints can be applied using the @NamedQuery annotation directly within the Entity class. This allows us to define a named query alongside specific hints. For example, let’s consider the following code snippet:

可使用 @NamedQuery 注解直接在 Entity 类中应用查询提示。例如，让我们来看看以下代码片段：

@Entity
@NamedQuery(name = "selectEmployee", query = "SELECT e FROM Employee e",
  hints = @QueryHint(name = "org.hibernate.fetchSize", value = "50"))
public class Employee {
    // Entity properties and methods
}

Once defined within the Entity class, the named query selectEmployee with associated hints can be invoked using the EntityManager‘s createNamedQuery() method:

一旦在 Entity 类中定义了命名查询 selectEmployee，就可以使用 EntityManager 的 createNamedQuery() 方法调用带有相关提示的命名查询 selectEmployee：

List<Employee> employees = em.createNamedQuery("selectEmployee").getResultList();

4. Query Hint Usage Scenarios

4.查询提示使用场景

Query hints can be used in a variety of scenarios to optimize the query performance. Here are some common use cases.

查询提示可用于各种情况，以优化查询性能。下面是一些常见的使用案例。

4.1. Timeout Management

4.1.超时管理

In scenarios where queries may run for extended durations, it becomes crucial to implement effective timeout management strategies. By utilizing the javax.persistence.query.timeout hint, we can establish a maximum execution time for queries. This practice ensures that queries don’t exceed specified time thresholds.

在查询可能长时间运行的场景中，实施有效的超时管理策略变得至关重要。通过使用javax.persistence.query.timeout提示，我们可以为查询设置最长执行时间。

The hint accepts a value in milliseconds, and if the query exceeds the limit, it throws a LockTimeoutException. Here’s an example where we set  a timeout of 5000 milliseconds for retrieving active employees:

该提示接受以毫秒为单位的值，如果查询超过限制，就会抛出 LockTimeoutException 异常。下面是一个示例，我们为检索在职员工设置了 5000 毫秒的超时：

@QueryHints(value = {@QueryHint(name = "javax.persistence.query.timeout<em>"</em>, value = "5000")})
List<Employee> findActiveEmployees(long inactiveDaysThreshold);

4.2. Caching Query Results

4.2.缓存查询结果

Query hints can be used to enable caching of query results using the jakarta.persistence.cache.retrieveMode hint. When set to USE, JPA attempts to retrieve the entity from the cache first before going to the database. On the other hand, setting it to BYPASS instructs JPA to ignore the cache and fetch the entity directly from the database.

查询提示可用于使用 jakarta.persistence.cache.retrieveMode 提示启用查询结果的缓存。当设置为 USE 时，JPA 会尝试首先从缓存中检索实体，然后再访问数据库。另一方面，将其设置为 BYPASS 会指示 JPA 忽略缓存并直接从数据库中获取实体。

Furthermore, we can also use jakarta.persistence.cache.storeMode to specify how JPA should handle storing entities in the second-level cache. When set to USE, JPA adds entities to the cache and updates existing ones. BYPASS mode instructs Hibernate to only update existing entities in the cache but not add new ones. Lastly, REFRESH mode refreshes entities in the cache before retrieving them, ensuring that the cached data is up-to-date.

此外，我们还可以使用jakarta.persistence.cache.storeMode来指定 JPA 应如何处理在二级缓存中存储实体的问题。当设置为USE时，JPA 将向缓存中添加实体并更新现有实体。BYPASS模式指示 Hibernate 只更新缓存中的现有实体，而不添加新实体。最后，REFRESH 模式会在检索缓存中的实体前刷新它们，确保缓存数据是最新的。

Below is an example demonstrating the usage of these hints:

下面的示例演示了这些提示的用法：

@QueryHints(value = {
    @QueryHint(name = "jakarta.persistence.cache.retrieveMode", value = "USE"),
    @QueryHint(name = "jakarta.persistence.cache.storeMode", value = "USE")
})
List<Employee> findEmployeesByName(String name);

In this scenario, both retrieveMode and storeMode are configured to USE, indicating that Hibernate actively utilizes the second-level cache for both retrieving and storing entities.

在这种情况下，retrieveMode 和 storeMode都被配置为 使用，这表明 Hibernate 会主动利用二级缓存来检索和存储实体。

4.3. Optimizing Query Execution Plans

4.3.优化查询执行计划

Query hints can be used to influence the execution plan generated by the database optimizer. For instance, when the data remains unaltered, we can use the org.hibernate.readOnly hint to denote that the query is read-only:

查询提示可用于影响数据库优化器生成的执行计划。例如，当数据保持不变时，我们可以使用 org.hibernate.readOnly 提示来表示查询为只读查询：

@QueryHints(@QueryHint(name = "org.hibernate.readOnly", value = "true"))
User findByUsername(String username);

4.4. Custom SQL Comment

4.4.自定义 SQL 注释

The org.hibernate.comment hint allows for the addition of a custom SQL comment to queries, aiding in query analysis and debugging. This feature is particularly useful when we want to provide context or notes within the generated SQL statements.

org.hibernate.comment 提示允许在查询中添加自定义 SQL 注释，从而帮助进行查询分析和调试。当我们想在生成的 SQL 语句中提供上下文或注释时，该功能尤其有用。

Here’s an example:

这里有一个例子：

@QueryHints(value = { @QueryHint(name = "org.hibernate.comment", value = "Retrieve employee older than specified age\"") })
List findByAgeGreaterThan(int age);

5. Conclusion

5.结论

In this article, we learned about the importance of query hints in Spring Data JPA and their significant impact on optimizing database queries to enhance application performance. We explored various techniques, including annotation-based configuration and direct JPQL manipulation, to apply query hints effectively.

在本文中，我们了解了 Spring Data JPA 中查询提示的重要性及其对优化数据库查询以提高应用程序性能的重大影响。我们探索了有效应用查询提示的各种技术，包括基于注解的配置和直接 JPQL 操作。

As always, the source code for the examples is available over on GitHub.

与往常一样，这些示例的源代码可在 GitHub 上获取。