How to use OR Condition in JPA Repository

by

Last updated on December 28th, 2024

In Spring Data JPA, filtering data based on the optional condition is common. The OR condition allows us to fetch records when at least one of the specified conditions is matched. We can use OR condition in JPA Repository using query method with Or keyword, JPQL/ Native SQL query with OR operator. In this topic, we will guide how to use OR condition in JPA Repository using the Spring Boot application in these ways. 

use or condition in JPA Repository

Use OR Condition in JPA Repository

These are the following ways to use OR condition in JPA Repository:

1. Using Query Method with Or Keyword

Add a custom query method with Or keyword in the repository interface. This method must start with findBy followed by column name followed by Or keyword followed by column name then add parameters[findByColumnNameOrColumnName(Parameter param1, Parameter param2)]. This Or keyword is used to the findBy with multiple columns in Spring Data JPA to fetch records.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
List<Staff> findByNameOrDepartment(String name, String department);
}
  • findByNameOrDepartment() creates an SQL query with an OR condition between name and department.
  • This query method automatically generates the appropriate SQL query with the OR operator.

2. Using JPQL with OR Operator

Add a custom method in the repository interface then use the @Query annotation to define JPQL query with the OR operator to use  OR condition JPA Repository using this way.

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query("SELECT s FROM Staff s WHERE s.name = :name OR s.department = :department")
List<Staff> findByNameOrDepartmentJPQL(String name, String department);
}
  • The @Query annotation defines a custom JPQL query.
  • The OR combines the conditions for name and department.

3. Using Native SQL Query with OR Operator

Add a custom method annotated with @Query annotation with its nativeQuery attributes in the repository interface to specify SQL query with OR operator to use OR condition in JPA Repository using native SQL query. 

Example

public interface StaffRepository extends JpaRepository<Staff, Long> {
@Query(value="SELECT * FROM staff WHERE name = :name OR department = :department",nativeQuery=true)
List<Staff> findByNameOrDepartmentNative(String name, String department);
}
  • The nativeQuery = true attribute of @Query annotation is used to indicate that the query is a native SQL query.
  • The OR operator is used directly in the SQL statement.

Step-by-step to Guide the Use of OR Condition in JPA Repository

Let’s make a Spring Boot Application step-by-step guide to use the OR condition to fetch records from the database table using the JPA Repository. We will create an example that returns the list of staff filtered based on either their name or their department to the client through the API endpoints in the ways mentioned above (Or keyword, JPQL and Native SQL 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 or condition in JPA Repository

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement the OR condition 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> findByNameOrDepartment(String name, String department);
@Query("SELECT s FROM Staff s WHERE s.name = :name OR s.department = :department")
List<Staff> findByNameOrDepartmentJPQL(String name, String department);
@Query(value="SELECT * FROM staff WHERE name = :name OR department = :department",nativeQuery=true)
List<Staff> findByNameOrDepartmentNative(String name, String department);
}

6. Create a Service Interface

Create a service interface for 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> getByNameOrDepart(String name, String depart);
List<Staff> getByNameOrDepartJPQL(String name, String depart);
List<Staff> getByNameOrDepartNative(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> getByNameOrDepart(String name, String depart) {
return staffRepo.findByNameOrDepartment(name, depart);
}

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

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

8. Create a Controller

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

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("/or-with-query-method")
public ResponseEntity<?> getByNameOrDepart(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameOrDepart(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("/or-with-jpql")
public ResponseEntity<?> getByNameOrDepartJPQL(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameOrDepartJPQL(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("/or-with-native")
public ResponseEntity<?> getByNameOrDepartNative(@RequestParam String name, @RequestParam String department) {
Map<String, Object> respStaff = new LinkedHashMap<String, Object>();
List<Staff> satffList= staffService.getByNameOrDepartNative(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 or condition in JPA Repository

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

use or condition 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=? or s1_0.department=?

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

use or condition 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=? or s1_0.department=?

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

use or condition 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 = ? OR department = ?

Conclusion

The OR operator in JPA repositories allows us to filter records based on multiple optional criteria. Depending on the complexity and performance needs, we can use it with the custom query method with the OR keyword, JPQL, or native SQL queries.

Leave a Comment