Custom queries with @Query

Spring Data JPA provides a powerful and flexible way to work with databases using the Java Persistence API (JPA). One of the key features of Spring Data JPA is the ability to define custom queries using the @Query annotation.

@Query annotations: an overview

When using Spring Data JPA to query data from a database, developers can choose between using property expressions or custom @Query annotations to define their queries. While both options can be effective, there are several advantages to using custom @Query annotations that make them a popular choice among developers.

1. Increased flexibility. One of the primary advantages of using custom @Query annotations is the increased flexibility they provide. With property expressions, developers are limited to a set of predefined methods for querying data. However, with custom @Query annotations, developers have complete control over the SQL that is generated, allowing for more complex and specific queries.

2. Ability to use native SQL. Custom @Query annotations also provide the ability to use native SQL, which can be beneficial for developers who are more comfortable working with SQL than property expressions. Using native SQL can also give more fine-grained control over the generated SQL, resulting in better query performance.

3. Easier to maintain. When using property expressions, it can be challenging to maintain complex queries, as they can quickly become difficult to read and understand. In contrast, custom @Query annotations can be easier to maintain, as developers can write SQL statements in a way that is more intuitive and easier to read. Suppose you have an e-commerce website with a Product entity that has a OneToMany relationship with an OrderDetail entity. Each OrderDetail represents a specific product that was ordered, and has a quantity and a price. Now, you want to retrieve the top 10 most frequently ordered products, along with their total revenue (quantity * price) across all orders. You also want to filter the results to only include only products that have been ordered at least 10 times. Using property expressions, you could write a method in your ProductRepository interface like this:

List<Product> findTop10ByOrderDetailsQuantityGreaterThanEqualOrderByOrderDetailsQuantityDesc(int quantity);

@Query parameters

Let's begin by creating an entity of a user, which we will use next:

Then, let's declare a class for the defined entity:

@Entity
public class User {
    @Id
    @GeneratedValue
    private long id;
    private String name;
    private String lastName;
    @Column(unique=true)
    private String phoneNumber;
    private String country;
    @Column(unique=true)
    private String emailAddress;
    private int age;

    // constructor, getters, setters
}

@Entity
data class User(
    @Id
    @GeneratedValue
    val id: Long = 0,

    var name: String = "",

    var lastName: String = "",

    @Column(unique = true)
    var phoneNumber: String = "",

    var country: String = "",

    @Column(unique = true)
    var emailAddress: String = "",

    var age: Int = 0
)

The @Query annotation allows you to write custom SQL or JPQL (Java Persistence Query Language) statements and map the results to your domain model objects. You can use the name or positional parameters to pass them to your request.

Here's an example of using @Query to define a custom query in a Spring Data repository interface:

public interface UserRepository extends CrudRepository<User, Long> {
 
    @Query("SELECT u FROM User u WHERE u.emailAddress = ?1 AND u.age >= 21")
    User findAdultUserByEmail(String email);
}

@Repository
interface UserRepository : CrudRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.emailAddress = ?1 AND u.age >= 21")
    fun findAdultUserByEmail(email: String): User?
}

If we want to pass more than one parameter, the following placeholders will be ?2, ?3, and so on.

In some cases, when you need to get only one or a few fields of the model class, it is better to use a native SQL query.

Here's the equivalent native SQL query for the previous method, which will return only the user's id:

@Query(value = "SELECT id FROM user WHERE email_address = ?1 AND age >= 21", nativeQuery = true)
Long findAdultUserByEmail(String email);

@Query(value = "SELECT id FROM user WHERE email_address = ?1 AND age >= 21", nativeQuery = true)
fun findAdultUserIdByEmail(email: String): Long?

In this example, we added the value attribute to specify the native SQL query, and set the nativeQuery attribute to true.

You can also use named parameters in your query by using the : prefix, like this:

public interface UserRepository extends CrudRepository<User, Long> {
 
    @Query("SELECT u FROM User u WHERE u.email = :email AND u.age >= 21")
    User findAdultUserByEmail(@Param("email") String email);
}

@Repository
interface UserRepository : CrudRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.email = :email AND u.age >= 21")
    fun findAdultUserByEmail(@Param("email") email: String): User?
}

The @Param annotation is used to specify the name of the named parameter email that matches the method parameter String email. If you skip using @Param, Spring Data JPA will try to infer the parameter name from the method signature.

Consider the entry for a native SQL query:

public interface UserRepository extends CrudRepository<User, Long> {
 
    @Query(value = "SELECT * FROM user WHERE email_address = :email AND age >= 21", nativeQuery = true)
    User findAdultUserByEmail(@Param("email") String email);
}

@Repository
interface UserRepository : CrudRepository<User, Long> {

    @Query(value = "SELECT * FROM user WHERE email_address = :email AND age >= 21", nativeQuery = true)
    fun findAdultUserByEmail(@Param("email") email: String): User?
}

Also, you can use a collection parameter in a @Query annotation to pass in a list of values for an IN clause in a SQL query. Here's an example:

@Query("SELECT u FROM User u WHERE u.age >= 21 AND u.lastName IN :lastNames")
List<User> findAdultUsersByLastNames(@Param("lastNames") List<String> lastNames);

@Query("SELECT u FROM User u WHERE u.age >= 21 AND u.lastName IN :lastNames")
fun findAdultUsersByLastNames(@Param("lastNames") lastNames: List<String>): List<User>

Here is a similar example for SQL query:

@Query(value = "SELECT * FROM users WHERE age >= 21 AND last_name IN :lastNames", nativeQuery = true)
List<User> findAdultUsersByLastNames(@Param("lastNames") List<String> lastNames);

@Query(value = "SELECT * FROM users WHERE age >= 21 AND last_name IN :lastNames", nativeQuery = true)
fun findAdultUsersByLastNames(@Param("lastNames") lastNames: List<String>): List<User>

In the following example, we have a findByLastNames method that takes in a List of last names. The :lastNames named parameter in the @Query annotation corresponds to this parameter. The SQL query generated by this method will use an IN clause to match any users with a last name in the provided list.

Here's an example of how you could call this method:

List<String> lastNames = Arrays.asList("Doe", "Smith", "Johnson");
List<User> users = userRepository.findByLastNames(lastNames);

val lastNames = listOf("Doe", "Smith", "Johnson")
val users: List<User> = userRepository.findAdultUsersByLastNames(lastNames)

The generated SQL query might look something like this:

SELECT * FROM user WHERE last_name IN ('Doe', 'Smith', 'Johnson');

Sorting and pagination

Spring Data allows us to pass the Sort object as a parameter to a method annotated with @Query to specify the sorting order of the results. Here's an example:

@Query("SELECT u FROM User u WHERE u.age >= :minAge")
List<User> findUsersWithAgeGreaterThan(@Param("minAge") int minAge, Sort sort);

@Query("SELECT u FROM User u WHERE u.age >= :minAge")
fun findUsersWithAgeGreaterThan(@Param("minAge") minAge: Int, sort: Sort): List<User>

In the code snippet above, we're using the @Query annotation to define a custom query that selects all users with age greater than or equal to the minAge parameter. We're also using the Sort parameter to specify the desired sorting order for the results.

To use this method, you can pass in a Sort object that specifies the desired sorting direction and properties:

List<User> users = userRepository.findUsersWithAgeGreaterThan(18, Sort.by(Sort.Direction.DESC, "age"));

val users: List<User> = userRepository.findUsersWithAgeGreaterThan(18, Sort.by(Sort.Direction.DESC, "age"))

The generated JPQL query that we provided earlier would look something like this:

SELECT u FROM User u WHERE u.age >= :minAge ORDER BY u.age DESC

Let's come up with a more complex example of calling this method:

Sort sortByNameAndLastName = Sort.by(Sort.Direction.ASC, "lastName").and(Sort.by(Sort.Direction.DESC, "firstName"));
List<User> users = userRepository.findUsersWithAgeGreaterThan(18, sortByNameAndLastName);

val sortByNameAndLastName = Sort.by(Sort.Direction.ASC, "lastName").and(Sort.by(Sort.Direction.DESC, "firstName"))
val users: List<User> = userRepository.findUsersWithAgeGreaterThan(18, sortByNameAndLastName)

This invocation will return all users with age greater than or equal to 18, sorted first by last name in ascending order and then by first name in descending order.

SELECT u FROM User u WHERE u.age >= :minAge ORDER BY u.lastName ASC, u.firstName DESC

Unfortunately, Spring Data doesn't provide this feature for native SQL. But you can, of course, add your own ORDER BY clause to your query. Here is an example:

@Query(value = "SELECT * FROM users WHERE age >= :minAge" +
 "ORDER BY last_name ASC, first_name DESC", nativeQuery = true)
List<User> findUsersWithAgeGreaterThan(@Param("minAge") int minAge);

@Query(value = "SELECT * FROM users WHERE age >= :minAge " +
 "ORDER BY last_name ASC, first_name DESC", nativeQuery = true)
fun findUsersWithAgeGreaterThan(@Param("minAge") minAge: Int): List<User>

To implement pagination to a method with a @Query annotation in JPQL, you can pass Pageable as a parameter.

Let's look at this code:

@Query("SELECT u FROM User u WHERE u.country = :country")
Page<User> findByCountry(@Param("country") String country, Pageable pageable);

@Query("SELECT u FROM User u WHERE u.country = :country")
fun findByCountry(@Param("country") country: String, pageable: Pageable): Page<User>

This example demonstrates that we have also defined a Pageable parameter named pageable, which is used for pagination. Spring Data automatically generates a SQL query that includes the LIMIT and OFFSET clauses based on the pageable parameter.

Spring Data provides support for pagination with native SQL queries through the PageableExecutionUtils class. You can use this class to convert the results of your native SQL query to a Page object with pagination information. Here's an example of how to use PageableExecutionUtils with the findByCountry method:

@Query(value = "SELECT * FROM users WHERE country = :country",
       countQuery = "SELECT COUNT(*) FROM user WHERE country = :country",
       nativeQuery = true)
Page<User> findByCountry(@Param("country") String country, Pageable pageable);

@Query(value = "SELECT * FROM users WHERE country = :country",
           countQuery = "SELECT COUNT(*) FROM users WHERE country = :country",
           nativeQuery = true)
fun findByCountry(@Param("country") country: String, pageable: Pageable): Page<User>

When using a native SQL query with pagination in Spring Data, you need to provide two queries: one to retrieve the actual data and another to count the total number of records that match the query. The countQuery parameter is used to define the query for counting the number of records.

To use this method with pagination, you can simply call it like this:

Page<User> pageUserResult = UserRepository.findByCountry("Canada", PageRequest.of(0, 10));

val pageUserResult: Page<User> = userRepository.findByCountry("Canada", PageRequest.of(0, 10))

This will retrieve the first page of results that match the country value of "Canada", with a page size of 10. If there are more than 10 results, they will be included in subsequent pages. You can retrieve other pages by changing the page number in the PageRequest object.

The generated SQL query would look something like this:

SELECT u.* FROM users u WHERE u.country = "Canada" LIMIT 10 OFFSET 0

To use this method with sorting, you can simply call it like this:

Page<User> pageUserResult = UserRepository.findByCountry("Canada", PageRequest.of(0, 10, Sort.by("name"));

val pageUserResult: Page<User> = userRepository.findByCountry(
    "Canada",
    PageRequest.of(0, 10, Sort.by("name"))
)

Also just like with sorting, we can add OFFSET and LIMIT clauses to the query string:

@Query(value = "SELECT * FROM users WHERE country = :country LIMIT 10 OFFSET :page * 10",
       nativeQuery = true)
List<User> findByCountry(@Param("country") String country,@Param("page") int page);

@Query(value = "SELECT * FROM users WHERE country = :country LIMIT 10 OFFSET :page * 10", nativeQuery = true)
    fun findByCountry(@Param("country") country: String, @Param("page") page: Int): List<User>

Conclusion

Spring Data JPA provides a flexible and powerful way to define custom queries to retrieve data from a database with custom @Query annotations. By using @Query, you can write your own SQL or JPQL queries to retrieve data from a database and map the results to your domain objects.

@Query annotations also support pagination and sorting, which are important features when dealing with large datasets. By passing a Pageable or a Sort object as a parameter to your repository method, you can easily retrieve a subset of data and sort it according to specific criteria.

One of the main advantages of using custom @Query annotations are that they provide a high degree of flexibility in how you retrieve data from a database. This can be particularly useful when dealing with complex queries that cannot be expressed using the built-in query methods provided by Spring Data JPA.

However, it's important to keep in mind that custom queries can be more difficult to maintain than the built-in query methods. When you write your own queries, you are responsible for ensuring that they are correct, efficient, and up-to-date with changes to your database schema.

Overall, using custom @Query annotations can be a powerful tool for working with databases in Spring Data JPA. But remember to use it wisely, ensure that your queries are correct, efficient, and maintainable over time.