Building Queries with Spring Data JPA
Overview
Spring boot data repository offers basic crud functionality, which is fine for simple operation and application. But in reality, data is fetched using complex queries and stored procedures. Spring boot offers different ways to write queries , from simple to complex, and execute them on the database.
Building Queries
There are three methods we can use to build queries:
- Query Methods
- JPA Criteria API
- Querydsl
The scope of this article is limited to the Query Methods.
Query Generation From the Method Name
Query generation from method name is a strategy where the executed query is generated from the method's name in the repository interface. The naming convention, which is used to create the names of the query method, has three important parts.
- Method prefixes
- Property expressions
- Conditions
Method syntax to generate query goes like this [MethodPrefixes][PropertyExpression][Conditions].
Method Prefixes
To ensure that method is identified as a query method, it must have predefined prefixes. The supported prefixes are findBy, find, readBy, read, getBy, and get.
Property Expressions
Property expressions refer to either a direct property or nested property of a JPA entity. For example, the property expression will be an Employee entity with the attribute employeeName.
| Property Expression | Referred Property |
|---|---|
| EmployeeName | Employee#employeeName |
Conditions
Conditions specify constraints against the value of property referred by property expression. Two rules are used to combine property expression with conditions.
- We can create a constraint by adding a keyword after the property expression.
- We can combine constraints by adding either the And or Or keywords.
Sample Queries
Let’s build some queries using the method name.
| Prefix | Property expression | Keyword | Method Signature | Generated JPAQL |
|---|---|---|---|---|
| findBy | EmployeeName | NONE | findByEmployeeName(String employeeName) | Select e from Employee e where e.employeeName=?1 |
| findBy | EmployeeName | StartingWith | findByEmployeeNameStartingWith(String employeeName) | Select e from Employee where e.employeeName like ?1 (Parameter bound with appended %) |
The corresponding repository code with the method name will be
Complex Query Using @Query Annotation
The method name approach is good for a simple query. But with complex queries, method names become long and ugly. One of the limitations of the method name parser is the lack of the Lower keyword. This means we can not implement case-insensitive searches using this strategy. This is where the @Query annotation is useful.
We can create a query method that uses JPQL by:
- Adding a new method into the EmployeeRepository.java interface.
- Using the @param annotation to identify the method's parameter as the value of the named parameter.
- Annotating the method with @Query annotation and setting JPAQL inside it.
The repository code with query annotation will be
@Query("SELECT e from Employeee WHERE e.employeeName LIKE :searchTerm")
List<Employee> searchEmployee(@Param("searchTerm") String searchTerm);
Conclusion
- We have covered different approaches to generating queries in Spring Data JPA.
- Query generation generates queries from a parsed method name.
- @Query annotation uses both query and method names in the repository class. It offers better readability and debuggability.