How to Get Last Record in Spring Data JPA – 如何在 Spring Data JPA 中获取最后一条记录

1. Overview

1.概述

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.

在这个简短的教程中，我们将探讨在 Spring Data JPA 中获取最后一条记录的不同方法。首先，我们将了解如何使用派生查询方法。然后，我们将探讨如何使用 @Query 注解实现同样的目的。

2. Setup

2.设置

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

首先，创建并初始化我们要查询的表。首先创建一个 Post 实体类：

@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.

这里，@Entity 表示注解的类代表数据库中的一个表。同样，@Id 注解定义了主键。

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:

为了保持简单，我们将使用 H2 作为内存数据库。首先，让我们添加一个基本的 SQL 脚本来创建映射到 Post 类的 post 表：

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.

我们可以看到，最后一条记录的id是 5。因此，为了实现获取最后一条记录的目标，我们将根据 publication_date 来颠倒记录的顺序。然后，我们将使用 Spring Data JPA 方法从排序结果中获取第一条记录。这样，我们就能获得表中的最后一条记录。

3. Using Derived Query Methods

3.使用派生查询方法

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 因其派生查询方法而备受赞誉。该功能提供了一种方便的方法，可根据方法名称生成查询，而无需手动编写 SQL 语句。

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.

Spring Data JPA 并不提供获取最后一条记录的直接方法。另一方面，它提供了从一组记录的起始位置检索数据的直接方法。

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:

例如，我们可以使用 findFirst 前缀来创建获取第一条记录的派生查询。那么，让我们来看看它的实际效果：

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.

方法名称 findFirstByOrderByPublicationDateDesc() 的每个部分都有其意义。动词“find”告诉 Spring Data JPA 生成一个选择查询，而“First”表示应从结果集中检索第一条记录。

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

此外，“OrderByPublicationDateDesc” 表示我们希望按照 publicationDate 属性对记录进行倒序排序。

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.

在这里，Spring Data JPA 会智能地评估方法名称。首先，它会按发布日期降序排列帖子。这样，它就会把最后一条记录放在结果的开头。

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

然后，它会解释 “findFirst” 以返回排序记录的第一个元素。结果，我们得到了表中的最后一条记录。

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:

同样，我们可以使用 findTop 关键字来实现相同的结果。我们可以交替使用 firstFirst 或 findTop 而不会出现任何问题：

Post findTopByOrderByPublicationDateDesc();

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

最后，让我们为 findTopByOrderByPublicationDateDesc() 方法创建另一个测试用例：

@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

4.使用 @Query 注释

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 注解将方法绑定到可检索最后一条记录的查询。默认情况下，@Query 接受 JPQL 查询。因此，让我们在 PostRepository 中添加另一个名为 findLastPost() 的方法，并使用 @Query 指定获取最后一条记录的查询：

@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.

简而言之，我们选择了按发布日期倒序排序的帖子。然后，我们使用 LIMIT 1 只检索一篇文章。返回的帖子表示最后一条记录。

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.

不出所料，最后一条记录就是id 5 的帖子。

5. Conclusion

5.结论

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.

在本教程中，我们探索了使用 Spring Data JPA 检索特定表中最后一条记录的不同方法。首先，我们了解了如何使用派生查询方法来实现这一目标。然后，我们在 @Query 注解中编写了一个 JPQL 查询，得到了相同的结果。

As always, the complete code for this article can be found over on GitHub.

与往常一样，本文的完整代码可在 GitHub 上找到。