How to Use Before Date in JPA Repository

by

In the JPA Repository, if we want to fetch records created before the specific date in the database table then we can use findBy…….Before() query method, less than(<) operator with JPQL and less than(<) operator with Native SQL query. This topic will teach us how to use before date in JPA Repository using various methods.

use before date in JPA Repository

Use Before Date in JPA Repository

1. Using findBy…….Before()

Let’s assume that our JPA Entity class has the LocalDate field such as orderDate and we want to fetch all records inserted before the specific date. We can add the query method findByOrderDateBefore(LocalDate Date) in the repository interface to get records before a given date.

Example

import java.time.LocalDate;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
List<OrderDetail> findByOrderDateBefore(LocalDate date);
}
  • findByOrderDateBefore(LocalDate date) returns a list of OrderDetail records that are inserted before the given date.

2. Using JPQL with (<) Operator

Add a custom method in the repository interface to specify a query with (<) with the help of @Query annotation to get the records to use before date in the JPA Repository.

Example

import java.time.LocalDate;
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 od FROM OrderDetail od WHERE od.orderDate < ?1")
List<OrderDetail> findOrderBeforeDate(LocalDate date);
}
  • This orderDate ?1 returns a list of OrderDetail records where the orderDate is less than the given date.

3. Using Native SQL with (<) Operator

Add a custom method to use Native SQL with the (<) operator in the repository interface to get the records to use before a date with the less operator in the JPA Repository.

Example

import java.time.LocalDate;
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 order_date < ?1", nativeQuery = true)
List<OrderDetail> findOrderBeforeDateNative(LocalDate date);
}
  • This order_date < ?1 returns a list of OrderDetail records where orderDate is less than the given date.

Step-by-step Guide to Use Before Date in JPA Repository

Let’s create a Spring Boot Application step-by-step guide for using the before date in the JPA Repository to fetch records from the database table. We will create an example for getting order records where the orderDate is less than the given date 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 DTO classes
  9. Configure the ModelMapper class
  10. Create a Controller
  11. 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 after date in JPA Repository.

Add Model Mapper Dependency

We need to add Model Mapper dependency to the pom.xml file of the project for converting Entity to DTO and visa versa.

<dependency>
<groupId>org.modelmapper</groupId>
<artifactId>modelmapper</artifactId>
<version>3.2.1</version>
</dependency>

Here is the complete pom.xml file with the Model Mapper Library of this project.

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>
		<dependency>
			<groupId>org.modelmapper</groupId>
			<artifactId>modelmapper</artifactId>
			<version>3.2.1</version>
		</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 java.time.LocalDate;

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;

  private LocalDate orderDate;

}

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.time.LocalDate;

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 > findByOrderDateBefore(LocalDate date);

  @Query("SELECT od FROM OrderDetail od WHERE od.orderDate < ?1")

  List < OrderDetail > findOrderBeforeDate(LocalDate date);

  @Query(value = "SELECT * FROM order_detail WHERE order_date < ?1", nativeQuery = true)

  List < OrderDetail > findOrderBeforeDateNative(LocalDate date);

}

6. Create a Service Interface

Create a Service interface OrderService with some method declaration.

OrderService.java

package com.springjava.service;

import java.time.LocalDate;

import java.util.List;

import com.springjava.entity.OrderDetail;

public interface OrderService {

  void saveAll(List < OrderDetail > orderList);

  List < OrderDetail > getOrderListBeforeDate(LocalDate date);

  List < OrderDetail > getOrderListBeforeDateJPQL(LocalDate date);

  List < OrderDetail > getOrderListBeforeDateNative(LocalDate date);

}

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.time.LocalDate;

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 > getOrderListBeforeDate(LocalDate date) {

    return orderRepo.findByOrderDateBefore(date);

  }

  @Override

  public List < OrderDetail > getOrderListBeforeDateJPQL(LocalDate date) {

    return orderRepo.findOrderBeforeDate(date);

  }

  @Override

  public List < OrderDetail > getOrderListBeforeDateNative(LocalDate date) {

    return orderRepo.findOrderBeforeDateNative(date);

  }

}}

8. Create DTO classes

We are creating a DTO class OrderDetailDTO to request for saving OrderDetail.

OrderDetailDTO.java

package com.springjava.dto;

import lombok.Data;

@Data

public class OrderDetailDTO {

  private Long customerId;

  private Double amount;

  private String date;

}

We are creating a DTO class OrderDTO to get OrderDetail records.

OrderDTO.java

package com.springjava.dto;

import lombok.Data;

@Data

public class OrderDTO {

  private Long id;

  private Long customerId;

  private Double amount;

  private String date;

}

9. Configure a Model Mapper class

We are configuring a Model Mapper class in the SpringBootApplication class.

DemoApplication.java

package com.springjava;

import java.time.LocalDate;

import java.time.format.DateTimeFormatter;

import org.modelmapper.Converter;

import org.modelmapper.ModelMapper;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.annotation.Bean;

import com.springjava.dto.OrderDTO;

import com.springjava.dto.OrderDetailDTO;

import com.springjava.entity.OrderDetail;

@SpringBootApplication

public class DemoApplication {

  public static void main(String[] args) {

    SpringApplication.run(DemoApplication.class, args);

  }

  @Bean

  ModelMapper modelMapper() {

    ModelMapper modelMapper = new ModelMapper();

    Converter < LocalDate, String > localDateToString = ctx -> ctx.getSource() != null ? ctx.getSource().format(DateTimeFormatter.ofPattern("d-MM-yyyy")) : null;

    Converter < String, LocalDate > stringtoLocalDate = ctx -> ctx.getSource() != null ? LocalDate.parse(ctx.getSource(), DateTimeFormatter.ofPattern("d-MM-yyyy")) : null;

    // Add the converter for mapping OrderDetail to OrderDTO

    modelMapper.typeMap(OrderDetail.class, OrderDTO.class).addMappings(mapper -> {

      mapper.using(localDateToString).map(OrderDetail::getOrderDate, OrderDTO::setDate);

    });

    //Add the converter for mapping OrderDetailDTO to OrderDetail

    modelMapper.typeMap(OrderDetailDTO.class, OrderDetail.class).addMappings(mapper -> {

      mapper.using(stringtoLocalDate).map(OrderDetailDTO::getDate, OrderDetail::setOrderDate);

    });

    return modelMapper;

  }

}
  • We configured the ModelMapper class for this project and converted the String type property of DTO to the LocalDate type of Entity and vice-versa.
  • We used Lambda Expression and Method Reference in the code.

10. 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.time.LocalDate;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import org.modelmapper.ModelMapper;

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.dto.OrderDTO;

import com.springjava.dto.OrderDetailDTO;

import com.springjava.entity.OrderDetail;

import com.springjava.service.OrderService;

@RestController

@RequestMapping("/api/order")

public class OrderController {

  @Autowired

  private OrderService orderService;

  @Autowired

  private ModelMapper modelMapper;

  @PostMapping("/save-all")

  public ResponseEntity < ? > save(@RequestBody List < OrderDetailDTO > orderList) {

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

    List < OrderDetail > orderDetailList = new ArrayList < > ();

    if (!orderList.isEmpty()) {

      for (OrderDetailDTO orderDetailDTO: orderList) {

        orderDetailList.add(modelMapper.map(orderDetailDTO, OrderDetail.class));

      }

    }

    orderService.saveAll(orderDetailList);

    respOrder.put("status", 1);

    respOrder.put("message", "Record is Saved Successfully!");

    return new ResponseEntity < > (respOrder, HttpStatus.CREATED);

  }

  @GetMapping("/before-date-with-query-method")

  public ResponseEntity < ? > getOrderListBeforeDate(@RequestParam String date) {

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

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("d-MM-yyyy");

    List < OrderDetail > orderList = orderService.getOrderListBeforeDate(LocalDate.parse(date, formatter));

    List < OrderDTO > orderDtoList = new ArrayList < > ();

    if (!orderList.isEmpty()) {

      for (OrderDetail orderDetail: orderList) {

        orderDtoList.add(modelMapper.map(orderDetail, OrderDTO.class));

      }

      respOrder.put("status", 1);

      respOrder.put("data", orderDtoList);

      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("/before-date-with-jpql")

  public ResponseEntity < ? > getOrderListBeforeDateJPQL(@RequestParam String date) {

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

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("d-MM-yyyy");

    List < OrderDetail > orderList = orderService.getOrderListBeforeDateJPQL(LocalDate.parse(date, formatter));

    List < OrderDTO > orderDtoList = new ArrayList < > ();

    if (!orderList.isEmpty()) {

      for (OrderDetail orderDetail: orderList) {

        orderDtoList.add(modelMapper.map(orderDetail, OrderDTO.class));

      }

      respOrder.put("status", 1);

      respOrder.put("data", orderDtoList);

      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("/before-date-with-native")

  public ResponseEntity < ? > getOrderListBeforeDateNative(@RequestParam String date) {

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

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("d-MM-yyyy");

    List < OrderDetail > orderList = orderService.getOrderListBeforeDateNative(LocalDate.parse(date, formatter));

    List < OrderDTO > orderDtoList = new ArrayList < > ();

    if (!orderList.isEmpty()) {

      for (OrderDetail orderDetail: orderList) {

        orderDtoList.add(modelMapper.map(orderDetail, OrderDTO.class));

      }

      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);

    }

  }

11. 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,
    "date": "30-09-2024"
  },
  {
    "customerId": 2,
    "amount": 900,
    "date": "01-10-2024"
  },
  {
    "customerId": 3,
    "amount": 800,
    "date": "01-10-2024"
  }
]

Test the APIs on the Postman Tool

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

use before date in JPA Repository

View ORDER_DETAIL table

GET: http://localhost:8080/api/order/before-date-with-query-method?date=01-10-2024

use before date 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 od1_0.id,od1_0.amount,od1_0.customer_id,od1_0.order_date from order_detail od1_0 where od1_0.order_date<?

GET: http://localhost:8080/api/order/before-date-with-jpql?date=01-10-2024

use before date 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 od1_0.id,od1_0.amount,od1_0.customer_id,od1_0.order_date from order_detail od1_0 where od1_0.order_date<?

GET: http://localhost:8080/api/order/before-date-with-native?date=01-10-2024

use before date 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 order_detail WHERE order_date < ?

Conclusion

In this topic, we learnt how to use before date in JPA Repository using findBy….Before(), JPQL and Native SQL with(<) operator with Spring Boot Rest API. How do we configure the Model Mapper Library and convert it to Entity to DTO and vice versa?

1 thought on “How to Use Before Date in JPA Repository”

  1. Thanks for any other excellent post. The place else may anyone get that kind of information in such an ideal manner of writing? I’ve a presentation next week, and I am at the search for such info.

    Reply

Leave a Comment