How do you create a custom query in Spring Data JPA?

Table of Contents

Introduction

Spring Data JPA simplifies database interaction in Spring Boot applications by automatically implementing repository methods. However, for more complex queries that cannot be easily expressed with method names, you can create custom queries using the @Query annotation. These queries can be written in either JPQL (Java Persistence Query Language) or native SQL, depending on the use case.

In this guide, we’ll show you how to create custom queries in Spring Data JPA, including both JPQL and native SQL queries, and how to use them effectively within your repository methods.

1. Using the @Query Annotation for Custom Queries

The @Query annotation allows you to define custom queries directly on repository methods. You can use it to write complex queries that are not covered by the default repository methods. These queries can be in JPQL or native SQL.

1.1 JPQL (Java Persistence Query Language)

JPQL is a query language that is similar to SQL but operates on entities rather than database tables. It works with object-oriented models, so you refer to the entity class names and their fields.

Example: Custom JPQL Query

Suppose you have an entity Product with fields name and category. You want to retrieve all products belonging to a certain category.

Explanation:

  • @Query("SELECT p FROM Product p WHERE p.category = :category"): This is a custom JPQL query that retrieves all products (Product p) where the category field matches the specified parameter.
  • @Param("category"): Binds the method parameter to the query’s category parameter.

Example: JPQL with Multiple Conditions

You can also add more conditions to the query.

1.2 Native SQL Queries

Sometimes, JPQL is not sufficient, especially when you need to use database-specific features or functions. In such cases, you can use native SQL queries directly in the @Query annotation by setting the nativeQuery attribute to true.

Example: Custom Native SQL Query

Explanation:

  • nativeQuery = true: This tells Spring Data JPA that the query is a native SQL query, not JPQL.
  • SELECT * FROM product WHERE category = :category: A SQL query that fetches all rows from the product table where the category matches the parameter.

1.3 Using Aggregation Functions in Custom Queries

You can also use aggregation functions like COUNT, SUM, AVG, etc., in custom queries. This is useful for generating reports or analyzing data.

Example: Count Products by Category

Explanation:

  • COUNT(p): Counts the number of products for each category.
  • GROUP BY p.category: Groups the results by category.

The result is a list of Object[] arrays, where each array contains a category and the count for that category.

2. Using Method Query Derivation

Spring Data JPA also allows you to automatically generate queries based on method names. However, when these default queries are not sufficient, using the @Query annotation provides more control and flexibility.

Example: Method Query Derivation

By default, Spring Data JPA can create simple queries based on method names. For instance:

This method will automatically generate a query to fetch all products with the given category using a query like:

While this is convenient, more complex queries (like joins or aggregation) require the @Query annotation.

3. Handling Parameters in Custom Queries

You can pass parameters to custom queries using the @Param annotation. This is essential when you want to bind values from method parameters to the query.

Example: Custom Query with Multiple Parameters

Explanation:

  • :category and :minPrice: These are placeholders in the JPQL query, and their values are set via the @Param annotations.

4. Returning Custom Projections with @Query

In some cases, you might want to return a subset of data or a custom structure. Spring Data JPA allows you to define projections to return only specific fields or to transform query results.

Example: Returning Specific Fields (DTO Projection)

Suppose you want to return only the name and price fields of the Product entity as a custom DTO object.

Explanation:

  • SELECT new com.example.ProductDTO(p.name, p.price): This is a constructor expression in JPQL. It creates a new instance of ProductDTO for each result row with the specified fields (name and price).
  • The method returns a List<ProductDTO>, a custom projection that contains only the necessary fields.

5. Pagination and Sorting in Custom Queries

You can combine pagination and sorting with custom queries by using the Pageable object, which includes sorting information, and passing it to your query.

Example: Pagination and Sorting with Custom Query

Explanation:

  • Pageable pageable: This automatically includes pagination and sorting information from the client request.
  • Page<Product>: Returns a page of products, which is ideal for handling large datasets.

6. Named Queries

Another way to define custom queries is by using named queries. These queries are defined in the entity class using the @Query annotation and can be referenced by name in repository methods.

Example: Named Query in Entity Class

Repository Method:

Explanation:

  • The query is defined in the Product entity class using the @NamedQuery annotation.
  • You can then call this named query by its name in the repository method, making the code more modular and easier to maintain.

Conclusion

Creating custom queries in Spring Data JPA is straightforward with the @Query annotation. You can write JPQL queries to interact with your entities, or you can write native SQL queries to work directly with the database tables. Additionally, Spring Data JPA supports custom projections, pagination, sorting, and named queries, making it a powerful tool for handling complex data retrieval operations.

By using custom queries, you can extend the functionality of Spring Data repositories and handle advanced database operations efficiently.

Share
Similar Questions