Spring Data JPA AND OR Combination

by

When working on real-time Spring Boot with Spring Data JPA applications, we need to filter data often requires combining AND and OR conditions within a single query. We can use the AND OR combination in Spring DATA JPA by creating a repository interface adding And and Or keywords in a custom query method, and adding AND and OR operators in JPQL/Native SQL query. In this topic, we will explain how to use the AND OR combination in Spring Data JPA using the Spring Boot application in various ways.

use AND OR Combination in Spring Data JPA

Use AND OR combination in Spring Data JPA

There are three ways to combine AND and OR conditions in Spring Data JPA Repository:

1. Adding And and Or keywords in a Custom Query Method

Add a custom query method by adding these And and Or keywords to it in the repository interface. Adding these keywords, we can use findBy method with multiple columns in JPA Repository such as findByColumnNameAndColumnNameOrColumnName(Parameter param1, Parameter param2, Parameter param3). 

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
List<Staff> findByDepartmentAndNameOrSalaryGreaterThan(String Depart,String name, double salary);
}
  • The generated query will interpret the method as:
WHERE department = ? AND (name = ? OR salary > ?)
  • However, by default, this method name interprets conditions left-to-right, so parentheses are implied based on precedence. This means:
(department = ? AND name = ?) OR salary > ?

2. Adding AND and OR operators in JPQL

Create a JPQL query and use AND and OR in it to use AND OR combination in the repository interface. 

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query("SELECT s FROM Staff s WHERE s.department = :department AND (s.name = :name OR s.salary > :salary)")
List<Staff> findByfindByDepartmentAndNameOrSalaryGreaterThanJPQL(String Department,String name, double salary);

}
  • The JPQL query groups name and salary with parentheses to ensure correct evaluation.
  • The AND condition is applied first, then the OR conditions inside the parentheses using the above JPQL.

3. Adding AND and OR operators in Native SQL Query

We are adding AND and OR operators in the Native SQL Query to combine these operators to make the query with these in the repository interface.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query(value = "SELECT * FROM staff WHERE department = :department AND (name = :name OR salary > :salary)", nativeQuery = true)
List<Staff> findByfindByDepartmentAndNameOrSalaryGreaterThanNative(String department,String name, double salary);
}
  • This native query mirrors the JPQL example but  runs directly against the database.

Step-by-step to Guide the Use AND OR Combination in Spring Data JPA

Let’s create a Spring Boot Application step-by-step guide for using the AND and OR condition to fetch records from the database table using the JPA Repository. We will create an example that returns a list of staff filtered based on multiple criteria (who belong to a specific department and either have a specific name or earn more than a certain salary) to the client through the API endpoints in the ways mentioned above. 

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 OR Combination in Spring Data JPA

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement the AND OR combination in the Spring Data 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> findByDepartmentAndNameOrSalaryGreaterThan(String Depart,String name, double salary);

@Query("SELECT s FROM Staff s WHERE s.department = :department AND (s.name = :name OR s.salary > :salary)")
List<Staff> findByfindByDepartmentAndNameOrSalaryGreaterThanJPQL(String department,String name, double salary);

@Query(value = "SELECT * FROM staff WHERE department = :department AND (name = :name OR salary > :salary)", nativeQuery = true)
List<Staff> findByfindByDepartmentAndNameOrSalaryGreaterThanNative(String department,String name, double salary);

}

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> getByDepartmentAndNameOrSalaryGreaterThan(String Depart,String name, double salary);
List<Staff> getByDepartmentAndNameOrSalaryGreaterThanJPQL(String Depart,String name, double salary);
List<Staff> getByDepartmentAndNameOrSalaryGreaterThanNative(String Depart,String name, double salary);
}

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> getByDepartmentAndNameOrSalaryGreaterThan(String Depart, String name, double salary) {
return staffRepo.findByDepartmentAndNameOrSalaryGreaterThan(Depart, name, salary);
}

@Override
public List<Staff> getByDepartmentAndNameOrSalaryGreaterThanJPQL(String Depart, String name, double salary) {
return staffRepo.findByfindByDepartmentAndNameOrSalaryGreaterThanJPQL(Depart, name, salary);
}

@Override
public List<Staff> getByDepartmentAndNameOrSalaryGreaterThanNative(String Depart, String name, double salary) {
return staffRepo.findByfindByDepartmentAndNameOrSalaryGreaterThanNative(Depart, name, salary);
}
}

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-or-with-query-method")
public ResponseEntity<?> getByDepartmentAndNameOrSalaryGreaterThan(@RequestParam String department,@RequestParam String name,@RequestParam double salary) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByDepartmentAndNameOrSalaryGreaterThan(department, name, salary);
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-or-with-jpql")
public ResponseEntity<?> getByDepartmentAndNameOrSalaryGreaterThanJPQL(@RequestParam String department,@RequestParam String name,@RequestParam double salary) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByDepartmentAndNameOrSalaryGreaterThanJPQL(department, name, salary);
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-or-with-native")
public ResponseEntity<?> getByDepartmentAndNameOrSalaryGreaterThanNative(@RequestParam String department,@RequestParam String name,@RequestParam double salary) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByDepartmentAndNameOrSalaryGreaterThanNative(department, name, salary);
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 OR Combination in Spring Data JPA

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

use AND OR Combination in Spring Data JPA

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.department=? and s1_0.name=? or s1_0.salary>?

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

use AND OR Combination in Spring Data JPA

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.department=? and (s1_0.name=? or s1_0.salary>?)

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

use AND OR Combination in Spring Data JPA

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 department = ? AND (name = ? OR salary > ?)

Conclusion

Combining AND and OR conditions in Spring Data JPA provides powerful filtering capabilities. Whether using derived methods, JPQL, or native queries, ensure that your conditions are logically grouped to get the expected results. Understanding these techniques helps you write cleaner and more efficient database queries.

Leave a Comment