How do you implement custom queries using the @Query annotation in Spring Data JPA?

Table of Contents

• Introduction
  • 1. Custom Queries Using JPQL
    • Example: Basic Custom Query with JPQL
    • Example: Custom Query with Named Parameters
  • 2. Custom Queries Using Native SQL
    • Example: Custom Query with Native SQL
    • Example: Using Named Parameters with Native SQL
  • 3. Returning Non-Entity Results
    • Example: Query Returning a DTO
    • Example: Query Returning an Array
  • 4. Updating and Deleting Using @Query
    • Example: Update Query
    • Example: Delete Query
  • 5. Pagination and Sorting with Custom Queries
    • Example: Pagination with Custom Query
• Conclusion

Introduction

In Spring Data JPA, the @Query annotation allows you to define custom queries for database operations directly within your repository interfaces. This gives you the flexibility to write more complex queries that cannot be expressed using the default query derivation mechanism. With @Query, you can write JPQL (Java Persistence Query Language) or native SQL queries to handle specific use cases.

This guide will walk you through the process of using the @Query annotation for custom queries in Spring Data JPA, including both JPQL and native SQL examples.

1. Custom Queries Using JPQL

JPQL (Java Persistence Query Language) is an object-oriented query language that operates on entity objects rather than directly on database tables. It allows you to write queries based on the structure of the entity classes.

Example: Basic Custom Query with JPQL

Suppose you have an Employee entity, and you want to retrieve employees who are working in a particular department.

You can define a custom query in your repository interface using the @Query annotation:

In this example:

• The @Query annotation is used to specify a custom JPQL query.
• ?1 is a placeholder for the first parameter (department) passed to the findByDepartment method.

Example: Custom Query with Named Parameters

You can also use named parameters instead of positional ones, which can improve readability and avoid confusion with multiple parameters.

In this case:

• :department and :salary are named parameters that correspond to method parameters.
• @Param annotation is used to bind the method parameters to the query parameters.

2. Custom Queries Using Native SQL

If you need to write a query that cannot be expressed in JPQL (e.g., a query involving database-specific SQL functions or operations), you can use native SQL. Native SQL queries are written in the syntax of the underlying database.

Example: Custom Query with Native SQL

Suppose you want to use a native SQL query to retrieve employees whose salary is greater than a specified amount.

In this example:

• The nativeQuery = true attribute indicates that this is a native SQL query, not a JPQL query.
• The query uses * to select all columns from the employee table where the salary is greater than a specified amount.

Example: Using Named Parameters with Native SQL

You can also use named parameters in native SQL queries:

This approach works the same way as with JPQL but allows you to write SQL-specific queries.

3. Returning Non-Entity Results

Sometimes, you may want to execute a query that doesn't directly map to an entity but rather returns specific data, such as a projection or a calculated value. You can achieve this by using DTO (Data Transfer Objects) or by directly returning an array of values.

Example: Query Returning a DTO

First, create a DTO class:

Then, define the custom query in the repository:

In this case:

• new com.example.EmployeeDTO(e.name, e.department) is a constructor expression that creates a new EmployeeDTO for each result.
• The query returns a list of EmployeeDTO objects containing only the name and department fields of Employee entities whose salary is greater than the specified amount.

Example: Query Returning an Array

You can also return specific columns as an array:

In this case:

• The query returns a list of Object[], where each Object[] contains the name and department of an employee.

4. Updating and Deleting Using @Query

The @Query annotation can also be used for update or delete operations. For these types of queries, JPA doesn’t return any entity objects, but rather the number of affected rows or void.

Example: Update Query

In this case:

• @Modifying is used to indicate that the query modifies data (not a SELECT query).
• The query updates the salary of employees in a specific department.
• The method returns an int, which represents the number of rows affected by the update.

Example: Delete Query

This query deletes employees whose salary is below the specified amount, and the method returns the number of deleted rows.

5. Pagination and Sorting with Custom Queries

Spring Data JPA also supports pagination and sorting for custom queries, which can be useful when dealing with large datasets.

Example: Pagination with Custom Query

• The method signature includes Page<Employee>, which allows you to return paginated results.
• Pageable is passed as a method parameter, which provides pagination information such as page size, page number, and sorting.

Conclusion

The @Query annotation in Spring Data JPA is a powerful feature that allows you to write custom queries to meet specific requirements. Whether using JPQL or native SQL, you can define complex queries for data retrieval, manipulation, and custom projections. Additionally, you can use it for update, delete, pagination, and sorting operations. By mastering the @Query annotation, you gain the flexibility to extend your repository layer beyond the default query derivation mechanism, ensuring your application can handle a wide range of data operations efficiently.