Custom Query in Spring Data JPA - without using any annotation
JPA Repository
The JPA Repository is the component that manages the database operation and specifies the entity classes. Each entity class will have a repository to store and retrieve records from the database. Below we have EmployeeRepository, which extends JpaRepository interface.
In order to tell spring boot that this EmployeeRepository is a repository we have to annotate the interface with @Repository.
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.employee.example.model.Employee;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {}
It provided save(), findById(), findAll(), count(), delete(), and existsById() methods for CURD operations on domain classes managed by the repository.
Spring JPA Custom Query
So let's say we need to find an employee with a name like John or we want to count the number of employees in a particular department. When we need records that cannot be obtained from built-in methods, we use custom queries.
Using Methods.
The Spring JPA repository has a built-in query builder mechanism that builds queries for entities.
Custom queries can be created by defining custom methods in our repository. Our builder will parse the method name and create the query.
The method name contains two parts: subject and predicate. A custom method's name must contain a subject, which tells the operation to be executed. If we are retrieving records based on some condition then the method name must also include a predicate.
Let's say we want to get records based on firstname and lastname of employee. The method name would be findByLastnameAndFirstname, where findBy is the subject and And is the predicate, and firstname and lastname are the columns.
A list of all available subjects and predicates can be found in the Spring Boot JPA Query creation Javadoc.
Subject
Now we'll look through each subject available in JPA query creation one by one with an example. By using this subject in the method name, we are able to generate the query, but if we are not using any subject or predicate and make our own method names instead, then we need to use the @Query annotation and pass our SQL query into it, which is executed by JPA.
Below are the available subjects which are used to perform operations on records such as fetching, checking, deleting, counting.
FindBy, readBy, getBy, queryBy, searchBy, streamBy - these are all general subjects used to retrieve records and can be used with a predicate to retrieve an individual record, for example findByLastnameAndFirstname.
existsBy - check whether a record exits or not if yes, return true, otherwise false, e.g. existsByFirstname
countBy - return the count of records as a numeric value e.g. countByJobTitle
deleteBy,removeBy - delete a record which return void e.g. deleteByFirstname.
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
List<Employee> findByName(String name);
List<Employee> findByJobTitle(String jobTitle);
List<Employee> findByDepartment(Department department);
boolean existsByName(String name);
long countByJobTitle(String jobTitle);
}
Limiting the records
We use findBy subject to fetch records based on column name. However, in some cases we don’t want all records from the database. Instead, we need only a few records because fetching all records at once can take time or make it difficult to process, so we can limit the records.
Using First<number>,Top<number> subject, for example findFirst10ByDateofbirth fetches only the first 10 records from the database.
Employee findTopByOrderByName(String name);
Employee findFirstByOrderByNameAsc(String name);
List<Employee> findFirst10ByDateofbirth(String dateofbirth);
List<Employee> findFirst10ByJobTitle(String jobTitle);Fetch Distinct record
To fetch distinct records from the database, we use distinct subjects.
Distinct-used to fetch only unique or distinct results. This keyword can occur in any part of the subject between find (and the other keywords) and by.
Predicate
Combining columns for Fetching records
To this point, we have used a single column to fetch records, but in real-time we may need to fetch records or perform operations based on multiple conditions. Using the And and Or keywords you can combine search conditions, such as findByNameAndJobTitle. This generates a query that selects records which match both the employee name and their job title.
List<Employee> findByNameAndJobTitle(String name, String jobTitle);
List<Employee> findByNameOrMobileno(String name, String mobileNo);
Numerical Predicate
If we want to search record having salary greater 25000 or employee age lesser than or equal to 30, then the GreaterThan, LessThanEqual keyword can be used for numerical operation.
List<Employee> findBySalaryGreaterThan(Integer salary);
List<Employee> findBySalaryGreaterThanEqual(Integer salary);
List<Employee> findBySalaryLessThan(Integer salary);
List<Employee> findBySalaryLessThanEqual(Integer salary);
List<Employee> findBySalaryBetween(Integer minsalary, Integer maxsalary);
String handling Predicate
We also have predicate keywords to do search based on string operation for example if we want all employees whose names start or end with “John”. In this we can use StartingWith, EndingWith keywords and there are lots more keywords for various operations.
List<Employee> findByNameStartingWith(String name);
List<Employee> findByNameEndingWith(String name);
Collections Based Predicate
If you want to fetch an employee whose job title is developer, tester, and so on, then we can use the In keyword which takes group values. If the value is present in the list, it will be returned in the resultset.
// collection
List<Employee> findByJobTitleIn(List<String> name);
List<Employee> findByJobTitleNotIn(List<String> name);
Furthermore, if you want records whose values must not be present in the list, then the NotIn keyword can be used. This will return records whose values do not exist in the list.