Ultimate Guide: Custom Queries with Spring Data JPA’s @Query Annotation

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Derived queries are very comfortable to use as long as the queries are not too complicated. But as soon as you use more than 2-3 query parameters or need to define multiple joins to other entities, you need a more flexible approach. In these situations, you better use Spring Data JPA’s @Query annotation to specify a custom JPQL or native SQL query.

The @Query annotation gives you full flexibility over the executed statement, and your method name doesn’t need to follow any conventions. The only thing you need to do is to define a method in your repository interface, annotate it with @Query, and provide the statement that you want to execute.

Spring Data JPA provides the required JPA code to execute the statement as a JPQL or native SQL query. Your preferred JPA implementation, e.g., Hibernate or EclipseLink, will then execute the query and map the result.

Let’s take a more detailed look at the two query options.

JPQL Queries

Most developers prefer JPQL queries because they allow you to define your query based on your domain model. Another advantage is that your JPA implementation handles the different database dialects so that the JPQL query is database agnostic. But these queries also have a downside. JPQL only supports a subset of the SQL standard. So, it’s not a great fit for complex queries.

When you define a JPQL query in your repository definition, Spring Data JPA only provides the required JPA code to execute that query. The query itself gets processed and executed by your JPA implementation. So, you can still use everything you learned about JPQL queries with Hibernate or EclipseLink with Spring Data JPA. It just gets a little bit easier.

In the following paragraphs, I will focus on the @Query annotation and expect that you are already familiar with JPQL. If that’s not the case, please take a look at my free Ultimate Guide to JPQL Queries or signup for my JPA for Beginners course.

Defining a Custom JPQL Query

Creating a JPQL query with Spring Data JPA’s @Query annotation is pretty straightforward. You need to annotate a method on your repository interface with the @Query annotation and provide a String with the JPQL query statement.

Here you can see an example containing 2 queries that select Author entities by their firstName or by their firstName and lastName. Please ignore the method and query parameters for now. We will take a closer look at them in a later part of this article.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("FROM Author WHERE firstName = ?1")
    List<Author> findByFirstName(String firstName);

    @Query("SELECT a FROM Author a WHERE firstName = ?1 AND lastName = ?2")
    List<Author> findByFirstNameAndLastName(String firstName, String lastName);

}

As you can see in the code snippet, both methods return a List of Author entities. But their SELECT clauses look very different.

The findByFirstName method doesn’t define any SELECT clause, and the findByFirstNameAndLastName method uses the Author entity to specify the projection. If you take a look at the executed SQL statements, you can see that Hibernate, my preferred JPA implementation, generates the same SELECT clause for both queries.

The SELECT clause of the findByFirstName query gets automatically generated so that it selects all columns mapped by the entities referenced in the FROM clause. In this example, these are all columns mapped by the Author entity.

2019-05-15 16:43:34.675 DEBUG 5204 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=?
2019-05-15 16:43:34.825 DEBUG 5204 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=? 
        and author0_.last_name=?

Based on this approach, you can define and execute any JPQL query that you might need for your use cases. So, feel free to give it a try and to write your own queries.

Sorting Your Query Results

For some use cases, you might want to retrieve the query result in a specific order. Using Spring Data JPA, you can define the sorting of your query results in 2 ways:

1. You can add an ORDER BY clause to your JPQL query or
2. You can add a parameter of type Sort to your method signature.

Using the ORDER BY Clause in JPQL

You’re probably familiar with the first option. The ORDER BY clause is defined in the JPA standard, and it is very similar to the ORDER BY clause you know from SQL. You simply reference one or more entity attributes and use ASC or DESC to specify if you want to sort them in ascending or descending order. I explained the ORDER BY clause and all other parts of a JPQL query in more details my guide to JPQL.

public interface AuthorRepository extends JpaRepository<Author, Long> {
        
    @Query("FROM Author WHERE firstName = ?1 ORDER BY lastName ASC")
    List<Author> findByFirstNameOrderByLastname(String firstName);
}

Using a Sort Object

With Spring Data JPA, you can also add a parameter of type Sort to your method definition. Spring Data JPA will then generate the required ORDER BY clause. That is the same approach as you can use in a derived query.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("FROM Author WHERE firstName = ?1")
    List<Author> findByFirstName(String firstName, Sort sort);
}

When you want to call the findAuthors method, you need to provide a Sort object. Its constructor expects an enumeration that defines the sorting direction and one or more Strings that reference the entity attributes, which you want to use to order the result, by their name.

Sort sort = new Sort(Direction.ASC, "firstName");
List<Author> authors = authorRepository.findByFirstName("Thorben", sort);

Independent of the approach you choose to define the sorting of your query result, your JPA implementation, e.g., Hibernate, will add an ORDER BY clause to the generated SQL query.

2019-05-15 16:42:43.580 DEBUG 13164 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=? 
    order by
        author0_.last_name ASC

Paginating Your Query Results

In addition to sorting, Spring Data JPA also provides very comfortable support for pagination. If you add a method parameter of type Pageable to your repository method, Spring Data JPA generates the required code to handle the pagination of the query result.

public interface AuthorRepository extends JpaRepository<Author, Long> {
    
    @Query("FROM Author WHERE firstName = ?1")
    List<Author> findByFirstName(String firstName, Pageable pageable);
}

When you call the findByFirstName method, you need to provide an object that implements the Pageable interface. You can do that by calling the of method on the PageRequest method with the number of the page and the size of each page as parameters.

Pageable pageable = PageRequest.of(0, 10);
List<Author> authors = authorRepository.findByFirstName("Thorben", pageable);

Spring Data JPA then calculates the required limit and offset values based on the provided page number and size and generates the JPQL query. For this JPQL query, your JPA implementation, e.g., Hibernate, then creates a SQL query and sends it to the database.

2019-05-15 17:31:19.699 DEBUG 15372 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=? limit ? offset ?

SpEL Expressions for Entity Names and Advanced Like Expressions

In addition to the previously discussed query features, Spring Data JPA also supports SpEL expressions within your query. You can use it to avoid hard references to the name of an entity or to create advanced like expressions.

Avoid Entity Name References

In the previous queries, I always referenced entities by their name. That prevents you from defining queries for generic repositories. It also causes a lot of work if you decide to rename an entity because you then need to update all queries that reference the old name of the entity.

You can avoid that by using the entityName variable in a SpEL expression. I do that in the following query to avoid referencing the Author entity by its name.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("FROM #{#entityName} WHERE firstName = ?1")
    List findByFirstName(String firstName);
}

Spring Data JPA replaces the #{#entityName} expression with the entityName of the domain type of the repository. So, in this example, Spring Data JPA replaces #{#entityName} with Author.

Define Advanced Like Expressions

Another great feature that you get with the SpEL expressions is the definition of advanced like expressions. You can, for example, append ‘%’ to the beginning and end of a parameter and change the provided bind parameter value to upper case.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("FROM Author WHERE UPPER(firstName) LIKE %?#{[0].toUpperCase()}%")
    List<Author> findByFirstNameContainingIgnoreCase(String firstName);
}

As you can see in the following log statements, Spring Data JPA called the toUpperCase() method on the value of the method parameter firstName and added a ‘%’ to the beginning and end of it. In the next step, it set the modified String as a bind parameter value.

2019-05-16 10:42:38.367 DEBUG 18752 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        upper(author0_.first_name) like ?
2019-05-16 10:42:38.385 TRACE 18752 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%THO%]
2019-05-16 10:42:38.408 TRACE 18752 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_0_] : [BIGINT]) - [1]
2019-05-16 10:42:38.422 TRACE 18752 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([first_na2_0_] : [VARCHAR]) - [Thorben]
2019-05-16 10:42:38.423 TRACE 18752 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([last_nam3_0_] : [VARCHAR]) - [Janssen]
2019-05-16 10:42:38.428 TRACE 18752 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([version4_0_] : [INTEGER]) - [0]

Native Queries

You can also use the @Query annotation to execute a native SQL query. As explained earlier, Spring Data JPA provides the required JPA code to execute the query. The query itself still gets handled by Hibernate or EclipseLink. So, you can use all your experience with these frameworks and read articles about them to get a deeper understanding of the JPA standard.

In the following paragraphs, I will focus on Spring Data JPA’s support for native queries. If you want to learn more about native queries in general, please read my article Native Queries- How to call native SQL queries with JPA & Hibernate.

Defining a Custom Native Query

Similar to the custom JPQL query, you can use the @Query annotation to specify a custom native SQL query. But you need to tell Spring Data JPA, that you are defining a native query, so that it can execute the query in the right way. You can do that by setting the nativeQuery attribute of the @Query annotation to true.

Here you can see an example of a repository method that uses a native SQL query to load all Author entities with a give firstName.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query(value = "SELECT * FROM author WHERE first_name = :firstName", nativeQuery = true)
    List<Author> findAuthorsByFirstName(@Param("firstName") String firstName);
}

The query that you provide as the value of the annotation will not be parsed or changed by your JPA implementation. It gets directly send to your database. So, you need to make sure that you provide a valid SQL statement and follow the specific dialect of your database.

2019-05-16 08:49:50.912 DEBUG 7332 --- [           main] org.hibernate.SQL                        : 
    SELECT
        * 
    FROM
        author 
    WHERE
        first_name = ?

Parameter Handling

To use both kinds of queries with Spring Data JPA, you need to be familiar with bind parameters. Bind parameters act as placeholders in your query for which you can set values before the query gets executes. They are regularly used in the WHERE clause of a query. You should always prefer them over inlining a parameter value because bind parameters:

• prevent SQL injection vulnerabilities,
• get automatically converted to the correct SQL type and
• enable your persistence provider and database to optimize your query.

Spring Data JPA handles the bind parameters of JPQL and native SQL queries in the same way. For each query, you can choose if you want to use positional or named bind parameters.

A positional bind parameter gets referenced by its position. In the query, positional bind parameters consist of a `?` followed by a number that specifies the position. Spring Data JPA then provides the required code to set the bind parameter values. It sets the value of each method parameter as the value of a bind parameter with the same position. So, the value of the first method parameter gets set as the value of the bind parameter at position 1, the value of the second method parameter as the value of the bind parameter at position 2 and so on.

Named bind parameters specify a name for each parameter. In your query, named bind parameters start with a ‘:’ followed by the name of the parameter. That makes them much more expressive and easier to reference. Spring Data JPA can provide the required code to set the values of these bind parameters. But it needs a little bit of help to match the method parameters with the bind parameters in your query. You either need to annotate the method parameter with @Param and specify the name of the parameter, or you need to compile your application with Java 8 or higher and set the compiler flag -parameters.

Here you can see an example of both bind parameter types.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("FROM Author WHERE firstName = ?1")
    List<Author> findByFirstName(String firstName);

    @Query("SELECT a FROM Author a WHERE firstName = :firstName AND lastName = :lastName")
    List<Author> findByFirstNameAndLastName(@Param("lastName") String firstName, @Param("firstName") String lastName);
}

As you can see in the code snippets, named bind parameters are much easier to read and are not as likely to cause problems during refactoring. You should, therefore, prefer named bind parameters in your queries.

Modifying Queries

You can not only use the @Query annotation to define queries that read data from your database. You can also create queries that add, change, or remove records in your database. That enables you to implement bulk update or remove operations which can provide substantial performance improvements.

These queries require a slightly different JPA code. That’s why you need to tell Spring Data JPA that your query modifies data by annotating your repository method with an additional @Modifying annotation. It will then execute the provide JPQL or native query as an update operation.

I use this approach in the following code snippet to specify a query that adds a provided prefix to the firstName of all Authors.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("UPDATE Author SET firstName = :prefix || firstName")
    @Modifying
    void addPrefixToFirstName(@Param("prefix") String prefix);
}

2019-05-16 10:04:27.805 DEBUG 20232 --- [           main] org.hibernate.SQL                        : 
    update
        author 
    set
        first_name=(?||first_name)

Conclusion

Spring Data JPA provides multiple options to define a query on your repository interface. You can:

• use derived queries for which Spring Data JPA generates the required query based on the name of the repository method. I explained that in great details in a previous tutorial.
• use the @Query annotation to define your own query. That’s what we discussed in this article.
• reference a named query which you specified on one of your entities. We will take a closer look at that in a future tutorial.

By using the @Query annotation, you get full control over the executed query. You can choose between a native SQL or a JPQL query. By default, Spring Data JPA expects that you provide a JPQL query. If you want to execute a native query, you need to set the nativeQuery parameter of the @Query annotation to true.

Both kinds of queries get processed by the underlying JPA implementation. That enables you to reuse all your Hibernate and EclipseLink knowledge and to use the query features supported by these frameworks.

You can also specify modifying queries which insert, update, or remove records from the database. Spring Data JPA needs to generate a slightly different JPA code for these. You, therefore, need to annotate repository methods that execute modifying queries with the @Modifying annotation.