How to use Less Than or Equal to JPA Repository

by

Last updated on October 5th, 2024

We can easily perform comparison operations such as Less Than or Equal to JPA Repository using the LessThanEqual keyword with the query method and the <= operator with JPQL and Native SQL query. In this topic, we will know how to use less than or equal to JPA Repository using the Spring Boot application.

less than or equal to JPA Repository

Use Less Than or Equal to JPA Repository

1. Query Method with LessThanEqual keyword

Add a findByFieldNameLessThanEqual(Parameter param) followed by the parameter method in the repository interface.

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> {
List<OrderDetail> findByAmountLessThanEqual(Double amount);
}
  • The method findByAmountLessThanEqual(Double amount) returns the list of OrderDetail where the amount is less than or equal to the given value.

2. JPQL with <= operator

Add a custom method with @Query annotation to specify a query with the <= operator in the repository interface for using less than or equal to 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> findOrdersWithAmountLessThanEqual(Double amount);
}
  • In the JPQL query, o.amount <= :amount specifies that we want to select OrderDetail where the amount is less than or equal to the given value.

3. Native SQL Query with <= operator

Add a custom method with @Query annotation with nativeQuery attribute to specify a query with <= operator in the repository interface for using less than or equal to use 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 Less Than or Equal to JPA Repository

Let’s create a Spring Boot Application step-by-step guide for using Less Than or Equal to JPA Repository to fetch records from the database table. We will create an example to provide a list of orders where the amount is less than or equal to the specified value using API endpoints through the methods mentioned above.  

These are the following steps:

  1. Create a 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 a Spring Boot Project

We are creating a Spring Boot Project using 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.

less than or equal to JPA Repository

Maven Dependency

Here is the complete maven dependencies file pom.xml for the project which will implement methods to use Less Than or Equal to 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. Consider an OrderDetail entity and use Lombok to generate 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 > findByAmountLessThanEqual(Double amount);

  @Query("SELECT o FROM OrderDetail o WHERE o.amount <= :amount")

  List < OrderDetail > findOrdersWithAmountLessThanEqual(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 > getOrderByAmountLessThanEqual(Double amount);

  List < OrderDetail > getOrderByAmountLessThanEqualWithJPQL(Double amount);

  List < OrderDetail > getOrderByAmountLessThanEqualWithNative(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 > getOrderByAmountLessThanEqual(Double amount) {

    return orderRepo.findByAmountLessThanEqual(amount);

  }

  @Override

  public List < OrderDetail > getOrderByAmountLessThanEqualWithJPQL(Double amount) {

    return orderRepo.findOrdersWithAmountLessThanEqual(amount);

  }

  @Override

  public List < OrderDetail > getOrderByAmountLessThanEqualWithNative(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("/less-than-equal-query-method/{amount}")

  public ResponseEntity < ? > getOrdersByAmtLessThanEqual(@PathVariable Double amount) {

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

    List < OrderDetail > orderList = orderService.getOrderByAmountLessThanEqual(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("/less-than-equal-jpql/{amount}")

  public ResponseEntity < ? > getOrdersByAmtLessThanEqualJPQL(@PathVariable Double amount) {

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

    List < OrderDetail > orderList = orderService.getOrderByAmountLessThanEqualWithJPQL(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("/less-than-equal-native/{amount}")

  public ResponseEntity < ? > getOrdersByAmtLessThanEqualNative(@PathVariable Double amount) {

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

    List < OrderDetail > orderList = orderService.getOrderByAmountLessThanEqualWithNative(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

less than or equal to JPA Repository

GET: http://localhost:8080/api/order/less-than-equal-query-method/900

less than or equal to 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 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/less-than-equal-jpql/900

less than or equal to 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 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/less-than-equal-native/900

less than or equal to 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 order_detail WHERE amount <= ?

Conclusion

Less Than or Equal to is used in the JPA Repository through query method with LessThanEqual keyword, JPQL and Native SQL query with (<=) operator using Spring Boot application.

Leave a Comment