Last updated on October 5th, 2024
In the JPA Repository, we can easily perform comparisons involving fetching records where the field value exceeds a specified value. This can be achieved using the JPQL, Native SQL query, and the GreaterThan keyword with the query method. In this topic, we will learn how to use greater than in JPA Repository.
Use Greater Than in JPA Repository
1. Query Method with GreaterThan Keyword
Add a method with the findBy keyword and then the add field name add the suffix GreaterThan keyword and after that add a parameter[findByFieldNameGreaterThan(Parameter param)] in the repository interface.
Example
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
List<OrderDetail> findByAmountGreaterThan(Double amount);
}
- findByAmountGreaterThan(Double amount) returns a list of OrderDetail entities where the amount field is greater than the specified value.
2. JPQL with Greater Than
Create a custom method with @Query annotation to specify greater than query using JPQL.
Example
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query("SELECT o FROM OrderDetail o WHERE o.amount> :amount")
List<OrderDetail> findOrdersWithAmountGreaterThan(Double amount);
}
- In this query o.amount > :amount specifies that we want to select the orders where the amount is greater than the given parameter.
3. Native SQL Query with Greater Than
Create a custom method with @Query annotation with nativeQuery attribute to specify greater than query using Native SQL Query.
Example
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
@Query(value = "SELECT * FROM order_detail WHERE amount > :amount", nativeQuery = true)
List<OrderDetail> findOrdersWithNativeQuery(Double amount);
}
Step-by-step to Guide the Use of Greater Than in JPA Repository
Let’s make a Spring Boot Application step-by-step guide to use the greater than to fetch records from the database table using the JPA Repository. We will create an example to provide a list of orders where the amount is greater than the specified value using API endpoints through the methods mentioned above.
These are the following steps:
- Create a Spring Boot Project
- Setup in the IDE
- Configure H2 Database
- Create a JPA Entity
- Create a Repository Interface
- Create a Service Interface
- Implement the Service Interface
- Create a Controller
- Run the Spring Boot Application
1. Create a 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.
Maven Dependency
Here is the complete maven dependencies file pom.xml for the project which will implement methods to use Greater Than 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 an OrderDetail entity and use Lombok for generating setter and getter methods, a constructor, etc.
OrderDetail.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 OrderDetail {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private Long customerId;
private double amount;
}
5. Create a Repository Interface
Create a repository interface for the OrderDetail JPA Entity class that interface extends the JpaRepository interface to perform persistence operations on the order_detail database table.
OrderRepository.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.OrderDetail;
public interface OrderRepository extends JpaRepository < OrderDetail, Long > {
List < OrderDetail > findByAmountGreaterThan(Double amount);
@Query("SELECT o FROM OrderDetail o WHERE o.amount > :amount")
List < OrderDetail > findOrdersWithAmountGreaterThan(Double amount);
@Query(value = "SELECT * FROM order_detail WHERE amount > :amount", nativeQuery = true)
List < OrderDetail > findOrdersWithNativeQuery(Double amount);
}
6. Create a Service Interface
Create a Service interface OrderService with some method declaration.
OrderService.java
package com.springjava.service;
import java.util.List;
import com.springjava.entity.OrderDetail;
public interface OrderService {
void saveAll(List < OrderDetail > orderList);
List < OrderDetail > getOrderByAmountGreaterThan(Double amount);
List < OrderDetail > getOrderByAmountGreaterThanWithJPQL(Double amount);
List < OrderDetail > getOrderByAmountGreaterThanWithNative(Double amount);
}
7. Implement the Service Interface
Implement the OrderService interface in the OrderServiceImpl class. This class is annotated with @Service annotation, where we inject OrderRepository to call all its methods.
OrderServiceImpl.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.OrderDetail;
import com.springjava.repository.OrderRepository;
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderRepository orderRepo;
@Override
public void saveAll(List < OrderDetail > orderList) {
orderRepo.saveAll(orderList);
}
@Override
public List < OrderDetail > getOrderByAmountGreaterThan(Double amount) {
return orderRepo.findByAmountGreaterThan(amount);
}
@Override
public List < OrderDetail > getOrderByAmountGreaterThanWithJPQL(Double amount) {
return orderRepo.findOrdersWithAmountGreaterThan(amount);
}
@Override
public List < OrderDetail > getOrderByAmountGreaterThanWithNative(Double amount) {
return orderRepo.findOrdersWithNativeQuery(amount);
}
}
8. Create a Controller
Create a controller class OrderController. This is annotated with @RestController to make this class a RestController.
OrderController.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.PathVariable;
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.RestController;
import com.springjava.entity.OrderDetail;
import com.springjava.service.OrderService;
@RestController
@RequestMapping("/api/order")
public class OrderController {
@Autowired
private OrderService orderService;
@PostMapping("/save-all")
public ResponseEntity < ? > save(@RequestBody List < OrderDetail > orderList) {
Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();
orderService.saveAll(orderList);
respOrder.put("status", 1);
respOrder.put("message", "Record is Saved Successfully!");
return new ResponseEntity < > (respOrder, HttpStatus.CREATED);
}
@GetMapping("/greater-than-query-method/{amount}")
public ResponseEntity < ? > getOrdersByAmtGreaterThan(@PathVariable Double amount) {
Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();
List < OrderDetail > orderList = orderService.getOrderByAmountGreaterThan(amount);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity < > (respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity < > (respOrder, HttpStatus.NOT_FOUND);
}
}
@GetMapping("/greater-than-jpql/{amount}")
public ResponseEntity < ? > getOrdersByAmtGreaterThanJPQL(@PathVariable Double amount) {
Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();
List < OrderDetail > orderList = orderService.getOrderByAmountGreaterThanWithJPQL(amount);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity < > (respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity < > (respOrder, HttpStatus.NOT_FOUND);
}
}
@GetMapping("/greater-than-native/{amount}")
public ResponseEntity < ? > getOrdersByAmtGreaterThanNative(@PathVariable Double amount) {
Map < String, Object > respOrder = new LinkedHashMap < String, Object > ();
List < OrderDetail > orderList = orderService.getOrderByAmountGreaterThanWithNative(amount);
if (!orderList.isEmpty()) {
respOrder.put("status", 1);
respOrder.put("data", orderList);
return new ResponseEntity < > (respOrder, HttpStatus.OK);
} else {
respOrder.clear();
respOrder.put("status", 0);
respOrder.put("message", "Data is not found");
return new ResponseEntity < > (respOrder, 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 use the URL http://localhost:[server_port]/h2-console.
JSON Array
We are creating a sample JSON Array to test the API http://localhost:8080/api/order/save-all.
[
{
"customerId": 1,
"amount": 700
},
{
"customerId": 1,
"amount": 800
},
{
"customerId": 2,
"amount": 900
},
{
"customerId": 3,
"amount": 800
}
]
Test the APIs on the Postman Tool
POST: http://localhost:8080/api/order/save-all
GET: http://localhost:8080/api/order/greater-than-query-method/700
This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:
Hibernate: select od1_0.id,od1_0.amount,od1_0.customer_id from order_detail od1_0 where od1_0.amount>?
GET: http://localhost:8080/api/order/greater-than-jpql/700
This API hits then Spring Data JPA (internally uses Hibernate as a JPA provider) generates SQL statement in the console below here:
Hibernate: select od1_0.id,od1_0.amount,od1_0.customer_id from order_detail od1_0 where od1_0.amount>?
GET: http://localhost:8080/api/order/greater-than-native/700
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 order_detail WHERE amount > ?
Conclusion
Greater Than is used in the JPA Repository through query method with GreaterThan keyword, JPQL and Native SQL query.