Spring Data JPA Derived Query Methods Example
In this tutorial, we’ll learn about Spring Data JPA derived query methods example. Spring Data JPA gives the flexibility to create custom findBy
, existsBy
, countBy
and deleteBy
derived query methods as per need or requirement. Spring Data defined some rules for method naming convention.
Spring Data JPA repository provides us with some predefined method to perform the basic create, read, update and delete (CRUD) operation.
Related Article: Spring Data CrudRepository interface Example
What we’ll build
We’ll create the custom derived query method by following the method naming convention of Spring Data JPA. The derived query method can be applied for the followings:
- findBy → Retrieve the record/records and the return type is entity or list of entities, e.g.
Customer
orIterable<Customer>
. - existsBy → Check whether the record/records exist or not and it’s return type is
boolean
. - countBy → Count the records and return type is
long
. - deleteBy → Delete the record/records and its return type is
void
.
Dependency Required
The required dependency is spring-boot-starter-data-jpa and others related to the project or application.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
Query creation from method names
The derived query method mechanism built into the Spring Data repository and useful for building constraining queries over entities of the repository. The derived method mainly has two parts separated by “By“ delimiter to indicate the start of the actual criteria:
- The first part is the find or exists or count or delete → it is just like introducer, what you want to perform with the method
- And second is By{FirstName or LastName or Country or Mobile, etc} → is the criteria, where (field name) you want to perform the operation
Let’s have look on the below code snippet:
// findBy
List<Customer> findByFirstName(String firstName);
Customer findByMobile(long mobile);
// existsBy
boolean existsByFirstName(String firstName);
boolean existsByMobile(long mobile);
// countBy
long countByFirstName(String firstName);
long countByCountry(String country);
// deleteBy
@Transactional
void deleteByFirstName(String firstName);
@Transactional
void deleteByMobile(long mobile);
Entity
Create the entity class along with fields on which we create our derived query methods. In my case Customer
is an entity as given below:
package org.websparrow.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String firstName;
private String lastName;
private Long mobile;
private String country;
// Generate Getters and Setters...
}
1. findBy
The findBy
query method is used to fetch the records or list of records from the database. You can learn more about findBy
in one of our detailed article.
findBy
can be used with single or multiple criteria. And even we can do more with it like ordering the records, case sensitive/insensitive, less than or greater than, etc.
Note: Spring Data also supports
readBy
,queryBy
, andgetBy
which behave the same asfindBy
and gives the same outcome.
1.1 findBy on Single Criteria
List<Customer> findByFirstName(String firstName);
List<Customer> findByCountry(String country);
Customer findByMobile(long mobile);
1.2 findBy on Multiple Criteria
List<Customer> findByFirstNameAndLastName(String firstNae, String lastName);
List<Customer> findByFirstNameOrLastName(String firstNae, String lastName);
List<Customer> findByFirstNameAndLastNameAndCountry(String firstName,
String lastName, String country);
1.3 findBy with Ordering
The OrderBy
clause used to order the result on the given criteria. By default, the ordering will be in ascending order.
List<Customer> findByFirstNameOrderByCountry(String firstName);
List<Customer> findByFirstNameOrderByCountryDesc(String firstName);
We can also use multiple criteria with the OrderBy
clause.
List<Customer> findByFirstNameAndLastNameOrderByCountry(String firstName, String lastName);
List<Customer> findByFirstNameOrMobileOrderByCountryDesc(String firstName, long mobile);
1.4 findBy with Case Sensitive/Insensitive
List<Customer> findByFirstNameIgnoreCase(String firstName);
List<Customer> findByLastNameIgnoreCaseOrderByCountry(String lastName);
1.5 findBy to check Null or Not Null
1.5.1 IsNull or Null
Below both derived query method gives the same outcome. The only differences are in the way of the writing method names, technically both are the same.
List<Customer> findByFirstNameIsNull();
List<Customer> findByFirstNameNull();
1.5.2 IsNotNull or NotNull
It also gives the same result as described in IsNull or Null.
List<Customer> findByCountryIsNotNull();
List<Customer> findByCountryNotNull();
2. existsBy
existsBy
is used to check whether the record/records exist or not in the table. It can also be used with single as well as multiple criteria. If the record/records exist it will return true
otherwise false
.
boolean existsByFirstName(String firstName);
boolean existsByFirstNameAndLastName(String firstName, String lastName);
boolean existsByFirstNameOrCountry(String firstName, String country);
boolean existsByMobile(long mobile);
3. countBy
It counts the number of records on the given criteria. It also supports single and multiple criteria. The return type of countBy
can be long
or int
etc depends on the how much data return by the query and capacity of return type to hold the value of count. Usually, the developer uses long. Learn more about the Primitive Datatypes in Java.
long countByFirstName(String firstName);
long countByFirstNameAndLastName(String firstName, String lastName);
long countByFirstNameOrCountry(String firstName, String country);
long countByCountry(String country);
4. deleteBy
Used for deleting the record/records from the table and always used with @Transactional
annotation. It also supports the single as well as multiple criteria. The return type of deleteBy
clause is void
.
@Transactional
void deleteByFirstName(String firstName);
@Transactional
void deleteByFirstNameAndLastName(String firstName, String lastName);
@Transactional
void deleteByFirstNameOrLastName(String firstName, String lastName);
@Transactional
void deleteByMobile(long mobile);
Supported keywords inside method names
Check out the list of supported keywords inside the method names while creating a derived query method using Spring Data JPA.
Source: docs.spring.io
References
- Spring Data Derived findBy Query Methods Example
- Spring Data CrudRepository Interface Example
- Spring Data JPA – Reference Documentation
- Spring Data JPA- Query Creation