Spring Boot Jdbctemplate 指南-工具盒子

Spring Boot 对 JDBC 的支持 {#spring-boot-对-jdbc-的支持}

Spring 的 JdbcTemplate 在 DataSource 的基础上提供了执行数据库操作的高级抽象。配合 Spring 的声明式事务，能以很简单的方式管理数据库事务，而无需编写模板式的代码。

Spring Boot 通过自动配置机制简化了 DataSource、TransactionManager 等的配置。

让我们看看如何使用 JdbcTemplate 对 PostgreSQL 数据库执行 CRUD 操作。

首先，访问 https://start.springboot.io/，选择 JDBC API、PostgreSQL Driver 和 Testcontainers starter，创建 Spring Boot 应用程序。

假设，我们正在开发一个管理书签（bookmarks）的简单应用。因此，我们将创建包含 id、title、url 和 created_at 列的 bookmarks 表。

初始化数据源 {#初始化数据源}

Spring Boot 提供了一种方便的数据库初始化机制。我们可以在 src/main/resources 下创建 schema.sql 和 data.sql 文件，这些文件将在启动应用程序时自动执行。不过，只有在使用 HSQL、H2 等内存数据库时，才会默认启用自动脚本执行功能，否则就会禁用。

我们可以在 src/main/resources/application.properties 文件中添加以下属性来启用脚本初始化。

spring.sql.init.mode=always

现在，让我们创建 src/main/resources/schema.sql 文件，如下所示：

create table if not exists bookmarks
(
    id         bigserial not null,
    title      varchar   not null,
    url        varchar   not null,
    created_at timestamp,
    primary key (id)
);

要插入一些示例数据，请创建 src/main/resources/data.sql 文件，如下所示：

truncate table bookmarks;
ALTER SEQUENCE bookmarks_id_seq RESTART WITH 1;

insert into bookmarks(title, url, created_at) values
('How (not) to ask for Technical Help?','https://sivalabs.in/how-to-not-to-ask-for-technical-help', CURRENT_TIMESTAMP),
('Announcing My SpringBoot Tips Video Series on YouTube','https://sivalabs.in/announcing-my-springboot-tips-video-series', CURRENT_TIMESTAMP),
('Kubernetes - Exposing Services to outside of Cluster using Ingress','https://sivalabs.in/kubernetes-ingress', CURRENT_TIMESTAMP),
('Kubernetes - Blue/Green Deployments','https://sivalabs.in/kubernetes-blue-green-deployments', CURRENT_TIMESTAMP),
('Kubernetes - Releasing a new version of the application using Deployment Rolling Updates','https://sivalabs.in/kubernetes-deployment-rolling-updates', CURRENT_TIMESTAMP),
('Getting Started with Kubernetes','https://sivalabs.in/getting-started-with-kubernetes', CURRENT_TIMESTAMP),
('Get Super Productive with Intellij File Templates','https://sivalabs.in/get-super-productive-with-intellij-file-templates', CURRENT_TIMESTAMP),
('Few Things I learned in the HardWay in 15 years of my career','https://sivalabs.in/few-things-i-learned-the-hardway-in-15-years-of-my-career', CURRENT_TIMESTAMP),
('All the resources you ever need as a Java & Spring application developer','https://sivalabs.in/all-the-resources-you-ever-need-as-a-java-spring-application-developer', CURRENT_TIMESTAMP),
('GoLang from a Java developer perspective','https://sivalabs.in/golang-from-a-java-developer-perspective', CURRENT_TIMESTAMP),
('Imposing Code Structure Guidelines using ArchUnit','https://sivalabs.in/impose-architecture-guidelines-using-archunit', CURRENT_TIMESTAMP),
('SpringBoot Integration Testing using TestContainers Starter','https://sivalabs.in/spring-boot-integration-testing-using-testcontainers-starter', CURRENT_TIMESTAMP),
('Creating Yeoman based SpringBoot Generator','https://sivalabs.in/creating-yeoman-based-springboot-generator', CURRENT_TIMESTAMP),
('Testing REST APIs using Postman and Newman','https://sivalabs.in/testing-rest-apis-with-postman-newman', CURRENT_TIMESTAMP),
('Testing SpringBoot Applications','https://sivalabs.in/spring-boot-testing', CURRENT_TIMESTAMP)
;

使用数据库迁移工具

虽然 Spring Boot 提供了使用 schema.sql 和 data.sql 初始化数据库的简便方法，但我们更倾向于使用合适的数据库迁移工具，如 Liquibase 或 Flyway。

使用 JdbcTemplate 实现 CRUD 操作 {#使用-jdbctemplate-实现-crud-操作}

先创建 Bookmark 类。

import java.time.Instant;

public record Bookmark(
        Long id,
        String title,
        String url,
        Instant createdAt) {}

我们使用 Java record 来创建 Bookmark 对象。

现在，让我们创建 BookmarkRepository 类，并且注入 JdbcTemplate。如下所示：

@Repository
public class BookmarkRepository {
    private final JdbcTemplate jdbcTemplate;

    public BookmarkRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
}

让我们从实现 findAll() 方法开始，该方法用于从 bookmarks 表中获取所有记录。

实现 `findAll()` 方法 {#实现-findall-方法}

当我们查询数据库时，它会返回一个 ResultSet。我们可以提供一个 RowMapper 实现，将 ResultSet 数据封装到我们的 Bookmark 对象中，如下所示：

@Repository
public class BookmarkRepository {
    private final JdbcTemplate jdbcTemplate;

    public BookmarkRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Bookmark> findAll() {
        String sql = "select id, title, url, created_at from bookmarks";
        return jdbcTemplate.query(sql, INSTANCE);
    }

    static class BookmarkRowMapper implements RowMapper<Bookmark> {
        public static final BookmarkRowMapper INSTANCE = new BookmarkRowMapper();
        
        private BookmarkRowMapper(){}
        
        @Override
        public Bookmark mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Bookmark(
                    rs.getLong("id"),
                    rs.getString("title"),
                    rs.getString("url"),
                    rs.getTimestamp("created_at").toInstant()
            );
        }
    }
}

我们创建了实现 RowMapper 接口的 BookmarkRowMapper 实现类（单例设计）。然后，我们使用 findAll() 方法从 bookmarks 表中获取所有行，并使用 BookmarkRowMapper 将其封装为 Bookmark 对象。

注意：如果记录数量很多，一次性从表中获取所有记录可能会导致 OutOfMemoryException 异常。因此，最好使用分页功能，只获取部分记录并进行处理。

实现 `findById()` 方法 {#实现-findbyid-方法}

让我们实现 findById(Long id) 方法，按 id 检索 bookmark，如下所示：

public Optional<Bookmark> findById(Long id) {
    String sql = "select id, title, url, created_at from bookmarks where id = ?";
    try {
        Bookmark bookmark = jdbcTemplate.queryForObject(sql, INSTANCE, id);
        return Optional.of(bookmark);
    } catch (EmptyResultDataAccessException e) {
        return Optional.empty();
    }
}

findById() 方法返回的是 Optional<Bookmark>，因为给定 id 的 bookmark 可能存在，也可能不存在。我们在此使用的是 jdbcTemplate.queryForObject(...) 方法，如果没有找到记录，该方法会抛出 EmptyResultDataAccessException 异常。因此，我们将处理该异常并返回 Optional.empty()。

实现 `create()` 方法 {#实现-create-方法}

在 create() 方法中，我们将向 bookmarks 表插入一条记录，并返回自增的主键值。

public Long create(Bookmark bookmark) {
    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbcTemplate.update(connection -> {
        String sql = "insert into bookmarks(title, url, created_at) values(?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
        ps.setString(1, bookmark.title());
        ps.setString(2, bookmark.url());
        ps.setTimestamp(3, Timestamp.from(bookmark.createdAt()));
        return ps;
    }, keyHolder);

    return (long) keyHolder.getKey();
}

请注意，在较新版本的 PostgreSQL 中，我们使用 connection.prepareStatement(sql, new String[] { "id" }) 指定自动生成的 key。在以前的版本中，我们可以使用 connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) 来指定。

实现 `update()` 方法 {#实现-update-方法}

让我们以这样一种逻辑来实现 update() 方法：如果给定 id 的 bookmark 记录存在，我们就需要更新 title 和 url 列。否则，将抛出异常。

public void update(Bookmark bookmark) {
    String sql = "update bookmarks set title = ?, url = ? where id = ?";
    int count = jdbcTemplate.update(sql, bookmark.title(), bookmark.url(), bookmark.id());
    if (count == 0) {
        throw new RuntimeException("Bookmark not found");
    }
}

jdbcTemplate.update(...) 方法会返回受影响的行数。如果 count 为 0，则表示指定 id 的记录不存在，因此我们会抛出异常。

实现 `delete()` 方法 {#实现-delete-方法}

让我们以这样的逻辑来实现 delete() 方法：如果指定 id 的 bookmark 记录存在，则删除该记录，否则抛出异常。

public void delete(Long id) {
    String sql = "delete from bookmarks where id = ?";
    int count = jdbcTemplate.update(sql, id);
    if (count == 0) {
        throw new RuntimeException("Bookmark not found");
    }
}

使用 Testcontainers 测试 Repository {#使用-testcontainers-测试-repository}

在测试中，我们应该使用相同类型的数据库来测试 repository。Testcontainers 可以帮助我们使用真实的依赖来测试应用程序，而不是使用模拟（mock）或 H2 等内存数据库。

编写 BookmarkRepositoryTest，使用 Testcontainers 测试 repository。

如下：

package com.sivalabs.bookmarks.domain;

import static org.assertj.core.api.Assertions.assertThat;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestMethodOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.JdbcTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.time.Instant;
import java.util.List;
import java.util.Optional;

@JdbcTest(properties = {
   "spring.test.database.replace=none",
   "spring.datasource.url=jdbc:tc:postgresql:15.2-alpine:///db"
})
class BookmarkRepositoryTest {

    @Autowired
    JdbcTemplate jdbcTemplate;

    BookmarkRepository bookmarkRepository;

    @BeforeEach
    void setUp() {
        bookmarkRepository = new BookmarkRepository(jdbcTemplate);
    }

    @Test
    void shouldFindAllBookmarks() {
        List<Bookmark> bookmarks = bookmarkRepository.findAll();
        assertThat(bookmarks).isNotEmpty();
        assertThat(bookmarks).hasSize(15);
    }

    @Test
    void shouldCreateBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);
        assertThat(id).isNotNull();
    }

    @Test
    void shouldGetBookmarkById() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);

        Optional<Bookmark> bookmarkOptional = bookmarkRepository.findById(id);
        assertThat(bookmarkOptional).isPresent();
    }

    @Test
    void shouldEmptyWhenBookmarkNotFound() {
        Optional<Bookmark> bookmarkOptional = bookmarkRepository.findById(9999L);
        assertThat(bookmarkOptional).isEmpty();
    }

    @Test
    void shouldUpdateBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);

        Bookmark updatedBookmark = new Bookmark(id, "My Updated Title", "https://www.sivalabs.in", bookmark.createdAt());
        bookmarkRepository.update(updatedBookmark);

        updatedBookmark = bookmarkRepository.findById(id).orElseThrow();
        assertThat(updatedBookmark.id()).isEqualTo(id);
        assertThat(updatedBookmark.title()).isEqualTo("My Updated Title");
        assertThat(updatedBookmark.url()).isEqualTo("https://www.sivalabs.in");
    }
    
    @Test
    void shouldDeleteBookmark() {
        Bookmark bookmark = new Bookmark(null, "My Title", "https://sivalabs.in", Instant.now());
        Long id = bookmarkRepository.save(bookmark);

        bookmarkRepository.delete(id);
    }
}

我们使用 Spring Boot Test Slice 注解 @JdbcTest 来只测试 Repository，而不是加载整个应用程序。然后，我们使用 Testcontainers 特殊的 JDBC URL 支持，使用 postgres:15.2-alpine 镜像启动 PostgreSQL 数据库。然后，我们编写了各种测试方法来测试我们的 CRUD 操作。