Spring Data JPA @Query

Introduction

Derived queries are good as long as they are not complex. For the complicated situations, you should rather use the Spring Data JPA’s @Query annotation to define a custom JPQL or native SQL query.

Select Query

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

Let’s assume that we have entity name is User

@Entity  
@Table(name = "users")  
@Data  
@NoArgsConstructor(force = true)  
public class User {  
    
    @Id @GeneratedValue 
    private Long id;
 
    @Column(name = "first_name")
    private String firstName;
    @Column(name = "last_name")
    private String lastName;
    @Column(name = "age")
    private Integer age;  
}  

1. JPQL

By default, the query definition uses JPQL.
Let us define a custom query using JPQL to retrieve User by first name :

@Repository  
public interface UserRepository extends JpaRepository<User, Long> {  
  
 @Query("FROM User u WHERE u.firstName = ?1") List<User> findUserByFirstName(String firstName);}  

2. Native query

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 users u WHERE u.last_name = ?1", nativeQuery = true)  
List<User> findUserByLastName(String lastName);  

Sorting and Paginate Custom Query Results

1. Sorting Custom Query Results

For some use cases, you might want to retrieve the query result in a specific order, for static ordering, the simplest and fastest way is to use the JPQL’s ORDER BY clause inside the query :

@Query("FROM User u WHERE u.age = ?1 ORDER BY u.lastName ASC")  
List<User> findUserByAgeOrderByLastNameAsc(Integer age);  

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.

@Query("FROM User u WHERE u.age = ?1")  
List<User> findUserByAgeWithSort(Integer age, 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:

userRepostiry.findUserByAgeWithSort(25, Sort.by(Sort.Direction.DESC, "lastName"));  

When the @Query annotation uses native SQL, then it’s not possible to define a Sort.

If we do, we’ll receive an exception:

org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination

In Spring Data JPA documentation, we find :

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL.

1. Paginate Custom Query Results

In addition to sorting, Spring Data JPA also 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 about the total number of elements and pages available.

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

Here is an example for JPQL :

@Query(value = "SELECT u FROM User u ORDER BY u.id")  
Page<User> findAllUsersWithPagination(Pageable pageable);  

You can use native queries for pagination by specifying the count query yourself, as shown in the following example:

@Query(  
	 value = "SELECT * FROM Users ORDER BY id",
	 countQuery = "SELECT count(*) FROM Users", 
	 nativeQuery = true)
Page<User> findAllUsersWithPagination(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 = userRepostiry.findAllUsersWithPagination(pageable);  

Update Queries With @Modifying

You can also use the @Query annotation to define queries that insert, update, or delete records in 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.

1. JPQL

The repository method that modifies the data has two difference in comparison to the select query, it has the @Modifying annotation and, of course, the JPQL query uses update instead of select:

@Modifying  
@Query("update User u set u.firstName = ?1 where u.lastName = ?2")  
int setFixedFirstnameFor(String firstName, String lastName);

The return value defines how many rows the execution of the query updated.

2.Native Query

We can modify the state of the database also with a native query:

@Modifying  
@Query(value= "update users u set u.first_name = ?1 where u.last_name = ?2", nativeQuery=true)  
int setFixedFirstnameFor(String firstName, String lastName);

Conclusion

In this article, we covered several ways of defining queries in Spring Data JPA repository methods using the @Query annotation.

The complete code examples used in this tutorial are available over on Github.