JPA Repository Find By Multiple Columns

Spring Data JPA Repository provides finder methods(findBy query method) to select records from a database table without writing SQL queries. But in the background, Spring Data JPA will make SQL queries as per the finder methods and execute the queries for us. We can use findBy method with multiple columns of the table to select records according to the columns. To use the JPA Repository findBy method with multiple columns we need to use AND  and OR logical operators. In this topic, we will learn how to use JPA Repository find by multiple columns using Spring Boot, Maven, Spring Data JPA, Lombook and H2 database.

Spring Data JPA Repository find by multiple columns   

There are the following ways:

• Using AND operator
• Using OR operator
• Using AND and OR operators

Let us consider a JPA entity Employee class with four properties.

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import lombok.Data;

@Data

@Entity

public class Employee {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String firstName;

  private String lastName;

  private String department;

}

findBy with multiple columns using AND operator

Employee findByFirstNameAndLastName(String firstName, String lastName);

• This method fetches employee records where first name and last name are matched by providing parameters. 

findBy with multiple columns using OR operator

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

• This method fetches the list of employee records where either first name or last name matches by providing parameters.

findBy with multiple columns using AND and OR operators

List<Employee> findByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department);

• This method fetches the list of employee records where either a first name or last name and department are matched by providing parameters.

Let’s implement how to use the JPA Repository findBy method with multiple columns using the Spring Boot Restful web services example step-by-step.

1. Creating a Spring Boot Starter Project

 We are creating a Spring Boot Application from the web tool Spring Initializr or you can create it from the IDE(STS, VS Code etc.) you are using. 

Add the following dependencies: 

• Spring Web
• Spring Data JPA
• Lombok
• H2 Database

2. Keep the IDE ready

We are importing this created application into our Eclipse IDE or you can import it into another IDE you are using. You can refer to this article to create and set up the Spring Boot Project in Eclipse IDE.

Project Structure 

3. Maven Dependency

Here is the complete pom.xml file for the Spring Boot Application.
pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project
	xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.3</version>
		<relativePath />
		<!-- lookup parent from repository -->
	</parent>
	<groupId>com.springjava</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

4. Defining the configuration

We are configuring the H2 database configuration in the application.properties file.
application.properties

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true

5. Creating a JPA Entity

We are creating a JPA entity class Employee with these properties(id, firstName,lastName, and department).
Employee.java

package com.springjava.entity;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import lombok.Data;

@Data

@Entity

public class Employee {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;

  private String firstName;

  private String lastName;

  private String department;

}

• This @Data annotation is used to generate a constructor, setter method, getter method, etc.
• This @Entity annotation is used to create a table through Java code in the database.

6. Creating a JPA Repository

We are a JPA Repository to interact with the JPA Entity class and add the findBy method with multiple columns
EmplRepository.java

package com.springjava.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.springjava.entity.Employee;

public interface EmplRepository extends JpaRepository < Employee, Long > {

  Employee findByFirstNameAndLastName(String firstName, String lastName);

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

  List < Employee > findByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department);

}

7. Creating a Service Interface

We are creating a Service interface with some method declaration. So the implementation class of this interface overrides these declared methods.
EmployeeService.java

package com.springjava.service;

import java.util.List;

import com.springjava.entity.Employee;

public interface EmployeeService {

  void save(Employee employee);

  Employee getByFirstNameAndLastName(String firstName, String lastName);

  List < Employee > getByFirstNameOrLastName(String firstName, String lastName);

  List < Employee > getByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department);

}

8. Creating a Service class

We are creating a Service class EmployeeServiceImpl and this class is implementing the EmployeeService interface. This class is annotated with @Service annotation to act service for this implementation of the findBy method of JPA Repository with multiple columns.
EmployeeServiceImpl.java

package com.springjava.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.springjava.entity.Employee;

import com.springjava.repository.EmplRepository;

@Service

public class EmployeeServiceImpl implements EmployeeService {

  @Autowired

  EmplRepository empRepo;

  @Override

  public void save(Employee employee) {

    empRepo.save(employee);

  }

  @Override

  public Employee getByFirstNameAndLastName(String firstName, String lastName) {

    return empRepo.findByFirstNameAndLastName(firstName, lastName);

  }

  @Override

  public List < Employee > getByFirstNameOrLastName(String firstName, String lastName) {

    return empRepo.findByFirstNameOrLastName(firstName, lastName);

  }

  @Override

  public List < Employee > getByFirstNameOrLastNameAndDepartment(String firstName, String lastName, String department) {

    return empRepo.findByFirstNameOrLastNameAndDepartment(firstName, lastName, department);

  }

}

9. Creating a Rest Controller class

We are creating a RestController class EmployeeController in which all methods are created for API endpoints for handling requests from the clients. 
EmployeeController.Java

package com.springjava.controller;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

import com.springjava.entity.Employee;

import com.springjava.service.EmployeeService;

@RestController

@RequestMapping("/api/employee")

public class EmployeeController {

  @Autowired

  EmployeeService empService;

  @PostMapping("/save")

  public ResponseEntity < ? > save(@RequestBody Employee employee) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    empService.save(employee);

    respEmp.put("status", 1);

    respEmp.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respEmp, HttpStatus.CREATED);

  }

  @GetMapping("/firstandlastname")

  public ResponseEntity < ? > getByFirstNameAndLastName(@RequestParam String firstName, @RequestParam String lastName) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    try {

      Employee emp = empService.getByFirstNameAndLastName(firstName, lastName);

      respEmp.put("status", 1);

      respEmp.put("data", emp);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } catch (Exception ex) {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/firstorlastname")

  public ResponseEntity < ? > getByFirstNameOrLastName(@RequestParam String firstName, @RequestParam String lastName) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    List < Employee > emList = empService.getByFirstNameOrLastName(firstName, lastName);

    if (!emList.isEmpty()) {

      respEmp.put("status", 1);

      respEmp.put("data", emList);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } else {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

  @GetMapping("/firstorlastnameanddepart")

  public ResponseEntity < ? > getByFirstNameOrLastNameAndDepartment(@RequestParam String firstName, @RequestParam String lastName, @RequestParam String department) {

    Map < String, Object > respEmp = new LinkedHashMap < String, Object > ();

    List < Employee > emList = empService.getByFirstNameOrLastNameAndDepartment(firstName, lastName, department);

    if (!emList.isEmpty()) {

      respEmp.put("status", 1);

      respEmp.put("data", emList);

      return new ResponseEntity < > (respEmp, HttpStatus.OK);

    } else {

      respEmp.clear();

      respEmp.put("status", 0);

      respEmp.put("message", "Data is not found");

      return new ResponseEntity < > (respEmp, HttpStatus.NOT_FOUND);

    }

  }

}

• This class is annotated with @RestController annotation to make this class act as a Rest Controller for giving responses in JSON form.
• We used @RequestMapping annotation to define the base URL for the application.
• We used @PostMapping and @GetMapping annotations in the methods for handling HTTP requests from the client.
• We used ResponseEntity to represent the entire HTTP response.
• We used @Autowired to inject EmployeeService in this class.
• @RequestParam annotation is used to query parameters followed by the “?” mark symbol in the URL.

10. Run the Spring Boot Application and Check

Please Right-Click this Spring Boot application on the DemoApplication.java then click on the Run As, and select Java Application. 

Check H2 Database

Check the H2 database console and browse this URL “http://localhost:8080/h2-console”.

See the below table here:

Testing API on the Postman

Saving the employee data
POST: http://localhost:8080/api/employee/save

Check the table:

Retrieving the employee by first name and last name
GET: http://localhost:8080/api/employee/firstandlastname?firstName=Robert&lastName=William

After this API hit Spring Data JPA ( internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ from employee employee0_ where employee0_.first_name=? and employee0_.last_name=?

Retrieving the employees by first name or last name
GET: http://localhost:8080/api/employee/firstorlastname?firstName=Robert&lastName=John

After this API hit Spring Data JPA ( internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ from employee employee0_ where employee0_.first_name=? or employee0_.last_name=?

Retrieving the employee by first name or last name and department
GET: http://localhost:8080/api/employee/firstorlastnameanddepart?firstName=Peter&lastName=William&department=IT

After this API hit Spring Data JPA ( internally uses Hibernate as a JPA provider) generated SQL statement in the console below here:

Hibernate: select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ from employee employee0_ where employee0_.first_name=? or employee0_.last_name=? and employee0_.department=?

Conclusion

In this topic, we learnt about how to use the JPA Repository findBy method with multiple columns in the Spring Boot Restful web services application.