Spring Data JPA Derived Query Methods Example

By Atul Rai | Last Updated: March 15, 2020 Previous       Next

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:

1. findBy → Retrieve the record/records and the return type is entity or list of entities, e.g. Customer or Iterable<Customer>.
2. existsBy → Check whether the record/records exist or not and it’s return type is boolean.
3. countBy → Count the records and return type is long.
4. 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:

1. 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
2. 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, and getBy which behave the same as findBy 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

1. Spring Data Derived findBy Query Methods Example
2. Spring Data CrudRepository Interface Example
3. Spring Data JPA – Reference Documentation
4. Spring Data JPA- Query Creation