Spring Data JPA Custom Queries using @Query Annotation

In an earlier article, I explained how to create and use derived query methods to retrieve data from the database in Spring Data JPA. This article is an extension of the previous article to learn how to use the @Query annotation to define JPQL (Java Persistence Query Language) and native queries in Spring Data JPA.

Derived queries are good as long as they are not complex. As the number of query parameters goes beyond 3 to 4, you need a more flexible strategy. Then add sorting, filtering, comparison, and result size limiting keywords to derived queries, and it would have become practically impossible to read and maintain these queries.

For such complicated situations, you should use the Spring Data JPA's @Query annotation to define a custom JPQL or native SQL query instead.

The @Query annotation defines queries directly on repository methods. This gives you complete flexibility to run any query without following the method naming conventions.

Create an Application

We have already created a Spring Data JPA and H2 database application for the previous tutorial. Let us extend this application to add another entity and a repository interface.

Add the following Note entity to store notes:

Note.java

package com.attacomsian.jpa.custom.domains;

import javax.persistence.*;
import java.util.Date;

@Entity
public class Note {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String title;
    private int priority;
    private boolean featured;
    private Date created;

    public Note() {
    }

    public Note(String title, int priority, boolean featured, Date created) {
        this.title = title;
        this.priority = priority;
        this.featured = featured;
        this.created = created;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}

Next, add the following NoteRepository interface to save and fetch notes from the database:

NoteRepository.java

package com.attacomsian.jpa.custom.repositories;

import com.attacomsian.jpa.custom.domains.Note;
import org.springframework.data.repository.CrudRepository;

public interface NoteRepository extends CrudRepository<Note, Long> {

    // TODO: add custom methods
}

JPQL vs Native SQL Query

According to Wikipedia:

The Java Persistence Query Language (JPQL) is a platform-independent object-oriented query language defined as part of the Java Persistence API (JPA) specification. JPQL is used to make queries against entities stored in a relational database. It is heavily inspired by SQL, and its queries resemble SQL queries in syntax, but operate against JPA entity objects rather than directly with database tables.

JPQL is just an object-oriented way of defining queries based on entity attributes. Spring Data JPA supports both JPQL as well as native SQL queries. The JPA implementation you use (Hibernate) will execute the query and return the result. The only downside of using JPQL is that it supports a subset of the SQL standard. So, it may not be a good choice for complex queries.

Let us define a custom query using JPQL to retrieve all featured notes:

@Query("SELECT n FROM Note n WHERE n.featured = true")
List<Note> findByActiveNotes();

// SELECT clause is optional - it can be omitted
@Query("FROM Note n WHERE n.featured = true")
List<Note> findByActiveNotes();

The same query can be written in native SQL format. All you need to do is just set the nativeQuery attribute value to true and define the native SQL query in the value attribute of the @Query annotation:

@Query(value = "SELECT * FROM Notes n WHERE n.featured = 1",
            nativeQuery = true)
List<Note> findByFeaturedNotesNative();

Positional vs Named Bind Parameters

Bind parameters act as a placeholder in a custom query that must be replaced with actual values before the query gets executed. There are two ways to bind parameters in Spring Data JPA. You can either use positional (also called indexed) or named bind parameters. Spring Data JPA treats both JPQL and native SQL queries bind parameters the same way.

Positional Parameters

A positional bind parameter is referenced by its position in the query. These are defined with ? followed by a number that specifies the position (?1, ?2, etc.). Spring Data JPA will automatically set the bind parameter values. It replaces the value of each method parameter with the value of a bind parameter in the same position. For example, the first method parameter value becomes the bind parameter value at position 1, the second method parameter value becomes the bind parameter value at position 2, and so on.

Here is an example of positional-based bind parameters:

// single bind parameter
@Query("SELECT n FROM Note n WHERE n.title = ?1")
List<Note> findByTitlePositionalBind(String title);

// multiple bind parameters
@Query("SELECT n FROM Note n WHERE n.title = ?1 AND n.featured = ?2")
List<Note> findByTitleAndFeaturedPositionalBind(String title, boolean featured);

Named Parameters

Named bind parameters are another way of passing method parameter values to the query bind parameters. A named bind parameter starts with : followed by the parameter name. It allows you to pass method parameters in any order without worrying about their position.

You can use the @Param annotation to specify the name of the bind parameter in the method definition. Each method parameter annotated with @Param must have a corresponding bind parameter in the JPQL or SQL query. Named parameters are easier to read and less error-prone.

Let us have an example to demonstrate how to use named bind parameters:

// single named parameter
@Query("SELECT n FROM Note n WHERE n.title = :title")
List<Note> findByTitleNamedBind(@Param("title") String title);

// multiple named parameters
@Query("SELECT n FROM Note n WHERE n.title = :title AND n.featured = :featured")
List<Note> findByTitleAndFeaturedNamedBind(@Param("featured") boolean featured,
                                           @Param("title") String title);

Named bind parameters are helpful when you want to use a single method parameter value multiple times in the query. A common example is a search query:

@Query("SELECT u FROM User u WHERE " +
        "lower(u.name) LIKE lower(CONCAT('%', :keyword, '%')) OR " +
        "lower(u.email) LIKE lower(CONCAT('%', :keyword, '%'))")
List<User> searchUsers(@Param("keyword") String keyword);

JPQL Selection Queries

The next step is to write some JPQL selection queries to retrieve data from the database. We'll be writing custom queries for the following JPA keywords using the @Query annotation:

And / Or
Equality
Ignore Case
Not Equal
Like / Contains / Starts With / Ends With
Less Than / Greater Than
Between
Before / After
Null / Not Null
In

// And / Or
@Query("SELECT n FROM Note n WHERE n.title = ?1 AND n.featured = ?2 OR n.priority = ?3")
List<Note> findByTitleAndFeaturedOrPriority(String title, boolean featured, int priority);

// Equality
@Query("SELECT n FROM Note n WHERE n.title = ?1")
List<Note> findByTitle(String title);

// Ignore Case
@Query("SELECT n FROM Note n WHERE lower(n.title) = lower(?1) ")
List<Note> findByTitleIgnoreCase(String title);

// Not Equal
@Query("SELECT n FROM Note n WHERE n.title <> ?1")
List<Note> findByTitleNotEqual(String title);

// Like / Contains / Starts With / Ends With
@Query("SELECT n FROM Note n WHERE n.title LIKE ?1")
List<Note> findByTitleLike(String pattern);

// Less Than
@Query("SELECT n FROM Note n WHERE n.priority < ?1")
List<Note> findByPriorityLessThan(int priority);

// Greater Than
@Query("SELECT n FROM Note n WHERE n.priority > ?1")
List<Note> findByPriorityGreaterThan(int priority);

// Between
@Query("SELECT n FROM Note n WHERE n.priority BETWEEN  ?1 AND ?2")
List<Note> findByPriorityBetween(int start, int end);

// Before
@Query("SELECT n FROM Note n WHERE n.created < ?1")
List<Note> findByCreatedBefore(Date before);

// After
@Query("SELECT n FROM Note n WHERE n.created > ?1")
List<Note> findByCreatedAfter(Date before);

// Null
@Query("SELECT n FROM Note n WHERE n.title IS NULL")
List<Note> findByTitleIsNull();

// Not Null
@Query("SELECT n FROM Note n WHERE n.title IS NOT NULL")
List<Note> findByTitleIsNotNull();

// In
@Query("SELECT n FROM Note n WHERE n.priority IN ?1")
List<Note> findByPriorityIn(Set<Integer> priorities);

Sorting Custom Query Results

Like derived queries, you can add sorting to any custom query in Spring Data JPA. For static ordering, the simplest and fastest way is to use the JPQL's ORDER BY clause inside the query. It is similar to what you use for the native SQL query. Here is an example:

// sort notes in ascending order
@Query("SELECT n FROM Note n WHERE n.title = ?1 ORDER BY n.priority ASC")
List<Note> findByTitleOrderByPriorityAsc(String title);

// sort notes in descending order
@Query("SELECT n FROM Note n WHERE n.featured = ?1 ORDER BY n.created DESC")
List<Note> findByFeaturedOrderByCreatedDesc(boolean featured);

For dynamic sorting, Spring Data JPA allows you to add a special parameter of type Sort to the custom method definition. Spring Data JPA will then generate the ORDER BY clause automatically. This is similar to what we have used in a derived query:

@Query("SELECT n FROM Note n WHERE n.title = ?1")
List<Note> findByTitle(String title, Sort sort);

Now, if you want to call the above sorting method, you need to create a Sort object to specify the entity attributes and their ordering:

// sort by title ascending
List<Note> startupNotes = noteRepository.findByTitle("startup", Sort.by("title").ascending());

// sort by priority descending
List<Note> techNotes = noteRepository.findByTitle("tech", Sort.by("priority").descending());

// sort by title's length in ascending order
List<Note> lengthyNotes = noteRepository.findByTitle("job", JpaSort.unsafe("LENGTH(title)"));

Paginate Custom Query Results

In addition to sorting, Spring Data JPA allows you to apply paging to your custom query results. When you use pagination, you only get a subset of the complete result as a Page object. A Page knows the total number of elements and pages available.

To use pagination in a JPQL query, you pass the special parameter Pageable to your method definition. Spring Data JPA will automatically generate the required code to handle the pagination of the query result.

Here is an example:

@Query("SELECT n FROM Note n")
Page<Note> findAllNotesWithPagination(Pageable pageable);

When you call the findAllNotesWithPagination method, you need to pass an object that implements the Pageable interface:

Pageable pageable = PageRequest.of(0, 10);
Page<Note> notePage = noteRepository.findAllNotesWithPagination(pageable);

Sorting can also be added to the PageRequest instance:

Pageable pageable = PageRequest.of(0, 10, Sort.by("title").descending());
Page<Note> notePage = noteRepository.findAllNotesWithPagination(pageable);

Update Queries with `@Modifying` Annotation

The @Query annotation is not just limited to defining queries that read data from the database. You can also use it to declare queries that insert, update, or delete records into the database.

Since these queries change the state of the database, they are treated differently. You need to explicitly tell Spring Data JPA that your custom query changes the data by annotating the repository method with an additional @Modifying annotation. It will then execute the custom query as an update operation.

Here are some examples that demonstrate how to use the @Modifying annotation for update queries:

@Modifying
@Query("UPDATE Note n SET n.title = ?1 WHERE n.id = ?2")
int updateTitleById(String title, Long id);

@Modifying
@Query("DELETE FROM Note n WHERE n.title = ?1")
void deleteByTitle(String title);

@Modifying
@Query("UPDATE Note n SET n.title = ?1 WHERE n.id IN ?2")
int bulkUpdateTitle(String title, Set<Long> id);

@Modifying
@Query("DELETE FROM Note n WHERE n.featured = ?1 AND n.id IN ?2")
void bulkDeleteByFeatured(boolean featured, Set<Long> id);

Dynamic Queries with SpEL Expressions

Spring Data JPA also allows you to use the Spring Expression Language (SpEL) expressions in custom queries defined using the @Query annotation. Upon query execution, these expressions are evaluated against a predefined set of variables.

Generic Entity Names

In the above queries, we always referenced the entity by its name. However, you can replace the actual entity name with the entityName expression variable. This allows you to define queries for generic repositories. The entityName variable is helpful if you decide to rename the entity in the future. Because you don't need to update all queries that reference the old entity name.

The following example query avoids referencing the Note entity by its name:

@Query("SELECT n from #{#entityName} n WHERE n.title = ?1")
List<Note> findByTitleGeneric(String title);

Spring Data JPA will replace the #{#entityName} SpEL expression with the actual entity name of the domain type of the repository. In the above example, Spring Data JPA replaces #{#entityName} with Note.

Advanced `LIKE` Expressions

Another example of SpEL expressions is the definition of advanced LIKE expressions. You can, for example, append % to the beginning and end of a bind parameter and change the given parameter value to lower case:

@Query("SELECT n FROM Note n WHERE lower(n.title) LIKE %?#{[0].toLowerCase()}%")
List<Note> findByTitleIgnoreCaseSpEL(String title);

Source Code: Download the complete source code from GitHub available under MIT license.

Conclusion

That's all. In this article you have learned about different ways to define custom queries in Spring Data JPA using the @Query annotation. Custom queries defined through the @Query annotation take precedence over all other query generation strategies, including derived queries.

The @Query annotation gives you complete control over the executed query. You can choose between a JPQL or a native SQL query. By default, Spring Data JPA, expects a JPQL query with the @Query annotation. If you want to run a native query instead, you set the nativeQuery parameter to true.

The @Query annotation can also define modifying queries that insert, update, or remove records from the database. However, you need to annotate repository methods that execute the modifying queries with the @Modifying annotation.

Continue reading the next part of this tutorial that explains how to create and use named JPQL and native queries.

Spring Data JPA Custom Queries using @Query Annotation

Create an Application

JPQL vs Native SQL Query

Positional vs Named Bind Parameters

Positional Parameters

Named Parameters

JPQL Selection Queries

Sorting Custom Query Results

Paginate Custom Query Results

Update Queries with `@Modifying` Annotation

Dynamic Queries with SpEL Expressions

Generic Entity Names

Advanced `LIKE` Expressions

Conclusion

Further Reading

You might also like...

Buy me a coffee ☕

✨ Learn to build modern web applications using JavaScript and Spring Boot

Spring Data JPA Custom Queries using @Query Annotation

Create an Application

JPQL vs Native SQL Query

Positional vs Named Bind Parameters

Positional Parameters

Named Parameters

JPQL Selection Queries

Sorting Custom Query Results

Paginate Custom Query Results

Update Queries with @Modifying Annotation

Dynamic Queries with SpEL Expressions

Generic Entity Names

Advanced LIKE Expressions

Conclusion

Further Reading

You might also like...

Buy me a coffee ☕

✨ Learn to build modern web applications using JavaScript and Spring Boot

Update Queries with `@Modifying` Annotation

Advanced `LIKE` Expressions