Spring Data Derived findBy Query Methods Example

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

In this tutorial, we’ll explore Spring Data derived findBy query methods example. Apart from the basic CRUD methods in the CrudRepository interface, Spring Data gives leverage to create the custom query methods by following JPA naming convention.

Related Post: Spring Data JPA Derived Query Methods Example

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 → it is just like introducer, what you want to perform with the method
2. And second is ByLastName → is the criteria, where (field name) you want to perform the operation

For example, we wish to retrieve/fetch all records from the database based on the last name. In this case, the derived query method is will be:

List<T> findByLastName(String lastName);

Spring Data also supports readBy, queryBy, and getBy which behave the same as findBy and gives the same outcome.

List<T> readByLastName(String lastName);
//OR
List<T> queryByLastName(String lastName);
//OR
List<T> getByLastName(String lastName);

What we’ll build

In this example, we will create the derived query methods based on the use case and retrieve the data from the table. See the below:

1. Query on the single & multiple criteria
2. Define conditions on entity properties
3. Enables the distinct flag for the query
4. Enabling ignoring case
5. Enabling order by for a query

Technology Used

Find the list of all technologies used in this application.

1. Spring Tool Suite 4
2. JDK 8
3. Spring Boot 2.1.3.RELEASE
4. Spring Data 2.1.5.RELEASE
5. MySQL Database
6. Maven 3

Database Schema

Find the modified table structure customers managed by Spring Data in MySQL database. Slightly different from the last tutorial.

CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Insert some dummy data into the table which look like:

Dependency Required

Dependencies used in this example. Add them to pom.xml.

<dependencies>
	<!-- Spring boot data -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-jpa</artifactId>
	</dependency>
	<!-- spring boot web -->
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<!-- MySQL database connector -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>
</dependencies>

application.properties

Setup the database connection strings in application.properties.

# MySQL database connection strings
spring.datasource.url=jdbc:mysql://localhost:3306/demo
spring.datasource.username=root
spring.datasource.password=

# JPA property settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.show_sql=true

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 java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "customer")
public class Customer implements Serializable {

	private static final long serialVersionUID = -7280187378770180724L;

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;
	private String firstName;
	private String lastName;
	private String email;
	private Long mobile;
	private String country;
	// Generate getters and setters...

	// Default constructor
	public Customer() {
	}
}

Repository

Create a CostomerRepository interface which extends CrudRepository which contains our derived query method.

package org.websparrow.repository;

@Repository
public interface CustomerRepository extends CrudRepository<Customer, Integer> {
//Derived query method will be added here
}

Below query method will be added in the CostomerRepository later.

1. Query on the single & multiple criteria

Query on single & multiple criteria returns the instance of the given entity.

1.1 Single Criteria Query Method

The single criteria (here criteria is field name of entity class) query method will design by adding prefix findBy and criteria name i.e. findBy{FieldName}. It might return the list of an entity or single entity.

findByFirstName or findByLastName → It queries into the table and returns the list of customers matched given first name or last name input. Why return list? Because of multiple customers can have the same first or last name.

List<Customer> findByFirstName(String firstName);

List<Customer> findByLastName(String lastName);

findByEmail → Return customer instance matched with the email.

Optional<Customer> findByEmail(String email);

1.2 Multiple Criteria Query Method

Multiple criteria query method applies to more than one field name and separator by And or Or.

findByFirstNameAndLastName → Return all the records form the table with the given first name and last name.

List<Customer> findByFirstNameAndLastName(String firstName, String lastName);

findByFirstNameOrLastName → Return all the records form the table with the given first name or last name.

List<Customer> findByFirstNameOrLastName(String firstName, String lastName);

findByFirstNameAndLastNameAndEmail → Return all the records form the table with the given first name and last name and email.

List<Customer> findByFirstNameAndLastNameAndEmail(String firstName, String lastName, String email);

2. Define conditions on entity properties

JPA also allows us to apply condition on the entity properties and filter the data based on the condition. It is just similar to multiple criteria query methods but we can do more.

findByFirstNameAndLastNameAndEmail → Return only those records matched with the given first name and last name and email.

findByCountryIsNull → Return only those customers whos country is null.

List<Customer> findByCountryIsNull();

findByFirstNameStartingWith → Return customers whos first name start with the given name (parameter bound with appended %).

List<Customer> findByFirstNameStartingWith(String firstName);

Similarly, we can apply the followings. Check the link for more info.

• GreaterThan
• LessThanEqual
• Like
• NotLike

3. Enables the distinct flag for the query

The distinct flag can also be enabled by the Spring Data.

List<Customer> findCustomerDistinctByFirstName(String firstName);

List<Customer> findDistinctCustomerByFirstNameAndLastName(String firstName, String lastName);

4. Enabling ignoring case

To retrieve the data without exact matching (case insensitive), we can enable it using Spring Data. It can be applied to one or more entity properties.

4.1 Enable on a single property

Let’s assume, we want to retrieve the data matched with first name wheater it is in upper case, lower case, or mixed. In this case, the query method will be:

findByFirstNameIgnoreCase → Return the customers matched with first name (case insensitive). It will generate query like- … where UPPER(x.first_name) = UPPER(?1) and return the customer whos first name like- Sandeep or sandeep or SANDEEP or sAnDeEp, etc.

List<Customer> findByFirstNameIgnoreCase(String firstName);

4.2 Enable on multiple properties

findByFirstNameOrCountryAllIgnoreCase → Return the customer matched with first name or country ignoring case sensitive.

List<Customer> findByFirstNameOrCountryAllIgnoreCase(String firstName, String country);

5. Enabling order by for a query

To get sorted data based on the property name, Spring Data allows us to do that.

5.1 Ascending Order

It will sort the data in ascending order on which criteria it is applied. Let’s see the below:

findByFirstNameOrderByLastNameAsc → Returns the sorted data in ascending order on the last name matched with the first name.

List<Customer> findByFirstNameOrderByLastNameAsc(String firstName);

5.1 Descending Order

Sort the data in descending order.

findByFirstNameOrderByCountryDesc → Returns the sorted data in descending order on the country matched with the first name.

List<Customer> findByFirstNameOrderByCountryDesc(String firstName);

All the above, derived query method will be added in CostomerRepository interface and then finally it looks like:

package org.websparrow.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import org.websparrow.entity.Customer;

@Repository
public interface CustomerRepository extends CrudRepository<Customer, Integer> {

	List<Customer> findByFirstName(String firstName);

	List<Customer> findByLastName(String lastName);

	Optional<Customer> findByEmail(String email);

	List<Customer> findByFirstNameAndLastName(String firstName, String lastName);

	List<Customer> findByFirstNameOrLastName(String firstName, String lastName);

	List<Customer> findCustomerDistinctByFirstNameAndLastName(String firstName, String lastName);

	List<Customer> findByFirstNameIgnoreCase(String firstName);

	List<Customer> findByFirstNameOrCountryAllIgnoreCase(String firstName, String country);

	List<Customer> findByFirstNameOrderByLastNameAsc(String firstName);

	List<Customer> findByFirstNameOrderByCountryDesc(String firstName);

}

References

1. Spring Data CrudRepository interface Example
2. Spring Boot RESTful CRUD Example with MySQL Database
3. Spring Data JPA – Reference Documentation
4. Spring Data JPA- Query Creation