@Query Definitions With SpEL Support in Spring Data JPA – 在 Spring Data JPA 中支持 SpEL 的 @Query 定义

最后修改: 2023年 12月 28日

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

1. Overview

1.概述

SpEL stands for Spring Expression Language and is a powerful tool that can significantly enhance our interaction with Spring and provide an additional abstraction over configuration, property settings, and query manipulation.

SpEL 是 Spring Expression Language 的缩写，它是一种强大的工具，可显著增强我们与 Spring 的交互，并为配置、属性设置和查询操作提供额外的抽象。

In this tutorial, we’ll learn how to use this tool to make our custom queries more dynamic and hide database-specific actions in the repository layers. We’ll be working with @Query annotation, which allows us to use JPQL or native SQL to customize the interaction with a database.

在本教程中，我们将学习如何使用该工具使我们的自定义查询更具动态性，并在资源库层中隐藏特定于数据库的操作。我们将使用 @Query 注解，它允许我们使用 JPQL 或本地 SQL 来定制与数据库的交互。

2. Accessing Parameters

2.访问参数

Let’s first check how we can work with SpEL regarding the method parameters.

让我们先看看如何使用 SpEL 来处理方法参数。

2.1. Accessing by an Index

2.1.通过索引访问

Accessing parameters by an index isn’t optimal, as it might introduce hard-to-debug problems to the code. Especially when the arguments have the same types.

通过索引获取参数并非最佳选择，因为这可能会给代码带来难以调试的问题。尤其是当参数具有相同类型时。

At the same time, it provides us with more flexibility, especially at the development stage when the names of the parameters change often. IDEs might not handle updates in the code and the queries correctly.

同时，它还为我们提供了更大的灵活性，尤其是在参数名称经常变化的开发阶段。集成开发环境可能无法正确处理代码和查询的更新。

JDBC provided us with the ? placeholder we can use to identify the parameter’s position in the query. Spring supports this convention and allows writing the following:

JDBC为我们提供了?占位符，我们可以使用它来确定参数在查询中的位置：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (?1, ?2, ?3, ?4)",
  nativeQuery = true)
void saveWithPositionalArguments(Long id, String title, String content, String language);

So far, nothing interesting is happening. We’re using the same approach we used previously with the JDBC application. Note that @Modifying and @Transactional annotations are required for any queries that make changes in the database, and INSERT is one of them. All the examples for INSERT will use native queries because JPQL doesn’t support them.

到目前为止，没有发生任何有趣的事情。我们使用的方法与之前在 JDBC 应用程序中使用的方法相同。请注意，任何对数据库进行更改的查询都需要 @Modifying 和 @Transactional 注释，而 INSERT 就是其中之一。所有 INSERT 的示例都将使用本地查询，因为 JPQL 不支持本地查询。

We can rewrite the query above using SpEL:

我们可以使用 SpEL 重写上述查询：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (?#{[0]}, ?#{[1]}, ?#{[2]}, ?#{[3]})",
  nativeQuery = true)
void saveWithPositionalSpELArguments(long id, String title, String content, String language);

The result is similar but looks more cluttered than the previous one. However, as it’s SpEL, it provides us with all the rich functionality. For example, we can use conditional logic in the query:

结果很相似，但看起来比前一个更杂乱。不过，由于它是 SpEL，因此为我们提供了所有丰富的功能。例如，我们可以在查询中使用条件逻辑：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (?#{[0]}, ?#{[1]}, ?#{[2] ?: 'Empty Article'}, ?#{[3]})",
  nativeQuery = true)
void saveWithPositionalSpELArgumentsWithEmptyCheck(long id, String title, String content, String isoCode);

We used the Elvis operator in this query to check if the content was provided. Although we can write even more complex logic in our queries, it should be used sparingly as it might introduce problems with debugging and verifying the code.

我们在该查询中使用了 Elvis 操作符来检查是否提供了内容。虽然我们可以在查询中编写更复杂的逻辑，但应谨慎使用，因为这可能会给调试和验证代码带来问题。

2.2. Accessing by a Name

2.2.通过名称访问

Another way we can access parameters is by using a named placeholder, which usually matches the parameter name, but it’s not a strict requirement. This is yet another convention from JDBC; the named parameter is marked with the :name placeholder. We can use it directly:

我们访问参数的另一种方法是使用命名占位符，它通常与参数名称相匹配，但这并不是一个严格的要求。这是 JDBC 的另一个约定；命名参数用 :name 占位符标记。我们可以直接使用它：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (:id, :title, :content, :language)",
  nativeQuery = true)
void saveWithNamedArguments(@Param("id") long id, @Param("title") String title,
  @Param("content") String content, @Param("isoCode") String language);

The only additional thing required is to ensure that Spring will know the names of the parameters. We can do it either in a more implicit way and compile the code using a -parameters flag or do it explicitly with the @Param annotation.

唯一需要额外做的就是确保 Spring 知道参数的名称。我们可以通过使用 -parameters 标记或 @Param 注解显式地实现这一点。

The explicit way is always better, as it provides more control over the names, and we won’t get problems because of incorrect compilation.

显式方法总是更好，因为它提供了对名称的更多控制，我们也不会因为编译错误而出现问题。

However, let’s rewrite the same query using SpEL:

不过，让我们用 SpEL 重写相同的查询：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (:#{#id}, :#{#title}, :#{#content}, :#{#language})",
  nativeQuery = true)
void saveWithNamedSpELArguments(@Param("id") long id, @Param("title") String title,
  @Param("content") String content, @Param("language") String language);

Here, we have standard SpEL syntax, but additionally, we need to use # to distinguish the parameter name from an application bean. If we omit it, Spring will try to look for beans in the context with the names id, title, content, and language.

在这里，我们使用的是标准 SpEL 语法，但此外，我们还需要使用 # 将参数名称与应用程序 Bean 区分开来。如果我们省略它，Spring 将尝试在上下文中查找名称为 id、title、content 和 language 的 Bean。

Overall, this version is quite similar to a simple approach without SpEL. However, as discussed in the previous section, SpEL provides more capabilities and functionalities. For example, we can call the functions available on the passed objects:

总体而言，该版本与不使用 SpEL 的简单方法非常相似。不过，正如上一节所讨论的，SpEL 提供了更多的能力和功能。例如，我们可以调用传递对象上的函数：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (:#{#id}, :#{#title}, :#{#content}, :#{#language.toLowerCase()})",
  nativeQuery = true)
void saveWithNamedSpELArgumentsAndLowerCaseLanguage(@Param("id") long id, @Param("title") String title,
  @Param("content") String content, @Param("language") String language);

We can use the toLowerCase() method on a String object. We can do conditional logic, method invocation, concatenation of Strings, etc. At the same time, having too much logic inside @Query might obscure it and make it tempting to leak business logic into infrastructure code.

我们可以在 String 对象上使用 toLowerCase() 方法。我们可以执行条件逻辑、方法调用、String 连接等操作。同时，在 @Query 中包含过多逻辑可能会使其模糊不清，并诱使业务逻辑泄漏到基础架构代码中。

2.3. Accessing Object’s Fields

2.3.访问对象字段

While previous approaches were more or less mirroring the capabilities of JDBC and prepared queries, this one allows us to use native queries in a more object-oriented way. As we saw previously, we can use simple logic and call the objects’ methods in SpEL. Also, we can access the objects’ fields:

以前的方法或多或少反映了 JDBC 和 prepared queries 的功能，而现在的方法允许我们以更面向对象的方式使用本地查询。如前所述，我们可以在 SpEL 中使用简单的逻辑并调用对象的方法。此外，我们还可以访问对象的字段：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (:#{#article.id}, :#{#article.title}, :#{#article.content}, :#{#article.language})",
  nativeQuery = true)
void saveWithSingleObjectSpELArgument(@Param("article") Article article);

We can use the public API of an object to get its internals. This is quite a useful technique as it allows us to keep the signatures of our repositories tidy and don’t expose too much. It allows us to even reach to nested objects. Let’s say we have an article wrapper:

我们可以使用对象的公共 API 来获取其内部信息。这是一项相当有用的技术，因为它可以让我们保持软件源签名的整洁，避免暴露太多内容。它甚至可以让我们接触到嵌套对象。比方说，我们有一个文章包装器：

public class ArticleWrapper {
    private final Article article;
    public ArticleWrapper(Article article) {
        this.article = article;
    }
    public Article getArticle() {
        return article;
    }
}

And we can use it in our example:

我们可以在示例中使用它：

@Modifying
@Transactional
@Query(value = "INSERT INTO articles (id, title, content, language) "
  + "VALUES (:#{#wrapper.article.id}, :#{#wrapper.article.title}, " 
  + ":#{#wrapper.article.content}, :#{#wrapper.article.language})",
  nativeQuery = true)
void saveWithSingleWrappedObjectSpELArgument(@Param("wrapper") ArticleWrapper articleWrapper);

Thus, we can treat the arguments as Java objects inside SpEL and use any available fields or methods. We can add logic and method invocation to this query as well.

因此，我们可以将参数视为 SpEL 中的 Java 对象，并使用任何可用的字段或方法。我们还可以在此查询中添加逻辑和方法调用。

Additionally, we can use this technique with Pageable to get the information from the object, for example, offset or the page size, and add it to our native query. Although Sort is also an object, it has a more complex structure and would be harder to use.

此外，我们还可以将此技术与 Pageable 结合使用，从对象中获取偏移量或页面大小等信息，并将其添加到本地查询中。虽然 Sort 也是一个对象，但它的结构更为复杂，使用起来也更为困难。

3. Referencing an Entity

3.引用实体

Reducing duplicated code is a good practice. However, custom queries might make it challenging. Even if we have similar logic to extract to a base repository, the tables’ names are different, making it hard to reuse them.

减少重复代码是一种很好的做法。然而，自定义查询可能会让这一做法变得具有挑战性。即使我们有类似的逻辑提取到基础存储库，但表的名称不同，很难重复使用。

SpEL provides a placeholder for an entity name, which it infers from the repository parametrization. Let’s create such a base repository:

SpEL 为实体名称提供了一个占位符，它可以从资源库参数化中推导出实体名称。让我们创建这样一个基础资源库：

@NoRepositoryBean
public interface BaseNewsApplicationRepository<T, ID> extends JpaRepository<T, ID> {
    @Query(value = "select e from #{#entityName} e")
    List<Article> findAllEntitiesUsingEntityPlaceholder();

    @Query(value = "SELECT * FROM #{#entityName}", nativeQuery = true)
    List<Article> findAllEntitiesUsingEntityPlaceholderWithNativeQuery();
}

We’ll have to use a couple of additional annotations to make it work. The first one is @NoRepositoryBean. We need this to exclude this base repository from instantiation. As it doesn’t have specific parametrization, the attempt to create such a repository will fail the context. Thus, we need to exclude it.

我们必须使用几个附加注解来使其工作。第一个注解是 @NoRepositoryBean 。我们需要它来从实例化中排除该基础版本库。由于它没有特定的参数化，创建此类版本库的尝试将导致上下文失败。因此，我们需要将其排除在外。

The query with JPQL is quite straightforward and will use the entity name of a given repository:

JPQL 的查询非常简单，将使用给定存储库的实体名称：

@Query(value = "select e from #{#entityName} e")
List<Article> findAllEntitiesUsingEntityPlaceholder();

However, the case with a native query isn’t so simple. Without any additional changes and configurations, it will try to use the entity name, in our case, Article, to find the table:

但是，本地查询的情况就没那么简单了。在没有任何额外更改和配置的情况下，它会尝试使用实体名称（在我们的例子中为 Article）来查找表：

@Query(value = "SELECT * FROM #{#entityName}", nativeQuery = true)
List<Article> findAllEntitiesUsingEntityPlaceholderWithNativeQuery();

However, we don’t have such a table in the database. In the entity definition, we explicitly stated the name of the table:

但是，我们的数据库中并没有这样一个表。在实体定义中，我们明确指出了表的名称：

@Entity
@Table(name = "articles")
public class Article {
// ...
}

To handle this problem, we need to provide the entity with the matching name to our table:

要解决这个问题，我们需要向表提供名称匹配的实体：

@Entity(name = "articles")
@Table(name = "articles")
public class Article {
// ...
}

In this case, both JPQL and the native query will infer a correct entity name, and we’ll be able to reuse the same base queries across all entities in our application.

在这种情况下，JPQL 和本地查询都会推断出正确的实体名称，我们就可以在应用程序的所有实体中重复使用相同的基本查询。

4. Adding a SpEL Context

4.添加 SpEL 上下文

As pointed out, while referencing arguments or placeholders, we must provide an additional # before their names. This is done to distinguish the bean names from the argument names.

如前所述，在引用参数或占位符时，我们必须在其名称前提供一个额外的 # 。这样做是为了区分 Bean 名称和参数名称。

However, we cannot use beans from the Spring context directly in the queries. IDEs usually provide hints about beans from the context, but the context would fail. This happens because @Value and similar annotations and @Query are handled differently. We can refer to the beans from the context of the former but not the latter.

但是，我们不能在查询中直接使用 Spring 上下文中的 Bean。集成开发环境通常会提供有关上下文中 Bean 的提示，但上下文会失效。出现这种情况是因为 @Value 和类似注解与 @Query 的处理方式不同。

At the same time, we can use EvaluationContextExtension to register beans in the SpEL context, and this way, we can use them in @Query. Let’s imagine the following situation – we would like to find all the articles from our database but filter them based on the locale settings of a user:

同时，我们可以使用 EvaluationContextExtension 在 SpEL 上下文中注册 Bean，这样，我们就可以在@Query.中使用它们。让我们设想一下以下情况：我们希望从数据库中查找所有文章，但要根据用户的本地设置对它们进行筛选：

@Query(value = "SELECT * FROM articles WHERE language = :#{locale.language}", nativeQuery = true)
List<Article> findAllArticlesUsingLocaleWithNativeQuery();

This query would fail because we cannot access the locale by default. We need to provide our custom EvaluationContextExtension that would hold the information about the user’s locale:

该查询将失败，因为我们默认情况下无法访问本地语言。我们需要提供自定义的EvaluationContextExtension，它将保存用户的本地信息：

@Component
public class LocaleContextHolderExtension implements EvaluationContextExtension {

    @Override
    public String getExtensionId() {
        return "locale";
    }

    @Override
    public Locale getRootObject() {
        return LocaleContextHolder.getLocale();
    }
}

We can use LocaleContextHolder to access the current locale anywhere in the application. The only thing to note is that it’s tied to the user’s request and inaccessible outside this scope. We need to provide our root object and the name. Optionally, we can also add properties and functions, but we’ll work only with a root object for this example.

我们可以使用LocaleContextHolder来访问应用程序中任何位置的当前位置信息。唯一需要注意的是，它与用户请求绑定，在此范围外无法访问。我们需要提供根对象和名称。我们还可以选择添加属性和函数，但在本例中我们只使用根对象。

Another step we need to take before we’ll be able to use locale inside @Query is to register locale interceptor:

在@Query中使用locale之前，我们需要采取的另一个步骤是注册locale拦截器：

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        LocaleChangeInterceptor localeChangeInterceptor = new LocaleChangeInterceptor();
        localeChangeInterceptor.setParamName("locale");
        registry.addInterceptor(localeChangeInterceptor);
    }
}

Here, we can add information about the parameter we’ll be tracking, so whenever a request contains a locale parameter, the locale in the context will be updated. It’s possible to check the logic by providing the locale in the request:

在这里，我们可以添加要跟踪的参数信息，因此只要请求中包含了本地化参数，上下文中的本地化信息就会被更新。我们可以通过在请求中提供本地语言来检查逻辑：

@ParameterizedTest
@CsvSource({"eng,2","fr,2", "esp,2", "deu, 2","jp,0"})
void whenAskForNewsGetAllNewsInSpecificLanguageBasedOnLocale(String language, int expectedResultSize) {
    webTestClient.get().uri("/articles?locale=" + language)
      .exchange()
      .expectStatus().isOk()
      .expectBodyList(Article.class)
      .hasSize(expectedResultSize);
}

EvaluationContextExtension can be used to dramatically increase the power of SpEL, especially while using @Query annotations. The ways to use this can range from security and role restrictions to feature flagging and interaction between schemas.

EvaluationContextExtension可用于显著增强 SpEL 的功能，尤其是在使用 @Query 注释时。使用方法包括安全和角色限制、特征标记以及模式之间的交互。

5. Conclusion

5.结论

SpEL is a powerful tool, and as with all powerful tools, people tend to overuse them and attempt to solve all the problems using only it. It’s better to use complex expressions reasonably and only in cases when necessary.

SpEL 是一个功能强大的工具，与所有功能强大的工具一样，人们往往会过度使用它，并试图只用它来解决所有问题。只有在必要的情况下，才合理地使用复杂的表达式。

Although IDEs provide SpEL support and highlighting, complex logic might hide the bugs that would be hard to debug and verify. Thus, use SpEL sparingly and avoid “smart code” that might be better expressed in Java rather than hidden inside SpEL.

虽然集成开发环境提供了 SpEL 支持和高亮显示，但复杂的逻辑可能会隐藏难以调试和验证的错误。因此，请谨慎使用 SpEL，避免使用 “智能代码”，这些代码最好用 Java 来表达，而不是隐藏在 SpEL 中。

As usual, all the code used in the tutorial is available over on GitHub.

与往常一样，本教程中使用的所有代码都可以在 GitHub 上获取。