Get Last Record in JPA Repository

by

Last updated on October 5th, 2024

To get last record in JPA Repository, we can typically sort on specific columns, such as an auto-incremented id or a timestamp. JPA doesn’t provide a built-in method to get last record but we can use a findFirstByOrderBy…Desc(), findTopByOrderBy…Desc(), findByOrderBy…Desc(Limit limit), Pagination, JPQL and Native SQL query. This topic will teach us how to get the last record in JPA Repository using several approaches.

Get Last Record in JPA Repository

Methods to Get Last Record in JPA Repository

1. Using findFirstByOrderBy…Desc()

Let us assume the timestamp column in a table. We want to get last record from the table using the JPA Repository by sorting that timestamp column creating the custom query method with this name findFirstByOrderByTimestampDesc() in the repository interface.

Example

import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
OrderDetail findFirstByOrderByTimestampDesc();
}
  • This findFirstByOrderByTimestampDesc() method orders the record in descending order and fetches the first one that will give the last inserted record.

2. Using findTopByOrderBy…Desc()

Let us assume the timestamp column in a table. We want to get last record from the table using the JPA Repository by sorting that timestamp column creating the custom query method with this name findTopByOrderByTimestampDesc() in the repository interface.

Example

import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
OrderDetail findTopByOrderByTimestampDesc();
}
  • This findTopByOrderByTimestampDesc() method orders the record in descending order and fetches the top one that will give the last inserted record.

3. Using findByOrderBy…Desc(Limit limit)

Let’s consider the timestamp column in a table then we will get the last record from the table using the JPA Repository by sorting that timestamp column creating a custom query method findByOrderByTimestampDesc(Limit limit) with Limit interface as a parameter.

Example

import org.springframework.data.domain.Limit;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<OrderDetail, Long> {
OrderDetail findByOrderByTimestampDesc(Limit limit);
}

We need a Service class to call this method and then pass the Limit.of(1) to get last record from table.

Example

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.data.domain.Limit;

import org.springframework.stereotype.Service;

@Service

public class OrderServiceImpl implements OrderService {

  @Autowired

  private OrderRepository orderRepo;

  @Override

  public OrderDetail getLastOrderWithLimit() {

    return orderRepo.findByOrderByTimestampDesc(Limit.of(1));

  }

}

4. Using Pagination

We can get last record from the table using JPA Repository through pagination sorting in descending order.

Example

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.PageRequest;

import org.springframework.data.domain.Pageable;

import org.springframework.data.domain.Sort;

import org.springframework.stereotype.Service;

@Service

public class OrderServiceImpl implements OrderService {

  @Autowired

  private OrderRepository orderRepo;

  @Override

  public OrderDetail getLastOrderWithPagination() {

    Pageable lastPage = PageRequest.of(0, 1, Sort.by("timestamp").descending());

    Page < OrderDetail > page = orderRepo.findAll(lastPage);

    return page.getContent().get(0);

  }

}

5. Using JPQL

We can get last record from the table using JPA Repository by specifying the explicit query using @Query.

Example

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 ORDER BY od.timestamp DESC LIMIT 1")
OrderDetail findLastOrder();

}

6. Using Native SQL Query

We can get last record from the table using JPA Repository by specifying Native SQL query with ORDER BY and the LIMIT clauses.

Example

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 od ORDER BY timestamp DESC LIMIT 1", nativeQuery = true)
OrderDetail findLastOrderNative();

}

Step-by-step to Guide Get Last Record in JPA Repository

Let’s create a Spring Boot Application step-by-step guide to get last record in the JPA Repository to fetch records from the database table. We will create an example to get last order record 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 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 get the last record in the 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 java.time.LocalDateTime;

import org.hibernate.annotations.CreationTimestamp;

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;

  @CreationTimestamp

  private LocalDateTime timestamp;

}

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 org.springframework.data.domain.Limit;

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 > {

  OrderDetail findFirstByOrderByTimestampDesc();

  OrderDetail findTopByOrderByTimestampDesc();

  OrderDetail findByOrderByTimestampDesc(Limit limit);

  @Query("SELECT od FROM OrderDetail od ORDER BY od.timestamp DESC LIMIT 1")

  OrderDetail findLastOrder();

  @Query(value = "SELECT * FROM order_detail od ORDER BY timestamp DESC LIMIT 1", nativeQuery = true)

  OrderDetail findLastOrderNative();

}

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

  OrderDetail getLastOrderWithFirst();

  OrderDetail getLastOrderWithTop();

  OrderDetail getLastOrderWithLimit();

  OrderDetail getLastOrderWithPagination();

  OrderDetail getLastOrderWithJPQL();

  OrderDetail getLastOrderWithNative();

}

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.data.domain.Limit;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.PageRequest;

import org.springframework.data.domain.Pageable;

import org.springframework.data.domain.Sort;

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 OrderDetail getLastOrderWithFirst() {

    return orderRepo.findFirstByOrderByTimestampDesc();

  }

  @Override

  public OrderDetail getLastOrderWithTop() {

    return orderRepo.findTopByOrderByTimestampDesc();

  }

  @Override

  public OrderDetail getLastOrderWithLimit() {

    return orderRepo.findByOrderByTimestampDesc(Limit.of(1));

  }

  @Override

  public OrderDetail getLastOrderWithPagination() {

    Pageable lastPage = PageRequest.of(0, 1, Sort.by("timestamp").descending());

    Page < OrderDetail > page = orderRepo.findAll(lastPage);

    return page.getContent().get(0);

  }

  @Override

  public OrderDetail getLastOrderWithJPQL() {

    return orderRepo.findLastOrder();

  }

  @Override

  public OrderDetail getLastOrderWithNative() {

    return orderRepo.findLastOrderNative();

  }

}

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.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("/last-order-with-first")

  public ResponseEntity < ? > getLastOrderWithFirst() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithFirst();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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("/last-order-with-top")

  public ResponseEntity < ? > getLastOrderWithTop() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithTop();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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("/last-order-with-limit")

  public ResponseEntity < ? > getLastOrderWithLimit() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithLimit();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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("/last-order-with-pagination")

  public ResponseEntity < ? > getLastOrderWithPagination() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithPagination();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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("/last-order-with-jpql")

  public ResponseEntity < ? > getLastOrderWithJPQL() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithJPQL();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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("/last-order-with-native")

  public ResponseEntity < ? > getLastOrderWithNative() {

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

    OrderDetail lastOrder = orderService.getLastOrderWithNative();

    if (lastOrder != null) {

      respOrder.put("status", 1);

      respOrder.put("data", lastOrder);

      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

H2 Database Table

GET: http://localhost:8080/api/order/last-order-with-first

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.timestamp from order_detail od1_0 order by od1_0.timestamp desc fetch first ? rows only

GET: http://localhost:8080/api/order/last-order-with-top

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.timestamp from order_detail od1_0 order by od1_0.timestamp desc fetch first ? rows only

GET: http://localhost:8080/api/order/last-order-with-limit

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.timestamp from order_detail od1_0 order by od1_0.timestamp desc offset ? rows fetch first ? rows only

GET: http://localhost:8080/api/order/last-order-with-pagination

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.timestamp from order_detail od1_0 order by od1_0.timestamp desc offset ? rows fetch first ? rows only

Hibernate: select count(od1_0.id) from order_detail od1_0

GET: http://localhost:8080/api/order/last-order-with-jpql

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.timestamp from order_detail od1_0 order by od1_0.timestamp desc fetch first 1 rows only

GET: http://localhost:8080/api/order/last-order-with-native

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 od ORDER BY timestamp DESC LIMIT 1

Conclusion

To get the last record in JPA Repository of Spring Data JPA, We can use:

1. Sort by a timestamp field using findFirstByOrderByTimestampDesc()

2. Sort by a timestamp field using findTopByOrderByTimestampDesc()

3. Sort by a timestamp field using findByOrderByTimestampDesc(Limit limit)

4. Sort by a timestamp field using paging and sorting with PageRequest and Sort

5. Sort by a timestamp field using JPQL

6. Sort by a timestamp field using Native SQL query

Leave a Comment