Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation

The previous part of this tutorial described how we can create database queries from the method names of our query methods.

Although that strategy has its advantages, it has its weaknesses as well. This blog post describes how we can avoid those weaknesses by using the @Query annotation.

We will also implement a simple search function that has two requirements:

• It must return todo entries whose title or description contains the given search term.
• The search must be case insensitive.

Let’s get started.

Creating Query Methods

We can configure the invoked database query by annotating the query method with the @Query annotation. It supports both JPQL and SQL queries, and the query that is specified by using the @Query annotation precedes all other query generation strategies.

In other words, if we create a query method called findbyId() and annotate it with the @Query annotation, Spring Data JPA won’t (necessarily) find the entity whose id property is equal than the given method parameter. It invokes the query that is configured by using the @Query annotation.

Example:

Let’s assume that our repository interface looks as follows:

1import org.springframework.data.repository.Repository;
2
3import java.util.Optional;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7 	@Query("SELECT t FROM Todo t WHERE t.title = 'title'")
8	public List<Todo> findById();
9}

Even though the findById() method follows the naming convention that is used to create database queries from the method name of the query method, the findById() method returns todo entries whose title is ’title’, because that is the query which is specified by using the @Query annotation.

Let’s find out how we can create both JPQL and SQL queries with the @Query annotation.

Creating JPQL Queries

We can create a JPQL query with the @Query annotation by following these steps:

1. Add a query method to our repository interface.
2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation.

The source code of our repository interface looks as follows:

1import org.springframework.data.repository.Repository;
2
3import java.util.Optional;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7 	@Query("SELECT t FROM Todo t WHERE t.title = 'title'")
8	public List<Todo> findByTitle();
9}

Creating SQL Queries

We can create a SQL query with the @Query annotation by following these steps:

1. Add a query method to our repository interface.
2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation’s value attribute.
3. Set the value of the @Query annotation’s nativeQuery attribute to true.

The source code of our repository interface looks as follows:

 1import org.springframework.data.repository.Repository;
 2
 3import java.util.Optional;
 4
 5interface TodoRepository extends Repository<Todo, Long> {
 6
 7 	@Query(value = "SELECT * FROM todos t WHERE t.title = 'title'",
 8			nativeQuery=true
 9	)
10	public List<Todo> findByTitle();
11}

Let’s move on and find out how we can create a query method that fulfils the requirements of our search function.

Implementing the Search Function

This section describes how we can implement a case insensitive search function that returns all todo entries whose title or description contains the given search term.

Implementing the Search Function With JPQL

We can create the JPQL query that fulfils the requirements of our search function by following these steps:

1. Create a query method that returns a list of Todo objects.
2. Annotate the method with the @Query annotation.
3. Create the JPQL query which uses named parameters and returns todo entries whose title or description contains the given search term (remember to ignore case). Set the created query as the value of the @Query annotation.
4. Add one method parameter to the query method and configure the name of the named parameter by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

 1import org.springframework.data.jpa.repository.Query;
 2import org.springframework.data.repository.Repository;
 3import org.springframework.data.repository.query.Param;
 4
 5import java.util.List;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9    @Query("SELECT t FROM Todo t WHERE " +
10            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
11            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
12    List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm);
13}

Let’s find out how we can implement our search function by using SQL.

Implementing the Search Function With SQL

We can create the SQL query that fulfils the requirements of our search function by following these steps:

1. Create a query method that returns a list of Todo objects.
2. Annotate the method with the @Query annotation.
3. Create the invoked SQL query by following these steps:
4. Create the SQL query which uses named parameters and returns todo entries whose title or description contains the given search term (remember to ignore case). Set the created query as the value of the @Query annotation’s value attribute.
5. Set the value of the @Query annotation’s nativeQuery attribute to true.
6. Add one method parameter to the query method and configure the name of the named parameter by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

 1import org.springframework.data.jpa.repository.Query;
 2import org.springframework.data.repository.Repository;
 3import org.springframework.data.repository.query.Param;
 4
 5import java.util.List;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9    @Query(value = "SELECT * FROM todos t WHERE " +
10            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
11            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))",
12            nativeQuery = true
13    )
14    List<Todo> findBySearchTermNative(@Param("searchTerm") String searchTerm);
15}

Let’s move on and find out when we should create our database queries with the @Query annotation.

When Should We Use the @Query Annotation?

The @Query annotation has the following benefits:

• It supports both JPQL and SQL.
• The invoked query is found above the query method. In other words, it is easy to find out what the query method does.
• There is no naming convention for query method names.

The @Query annotation has the following drawbacks:

• There is no support for dynamic queries.
• If we use SQL queries, we cannot change the used database without testing that our SQL queries work as expected.

When we think about the pros and cons of the @Query annotation, it becomes clear that the query methods that use this strategy aren’t as easy to write or read as the query methods that use the query generation from the method name strategy.

However, these query methods have two crucial advantages:

1. If we need to find out what database query is invoked by our query method, we can find the invoked query above the query method.
2. The code that uses our query methods is easy to read because we don’t have to use long method names.

Thus, if we don’t want to use the query generation from the method name strategy, because the names of our query methods would be too long, we should use the @Query annotation.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us three things:

• The database queries specified by using the @Query annotation precedes all other query generation strategies.
• The @Query annotation supports both JPQL and SQL queries.
• If we cannot create our database queries from the method names of our query methods, because the method names would be too long, we should create them by using the @Query annotation.

The next part of my Spring Data JPA tutorial describes how we can create database queries by using named queries.

P.S. You get can get the example application of this blog post from Github.