@Query Definitions With SpEL Support in Spring Data JPA

1. Overview

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.

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.

2. Accessing Parameters

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

2.1. Accessing by an Index

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:

@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.

We can rewrite the query above using 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:

@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.

2.2. Accessing by a Name

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:

@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.

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:

@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.

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:

@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.

2.3. Accessing Object’s Fields

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:

@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:

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.

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.

3. Referencing an Entity

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:

@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.

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

@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:

@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.

4. Adding a SpEL Context

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.

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.

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:

@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:

@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.

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

@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.

5. Conclusion

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.

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.