How to Get Last Record in Spring Data JPA

1. Overview

In this short tutorial, we’ll explore different ways of getting the last record in Spring Data JPA. First, we’ll see how to do it using the Derived Queries Methods. Then, we’ll explore how to achieve the same with the @Query annotation.

2. Setup

First, let’s create and initialize the table we want to query. Let’s start by creating a Post entity class:

@Entity
public class Post {

    @Id
    private Long id;
    private String title;
    private LocalDate publicationDate;

    // standard getters and setters

}

Here, @Entity indicates that the annotated class represents a table in the database. Similarly, the @Id annotation defines the primary key.

To keep things simple, we’ll be using H2 as our in-memory database. First, let’s add a basic SQL script to create the post table mapped to the Post class:

DROP TABLE IF EXISTS post;
CREATE TABLE post(
    id INT PRIMARY KEY,
    title VARCHAR(200),
    publication_date DATE
)

Next, let’s seed the table with data:

INSERT INTO post (id, title, publication_date) VALUES(1, 'Facebook post', '2020-11-10');
INSERT INTO post (id, title, publication_date) VALUES(2, 'Instagram post', '2020-12-24');
INSERT INTO post (id, title, publication_date) VALUES(3, 'Twitter post', '2023-01-10');
INSERT INTO post (id, title, publication_date) VALUES(4, 'tiktok post', '2023-03-18');
INSERT INTO post (id, title, publication_date) VALUES(5, 'Pinterest post', '2023-09-09');

As we can see, the last record here is the one with the id 5. So, to achieve our goal of getting the last record, we’ll reverse the order of the records based on publication_date. Then, we’ll use the Spring Data JPA methods to get the first record from the sorted result. That way, we can get the last record of the table.

3. Using Derived Query Methods

Spring Data JPA is praised for its derived query methods. This feature offers a convenient way to generate queries from method names without having to write SQL statements manually.

Spring Data JPA doesn’t provide any direct method to get the last record. On the other side, it offers straightforward ways to retrieve data from the start of a set of records.

For example, we can use the findFirst prefix to create a derived query that gets the first record. So, let’s see it in action:

public interface PostRepository extends JpaRepository<Post, Integer> {

    Post findFirstByOrderByPublicationDateDesc();

}

Each part of the method name findFirstByOrderByPublicationDateDesc() has its significance. The verb “find” tells Spring Data JPA to generate a select query, and “First” indicates that it should retrieve the first record from the result set.

Furthermore, “OrderByPublicationDateDesc” signifies that we want to sort the records in reverse order by the publicationDate property.

Here, Spring Data JPA evaluates the method name intelligently. It first sorts the posts in descending order by the publication date. That way, it puts the last record at the beginning of the result.

Then, it interprets “findFirst” to return the first element of the sorted records. As a result, we get the last record of the table.

Now, let’s add a test case to confirm that everything works as expected:

@Test
void givenPosts_whenUsingFindFirstDerivedQuery_thenReturnLastPost() {
    Post post = postRepository.findFirstByOrderByPublicationDateDesc();

    assertNotNull(post);
    assertEquals(5, post.getId());
}

We can see our test passing successfully.

Similarly, we can use the findTop keyword to accomplish the same outcome. We can use firstFirst or findTop interchangeably without any issue:

Post findTopByOrderByPublicationDateDesc();

Lastly, let’s create another test case for the findTopByOrderByPublicationDateDesc() method:

@Test
void givenPosts_whenUsingFindTopDerivedQuery_thenReturnLastPost() {
    Post post = postRepository.findTopByOrderByPublicationDateDesc();

    assertNotNull(post);
    assertEquals(5, post.getId());
}

As shown above, the test case passes with success.

4. Using @Query Annotation

Another solution would be using the @Query annotation to bind a method to a query that retrieves the last record. By default, @Query accepts JPQL queries. So, let’s add another method called findLastPost() to our PostRepository and use @Query to specify the query that gets the last post:

@Query("SELECT p FROM Post p ORDER BY p.publicationDate DESC LIMIT 1")
Post findLastPost();

In a nutshell, we selected the posts sorted in reverse order by publication date. Then, we used LIMIT 1 to retrieve only one post. The returned post denotes the last record.

As always, let’s add a test case to test our new method:

@Test
void givenPosts_whenUsingQueryAnnotation_thenReturnLastPost() {
    Post post = postRepository.findLastPost();

    assertNotNull(post);
    assertEquals(5, post.getId());
}

Unsurprisingly, the last record is the post with the id 5.

5. Conclusion

In this tutorial, we explored different ways of retrieving the last record of a specific table using Spring Data JPA. First, we saw how to achieve it using  Derived Queries Methods. Then, we wrote a JPQL query inside a @Query annotation, obtaining the same result.