02-SpringBoot工程中JDBC应用实践

HikariCP应用实践

背景分析

项目开发过程中应用程序与数据库交互时，“获得连接”或“释放连接”是非常消耗系统资源的两个过程，频繁地进行数据库连接的建立和关闭会极大影响系统的性能，若多线程并发量很大，这样耗时的数据库连接就可能让系统变得卡顿。因为TCP连接的创建开支十分昂贵，并且数据库所能承载的TCP并发连接数也有限制，针对这种场景，数据库连接池应运而生。如下图所示：

池化思想分析

池化思想是我们项目开发过程中的一种非常重要的思想，如整数池，字符串池，对象池、连接池、线程池等都是池化思想的一种应用，都是通过复用对象，以减少因创建和释放对象所带来的资源消耗，进而来提升系统性能。例如Integer对象的内部池应用，代码如下：

package com.cy.java.pool;
public class TestInteger01 {
	public static void main(String[] args) {
		Integer n1=100;//Integer.valueOf(100) 编译时优化
		Integer n2=100;
		Integer n3=200;
		Integer n4=200;//池中没有则new Integer(200)
		System.out.println(n1==n2);//true
		System.out.println(n3==n4);//false 
	}
}

连接池原理分析

在系统初始化的时候，在内存中开辟一片空间，将一定数量的数据库连接作为对象存储在对象池里，并对外提供数据库连接的获取和归还方法。用户访问数据库时，并不是建立一个新的连接，而是从数据库连接池中取出一个已有的空闲连接对象；使用完毕归还后的连接也不会马上关闭，而是由数据库连接池统一管理回收，为下一次借用做好准备。如果由于高并发请求导致数据库连接池中的连接被借用完毕，其他线程就会等待，直到有连接被归还。整个过程中，连接并不会关闭，而是源源不断地循环使用，有借有还。数据库连接池还可以通过设置其参数来控制连接池中的初始连接数、连接的上下限数，以及每个连接的最大使用次数、最大空闲时间等，也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

Java中的连接池

Java官方，为了在应用程序中更好的应用连接池技术，定义了一套数据源规范，例如javax.sql.DataSource接口，基于这个接口，很多团队或个人创建了不同的连接池对象。然后我们的应用程序中通过耦合与DataSource接口，便可以方便的切换不同厂商的连接池。Java项目中通过连接池获取连接的一个基本过程，如下图所示：

在上图中，用户通过DataSource对象的getConnection()方法，获取一个连接。假如池中有连接，则直接将连接返回给用户。假如池中没有连接，则会调用Dirver（驱动，由数据库厂商进行实现）对象的connect方法从数据库获取，拿到连接以后，可以将连接在池中放一份，然后将连接返回给调用方。连接需求方再次需要连接时，可以从池中获取，用完以后再还给池对象。

数据库连接池在Java数据库相关中间件产品群中，应该算是底层最基础的一类产品，作为企业应用开发必不可少的组件，无数天才们为我们贡献了一个又一个的优秀产品，它们有的随时代发展，功成身退，有的则还在不断迭代，老而弥坚，更有新生代产品，或性能无敌，或功能全面。目前市场上常见的连接池有DBCP、C3P0，DRUID,HikariCP等。

HikariCP入门实践

数据初始化

登陆mysql，执行如下脚本，例如：

DROP DATABASE IF EXISTS `jy-blog`;
CREATE DATABASE `jy-blog` DEFAULT CHARACTER SET utf8mb4;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

USE `jy-blog`;

create table tb_sys_notices 
(
  id             bigint(20) unsigned auto_increment    comment 'ID',
  title          varchar(50) not null       comment '标题',
  type           char(1)      not null      comment '类型（1通知 2公告）',
  content        varchar(500)   default null    comment '公告内容',
  status         char(1)      default '0'   comment '状态（0正常 1关闭）',
  user_id        bigint(20)   unsigned not null comment '用户id',
  created_time   datetime                   comment '创建时间',
  modified_time  datetime                   comment '更新时间',
  remark        varchar(255)                comment '备注',
  primary key (id)
) engine=innodb auto_increment=1 comment = '通知公告表';

创建项目模块

基于spr-boot模块下创建spr-jdbc模块（module)，名字为spr-jdbc,初始pom.xml内容如下：

 <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>spr-boot</artifactId>
        <groupId>com.spring</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>spr-jdbc</artifactId>

</project>

添加项目依赖

打开spr-jdbc模块的pom.xml文件，然后添加如下依赖：

1. mysql数据库驱动依赖。

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

2. spring对象jdbc支持（此时会默认帮我们下载HiKariCP连接池）。

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

配置HikariCP连接池

在spr-jdbc模块中创建application.properties文件并打开，然后添加如下内容（必写）。

spring.datasource.url=jdbc:mysql:///jy-blog?serverTimezone=GMT%2B8&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root

hikariCP 其它额外配置(可选)，代码如下（具体配置不清晰的可自行百度）：

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=DatebookHikariCP
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1

创建项目启动类

package com.cy;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JdbcApplication {//Application.class
  public static void main(String[] args) {//Main Thread
    SpringApplication.run(JdbcApplication .class, args);
  }
}

HikariCP 连接池测试

第一步：在项目中添加单元测试类及测试方法,代码如下：

package com.cy.pj.common.datasource;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class DataSourceTests {
    @Autowired
	private DataSource dataSource;
	@Test
	public void testConnection() throws Exception{
		System.out.println(dataSource.getConnection());
	}
}

在当前测试类中我们需要：

• 掌握单元测试类、测试方法编写规范。
• 理解DataSource的设计规范及规范的实现。
• 分析在测试类中dataSource属性指向的对象是谁？
• 分析在测试类中DataSource的实现类对象由谁创建和管理？
• 思考基于DataSource接口获取连接的基本过程是怎样的？

第二步：API调用过程分析，如图所示：

测试BUG分析

• 类编译错误，DataSource为javax.sql包中的类型，如图所示：

• 连接错误：数据库连接不上，如图所示：

JDBC基本操作实践

业务分析

基于HikariCP，借助JDBC技术访问公告表中的数据。

业务原型设计

公告列表页面，如图所示：

代码设计及实现

基于JDBC技术操作表中数据，并进行单元测试。
第一步：定义单元测试类，关键代码如下：

package com.cy.pj.sys.dao;

import org.junit.jupiter.api.Test;
import javax.sql.DataSource;
import java.sql.*;

/**
 * 通过此单元测试类获取数据源对象,并且通过数据对象获取数据库连接
 * @SpringBootTest 注解描述的类
 * 为springboot中的单元测试类
 * 说明:
 * 1)springboot中的单元测试类必须放在启动类所在包
 * 或子包中
 * 2)springboot中的单元测试类必须使用@SpringBootTest注解描述
 */
@SpringBootTest
public class JdbcTests {//is a Object
    @Autowired
    private DataSource dataSource;//HikariDataSource (类)
}

第二步：在单元测试类中添加向表中写入数据的方法，关键代码如下：

  @Test
    void testAdd()throws Exception{
        //1.获取连接
        Connection conn=dataSource.getConnection();
        //2.创建Statement(基于此对象发送sql)
        String sql="insert into tb_sys_notices (title,type,content,status,user_id,created_time,modified_time) values (?,?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,"title-A");
        pstmt.setString(2, "1");
        pstmt.setString(3, "Content-A");
        pstmt.setString(4, "0");
        pstmt.setInt(5,101);
        pstmt.setTimestamp(6, new java.sql.Timestamp(System.currentTimeMillis()));
        pstmt.setTimestamp(7, new java.sql.Timestamp(System.currentTimeMillis()));
        //3.发送sql
        pstmt.execute();
        System.out.println("insert ok");
        //4.处理结果(一般查询操作处理结果)
        //5.释放资源
        pstmt.close();
        conn.close();
    }

第三步，添加查询通知的单元测试方法，关键代码如下：

 @Test
 void testQuery01()throws Exception{
        //1.获取连接
        Connection conn=dataSource.getConnection();
        //2.创建Statement(基于此对象发送sql)
        String sql="select id,title,type,content,status,user_id,created_time" +
                   " from tb_sys_notices where id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,1);
        //3.发送sql
        ResultSet resultSet = pstmt.executeQuery();
        //4.处理结果
        while(resultSet.next()){
            Map<String,Object> map=new HashMap<>();
            map.put("id",resultSet.getInt("id"));
            map.put("title",resultSet.getString("title"));
            map.put("type",resultSet.getString("type"));
            map.put("content",resultSet.getString("content"));
            map.put("status",resultSet.getString("status"));
            map.put("user_id",resultSet.getInt("user_id"));
            map.put("created_time",resultSet.getTimestamp("created_time"));
            System.out.println(map);
        }
        //5.释放资源
        resultSet.close();
        pstmt.close();
        conn.close();
    }

第四步，通过元数据让查询映射更加灵活，关键代码如下：

      @Test
    void testQuery02()throws Exception{
        //1.获取连接
        Connection conn=dataSource.getConnection();
        //2.创建Statement(基于此对象发送sql)
        String sql="select id,title,type,content,status,user_id userId,created_time createdTime" +
                " from tb_sys_notices where id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,1);
        //3.发送sql
        ResultSet resultSet = pstmt.executeQuery();
        //4.处理结果
        ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集中的元数据
        int columnCount = metaData.getColumnCount();
        while(resultSet.next()){
            Map<String,Object> map=new HashMap<>();
            for(int i=1;i<=columnCount;i++) {
                map.put(metaData.getColumnLabel(i),//列名或列别名
                        resultSet.getObject(i));//这里的字段下标从1开始
            }
            System.out.println(map);
        }
        //5.释放资源
        resultSet.close();
        pstmt.close();
        conn.close();
    }

JdbcTemplate操作实践（拓展）

业务描述

基于Spring官方提供的JdbcTemplate对象实现对数据库中数据的基本操作。
jdbcTemplate是Spring官方提供的，基于jdbc技术访问数据库的一个API对象。此对象基于模板方法模式，对jdbc操作进行了封装。我们可以基于此对象，以更简单的一个步骤操作数据库中的数据。我们只要在springboot工程中添加了spring-boot-starter-jdbc依赖，服务启动时，就会构建此对象，所以，你用的时候直接从spring容器去获取即可。

Pojo对象定义

定义一个Notice对象，基于此对象封装从数据库中查询到的notice信息，例如：

package com.spring.pojo;

import java.util.Date;

public class Notice {
    private Long id;
    private String title;
    private String type;
    private String content;
    private Long userId;
    private Date createdTime;
    private Date modifiedTime;
    private String remark;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public Date getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(Date createdTime) {
        this.createdTime = createdTime;
    }

    public Date getModifiedTime() {
        return modifiedTime;
    }

    public void setModifiedTime(Date modifiedTime) {
        this.modifiedTime = modifiedTime;
    }

    public String getRemark() {
        return remark;
    }
    public void setRemark(String remark) {
        this.remark = remark;
    }
    
    @Override
    public String toString() {
        return "Notice{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", type='" + type + '\'' +
                ", content='" + content + '\'' +
                ", userId=" + userId +
                ", createdTime=" + createdTime +
                ", modifiedTime=" + modifiedTime +
                ", remark='" + remark + '\'' +
                '}';
    }
}

单元测试逻辑设计及实现

定义单元测试类，在类中基于JdbcTmplate对象操作数据库中数据。例如：

package com.spring.jdbc;
import com.spring.pojo.Notice;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.Date;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class JdbcTemplateTests {
    /**
     * JdbcTemplate 是spring中基于模板方法模式对JDBC操作进行了封装的一个对象，
     * 可以将此对象理解为访问数据库的一个工具类，假如项目中添加了
     * spring-boot-starter-jdbc这个依赖，此时JdbcTemplate对象在服务启动时就会创建。
     *
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void testQuery01(){
        String sql="select id,title,type,content,status,user_id,created_time" +
                " from tb_sys_notices where id=?";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, 1);
        System.out.println(map);
    }
    @Test
    void testQuery02(){
        String sql=" select id,title,type,content,status,user_id,created_time" +
                   " from tb_sys_notices limit 3";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        System.out.println(list);
    }
    
    @Test
    void testQuery03(){
        String sql=" select id,title,type,content,status,user_id userId,created_time createdTime,modified_time       modifiedTime,remark" +
                " from tb_sys_notices limit 3";
        List<Notice> list = jdbcTemplate.query(sql,
                new BeanPropertyRowMapper<>(Notice.class));
        System.out.println(list);
    }

    @Test
    void testAdd(){
        String sql="insert into tb_sys_notices (title,type,content,status,user_id,created_time,modified_time) values (?,?,?,?,?,?,?)";
        Object[] args={"title-B","1","Conent-B","0",102,new Date(),new Date()};
        jdbcTemplate.update(sql,args);
    }

    @Test
    void testUpdate(){
        String sql="update tb_sys_notices set content=? where id=? ";
        Object[] args={"Content-B",2};
        jdbcTemplate.update(sql,args);
    }

    @Test
    void testDelete(){
        String sql="delete from tb_sys_notices where id=? ";
        Object[] args={100};
        int rows=jdbcTemplate.update(sql,args);
        System.out.println(rows);
    }
}

总结(Summary)

总之，本章节重点讲解了，SpringBoot工程下JDBC操作的基础应用。