Spring 之 JDBC

# Spring 之 JDBC

JDBC 是 Java 语言中用来规范客户端程序如何访问数据库的应用程序接口，提供了增、删、改、查数据库的方法。

# JDBC 入门示例

JDBC 的工作步骤大致如下：

创建实体类。
声明数据库读写接口的 DAO 接口。定义 DAO 的好处在于对于数据层上层的业务，调用 DAO 时仅关注对外暴露的读写方法，而不考虑底层的具体持久化方式。这样，便于替换持久化方式。
创建一个 DAO 接口的实现类，使用 Spring 的 JDBC 模板去实现接口。
最后，定义一个 DAO 接口的实现类的 JavaBean，并将数据源注入进去。

假设，我们要通过 Spring + JDBC 访问一张 Mysql 数据表 user，user 表的数据结构如下：

-- 创建用户表
CREATE TABLE `user` (
    `id`      BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name`    VARCHAR(255)        NOT NULL DEFAULT '' COMMENT '用户名',
    `age`     INT(3)              NOT NULL DEFAULT 0 COMMENT '年龄',
    `address` VARCHAR(255)        NOT NULL DEFAULT '' COMMENT '地址',
    `email`   VARCHAR(255)        NOT NULL DEFAULT '' COMMENT '邮件',
    PRIMARY KEY (`id`),
    UNIQUE (`name`)
) COMMENT = '用户表';

INSERT INTO `user` (`name`, `age`, `address`, `email`)
VALUES ('张三', 18, '北京', 'xxx@163.com');
INSERT INTO `user` (`name`, `age`, `address`, `email`)
VALUES ('李四', 19, '上海', 'xxx@163.com');

# 定义实体

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Objects;

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String address;
    private String email;
}

# 定义 DAO 接口

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * user 表 Dao 接口
 *
 * @author <a href="mailto:forbreak@163.com">Zhang Peng</a>
 * @since 2019-11-18
 */
public interface UserDao {

    // DML
    // -------------------------------------------------------------------
    void insert(User user);

    void batchInsert(List<User> users);

    void deleteByName(String name);

    void deleteAll();

    void update(User user);

    Integer count();

    List<User> list();

    User queryByName(String name);

    JdbcTemplate getJdbcTemplate();

    // DDL
    // -------------------------------------------------------------------
    void truncate();

    void recreateTable();

}

# 定义 DAO 实现类

通过 JdbcTemplate 执行对应数据源符合语法的 SQL，即可完成各种数据库访问。

package io.github.dunwu.springboot.core.data.jdbc;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

/**
 * user 表 Dao 接口实现类
 *
 * @author <a href="mailto:forbreak@163.com">Zhang Peng</a>
 * @since 2019-11-18
 */
@Repository
public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate;

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

    @Override
    public void insert(User user) {
        jdbcTemplate.update("INSERT INTO user(name, age, address, email) VALUES(?, ?, ?, ?)",
            user.getName(), user.getAge(), user.getAddress(), user.getEmail());
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void batchInsert(List<User> users) {
        String sql = "INSERT INTO user(name, age, address, email) VALUES(?, ?, ?, ?)";

        List<Object[]> params = new ArrayList<>();

        users.forEach(user -> {
            params.add(new Object[] { user.getName(), user.getAge(), user.getAddress(), user.getEmail() });
        });
        jdbcTemplate.batchUpdate(sql, params);
    }

    @Override
    public void deleteByName(String name) {
        jdbcTemplate.update("DELETE FROM user WHERE name = ?", name);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteAll() {
        jdbcTemplate.execute("DELETE FROM user");
    }

    @Override
    public void update(User user) {
        jdbcTemplate.update("UPDATE user SET name=?, age=?, address=?, email=? WHERE id=?",
            user.getName(), user.getAge(), user.getAddress(), user.getEmail(), user.getId());
    }

    @Override
    public Integer count() {
        try {
            return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM user", Integer.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<User> list() {
        return jdbcTemplate.query("SELECT * FROM user", new BeanPropertyRowMapper<>(User.class));
    }

    @Override
    public User queryByName(String name) {
        try {
            return jdbcTemplate.queryForObject("SELECT * FROM user WHERE name = ?",
                new BeanPropertyRowMapper<>(User.class), name);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    @Override
    public void truncate() {
        jdbcTemplate.execute("TRUNCATE TABLE user");
    }

    @Override
    public void recreateTable() {
        jdbcTemplate.execute("DROP TABLE IF EXISTS user");

        String sqlStatement =
            "CREATE TABLE IF NOT EXISTS user (\n"
                + "    id      BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Id',\n"
                + "    name    VARCHAR(255)         NOT NULL DEFAULT '' COMMENT '用户名',\n"
                + "    age     INT(3)              NOT NULL DEFAULT 0 COMMENT '年龄',\n"
                + "    address VARCHAR(255)         NOT NULL DEFAULT '' COMMENT '地址',\n"
                + "    email   VARCHAR(255)         NOT NULL DEFAULT '' COMMENT '邮件',\n"
                + "    PRIMARY KEY (id)\n"
                + ") COMMENT = '用户表';";
        jdbcTemplate.execute(sqlStatement);
    }

}

# 测试类

import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;

import java.util.ArrayList;
import java.util.List;

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

@Slf4j
@Rollback
@SpringBootTest(classes = { SpringBootDataJdbcApplication.class })
public class DataJdbcMysqlDataSourceTest {

    @Autowired
    private UserDao userDAO;

    @BeforeEach
    public void before() {
        userDAO.truncate();
    }

    @Test
    public void insert() {
        userDAO.insert(new User("张三", 18, "北京", "user1@163.com"));
        User linda = userDAO.queryByName("张三");
        assertThat(linda).isNotNull();
    }

    @Test
    public void batchInsert() {
        List<User> users = new ArrayList<>();
        users.add(new User("张三", 18, "北京", "user1@163.com"));
        users.add(new User("李四", 19, "上海", "user1@163.com"));
        users.add(new User("王五", 18, "南京", "user1@163.com"));
        users.add(new User("赵六", 20, "武汉", "user1@163.com"));

        userDAO.batchInsert(users);
        int count = userDAO.count();
        assertThat(count).isEqualTo(4);

        List<User> list = userDAO.list();
        assertThat(list).isNotEmpty().hasSize(4);
        list.forEach(user -> {
            log.info(user.toString());
        });
    }

    @Test
    public void delete() {
        List<User> users = new ArrayList<>();
        users.add(new User("张三", 18, "北京", "user1@163.com"));
        users.add(new User("李四", 19, "上海", "user1@163.com"));
        users.add(new User("王五", 18, "南京", "user1@163.com"));
        users.add(new User("赵六", 20, "武汉", "user1@163.com"));
        userDAO.batchInsert(users);

        userDAO.deleteByName("张三");
        User user = userDAO.queryByName("张三");
        assertThat(user).isNull();

        userDAO.deleteAll();
        List<User> list = userDAO.list();
        assertThat(list).isEmpty();
    }

    @Test
    public void update() {
        userDAO.insert(new User("张三", 18, "北京", "user1@163.com"));
        User oldUser = userDAO.queryByName("张三");
        oldUser.setName("张三丰");
        userDAO.update(oldUser);
        User newUser = userDAO.queryByName("张三丰");
        assertThat(newUser).isNotNull();
    }

}

# Spring Boot JDBC

完整示例：spring-boot-data-jdbc (opens new window)

# 引入 Spring Boot 依赖

你可以通过 Spring Boot 官方的初始化器（Spring Initializr (opens new window)）选择需要的组件来创建一个 Spring Boot 工程。或者，直接在 pom.xml 中引入所需要的依赖：

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.7</version>
  </parent>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
  </dependencies>

# 配置数据源

引入依赖后，需要在 application.properties 或 application.yml 文件中指定数据源配置。

下面是一个最基本的数据源配置示例：

spring.datasource.url = jdbc:mysql://localhost:3306/spring_tutorial?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.username = root
spring.datasource.password = root

需要根据实际情况，替换 url、username、password。

# 测试

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import javax.sql.DataSource;

@Slf4j
@SpringBootApplication
public class SpringBootDataJdbcApplication implements CommandLineRunner {

    private final JdbcTemplate jdbcTemplate;

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

    public static void main(String[] args) {
        SpringApplication.run(SpringBootDataJdbcApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        DataSource dataSource = jdbcTemplate.getDataSource();

        Connection connection;
        if (dataSource != null) {
            connection = dataSource.getConnection();
        } else {
            log.error("连接数据源失败！");
            return;
        }

        if (connection != null) {
            log.info("数据源 Url: {}", connection.getMetaData().getURL());
        } else {
            log.error("连接数据源失败！");
        }
    }

}

运行 main 方法后，控制台会输出以下内容，表示数据源连接成功：

20:50:18.449 [main] [INFO ] i.g.d.s.d.SpringBootDataJdbcApplication.run - 数据源 Url: jdbc:mysql://localhost:3306/spring_tutorial?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8

# Spring JDBC

完整示例：spring-data-jdbc (opens new window)

spring-boot-starter-data-jdbc 引入了 spring-jdbc ，其 JDBC 特性就是基于 spring-jdbc。

# 引入 Spring 依赖

在 pom.xml 中引入所需要的依赖：

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
        </dependency>
    </dependencies>
</project>

# 基于 JDBC 驱动的数据源配置

下面是一个 mysql 的 JDBC 数据源配置实例：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns="http://www.springframework.org/schema/beans"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd
            http://www.springframework.org/schema/jdbc
            http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

    <!-- 引入配置文件 -->
    <context:property-placeholder location="classpath:properties/mysql.properties" />

    <!-- 使用JDBC驱动的数据源 -->
    <!-- (1)在每个连接请求时都会返回一个新建的连接。性能不高 -->
    <bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- (2)在每个连接请求时都会返回同一个连接。不适用于多线程 -->
    <bean id="dataSource2" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- JDBC模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource1" />
    </bean>
    <bean id="userDao" class="io.github.dunwu.springboot.data.jdbc.UserDaoImpl">
        <constructor-arg ref="jdbcTemplate" />
    </bean>

    <!-- 初始化数据表结构 -->
    <jdbc:initialize-database data-source="dataSource1" ignore-failures="ALL">
        <jdbc:script location="classpath:sql/schema.sql" />
        <jdbc:script location="classpath:sql/data.sql" />
    </jdbc:initialize-database>
</beans>

# 测试


import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.io.IOException;
import java.sql.SQLException;

@SuppressWarnings("all")
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:data/spring-mysql.xml" })
public class MysqlJdbcTest {

    @Autowired
    private ApplicationContext ctx;

    @Before
    public void before() {
        ctx = JdbcDemo.getMysqlApplicationContext();
    }

    @Test
    public void testExecJdbcOper() throws SQLException, IOException {
        UserDao userDao = (UserDaoImpl) ctx.getBean("userDao");
        JdbcDemo.execJdbcOper(userDao);
    }

    @After
    public void after() {
        ((ClassPathXmlApplicationContext) ctx).close();
    }

}

# JdbcTemplate API

Spring 将数据访问的样板式代码提取到模板类中。Spring 提供了 3 个 JDBC 模板类：

JdbcTemplate：最基本的 Spring JDBC 模板，这个模板支持最简单的 JDBC 数据库访问功能以及简单的索引参数查询。
SimpleJdbcTemplate：改模板类利用 Java 5 的一些特性，如自动装箱、泛型以及可变参数列表来简化 JDBC 模板的使用。
NamedParameterJdbcTemplate：使用该模板类执行查询时，可以将查询值以命名参数的形式绑定到 SQL 中，而不是使用简单的索引参数。

spring-jdbc 最核心的 API 无疑就是 JdbcTemplate，可以说所有的 JDBC 数据访问，几乎都是围绕着这个类去工作的。Spring 对数据库的操作在 Jdbc 层面做了深层次的封装，利用依赖注入，把数据源配置装配到 JdbcTemplate 中，再由 JdbcTemplate 负责具体的数据访问。

JdbcTemplate 主要提供以下几类方法：

execute 方法：可以用于执行任何 SQL 语句，一般用于执行 DDL 语句；
update 方法及 batchUpdate 方法：update 方法用于执行新增、修改、删除等语句；batchUpdate 方法用于执行批处理相关语句；
query 方法及 queryForXXX 方法：用于执行查询相关语句；
call 方法：用于执行存储过程、函数相关语句。

为了方便演示，以下增删改查操作都围绕一个名为 user 的表（该表的主键 id 是自增序列）进行，该表的数据实体如下：

public class User {
    private Integer id;
    private String name;
    private Integer age;

    // 省略 getter/setter
}

数据实体只要是一个纯粹的 Java Bean 即可，无需任何注解修饰。

# execute 方法

使用 execute 执行 DDL 语句，创建一个名为 test 的数据库，并在此数据库下新建一个名为 user 的表。

public void recreateTable() {
    jdbcTemplate.execute("DROP DATABASE IF EXISTS test");
    jdbcTemplate.execute("CREATE DATABASE test");
    jdbcTemplate.execute("USE test");
    jdbcTemplate.execute("DROP TABLE if EXISTS user");
    jdbcTemplate.execute("DROP TABLE if EXISTS user");
    // @formatter:off
    StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE user (id int (10) unsigned NOT NULL AUTO_INCREMENT,\n")
        .append("name varchar (64) NOT NULL DEFAULT '',\n")
        .append("age tinyint (3) NOT NULL DEFAULT 0,\n")
        .append("PRIMARY KEY (ID));\n");
    // @formatter:on
    jdbcTemplate.execute(sb.toString());
}

# update 方法

新增数据

public void insert(String name, Integer age) {
    jdbcTemplate.update("INSERT INTO user(name, age) VALUES(?, ?)", name, age);
}

删除数据

public void delete(String name) {
    jdbcTemplate.update("DELETE FROM user WHERE name = ?", name);
}

修改数据

public void update(User user) {
    jdbcTemplate.update("UPDATE USER SET name=?, age=? WHERE id=?", user.getName(), user.getAge(), user.getId());
}

批处理

public void batchInsert(List<User> users) {
    String sql = "INSERT INTO user(name, age) VALUES(?, ?)";

    List<Object[]> params = new ArrayList<>();

    users.forEach(item -> {
        params.add(new Object[] {item.getName(), item.getAge()});
    });
    jdbcTemplate.batchUpdate(sql, params);
}

# query 方法

查单个对象

public User queryByName(String name) {
    try {
        return jdbcTemplate
            .queryForObject("SELECT * FROM user WHERE name = ?", new BeanPropertyRowMapper<>(User.class), name);
    } catch (EmptyResultDataAccessException e) {
        return null;
    }
}

查多个对象

public List<User> list() {
    return jdbcTemplate.query("select * from USER", new BeanPropertyRowMapper(User.class));
}

获取某个记录某列或者 count、avg、sum 等函数返回唯一值

public Integer count() {
    try {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM user", Integer.class);
    } catch (EmptyResultDataAccessException e) {
        return null;
    }
}

# SpringBoot JDBC 配置

# JdbcTemplateAutoConfiguration 类

JdbcTemplateAutoConfiguration 是 JdbcTemplate 自动配置类，它负责实例化 JdbcTemplate。

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration {

}

JdbcTemplateAutoConfiguration 类的源码解读：

@AutoConfigureAfter(DataSourceAutoConfiguration.class) 表明 JdbcTemplateAutoConfiguration 必须在 DataSourceAutoConfiguration 执行完之后才开始工作，这意味着：JdbcTemplate 的初始化必须在 DataSource 初始化之后。
JdbcProperties 是 JdbcTemplateAutoConfiguration 的配置选项类，允许使用者通过设置选项控制 JdbcTemplate 初始化行为。
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class }) 表明引入 JdbcTemplateConfiguration、NamedParameterJdbcTemplateConfiguration 两个配置类，具体的实例化 JdbcTemplate 的工作也是放在这两个配置中完成。

# JdbcTemplateConfiguration 类

JdbcTemplateConfiguration 源码如下：

@Configuration(proxyBeanMethods = false)
@ConditionalOnMissingBean(JdbcOperations.class)
class JdbcTemplateConfiguration {

	@Bean
	@Primary
	JdbcTemplate jdbcTemplate(DataSource dataSource, JdbcProperties properties) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		JdbcProperties.Template template = properties.getTemplate();
		jdbcTemplate.setFetchSize(template.getFetchSize());
		jdbcTemplate.setMaxRows(template.getMaxRows());
		if (template.getQueryTimeout() != null) {
			jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
		}
		return jdbcTemplate;
	}

}

JdbcTemplateConfiguration 源码解读：JdbcTemplateConfiguration 中根据 DataSource 和 JdbcProperties 实例化了一个 JdbcTemplate。

# NamedParameterJdbcTemplateConfiguration 类

NamedParameterJdbcTemplateConfiguration 源码如下：

@Configuration(proxyBeanMethods = false)
@ConditionalOnSingleCandidate(JdbcTemplate.class)
@ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
class NamedParameterJdbcTemplateConfiguration {

	@Bean
	@Primary
	NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
		return new NamedParameterJdbcTemplate(jdbcTemplate);
	}

}

NamedParameterJdbcTemplateConfiguration 源码解读：NamedParameterJdbcTemplateConfiguration 中根据 JdbcTemplate 实例化了一个 NamedParameterJdbcTemplate。

# spring-data-jdbc

Spring Data 项目包含了对 JDBC 的存储库支持，并将自动为 CrudRepository 上的方法生成 SQL。对于更高级的查询，提供了 @Query 注解。

当 classpath 上存在必要的依赖项时，Spring Boot 将自动配置 Spring Data 的 JDBC 存储库。它们可以通过 spring-boot-starter-data-jdbc 的单一依赖项添加到项目中。如有必要，可以通过将 @EnableJdbcRepositories 批注或 JdbcConfiguration 子类添加到应用程序来控制 Spring Data JDBC 的配置。

更多 Spring Data JDBC 细节，可以参考 Spring Data JDBC 官方文档 (opens new window)。

# 参考资料

📝 帮助改善此页面！

#Java #框架 #Spring #SpringBoot #JDBC #JdbcTemplate

上次更新: 2024/04/24, 07:33:33

← Spring 之数据源 Spring 之事务→