SpringBoot的JdbcTemplate

Sping Boot 框架对SQL数据库提供了广泛的支持，可以用JdbcTemplate直接访问JDBC。

Sping Data独立的项目提供对多种关系和非关系型数据库的访问支持，MySQL、Oracle、MongoDB、Redis、R2DBC、Apache Solr、Elasticsearch

JDBC依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

MySQL驱动

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

DataSource

通常开发过程中，知道了数据库的ip、端口，访问用户名和密码，以及数据库的类型信息，就可以初始化数据源，这里提到的数据源就是DataSource，数据源表示数据的来源，可以从某个ip上的数据库获取到数据。意简言赅就理解为数据仓库。

Java 代码 JDBC Connection 通知双向指令 DataSource

Spring Boot 支持多种数据库连接池，优先使用 HikariCp，其次是 Tomacat pooling 再次是 Commons DBCP2 如果以上都没有，最后会使用 Oracle UCP连接池，当项目中的 starter依赖了 spring-boot-starter-jdbc 或者是 spring-boot-starter-data-jpa ， 默认添加 HikariCP连接池依赖，也就是默认使用 HikariCP连接池。

JdbcTemplate 配置

# application.yml
# 配置数据源链接 数据库


spring:
  application:
    name: hikari-blog
  sql:
    init:
      mode: never  #ALWAYS：始终初始化数据库 ， EMBEDDED：仅初始化嵌入式数据库  ， NEVER 从不初始化数据库

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/blog?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
    hikari:
      maximum-pool-size: 1000 #最大连接数,默认值10.
      minimum-idle: 200 #最小空闲连接，默认值10.
      connection-timeout: 60000 #连接超时时间(毫秒),默认值30秒.
      #空闲连接超时时间，默认值600000(10分钟),只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
      #如果大于等于 max-lifetime 且 max-lifetime>0,则会被重置为0.
      idle-timeout: 600000
      max-lifetime: 3000000 #连接最大存活时间,默认值30分钟.设置应该比mysql设置的超时时间短
      connection-test-query: select 1 #连接测试查询

数据库启动模式

#ALWAYS：始终初始化数据库 ， EMBEDDED：仅初始化嵌入式数据库  ， NEVER 从不初始化数据库
spring.sql.init.mode =  DatabaseInitializationMode.   

schema.sql: -- DDL语句脚本 创建表结构

data.sql: -- DMl语句脚本 操作数据源

ide链接配置数据库驱动

ide链接配置数据库驱动

JdbcTemplateAutoConfiguration

与之对应的java JdbcTemplate 对应的 JdbcTemplateAutoConfiguration配置类

# JdbcTemplateAutoConfiguration.java

@AutoConfiguration(
    after = {DataSourceAutoConfiguration.class}
)
@ConditionalOnClass({DataSource.class, JdbcTemplate.class})
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties({JdbcProperties.class})
@Import({DatabaseInitializationDependencyConfigurer.class, JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class})
public class JdbcTemplateAutoConfiguration {
    public JdbcTemplateAutoConfiguration() {
    }
}

启动创建表&插入数据

• schema.sql

-- DDL语句脚本 创建表结构

CREATE TABLE `article`
(
    `id`          int(11)      NOT NULL AUTO_INCREMENT COMMENT '主键',
    `user_id`     int(11)      NOT NULL COMMENT '作者 ID',
    `title`       varchar(100) NOT NULL COMMENT '文章标题',
    `summary`     varchar(200) NOT NULL COMMENT '文章概要',
    `read_count`  int(11)      NOT NULL COMMENT '阅读读数',
    `create_time` datetime     NOT NULL COMMENT '创建时间',
    `update_time` datetime     NOT NULL COMMENT '最后修改时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4;



CREATE TABLE `article_detail`
(
    `id`        INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `article_id` INT(11) NOT NULL COMMENT '文章 ID 外键',
    `content`   text    NOT NULL COMMENT '文章内容',
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4

• data.sql

-- DMl语句脚本 操作数据源

Insert into article values ('1', '9527', 'Spring Boot 核心注解', '核心注解的主要作业作用', '20', '2023-07-12 11:34:36', '2023-07-12 11:34:50');


Insert into article values ('2', '1100', 'Spring Boot Web 分享', '源码解析分解', '31', '2023-07-12 11:35:01', '2023-07-12 11:35:07');

注意把模式：spring.sql.init.mode = ALWAYS 始终执行，在第一次启动的创建、后续就可以不执行

启动Spring Boot 项目就会创建表，插入data数据

插入成功的数据表

实体类

ArticlePO.java 博客

package com.example.springjdbc.model;

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

import java.time.LocalDateTime;

/**
 * 博客
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ArticlePO {

    private Integer id; //主键
    private Integer user_id; //作者 ID
    private String title; //文章标题
    private String summary; //文章概要
    private Integer read_count; //阅读读数
    private LocalDateTime create_time; //创建时间
    private LocalDateTime update_time; //最后修改时间

}

ArticleDetailPO.java 博客内容详情

package com.example.springjdbc.model;

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

/**
 * 博客内容详情
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ArticleDetailPO {

    private Integer id;  //主键
    private Integer article_id; //文章 ID 外键
    private String content;  //文章内容
}

增删改查

通过JdbcTemplate & NamedParameterJdbcTemplate操作

NamedParameterJdbcTemplate 在 JdbcTemplate 基础之上的 sql语句通过 paramMap形式传递参数

JdbcTemplate 提供了非常、实用的方法，

1. execute：可以用于执行任何SQL语句，常用来执行DDL语句
2. update、batchUpdate：用于执行新增、修改与删除等语句
3. query 和 queryForxxxx：用于执行查询相关的语句
4. call：执行数据库存储过程和函数相关的语句

注入 JdbcTemplate

@Autowired(required = false)
private JdbcTemplate jdbcTemplate;

查询总条目

@Test
void onTestJdbcTemplate() {
    String sql = "select count(*) as ct from article";
    Long count = jdbcTemplate.queryForObject(sql, Long.class);
    System.out.println("onTestJdbcTemplate 条目数 count: " + count);
}


//  onTestJdbcTemplate 条目数 count: 2

查询返回 JavaBean

@Test
void onTestJDBcPo() {
    String sql = "select * from article where id=? ";
    ArticlePO articlePO = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ArticlePO.class), 1);
    System.out.println("onTestJDBcPo articlePO : " + articlePO.toString());
}

// onTestJDBcPo articlePO : ArticlePO(id=1, user_id=9527, title=Spring Boot 核心注解, summary=核心注解的主要作业作用, read_count=20, create_time=2023-07-12T11:34:36, update_time=2023-07-12T11:34:50)

查看源码可知：

@Nullable
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException {
    List<T> results = (List)this.query((String)sql, (Object[])args, (ResultSetExtractor)(new RowMapperResultSetExtractor(rowMapper, 1)));
    return DataAccessUtils.nullableSingleResult(results);
}

1. sql：语句
2. RowMapper<T>: 接口类， 内部有个 T mapRow(ResultSet rs, int rowNum) throws SQLException;

public interface RowMapper<T> {
    @Nullable
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

3. Object... args : sql中的占位符 id=? , 这里是 数组，并且按照语句中从左到右的顺利

BeanPropertyRowMapper 是系统基于的封装映射实体Bean，

public class BeanPropertyRowMapper<T> implements RowMapper<T> {
    protected final Log logger = LogFactory.getLog(this.getClass());
    @Nullable
    private Class<T> mappedClass;
    private boolean checkFullyPopulated = false;
    private boolean primitivesDefaultedForNullValue = false;
    @Nullable
    private ConversionService conversionService = DefaultConversionService.getSharedInstance();
    @Nullable
    private Map<String, PropertyDescriptor> mappedFields;
    @Nullable
    private Set<String> mappedProperties;

    public BeanPropertyRowMapper() {
    }

    public BeanPropertyRowMapper(Class<T> mappedClass) {
        this.initialize(mappedClass);
    }

    public BeanPropertyRowMapper(Class<T> mappedClass, boolean checkFullyPopulated) {
        this.initialize(mappedClass);
        this.checkFullyPopulated = checkFullyPopulated;
    }

    //...略

    public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
        BeanWrapperImpl bw = new BeanWrapperImpl();
        this.initBeanWrapper(bw);
        T mappedObject = this.constructMappedInstance(rs, bw);
        bw.setBeanInstance(mappedObject);
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Set<String> populatedProperties = this.isCheckFullyPopulated() ? new HashSet() : null;

        for(int index = 1; index <= columnCount; ++index) {
            String column = JdbcUtils.lookupColumnName(rsmd, index);
            String field = this.lowerCaseName(StringUtils.delete(column, " "));
            PropertyDescriptor pd = this.mappedFields != null ? (PropertyDescriptor)this.mappedFields.get(field) : null;
            if (pd != null) {
                try {
                    Object value = this.getColumnValue(rs, index, pd);
                    if (rowNumber == 0 && this.logger.isDebugEnabled()) {
                        this.logger.debug("Mapping column '" + column + "' to property '" + pd.getName() + "' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "'");
                    }

                    try {
                        bw.setPropertyValue(pd.getName(), value);
                    } catch (TypeMismatchException var14) {
                        if (value != null || !this.primitivesDefaultedForNullValue) {
                            throw var14;
                        }

                        if (this.logger.isDebugEnabled()) {
                            this.logger.debug("Intercepted TypeMismatchException for row " + rowNumber + " and column '" + column + "' with null value when setting property '" + pd.getName() + "' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "' on object: " + mappedObject, var14);
                        }
                    }

                    if (populatedProperties != null) {
                        populatedProperties.add(pd.getName());
                    }
                } catch (NotWritablePropertyException var15) {
                    throw new DataRetrievalFailureException("Unable to map column '" + column + "' to property '" + pd.getName() + "'", var15);
                }
            }
        }

        if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
            throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields necessary to populate object of " + this.mappedClass + ": " + this.mappedProperties);
        } else {
            return mappedObject;
        }
    }

    //...略
}

查询 queryForObject 自定义实现

@Test
void onTestQueryRowMapper() {

    String sql = "select * from article where id=? ";
    ArticlePO articlePO = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
        System.out.println("rs: " + rs + "   rowNum:" + rowNum);
        Integer id = rs.getInt("id");
        Integer user_id = rs.getInt("user_id");
        String title = rs.getString("title");
        String summary = rs.getString("summary");
        Integer read_count = rs.getInt("read_count");
        LocalDateTime create_time = new Timestamp(rs.getTimestamp("create_time").getTime()).toLocalDateTime();
        LocalDateTime update_time = new Timestamp(rs.getTimestamp("update_time").getTime()).toLocalDateTime();
        return new ArticlePO(id, user_id, title, summary, read_count, create_time, update_time);
    }, 2);

    System.out.println("onTestQueryRowMapper articlePO : " + articlePO.toString());
}

//  rs: HikariProxyResultSet@1202929159 wrapping com.mysql.cj.jdbc.result.ResultSetImpl@193bb809   rowNum:0

//  onTestQueryRowMapper articlePO : ArticlePO(id=2, user_id=1100, title=Spring Boot Web 分享, summary=源码解析分解, read_count=31, create_time=2023-07-12T19:35:01, update_time=2023-07-12T19:35:07)

queryForList 查询

@Test
void onTestListMap() {
    String sql = "select * from article order by id";
    
    //一行list成员一行记录，Map是列名和值
    List<Map<String, Object>> listMapBean = jdbcTemplate.queryForList(sql);
    listMapBean.forEach(ls -> {
        ls.forEach((k, v) -> {
            System.out.println("key：" + k + "  value：" + v);
        });
        System.out.println("=========================");
    });
}

//以下为打印输出：

key：id  value：1
key：user_id  value：9527
key：title  value：Spring Boot 核心注解
key：summary  value：核心注解的主要作业作用
key：read_count  value：20
key：create_time  value：2023-07-12T11:34:36
key：update_time  value：2023-07-12T11:34:50
=========================
key：id  value：2
key：user_id  value：1100
key：title  value：Spring Boot Web 分享
key：summary  value：源码解析分解
key：read_count  value：31
key：create_time  value：2023-07-12T11:35:01
key：update_time  value：2023-07-12T11:35:07
=========================

update修改

@Test
void onTestUpdate() {
    String sql = "update article set title = ? , update_time = ? where id = ? ";
    int rows = jdbcTemplate.update(sql, "Java JVM 性能调休", "2023-07-13 09:53:31", 2);
    System.out.println("onTestUpdate ：" + rows);
}

// onTestUpdate ：1

注入 NamedParameterJdbcTemplate

@Autowired(required = false)
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

@Test
void onTestNameParameter() {
    String sql = "select *  from article where user_id=:uid and read_count > :num";

    Map<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("uid", 1100);
    paramMap.put("num", 22);

    System.out.println("=================================");
    List<ArticlePO> articlePOList = namedParameterJdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper<>(ArticlePO.class));
    System.out.println("onTestNameParameter articlePOList: " + articlePOList);

    System.out.println("=================================");
    ArticlePO articlePO = namedParameterJdbcTemplate.queryForObject(sql, paramMap, new BeanPropertyRowMapper<>(ArticlePO.class));
    System.out.println("onTestNameParameter articlePO: " + articlePO);
}


//以下为打印：
=================================
onTestNameParameter articlePOList: [ArticlePO(id=2, user_id=1100, title=Java JVM 性能调休, summary=源码解析分解, read_count=31, create_time=2023-07-12T11:35:01, update_time=2023-07-13T09:53:31)]

=================================
onTestNameParameter articlePO: ArticlePO(id=2, user_id=1100, title=Java JVM 性能调休, summary=源码解析分解, read_count=31, create_time=2023-07-12T11:35:01, update_time=2023-07-13T09:53:31)

自定义包装类

在实际开发中，我们查询的数据集合，不是单一的，很多情况对应的api结果集，需要不同表的某些字段，或者是包含其他实体类，

例如：需要在博客类，包含它的博客详情，做为一个实体类。

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ArticleMainPO {

    private Integer id; //主键
    private Integer user_id; //作者 ID
    private String title; //文章标题
    private String summary; //文章概要
    private Integer read_count; //阅读读数
    private LocalDateTime create_time; //创建时间
    private LocalDateTime update_time; //最后修改时间

    //模拟新增包含
    private ArticleDetailPO articleDetailPO;  //博客详情

}

查询

@Test
void onTestQueryContent() {
    String sql = """
            select m.* , d.id as detail_id , d.article_id , d.content
            from article m join article_detail d
            on m.id = d.article_id
            where m.id = :id
            """;
    Map<String, Object> paramMap = new HashMap();
    paramMap.put("id", 1);

    List<ArticleMainPO> articleMainPOList = namedParameterJdbcTemplate.query(sql, paramMap, (rs, rowNum) -> {
        System.out.println("rs: " + rs + "   rowNum:" + rowNum);
        Integer id = rs.getInt("id");
        Integer user_id = rs.getInt("user_id");
        String title = rs.getString("title");
        String summary = rs.getString("summary");
        Integer read_count = rs.getInt("read_count");
        LocalDateTime create_time = new Timestamp(rs.getTimestamp("create_time").getTime()).toLocalDateTime();
        LocalDateTime update_time = new Timestamp(rs.getTimestamp("update_time").getTime()).toLocalDateTime();

        Integer idd = rs.getInt("id");
        Integer article_id = rs.getInt("article_id");
        String content = rs.getString("content");

        ArticleDetailPO articleDetailPO = new ArticleDetailPO(idd, article_id, content);
        return new ArticleMainPO(id, user_id, title, summary, read_count, create_time, update_time, articleDetailPO);
    });

    articleMainPOList.forEach(m -> {
        Integer id = m.getId();
        System.out.println("id = " + id);

        String content = m.getArticleDetailPO().getContent();
        System.out.println("content = " + content);
    });

}

End。

---

简书·SpringBoot的JdbcTemplate

git 项目仓库