Skip to content

Latest commit

 

History

History
768 lines (596 loc) · 24.1 KB

02.Spring之JDBC.md

File metadata and controls

768 lines (596 loc) · 24.1 KB
Raw
title date order categories tags permalink
Spring 之 JDBC
2019-02-18 06:33:55 -0800
2
Java
框架
Spring
Spring数据
Java
框架
Spring
SpringBoot
JDBC
JdbcTemplate
/pages/cf19fd/

Spring 之 JDBC

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

JDBC 入门示例

JDBC 的工作步骤大致如下：

  1. 创建实体类。
  2. 声明数据库读写接口的 DAO 接口。定义 DAO 的好处在于对于数据层上层的业务，调用 DAO 时仅关注对外暴露的读写方法，而不考虑底层的具体持久化方式。这样，便于替换持久化方式。
  3. 创建一个 DAO 接口的实现类，使用 Spring 的 JDBC 模板去实现接口。
  4. 最后，定义一个 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, '北京', '[email protected]');
INSERT INTO `user` (`name`, `age`, `address`, `email`)
VALUES ('李四', 19, '上海', '[email protected]');

定义实体

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:[email protected]">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:[email protected]">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, "北京", "[email protected]"));
        User linda = userDAO.queryByName("张三");
        assertThat(linda).isNotNull();
    }

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

        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, "北京", "[email protected]"));
        users.add(new User("李四", 19, "上海", "[email protected]"));
        users.add(new User("王五", 18, "南京", "[email protected]"));
        users.add(new User("赵六", 20, "武汉", "[email protected]"));
        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, "北京", "[email protected]"));
        User oldUser = userDAO.queryByName("张三");
        oldUser.setName("张三丰");
        userDAO.update(oldUser);
        User newUser = userDAO.queryByName("张三丰");
        assertThat(newUser).isNotNull();
    }

}

Spring Boot JDBC

完整示例：spring-boot-data-jdbc

引入 Spring Boot 依赖

你可以通过 Spring Boot 官方的初始化器（Spring Initializr）选择需要的组件来创建一个 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

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 官方文档。

参考资料