How to Use AND Operator in JPA Repository

by

Last updated on December 20th, 2024

In Spring Data JPA, we need to filter data based on multiple conditions. The AND operator combines various situations in a query to get results. We can use the AND operator in the JPA Repository adding a custom query method with the And keyword and using the AND operator with JPQL/Native SQL query. In this topic, we will guide you through using the AND operator in the JPA Repository using Spring Boot.

use and operator in JPA Repository

Use the AND Operator in JPA Repository

These are the following ways to use AND operator in JPA Repository:

1. Using Query Method with And Keyword

Add a query method with the And keyword in the repository interface. This method must start with the findBy keyword followed by the column name followed by the And keyword followed by the column name, then add the parameters [findByColumnNameAndColumn(Parameter param1, Parameter param2)]. This And keyword is used in the findBy method with multiple columns in Spring Data JPA when we need to fetch the records that match with provided values.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
List<Staff> findByNameAndDepartment(String name, String department);
}
  • This findByNameAndDepartment query method with the And keyword returns the list of staff records that match provided name and department.
  • This method automatically generates the appropriate SQL query with an AND operator.

2. Using JPQL with AND Operator

Add a custom method in the repository interface. This added method must annotated with @Query annotation to specify JPQL query with AND operator.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query("SELECT s FROM Staff s WHERE s.name = :name AND s.department = :department")
List<Staff> findByNameAndDepartmentJPQL(String name, String department);
}
  • This @Query annotation is used for defining a custom query in the repository interface.
  • This AND operator combines the conditions for name and department.

3. Using Native SQL Query with AND Operator

Add a custom method in the repository interface. This added method must annotated with @Query annotation with its nativeQuery=true attribute to specify SQL query with AND operator.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query(value="SELECT * FROM staff  WHERE name = :name AND department = :department",nativeQuery=true)
List<Staff> findByNameAndDepartmentNative(String name, String department);
}
  • The nativeQuery = true attribute indicates native SQL query in the repository interface.
  • Use this AND operator directly in the SQL statement.

Step-by-step to Guide the use of AND Operator in JPA Repository

Let’s make a Spring Boot Application step-by-step guide to use the AND operator to fetch records from the database table using the JPA Repository. We will create an example which return staffs filtered by both name and department to the client through the API endpoints in the ways mentioned above(And keyword, JPQL and Native Query).  

These are the following steps:

  1. Create Spring Boot Project
  2. Setup in the IDE
  3. Configure H2 Database
  4. Create a JPA Entity
  5. Create a Repository Interface
  6. Create a Service Interface
  7. Implement the Service Interface
  8. Create a Controller
  9. Run the Spring Boot Application

1. Create Spring Boot Project

 We are creating a Spring Boot Project from the web tool Spring Initializr. By Providing details for the project and select the following Maven dependencies:

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

2. Setup in the IDE

We use Eclipse IDE to set up and configure the created Spring Boot Project. You can use other IDE to set up and configure the Spring Boot project.

Project Structure of Spring Boot

This image shows the project structure of Spring Boot in Eclipse IDE.

use and operator in JPA Repository

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement the AND operator in JPA Repository.

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>3.3.0</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>17</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>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</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>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

3. Configure H2 Database

We are going to configure the H2 database connection 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

4. Create a JPA Entity

Let’s create a JPA Entity class. For example, consider a Staff entity and use Lombok for generating setter and getter methods, a constructor, etc.

Staff.java

package com.springjava.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity
public class Staff {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String department;
private double salary;

}

5. Create a Repository Interface

Create a repository interface for the Staff JPA Entity class that interface extends the JpaRepository interface to perform persistence operations on the staff database table.

StaffRepository.java

package com.springjava.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.springjava.entity.Staff;

public interface StaffRepository extends JpaRepository<Staff, Long> {
List<Staff> findByNameAndDepartment(String name, String department);
@Query("SELECT s FROM Staff s WHERE s.name = :name AND s.department = :department")
List<Staff> findByNameAndDepartmentJPQL(String name, String department);
@Query(value="SELECT * FROM staff  WHERE name = :name AND department = :department",nativeQuery=true)
List<Staff> findByNameAndDepartmentNative(String name, String department);
}

6. Create a Service Interface

Create a Service interface StaffService with some method declaration.

StaffService.java

package com.springjava.service;

import java.util.List;
import com.springjava.entity.Staff;

public interface StaffService {
void saveAll(List<Staff> staffList);
List<Staff> getByNameAndDepart(String name, String depart);
List<Staff> getByNameAndDepartJPQL(String name, String depart);
List<Staff> getByNameAndDepartNative(String name, String depart);
}

7. Implement the Service Interface

Implement the StaffService interface in the StaffServiceImpl class. This class is annotated with @Service annotation, where we inject StaffRepository to call all its methods.

StaffServiceImpl.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.Staff;
import com.springjava.repository.StaffRepository;

@Service
public class StaffServiceImpl implements StaffService {

@Autowired
private StaffRepository staffRepo;

@Override
public void saveAll(List<Staff> staffList) {
staffRepo.saveAll(staffList);
}

@Override
public List<Staff> getByNameAndDepart(String name, String depart) {
return staffRepo.findByNameAndDepartment(name, depart);
}

@Override
public List<Staff> getByNameAndDepartJPQL(String name, String depart) {
return staffRepo.findByNameAndDepartmentJPQL(name, depart);
}

@Override
public List<Staff> getByNameAndDepartNative(String name, String depart) {
return staffRepo.findByNameAndDepartmentNative(name, depart);
    }
}

8. Create a Controller

Create a controller class StaffController. This is annotated with @RestController to make this class a RestController.

StaffController.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.Staff;
import com.springjava.service.StaffService;

@RestController
@RequestMapping("/api/staff")
public class StaffController {

@Autowired
private StaffService staffService;

@PostMapping("/save-all")
public ResponseEntity<?> save(@RequestBody List<Staff> staffList) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
staffService.saveAll(staffList);
respStaff.put("status", 1);
respStaff.put("message", "Record is Saved Successfully!");
return new ResponseEntity<>(respStaff, HttpStatus.CREATED);
}

@GetMapping("/and-with-query-method")
public ResponseEntity<?> getByNameAndDepart(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameAndDepart(name, department);
if (!satffList.isEmpty()) {
respStaff.put("status", 1);
respStaff.put("data", satffList);
return new ResponseEntity<>(respStaff, HttpStatus.OK);
} else {
respStaff.clear();
respStaff.put("status", 0);
respStaff.put("message", "Data is not found");
return new ResponseEntity<>(respStaff, HttpStatus.NOT_FOUND);
   }
}

@GetMapping("/and-with-jpql")
public ResponseEntity<?> getByNameAndDepartJPQL(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameAndDepartJPQL(name, department);
if (!satffList.isEmpty()) {
respStaff.put("status", 1);
respStaff.put("data", satffList);
return new ResponseEntity<>(respStaff, HttpStatus.OK);
} else {
respStaff.clear();
respStaff.put("status", 0);
respStaff.put("message", "Data is not found");
return new ResponseEntity<>(respStaff, HttpStatus.NOT_FOUND);
  }
}

@GetMapping("/and-with-native")
public ResponseEntity<?> getByNameAndDepartNative(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameAndDepartNative(name, department);
if (!satffList.isEmpty()) {
respStaff.put("status", 1);
respStaff.put("data", satffList);
return new ResponseEntity<>(respStaff, HttpStatus.OK);
} else {
respStaff.clear();
respStaff.put("status", 0);
respStaff.put("message", "Data is not found");
return new ResponseEntity<>(respStaff, HttpStatus.NOT_FOUND);
   }
 }
}

9. Run the Spring Boot Application

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

H2 Database Console

To check the H2 database console we need to browse the URL “http://localhost:[server_port]/h2-console” on the browser. 

JSON Array

We are creating a sample JSON Array to test the API http://localhost:8080/api/staff/save-all.

[
    {
        "name": "John",
         "department": "Finance",
          "salary": 65000.00
   },
   {
     "name": "Alice",
     "department": "IT",
      "salary": 80000.00
  },
    {
      "name": "Robert",
       "department": "IT",
      "salary": 80000.00
      }
]

Test the APIs on the Postman Tool

POST: http://localhost:8080/api/staff/save-all

use and operator in JPA Repository

GET: http://localhost:8080/api/staff/and-with-query-method?name=Alice&department=IT

use and operator in JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select s1_0.id,s1_0.department,s1_0.name,s1_0.salary from staff s1_0 where s1_0.name=? and s1_0.department=?

GET:http://localhost:8080/api/staff/and-with-jpql?name=Alice&department=IT

use and operator in JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: select s1_0.id,s1_0.department,s1_0.name,s1_0.salary from staff s1_0 where s1_0.name=? and s1_0.department=?

GET: http://localhost:8080/api/staff/and-with-native?name=Alice&department=IT

use and operator in JPA Repository

This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:

Hibernate: SELECT * FROM staff  WHERE name = ? AND department = ?

Conclusion

The AND operator in JPA repositories allows us to filter records based on multiple conditions. We can use it with query method, JPQL, or native SQL queries, depending on the complexity and performance needs.

Leave a Comment