SpringBoot与JDBC

JDBC

概念

• Java DataBase Connectivity(Java 数据库连接)， Java语言操作数据库

• JDBC本质：其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

• JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。

• 有了JDBC，向各种关系数据发送SQL语句就是一件很容易的事。换言之，有了JDBC API，就不必为访问Mysql专门写一个程序，为访问Oracle又专门写一个程序等等

• 程序员只需用JDBC API写一个程序就够了，它可向相应数据库发送SQL调用。同时，将Java语言和JDBC结合起来使程序员不必为不同的平台编写不同的应用程序，只须写一遍程序就可以让它在任何平台上运行，这也是Java语言“编写一次，处处运行”的优势。

• DriverManager 类是 JDBC 的管理层，作用于用户和驱动程序之间。它跟踪可用的驱动程序，并在数据库和相应驱动程序之间建立连接。

• DriverManager 类包含一列 Driver 类，它们已通过调用方法 DriverManager.registerDriver 对自己进行了注册。所有 Driver 类都必须包含有一个静态部分。它创建该类的实例，然后在加载该实例时 DriverManager 类进行注册。这样，用户正常情况下将不会直接调用 DriverManager.registerDriver；而是在加载驱动程序时由驱动程序自动调用。

常用类详解

JDBC API主要位于JDK中的java.sql包中（之后扩展的内容位于javax.sql包中

• DriverManager：负责加载各种不同驱动程序（Driver），并根据不同的请求，向调用者返回相应的数据库连接（Connection）。
• Driver：驱动程序，会将自身加载到DriverManager中去，并处理相应的请求并返回相应的数据库连接（Connection）。
• Connection：数据库连接，负责与进行数据库间通讯，SQL执行以及事务处理都是在某个特定Connection环境中进行的。可以产生用以执行SQL的Statement。
• Statement：用以执行SQL查询和更新（针对静态SQL语句和单次执行）。PreparedStatement：用以执行包含动态参数的SQL查询和更新（在服务器端编译，允许重复执行以提高效率）。
• CallableStatement：用以调用数据库中的存储过程。
• SQLException：代表在数据库连接的建立和关闭和SQL语句的执行过程中发生了例外情况（即错误）。

DriverManager

• 获取数据库连接：

• 方法：static Connection getConnection(String url, String user, String password)

• 参数

  • url：指定连接的路径
    • 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
    • 例子：jdbc:mysql://localhost:3306/db3
    • 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
  • user：用户名
  • password：密码

• 加载 Driver 类并在 DriverManager 类中注册后，它们即可用来与数据库建立连接。当调用 DriverManager.getConnection 方法发出连接请求时，DriverManager 将检查每个驱动程序，

Connection

• 数据库连接对象

1. 获取执行sql 的对象
   • Statement createStatement()
   • PreparedStatement prepareStatement(String sql)
2. 管理事务：
   • 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
   • 提交事务：commit()
   • 回滚事务：rollback()

Statement

• Statement安全性较低，不能有效防止sql注入问题，项目中很少使用。接口提供了三种执行 SQL 语句的方法：executeQuery、executeUpdate 和 execute。

• 执行sql

1. boolean execute(String sql) ：可以执行任意的sql 了解

2. int executeUpdate(String sql) ：执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句

   • 返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功
   • 返回值>0的则执行成功，反之，则失败。

3. ResultSet executeQuery(String sql) ：执行DQL（select)语句

   public static void main(String[] args) {
       Connection conn = null;
       Statement stmt = null;
       try {
           //1. 注册驱动
           Class.forName("com.mysql.jdbc.Driver");
           //2.获取连接对象
           conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "123456");
           //3.定义sql
           String sql  = "update account set balance = 1500 where id = 3";
           //4.获取执行sql对象
           stmt = conn.createStatement();
           //5.执行sql
           int count = stmt.executeUpdate(sql);
           //6.处理结果
           System.out.println(count);
           if(count > 0){
               System.out.println("修改成功！");
           }else{
               System.out.println("修改失败");
           }
   
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           //7.释放资源
           if(stmt != null){
               try {
                   stmt.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
   
           if(conn != null){
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
   }

ResultSet

• ResultSet：结果集对象,封装查询结果， 调用Statement对象的excuteQuery(sql)方法可以得到结果集

• 返回的实际上就是一张数据表，有一个指针， 指向数据表的第一行的前面，可以调用next()方法检测下一行是否有效，若有效则返回true，并且指针下移，相当于迭代器对象的hasNext()和next()的结合体。

• 当指针对位到确定的一行时，可以通过调用getXxx(index)或者getXxx(columnName)获取每一列的值，ResultSet当然也需要进行关闭。

• boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，

  • 如果是，则返回false，如果不是则返回true

• getXxx(参数):获取数据

  • Xxx：代表数据类型 如： int getInt() , String getString()
  • 参数：
    • int：代表列的编号,从1开始 如： getString(1)
    • String：代表列名称。 如： getDouble(“balance”)

• 使用步骤：

  1. 游标向下移动一行
  2. 判断是否有数据
  3. 获取数据

  public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try {
          //1. 注册驱动
          //2.获取连接对象
          //3.定义sql
          String sql  = "select * from account";
          //4.获取执行sql对象
          //5.执行sql
          rs = stmt.executeQuery(sql);
          //6.处理结果
          //6.1 让游标向下移动一行
          while(rs.next()){
              //6.2 获取数据
              int id = rs.getInt(1);
              String name = rs.getString("name");
              double balance = rs.getDouble(3);
              System.out.println(id + "---" + name + "---" + balance);
          }
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      } catch (SQLException e) {
          e.printStackTrace();
      } finally {
          //7.释放资源
          if(rs != null ||stmt != null||conn != null){
              try {
                  rs.close();
                  stmt.close();
                  conn.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
      }
  }

PreparedStatement

• 预编译的SQL,执行sql的对象,解决sql注入问题。

  • PreparedStatement接口继承Statement，并与之在两方面有所不同：
  • PreparedStatement 对象已预编译过，所以其执行速度要快于 Statement 对象

• SQL注入问题：在拼接sql时，有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题

• 输入用户随便，输入密码：a' or 'a' = 'a

  • select * from user where username = 'username' and password = 'a' or 'a' = 'a'

• sql的参数使用？作为占位符。 如：select * from user where username = ? and password = ?;

• sql语句的对象 PreparedStatement Connection.prepareStatement(String sql)

• 参数赋值:方法： setXxx(参数1,参数2)

  • 参数1：？的位置编号 从1 开始
  • 参数2：？的值

JDBC管理事务

• 事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。

• 在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所作出的影响会持久到数据库中，或者rollback，表示回滚到事务的起点，之前做的所有操作都被撤销了。

• 使用Connection对象来管理事务

  • 执行sql之前开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
  • 提交事务：commit() * 当所有sql都执行完提交事务
  • 回滚事务：rollback() * 在catch中回滚事务

  public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement pstmt1 = null;
      PreparedStatement pstmt2 = null;
      try {
          //1.获取连接
          conn = JDBCUtils.getConnection();
          //开启事务
          conn.setAutoCommit(false);
          //2.定义sql
          //2.1 张三 - 500
          String sql1 = "update account set balance = balance - ? where id = ?";
          //2.2 李四 + 500
          String sql2 = "update account set balance = balance + ? where id = ?";
          //3.获取执行sql对象
          pstmt1 = conn.prepareStatement(sql1);
          pstmt2 = conn.prepareStatement(sql2);
          //4. 设置参数
          pstmt1.setDouble(1,500);
          pstmt1.setInt(2,1);
  
          pstmt2.setDouble(1,500);
          pstmt2.setInt(2,2);
          //5.执行sql
          pstmt1.executeUpdate();
          // 手动制造异常
          int i = 3/0;
          pstmt2.executeUpdate();
          //提交事务
          conn.commit();
      } catch (Exception e) {
          //事务回滚
          try {
              if(conn != null) {
                  conn.rollback();
              }
          } catch (SQLException e1) {
              e1.printStackTrace();
          }
          e.printStackTrace();
      }finally {
          JDBCUtils.close(pstmt1,conn);
          JDBCUtils.close(pstmt2,null);
      }
  }

SpringBoot集成JDBC

• SQL脚本

  CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `create_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

• 编写对应的实体类

  /**
   * 实体类
   */
  public class User {
      private int id;
      private String name;
      private Date createTime;
      public int getId() {
          return id;
      }
      public void setId(int id) {
          this.id = id;
      }
      public String getName() {
          return name;
      }
      public void setName(String name) {
          this.name = name;
      }
      public Date getCreateTime() {
          return createTime;
      }
      public void setCreateTime(Date createTime) {
          this.createTime = createTime;
      }
      @Override
      public String toString() {
          return "RoncooUser [id=" + id + ", name=" + name + ", createTime=" + createTime + "]";
      }
  }

添加依赖

• 选择依赖：Web、jdbc API、Mysql Driver。

  <dependencies>
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <scope>runtime</scope>
      </dependency>
      <!-- 嵌入式数据库支持,用于开发和测试环境，-->
      <dependency>
          <groupId>org.hsqldb</groupId>
          <artifactId>hsqldb</artifactId>
          <scope>runtime</scope>
      </dependency>
  </dependencies>

配置文件

• 编写yaml配置文件连接数据库：

  spring:
    datasource:
      username: root
      password: baxiang
      url: jdbc:mysql://192.168.61.128:3306/springbootStudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
      #5.7版本使用com.mysql.jdbc.Driver
      driver-class-name: com.mysql.cj.jdbc.Driver
      schema:
        - classpath:department.sql
  
  logging:
    level:
      # 打印sql 语句的包:日志等级
      top.fulsun.mapper: debug

操作数据

• 使用原生JDBC语句或JDBCTemplate

• JDBCTemplate是对原生jdbc的轻量级封装，封装了jdbc对数据库的所有操作。且springboot配置了jdbcTemplate，注入使用即可。

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

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

dao 接口

/**
 * 用户 dao
 */
public interface UserDao {
    int insert(User user);
    int deleteById(int id);
    int updateById(User user);
    User selectById(int id);
}

实现类

public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int insert(User user) {
        String sql = "insert into user (name, create_time) values (?, ?)";
        return jdbcTemplate.update(sql, user.getName(), user.getCreateTime());
    }
    @Override
    public int deleteById(int id) {
        String sql = "delete from user where id=?";
        return jdbcTemplate.update(sql, id);
    }
    @Override
    public int updateById(User user) {
        String sql = "update user set name=?, create_time=? where id=?";
        return jdbcTemplate.update(sql, user.getName(), user.getCreateTime(), user.getId());
    }
    @Override
    public User selectById(int id) {
        String sql = "select * from user where id=?";
        return jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setCreateTime(rs.getDate("create_time"));
                return user;
            }
        }, id);
    }
}

测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDemo131ApplicationTests {
    @Autowired
    private RoncooUserDao roncooUserDao;
    //DI注入数据源
    @Autowired
    DataSource dataSource;

    // 测试连接是否成功
    @Test
    void contextLoads() throws SQLException {
        //观察默认数据源
        System.out.println(dataSource.getClass());
        //获取连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //关闭连接
        connection.close();
    }

    @Test
    public void insert() {
        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setName("测试");
        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.insert(roncooUser);
        System.out.println(result);
    }
    @Test
    public void delete() {
        int result = roncooUserDao.deleteById(1);
        System.out.println(result);
    }
    @Test
    public void update() {
        RoncooUser roncooUser = new RoncooUser();
        roncooUser.setId(2);
        roncooUser.setName("测试2");
        roncooUser.setCreateTime(new Date());
        int result = roncooUserDao.updateById(roncooUser);
        System.out.println(result);
    }
    @Test
    public void select() {
        RoncooUser result = roncooUserDao.selectById(2);
        System.out.println(result);
    }

    @Test
    public void select2() {
        RoncooUser result = roncooUserDao.selectById(7);
        System.out.println(result);
    }

    // 分页测试
    @Test
    public void queryForPage(){
        Page<RoncooUser> result = roncooUserDao.queryForPage(1, 20, "测试");
        System.out.println(result.getList());
    }
}

JDBC连接池

什么是数据源

• 可以看到，在java.sql中并没有数据源（Data Source）的概念。这是由于在java.sql中包含的是JDBC内核API，另外还有个javax.sql包，其中包含了JDBC标准的扩展API。而关于数据源（Data Source）的定义，就在javax.sql这个扩展包中。

• 实际上，在JDBC内核API的实现下，就已经可以实现对数据库的访问了，那么我们为什么还需要数据源呢？主要出于以下几个目的：

  1. 封装关于数据库访问的各种参数，实现统一管理
  2. 通过对数据库的连接池管理，节省开销并提高效率

• 在Java这个自由开放的生态中，已经有非常多优秀的开源数据源可以供大家选择，比如：DBCP、C3P0、Druid、HikariCP等。

• 而在Spring Boot 2.x中，对数据源的选择也紧跟潮流，采用了目前性能最佳的HikariCP。

HikariCP

• 上面默认输出的数据源为com.zaxxer.hikari.HikariDataSource。

• https://github.com/brettwooldridge/HikariCP

• 由于Spring Boot的自动化配置机制，大部分对于数据源的配置都可以通过配置参数的方式去改变。只有一些特殊情况，比如：更换默认数据源，多数据源共存等情况才需要去修改覆盖初始化的Bean内容。

• 在Spring Boot自动化配置中，对于数据源的配置可以分为两类：

  • 通用配置：以spring.datasource.*的形式存在，主要是对一些即使使用不同数据源也都需要配置的一些常规内容。比如：数据库链接地址、用户名、密码等。这里就不做过多说明了，通常就这些配置：

    spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username=root
    spring.datasource.password=123456
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver

  • 数据源连接池配置：以spring.datasource.<数据源名称>.*的形式存在，比如：Hikari的配置参数就是spring.datasource.hikari.*形式。下面这个是我们最常用的几个配置项及对应说明：

    #最小空闲连接，默认值10，小于0或大于maximum-pool-size，都会重置为maximum-pool-size
    spring.datasource.hikari.minimum-idle=10
    #最大连接数，小于等于0会被重置为默认值10；大于零小于1会被重置为minimum-idle的值
    spring.datasource.hikari.maximum-pool-size=20
    #空闲连接超时时间，默认值600000（10分钟），大于等于max-lifetime且max-lifetime>0，会被重置为0；不等于0且小于10秒，会被重置为10秒。
    spring.datasource.hikari.idle-timeout=500000
    #连接最大存活时间，不等于0且小于30秒，会被重置为默认值30分钟.设置应该比mysql设置的超时时间短
    spring.datasource.hikari.max-lifetime=540000
    #连接超时时间：毫秒，小于250毫秒，否则被重置为默认值30秒
    spring.datasource.hikari.connection-timeout=60000
    #用于测试连接是否可用的查询语句
    spring.datasource.hikari.connection-test-query=SELECT 1

安装

• 添加 spring-boot-starter-data-jdbc 或 spring-boot-starter-data-jpa 依赖即可

  <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jdbc -->
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
      <version>${parent.version}</version>
  </dependency>
  
  <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
      <version>${parent.version}</version>
  </dependency>

配置

基本配置	属性
dataSourceClassName	这是DataSourceJDBC驱动程序提供的类的名称。请查阅您的特定JDBC驱动程序的文档以获取此类名称，或参阅下表。注XA数据源不受支持。XA需要像bitronix这样的真正的事务管理器 。请注意，如果您正在使用jdbcUrl“旧式”基于DriverManager的JDBC驱动程序配置，则不需要此属性 。 默认值：无
jdbcUrl	该属性指示HikariCP使用“基于DriverManager的”配置。我们认为基于DataSource的配置（上图）由于各种原因（参见下文）是优越的，但对于许多部署来说，几乎没有显着差异。 在“旧”驱动程序中使用此属性时，您可能还需要设置该 driverClassName属性，但不要先尝试。 请注意，如果使用此属性，您仍然可以使用DataSource属性来配置您的驱动程序，实际上建议您使用URL本身中指定的驱动程序参数。 默认值：无
username	此属性设置从基础驱动程序获取连接时使用的默认身份验证用户名。请注意，对于DataSources，这通过调用DataSource.getConnection(username, password)基础DataSource 以非常确定的方式工作。但是，对于基于驱动程序的配置，每个驱动程序都不同。在基于驱动程序的情况下，HikariCP将使用此username属性来设置传递给驱动程序调用的user属性。如果这不是你所需要的，例如完全跳过这个方法并且调用。 默认值：无PropertiesDriverManager.getConnection(jdbcUrl, props)addDataSourceProperty(“username”, …)
password	此属性设置从基础驱动程序获取连接时使用的默认身份验证密码。请注意，对于DataSources，这通过调用DataSource.getConnection(username, password)基础DataSource 以非常确定的方式工作。但是，对于基于驱动程序的配置，每个驱动程序都不同。在基于驱动程序的情况下，HikariCP将使用此password属性来设置传递给驱动程序调用的password属性。如果这不是你所需要的，例如完全跳过这个方法并且调用。 默认值：无

常用配置	属性
autoCommit	此属性控制从池返回的连接的默认自动提交行为。它是一个布尔值。 默认值：true
connectionTimeout	此属性控制客户端（即您）将等待来自池的连接的最大毫秒数。如果在没有可用连接的情况下超过此时间，则会抛出SQLException。最低可接受的连接超时时间为250 ms。 默认值：30000（30秒）
idleTimeout	此属性控制允许连接在池中闲置的最长时间。 此设置仅适用于minimumIdle定义为小于maximumPoolSize。一旦池达到连接，空闲连接将不会退出minimumIdle。连接是否因闲置而退出，最大变化量为+30秒，平均变化量为+15秒。在超时之前，连接永远不会退出。值为0意味着空闲连接永远不会从池中删除。允许的最小值是10000ms（10秒）。 默认值：600000（10分钟）
maxLifetime	此属性控制池中连接的最大生存期。正在使用的连接永远不会退休，只有在关闭后才会被删除。在逐个连接的基础上，应用较小的负面衰减来避免池中的大量消失。 我们强烈建议设置此值，并且应该比任何数据库或基础设施规定的连接时间限制短几秒。 值为0表示没有最大寿命（无限寿命），当然是idleTimeout设定的主题。 默认值：1800000（30分钟）
connectionTestQuery	如果您的驱动程序支持JDBC4，我们强烈建议您不要设置此属性。这是针对不支持JDBC4的“传统”驱动程序Connection.isValid() API。这是在连接从池中获得连接以确认与数据库的连接仍然存在之前将要执行的查询。再一次，尝试运行没有此属性的池，如果您的驱动程序不符合JDBC4的要求，HikariCP将记录一个错误以告知您。 默认值：无
minimumIdle	该属性控制HikariCP尝试在池中维护的最小空闲连接数。如果空闲连接低于此值并且连接池中的总连接数少于此值maximumPoolSize，则HikariCP将尽最大努力快速高效地添加其他连接。但是，为了获得最佳性能和响应尖峰需求，我们建议不要设置此值，而是允许HikariCP充当固定大小的连接池。 默认值：与maximumPoolSize相同
maximumPoolSize	此属性控制池允许达到的最大大小，包括空闲和正在使用的连接。基本上这个值将决定到数据库后端的最大实际连接数。对此的合理价值最好由您的执行环境决定。当池达到此大小并且没有空闲连接可用时，对getConnection（）的调用将connectionTimeout在超时前阻塞达几毫秒。请阅读关于游泳池尺寸。 默认值：10
metricRegistry	该属性仅通过编程配置或IoC容器可用。该属性允许您指定池使用的Codahale / Dropwizard 实例MetricRegistry来记录各种指标。有关 详细信息，请参阅Metrics维基页面。 默认值：无
healthCheckRegistry	该属性仅通过编程配置或IoC容器可用。该属性允许您指定池使用的Codahale / Dropwizard 的实例HealthCheckRegistry来报告当前的健康信息。有关 详细信息，请参阅健康检查 wiki页面。 默认值：无
poolName	此属性表示连接池的用户定义名称，主要出现在日志记录和JMX管理控制台中以识别池和池配置。 默认：自动生成

不常用配置	属性
initializationFailTimeout	如果池无法成功初始化连接，则此属性控制池是否将“快速失败”。任何正数都取为尝试获取初始连接的毫秒数; 应用程序线程将在此期间被阻止。如果在超时发生之前无法获取连接，则会引发异常。此超时被应用后的connectionTimeout 期。如果值为零（0），HikariCP将尝试获取并验证连接。如果获得连接但未通过验证，将抛出异常并且池未启动。但是，如果无法获得连接，则会启动该池，但后续获取连接的操作可能会失败。小于零的值将绕过任何初始连接尝试，并且在尝试获取后台连接时，池将立即启动。因此，以后努力获得连接可能会失败。 默认值：1
isolateInternalQueries	此属性确定HikariCP是否在其自己的事务中隔离内部池查询，例如连接活动测试。由于这些通常是只读查询，因此很少有必要将它们封装在自己的事务中。该属性仅适用于autoCommit禁用的情况。 默认值：false
allowPoolSuspension	该属性控制池是否可以通过JMX暂停和恢复。这对于某些故障转移自动化方案很有用。当池被暂停时，呼叫 getConnection()将不会超时，并将一直保持到池恢复为止。 默认值：false
readOnly	此属性控制默认情况下从池中获取的连接是否处于只读模式。注意某些数据库不支持只读模式的概念，而其他数据库则在Connection设置为只读时提供查询优化。无论您是否需要此属性，都将主要取决于您的应用程序和数据库。 默认值：false
registerMbeans	该属性控制是否注册JMX管理Bean（“MBeans”）。 默认值：false
catalog	该属性设置默认目录为支持目录的概念数据库。如果未指定此属性，则使用由JDBC驱动程序定义的默认目录。 默认：驱动程序默认
connectionInitSql	该属性设置一个SQL语句，在将每个新连接创建后，将其添加到池中之前执行该语句。如果这个SQL无效或引发异常，它将被视为连接失败并且将遵循标准重试逻辑。 默认值：无
driverClassName	HikariCP将尝试通过DriverManager仅基于驱动程序来解析驱动程序jdbcUrl，但对于一些较旧的驱动程序，driverClassName还必须指定它。除非您收到明显的错误消息，指出找不到驱动程序，否则请忽略此属性。 默认值：无
transactionIsolation	此属性控制从池返回的连接的默认事务隔离级别。如果未指定此属性，则使用由JDBC驱动程序定义的默认事务隔离级别。如果您有针对所有查询通用的特定隔离要求，请仅使用此属性。此属性的值是从不断的名称Connection 类，如TRANSACTION_READ_COMMITTED，TRANSACTION_REPEATABLE_READ等 默认值：驱动程序默认
validationTimeout	此属性控制连接测试活动的最长时间。这个值必须小于connectionTimeout。最低可接受的验证超时时间为250 ms。 默认值：5000
leakDetectionThreshold	此属性控制在记录消息之前连接可能离开池的时间量，表明可能存在连接泄漏。值为0意味着泄漏检测被禁用。启用泄漏检测的最低可接受值为2000（2秒）。 默认值：0
dataSource	此属性仅通过编程配置或IoC容器可用。这个属性允许你直接设置DataSource池的实例，而不是让HikariCP通过反射来构造它。这在一些依赖注入框架中可能很有用。当指定此属性时，dataSourceClassName属性和所有DataSource特定的属性将被忽略。 默认值：无
schema	该属性设置的默认模式为支持模式的概念数据库。如果未指定此属性，则使用由JDBC驱动程序定义的默认模式。 默认：驱动程序默认
threadFactory	此属性仅通过编程配置或IoC容器可用。该属性允许您设置java.util.concurrent.ThreadFactory将用于创建池使用的所有线程的实例。在一些只能通过ThreadFactory应用程序容器提供的线程创建线程的有限执行环境中需要它。 默认值：无
scheduledExecutor	此属性仅通过编程配置或IoC容器可用。该属性允许您设置java.util.concurrent.ScheduledExecutorService将用于各种内部计划任务的实例。如果为ScheduledThreadPoolExecutor 实例提供HikariCP，建议setRemoveOnCancelPolicy(true)使用它。 默认值：无
dataSourceJNDI
dataSourceProperties
healthCheckProperties
metricsTrackerFactory

Druid

• https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

安装

<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>

配置

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 其他参数 serverTimezone=UTC&useUnicode=true
spring.datasource.url=jdbc:mysql://IipAddr/databasename?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=baxiang
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

#连接池的设置
#初始化时建立物理连接的个数
spring.datasource.druid.initial-size=5
#最小连接池数量
spring.datasource.druid.min-idle=5
#最大连接池数量 maxIdle已经不再使用
spring.datasource.druid.max-active=20
#获取连接时最大等待时间，单位毫秒
spring.datasource.druid.max-wait=60000
#申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
spring.datasource.druid.test-while-idle=true
#既作为检测的间隔时间又作为testWhileIdel执行的依据
spring.datasource.druid.time-between-eviction-runs-millis=60000
#销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接
spring.datasource.druid.min-evictable-idle-time-millis=30000
#用来检测连接是否有效的sql 必须是一个查询语句
#mysql中为 select 'x'
#oracle中为 select 1 from dual
spring.datasource.druid.validation-query=select 'x'
#申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-borrow=false
#归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
spring.datasource.druid.test-on-return=false
# 是否缓存preparedStatement
spring.datasource.druid.pool-prepared-statements: true
#合并多个DruidDataSource的监控数据
spring.datasource.druid.use-global-data-source-stat=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.druid.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456

配置多数据源

• DataSource: 下边就有了 @ConfigurationProperties(prefix = "hikari.primary"),用来指定你数据库的相关信息，porefix是指定你当前DataSource读取的数据源配置的位置的，生成DataSource对象，放入容器。
• SqlSessionFactory: 生产SqlSession的，Factory的意思是“工厂”。
• DataSourceTransactionManager: 从名称来看，也知道跟事务相关了。创建它的基本元素也是数据库的基本配置，所以直接引入DataSource对象就ok了。
• @MapperScan（指定该数据源对应的Mapper接口的位置，以及声明指定你创建成功的SqlSessionTemplate）和@Configuration（声明为配置类）这些注解。
• 在配置多数据源就是多写这么两个配置类，分别指定每个数据源对应的模板，Transaction，sqlSession，数据源的相关基本信息（url，name，password，最大连接数。。。）就ok了。

HikariCP

1. application.yml 配置如下

   hikari:
     primary:
       jdbc-url: jdbc:mysql://localhost:3306/database1?useSSL=false
       username: 123456
       password: 1234
     second:
       jdbc-url: jdbc:mysql://localhost:3306/database2?useSSL=false
       username: root
       password: 123456

2. PrimaryDatasourceConfig:

   @Configuration
   @MapperScan(basePackages = PrimaryDatasourceConfig.PACKAGE,sqlSessionFactoryRef = "primarySqlSessionFactory")
   public class PrimaryDatasourceConfig {
   
       static final String PACKAGE = "com.xu.scaffold.repository.primary";
   
       @Bean(name = "primaryDataSource")
       @Primary
       @ConfigurationProperties(prefix = "hikari.primary")
       public HikariDataSource dataSource() {
           return new HikariDataSource();
       }
   
       @Bean(name = "primaryTransactionManager")
       @Primary
       public DataSourceTransactionManager transactionManager() {
           return new DataSourceTransactionManager(this.dataSource());
       }
   
       @Bean(name = "primarySqlSessionFactory")
       @Primary
       public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
           final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
           sessionFactory.setDataSource(dataSource);
           sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
           return sessionFactory.getObject();
       }
   }

3. SecondDatasourceConfig:

   @Configuration
   @MapperScan(basePackages = SecondDatasourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
   public class SecondDatasourceConfig {
   
       static final String PACKAGE = "com.xu.scaffold.repository.second";
   
       @Bean(name = "secondDataSource")
       @ConfigurationProperties(prefix = "hikari.second")
       public HikariDataSource dataSource() {
           return new HikariDataSource();
       }
   
       @Bean(name = "secondTransactionManager")
       public DataSourceTransactionManager transactionManager() {
           return new DataSourceTransactionManager(this.dataSource());
       }
   
       @Bean(name = "secondSqlSessionFactory")
       public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
           final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
           sessionFactory.setDataSource(dataSource);
           sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
           return sessionFactory.getObject();
       }
   }

注解说明

• @Primary: 提升注入对象的权重，对象交托给Spring容器时使用。

  • 例子：两个类（A,B）都实现了一个接口（C），而A类对象放入容器时有@Primary修饰，那么注入C接口的对象实则注入的是A类的对象，而不是B类。
  • 当然如果你不加@Primar注解，那么你在注入C接口的对象时Spring不知道该给你注入哪个对象就会报错。

• @Qualifier: 确定取出哪个对象，从容器中取出对象注入时使用。

  • 例子：两个类（A,B）都实现了一个接口（C）注入容器时分别@Bean(name = “ABean或BBean”)，那么容器中C接口的可注入对象有两个，这两个Bean的名字分别是ABean和BBean
  • 那么注入时Spring是不知道该注入哪个的（先不说@Autowired），那么此时我们可以通过@Qualfier（”ABean）注解告诉Spring，将ABean的对象注入C接口来，也就类似于一成限制吧。

使用Spring Data JPA

• 在实际开发过程中，对数据库的操作大多可以归结为：“增删改查”。就最为普遍的单表操作而言，除了表和字段不同外，语句几乎都是类似的，开发人员需要写大量类似而枯燥的语句来完成业务逻辑。

• Spring Data JPA的出现正可以让这样一个已经很“薄”的DAO数据访问层变成只是一层接口的编写方式。比如，下面的例子：

  public interface UserRepository extends JpaRepository<User, Long> {
  
      User findByName(String name);
  
      @Query("from User u where u.name=:name")
      User findUser(@Param("name") String name);
  
  }

• 我们只需要通过编写一个继承自JpaRepository的接口就能完成数据访问，下面以一个具体实例来体验Spring Data JPA给我们带来的强大功能。

工程配置

• 由于Spring Data JPA依赖于Hibernate，在pom.xml中添加相关依赖，加入以下内容：

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

• 在application.xml中配置：数据库连接信息（如使用嵌入式数据库则不需要）、自动创建表结构的设置，例如使用mysql的情况如下：

  # spring.datasource.url=jdbc:mysql://localhost:3306/test
  spring.datasource.url=jdbc:hsqldb:mem://localhost/testdb;shutdown=true
  spring.datasource.username=sa
  spring.datasource.password=
  # spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.datasource.driver-class-name=org.hsqldb.jdbcDriver
  
  spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop

• spring.jpa.properties.hibernate.hbm2ddl.auto是hibernate的配置属性，其主要作用是：自动创建、更新、验证数据库表结构。该参数的几种配置如下：

• create：每次加载hibernate时都会删除上一次的生成的表，然后根据你的model类再重新来生成新表，哪怕两次没有任何改变也要这样执行，这就是导致数据库表数据丢失的一个重要原因。

• create-drop：每次加载hibernate时根据model类生成表，但是sessionFactory一关闭,表就自动删除。

• update：最常用的属性，第一次加载hibernate时根据model类会自动建立起表的结构（前提是先建立好数据库），以后加载hibernate时根据model类自动更新表结构，即使表结构改变了但表中的行仍然存在不会删除以前的行。要注意的是当部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会。

• validate：每次加载hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值。

• 至此已经完成基础配置。

创建实体

• 创建一个User实体，包含id（主键）、name（姓名）、age（年龄）属性，通过ORM框架其会被映射到数据库表中，由于配置了hibernate.hbm2ddl.auto，在应用启动的时候框架会自动去数据库中创建对应的表。

  import javax.persistence.Entity;
  import javax.persistence.GeneratedValue;
  import javax.persistence.Id;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  
  /**
   * @author fulsun
   * @description: 用户实体类
   * @date 6/11/2021 2:25 PM
   */
  @Entity
  @Data
  @NoArgsConstructor
  public class User {
  
    @Id @GeneratedValue private Long id;
  
    private String name;
    private Integer age;
  
    public User(String name, Integer age) {
      this.name = name;
      this.age = age;
    }
  }

• @Entity注解标识了User类是一个持久化的实体

• @Data和@NoArgsConstructor是Lombok中的注解。用来自动生成各参数的Set、Get函数以及不带参数的构造函数。

• @Id和@GeneratedValue用来标识User对应对应数据库表中的主键

• 注意：除了这些注解之外，还有很多用来精细化配置映射关系的注解，这里不做具体介绍。

创建数据访问接口

• 下面针对User实体创建对应的Repository接口实现对该实体的数据访问，如下代码：

  import org.springframework.data.jpa.repository.JpaRepository;
  import org.springframework.data.jpa.repository.Query;
  import org.springframework.data.repository.query.Param;
  import tk.fulsun.demo.entity.User;
  
  /**
   * @author fulsun
   * @description: 数据访问接口
   * @date 6/11/2021 2:28 PM
   */
  public interface UserRepository extends JpaRepository<User, Long> {
    User findByName(String name);
  
    User findByNameAndAge(String name, Integer age);
  
    @Query("from User u where u.name=:name")
    User findUser(@Param("name") String name);
  }

• 在Spring Data JPA中，只需要编写类似上面这样的接口就可实现数据访问。不再像我们以往编写了接口时候还需要自己编写接口实现类，直接减少了我们的文件清单。

UserRepository解释

• 下面对上面的UserRepository做一些解释，该接口继承自JpaRepository，通过查看JpaRepository接口的API文档，可以看到该接口本身已经实现了创建（save）、更新（save）、删除（delete）、查询（findAll、findOne）等基本操作的函数，因此对于这些基础操作的数据访问就不需要开发者再自己定义。

• 在我们实际开发中，JpaRepository接口定义的接口往往还不够或者性能不够优化，我们需要进一步实现更复杂一些的查询或操作。

• 在上例中，我们可以看到下面两个函数：

  • User findByName(String name)
  • User findByNameAndAge(String name, Integer age)
  • 它们分别实现了按name查询User实体和按name和age查询User实体，可以看到我们这里没有任何类SQL语句就完成了两个条件查询方法。这就是Spring-data-jpa的一大特性：通过解析方法名创建查询。

• 除了通过解析方法名来创建查询外，它也提供通过使用@Query 注解来创建查询，您只需要编写JPQL语句，并通过类似“:name”来映射@Param指定的参数，就像例子中的第三个findUser函数一样。

• Spring Data JPA的能力远不止本文提到的这些，由于本文主要以整合介绍为主，对于Spring Data JPA的使用只是介绍了常见的使用方式。诸如@Modifying操作、分页排序、原生SQL支持以及与Spring MVC的结合使用等等内容就不在本文中详细展开，后续再补文章填坑。

单元测试

• 在完成了上面的数据访问接口之后，按照惯例就是编写对应的单元测试来验证编写的内容是否正确。这里就不多做介绍，主要通过数据操作和查询来反复验证操作的正确性。

  package tk.fulsun.demo;
  
  import org.junit.Assert;
  import org.junit.jupiter.api.Test;
  import org.junit.runner.RunWith;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  import tk.fulsun.demo.dao.UserRepository;
  import tk.fulsun.demo.entity.User;
  
  /**
   * @author fulsun
   * @description: 主测试类
   * @date 6/11/2021 2:32 PM
   */
  @RunWith(SpringRunner.class)
  @SpringBootTest
  public class ApplicationTest {
  
    @Autowired private UserRepository userRepository;
  
    @Test
    public void test() throws Exception {
  
      // 创建10条记录
      userRepository.save(new User("AAA", 10));
      userRepository.save(new User("BBB", 20));
      userRepository.save(new User("CCC", 30));
      userRepository.save(new User("DDD", 40));
      userRepository.save(new User("EEE", 50));
      userRepository.save(new User("FFF", 60));
      userRepository.save(new User("GGG", 70));
      userRepository.save(new User("HHH", 80));
      userRepository.save(new User("III", 90));
      userRepository.save(new User("JJJ", 100));
  
      // 测试findAll, 查询所有记录
      Assert.assertEquals(10, userRepository.findAll().size());
  
      // 测试findByName, 查询姓名为FFF的User
      Assert.assertEquals(60, userRepository.findByName("FFF").getAge().longValue());
  
      // 测试findUser, 查询姓名为FFF的User
      Assert.assertEquals(60, userRepository.findUser("FFF").getAge().longValue());
  
      // 测试findByNameAndAge, 查询姓名为FFF并且年龄为60的User
      Assert.assertEquals("FFF", userRepository.findByNameAndAge("FFF", 60).getName());
  
      // 测试删除姓名为AAA的User
      userRepository.delete(userRepository.findByName("AAA"));
  
      // 测试findAll, 查询所有记录, 验证上面的删除是否成功
      Assert.assertEquals(9, userRepository.findAll().size());
    }
  }