数据库事务处理与资源池

2020-02-08
Java

数据库事务处理与资源池

2020-02-08

[TOC]

0x00 数据库事务

什么是事务？

答:事务（Transaction）是指包含多个微小逻辑单元的一组操作，只要其中有一个逻辑失败了，那么这一组操作就全部以失败告终所有的数据都回归到最初的状态(回滚),不存在一半成功，一半不成功的状况。

为什么要有事务?

答:为了确保逻辑的成功。
事务在平常的CRUD当中也许不太常用,但是如果我们有一种需求要求一组操作中必须全部成功执行，才算完成任务，只要有一个出错了，那么所有的任务都将回到最初的状况恢复原样,这就是使用事务的应用场景如:银行的转账例子;

数据库中操作事务流程与命令:

-- 查看autocommit自动提交是否关闭
> SHOW VARIABLES LIKE 'autocommit';  
"autocommit"	"ON"

-- 当前终端临时关闭自动提交
> SET autocommit = off;

-- 开始事务处理
> START TRANSACTION;

-- 提交或者回滚事务
commit;     --- 提交事务， 数据将会写到磁盘上的数据库
rollback ;  --- 数据回滚，回到最初的状态。

测试SQL语句:

CREATE TABLE account(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(64) NOT NULL,
    `money` FLOAT DEFAULT 0
);

INSERT INTO account VALUES (null,'WeiyiGeek',1000),(null,'muzi',1000);

1.JDBC中事务处理

描述:事务只是针对连接连接对象，如果再开一个连接对象，那么那是默认的提交（注意: 事务是会自动提交的）。

通过设置关闭自动提交(事务只是针对于连接) conn.setAutoCommit(false)
提交事务 conn.commit();
回滚事务 conn.rollback();

JDBC采用JAVA代码方式演示事务:

package top.weiyigeek.Web;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import top.weiyigeek.Util.db;

public class Test_transaction {
  @Test
  public void transaction() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      conn = db.getConn();
      
      //1.连接:事务默认就是自动提交的。 关闭自动提交。
      conn.setAutoCommit(false);
      
      String sql = "update account set money = money - ? where id = ?";
      ps = conn.prepareStatement(sql);
      
      //2.扣钱:扣ID为1 的100块钱
      ps.setInt(1, 100);
      ps.setInt(2, 1);
      ps.executeUpdate();
      
      //3.设置的异常，查看事务提交的影响
      int a = 10 /0 ;
      
      //4.加钱， 给ID为2 加100块钱
      ps.setInt(1, -100);
      ps.setInt(2, 2);
      ps.executeUpdate();
      
      //5.成功： 提交事务。
      conn.commit();
      
    } catch (SQLException e) {
      try {
        //6.在3步骤会产生异常则进入catch{} 将回滚事务
        conn.rollback();
        System.out.println("\nSQL 事务提交异常，已自动回滚事务!");
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
      e.printStackTrace();
      
    }finally {
      db.release(conn, ps, rs);
    }
  }
}

2.事务的特性

描述:事务有四个特性ACID包括:

原子性(Atomicity 英 /ˌætəˈmɪsəti/):事务中的逻辑要全部执行，不可分割。（原子是物理中最小单位）

指的是事务中包含的逻辑，不可分割。
一致性(Consistency 英 /kənˈsɪstənsi/ ):指事务执行前和执行后，数据的完整性保持一致

指的是事务执行前后。数据完整性
隔离性(Isolation 英 /ˌaɪsəˈleɪʃn/ ): 指一个事务在执行的过程中不应该受其他事务的影响

指的是事务在执行期间不应该受到其他事务的影响
持久性(Durability 英 /ˌdjʊərəˈbɪləti/ ):事务执行结束（提交或回滚），数据都应持久化到数据中

指的是事务执行成功，那么数据应该持久保存到磁盘上。

3.事务安全隐患

描述:在不考虑隔离级别设置时候，那么会出现以下问题。

1) 读时问题
- 脏读
- 不可重复读
- 幻读
2) 写时问题(丢失更新)
- 悲观锁
- 乐观锁

(1) 读时问题

脏读:指一个事务读到了另一个事务 还未提交的数据
不可重复读:指一个事务读到了另一个事务 提交的数据 导致多次查询结果不一致。
幻读:指一个事务读到了另一个事务 已提交的插入的数据(INSERT) 导致多次查询结果不一致。

(2) 写时问题
丢失更新:指一个事务去修改数据库同时另一个事务也修改数据库，最后的那个事务，不管是提交还是回滚都会造成前面一个事务的数据更新丢失;

解决丢失更新，通常有两种方法: 悲观锁和乐观锁

悲观锁:指事务在一开始就认为丢失更新一定会发生这是一件很悲观的事情。具体操作步骤如下：
- 1.所有事务在执行操作前，先查询一次数据，查询语句如下:select * from student for update ; 后面的for update 其实是数据库锁机制、一种排他锁。
- 2.哪个事务先执行这个语句，哪个事务就持有了这把锁，可以查询出来数据，后面的事务再执行这条语句，不会有任何数据显示，就只能等着。
- 3.一直等到前面的那个事务提交数据后，后面的事务数据才会出来，那么才可以往下接着操作。
- 悲观锁的机制：有点类似于去卫生间时候，如果谁先来谁就可以进去蹲,后面来的人得等着,只有里面的人出来了才能进去 这其实就是 java 中的同步的概念。

乐观锁:指从来不会觉得丢失更新会发生。
那么它的具体做法是什么呢?
要求程序员在数据库中添加字段然后在后续更新的时候，对该字段进行判定比对如果一致才允许更新。
- 1.数据库表中额外添加了一个version字段用于记录版本，默认从0 开始只要有针对表中数据进行修改的，那么version就+1.
- 2.开启A事务然后开启B事务。
- 3.A 先执行数据库表操作。因为以前都没有人修改过所以是允许A事务修改数据库的，但是修改完毕，就把version的值变成 1了。
- 4.B 事务这时候如果想执行修改，那么是不允许修改的。因为B事务以前是没有查询过数据库内容的，所以它认为数据库版本还是0; 但是数据库的版本经过A修改，已经是1了;所以这时候不允许修改,要求其重新查询。
- 5.B 重新查询后，将会得到version 为 1的数据，这份数据就是之前A 事务修改的数据了， B 在进行修改，也是在A的基础上修改的。所以就不会有丢失更新的情况出现了。
- 乐观锁的机制:其实是通过比对版本或者比对字段的方式来实现的，它与使用到的版本控制软件【SVN , GIT】机制是一样的。

4.隔离级别

描述:根据事务的隔离性常常是以下四种隔离级别:

1.Read Uncommitted【读未提交】: 引发脏读
2.Read Committed 【读已提交】: 解决脏读,引发不可重复读
3.Repeatable Read 【重复读】: MySQL 数据卷默认是该隔离级别;解决脏读、不可重复读 ,未解决幻读
4.Serializable 【可串行化】: 解决：脏读、不可重复读、幻读但是性能有所缺失；

问:如何查询当前会话的隔离性级别以及更改当前会话的隔离性级别?

-- 查询
SQL > select @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- 1 row in set (0.00 sec)

-- 修设置当前窗口的事务隔离级别为
SQL > set session transaction isolation level read uncommitted;
-- Query OK, 0 rows affected (0.00 sec)

1) Read Uncommitted【读未提交】
描述:一个事务可以读取到另一个事务还未提交的数据；就会引发 “脏读” 读取到的是数据库内存中的数据，而并非真正磁盘上的数据。

SQL示例1:

-- 1.修改当前隔离级别为`read uncommitted`
db_window-1 > set session transaction isolation level read uncommitted;
-- Query OK, 0 rows affected (0.00 sec)

-- 2.查询是否修改成功
db_window-1 > select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

-- 3.终端2的隔离级别默认就是可重复读
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > update account set money = money + 100 where id = 1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 4.开启事务
db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


db_window-2 > update account set money = money - 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 5.终端1读取了终端2还未提交的数据
db_window-1 > select * from account;
| id | name      | money |
|  1 | WeiyiGeek |  1100 |
|  2 | muzi      |  900 |


-- 6.提交事务（实际终端1已经从内存中读取了数据）
db_window-2 > commit;

2) Read Committed 【读已提交】
描述:它与前面的读未提交刚好相反，它只能读取到其他事务已经提交的数据，那些没有提交的数据是读不出来的。
导致问题:前后读取到的结果不一样发生了不可重复!!!, 所谓的不可重复读就是不能执行多次读取，否则出现结果不一 (此时我们引出了可重复读的隔离性级别)。
简单的说:该隔离级别能够屏蔽脏读的现象，但是引发了另一个问题就是不可重复读。

-- 1.设置终端1的隔离级别
db_window-1 > set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

-- 2.终端1开启事务
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 3.终端1查询原始数据
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 4.终端2开始事务
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 5.终端2执行更操作
db_window-2 > update account set money = money - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

db_window-2 > update account set money = money + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 6.终端1执行查询操作（数据没有变化）
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 7.终端2执行commit
db_window-2 > commit;
Query OK, 0 rows affected (0.05 sec)

-- 8.提交后终端1查询数据发现已改变
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |   900 |
|  2 | muzi      |  1100 |
+----+-----------+-------+
2 rows in set (0.00 sec)

3) Repeatable Read 【重复读】
描述:MySQL 默认的隔离级别就是这个。该隔离级别可以让事务在自己的会话中重复读取数据，并且不会出现结果不一样的状况，即使其他事务已经提交了，也依然还是显示以前的数据。
可重复读（REPEATABLE READ）功能：保证在同一个事务中多次读取同样数据的结果是一样的可避免脏读、不可重复读的发生。

-- 1.终端1设置隔离性可重复读并查看当前隔离性
db_window-1 > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ | 
+-----------------+
1 row in set (0.00 sec)

-- 2.终端1显示原始数据
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 3.终端1开始事务
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 4.终端2查看当前隔离性以及开启事务
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 5.终端2执行更新操作
db_window-2 > update account set money = money - 100 where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

db_window-2 > update account set money = money + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 6.终端1执行查询语句结果任然未改变
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

-- 7.终端2执行提交数据
db_window-2 > commit;
Query OK, 0 rows affected (0.08 sec)


-- 8.终端1执行查询数据任然无变化(查询结果和以前的查询结果一致不会发生改变。)
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

4) Serializable 【可串行化】
描述:该事务级别是最高级的事务级别了，比前面几种都要强大一点也就是前面几种的问题【脏读、不可重复读、幻读】都能够解决, 但是这种隔离级别一般比较少用容易造成性能上的问题(效率比较低)。

其他的事务必须得等当前正在操作表的事务先提交，才能接着往下否则只能一直在等着阻塞者；即如果有一个连接的隔离级别设置为了串行化谁先打开了事务，谁就有了先执行的权利（先入为主）等前面的那个事务，提交或者回滚后才能执行。

SQL示例:

-- 1.终端1设置 Serializable 可串行化隔离级别
db_window-1 > set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

db_window-1 > select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

-- 2.终端2当前隔离性
db_window-2 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

-- 3.终端1查询原始数据
db_window-1 > select * from account;
| id | name      | money |
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |

-- 4.终端2先开启事务
db_window-2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 5.终端1其次开启事务
db_window-1 > start transaction;
Query OK, 0 rows affected (0.00 sec)


-- 6.终端2执行增删改查SQL(没有任何问题)
db_window-2 > insert into account values (null,'mariadb',1000);
Query OK, 1 row affected (0.00 sec)


-- 7.终端1等待终端2的事务执行完成后执行，否则会阻塞包括(CURD)
db_window-1 > update account set money = money - 100 where id = 3;
-- 等待终端2Commit提交事务 
Query OK, 1 row affected (46.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 8.终端2的事务执行完成后，上面那条update语句才可以执行
db_window-2 > commit;
Query OK, 0 rows affected (0.07 sec)

-- 9.终端1此时可执行任意的CURD语句
db_window-1 > select * from account;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | WeiyiGeek |  1000 |
|  2 | muzi      |  1000 |
|  3 | mariadb   |   900 |
+----+-----------+-------+
3 rows in set (0.00 sec)

总结:
按效率划分，从高到低

读未提交 > 读已提交 > 可重复读 > 可串行化

按拦截程度，从高到底

可串行化 > 可重复读 > 读已提交 > 读未提交

数据库默认隔离级别:

mySql 默认的隔离级别是 可重复读
Oracle 默认的隔离级别是 读已提交

0x01 数据库连接池

什么是连接池?

连接池指：创建一个池子（容器）实际是在内存中开辟一块空间（集合），往池子里面放置多个连接对象，专门用来管理连接对象；

连接池有什么作用?

1.更快响应速度:连接池里的连接在一开始就已经创建好了，后面如果需要直接拿就可以了，无需创建。
2.资源的重复利用、避免重复创建对象:连接对象使用完毕后，再归还到池子中进行统一管理即可。

自定义连接池产生的问题有哪些?

1.需要额外的addBack方法将连接对象进行归还
2.需要设置单例防止对象重复实例化;
3.无法面向接口编程由于我们采用的是MySQL/Oracle提供的JDBC的jar包,而该接口里面又没有定义addBack方法;

如何解决?

1.以addBack为切入点所以使用这个连接池的地方需要额外记住这个方法，并且还不能面向接口编程;
3.使用设计模式中的装饰则模式就可以直接采用重写的close方法,调用close方法并不是真正的关闭数据库连接对象而是归还连接对象即可(只是关闭数据库结果集);

补充Java设计模式(四种非常重要)按照从易到难依次排序:

单例模式(java基础)
工程模式(java基础)
修饰模式(java基础)
动态代理(java基础)

基础示例:

// 调用连接池使用:/User/src/top/weiyigeek/Web/Test_Pool.java
public class Test_Pool {
  @Test
  public void testPool() throws SQLException {
    Connection conn = null;
    PreparedStatement ps =null;
    //注意这里需要避免重复申请对象所以需要使用单例;
    CustomDatasource ds = CustomDatasource.getInstance();
    try {
      //获取资源池的链接对象
      conn = ds.getConnection();
      String sql = "SELECT * FROM user LIMIT 0,10";
      ps = conn.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      while(rs.next()) {
        System.out.println("ID: " + rs.getInt("id") + " , Name = " + rs.getString("name")+", 登录时间 = " + rs.getDate("uptime"));
      }
      rs.close();
    } catch (Exception e) {
      // TODO: handle exception
      e.printStackTrace();
    } finally {
      try {
        //关闭PreparedStatementd对象
        ps.close();
      } catch (Exception e2) {
        // TODO: handle exception
        e2.printStackTrace();
      }
      //归还对象
      //ds.addBack(conn); //采用装饰后便不使用此种方法
      conn.close();
    }
  }
}

修饰模式类:/User/src/top/weiyigeek/pool/ConnectionWrap.java

/**
 * @Desc:装饰设计模式数据库连接关闭归还
 * @author WeiyiGeek
 * @CreatTime 下午2:48:41
 */
public class ConnectionWrap implements Connection {
  //1.构造方法接入connection连接以及连接池集合
  Connection connection = null;
  List <Connection> list ;
  public ConnectionWrap(Connection connection , List <Connection> list) {
    super();
    this.connection = connection;
    this.list = list;
  }
  
  //2.解析预处理SQL语句
  @Override
  public PreparedStatement prepareStatement(String sql) throws SQLException {
    return connection.prepareStatement(sql);
  }
  
  //3.重写Close方法
  @Override
  public void close() throws SQLException {
    // TODO Auto-generated method stub
    //connection.close(); //还可以调用原本的close
    System.out.println("有人来归还连接对象了> 归还之前，池子里面可用连接数："+list.size());
    list.add(connection);
    System.out.println("有人来归还连接对象了> 归还之后，池子里面可用连接数："+list.size());
    }

    ......   
}

自定义连接池类：/User/src/top/weiyigeek/pool/CustomDatasource.java

import top.weiyigeek.Util.db;
public class CustomDatasource implements DataSource {
  // 数据库资源池建立的方法
  //0.创建本类对象并且对外提供公共的访问方法
  private static CustomDatasource s = new CustomDatasource();
  public static CustomDatasource getInstance() {
    return s;
  }
  // 1.建立存储连接池对象的集合
  List <Connection> list = new ArrayList<Connection>();
  
   public CustomDatasource()  {
     //2.建立连接对象存储到资源池中
     for(int i = 0; i < 10;i++) {
      Connection conn;
      try {
        conn = db.getConn();
        list.add(conn);
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
     }
  }
  
  //3.连接池对外公布的获取连接的方法 
  @Override
  public Connection getConnection() throws SQLException {
    // TODO Auto-generated method stub
    //4.判断资源池是否用完是则添加3个连接对象到资源池(当然资源池是有限制的)
     if (list.size() == 0 ) {
       try {
         for(int i = 0; i < 5; i++)
           list.add(db.getConn());
      } catch (SQLException e) {
        e.printStackTrace();
      }
     }
    //返还并移出第一个元素
    Connection conn = list.remove(0); 
    //修饰模式在把这个对象抛出去的时候， 对这个对象进行包装。(重点值得学习)
    Connection connection = new ConnectionWrap(conn, list);
    return connection;
  }

  
  //5.归还连接对象
  public void addBack(Connection conn) {
    list.add(conn);
  }

    .....
}

执行结果:

总结:

1.我们在实际的开发中一般不自己写连接池，我们要站在巨人的肩膀上，但是我们需要进行了解其运行原理;

0x02 开源连接池

1.OBCP

描述:DBCP（DataBase Connection Pool）数据库连接池，是java数据库连接池的一种，由Apache开发通过数据库连接池，可以让程序自动管理数据库连接的释放和断开;
官网下载地址:http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
当前2020306最新版本:

commons-dbcp:https://mirrors.tuna.tsinghua.edu.cn/apache//commons/dbcp/binaries/commons-dbcp2-2.7.0-bin.zip
commons-pool:https://mirrors.tuna.tsinghua.edu.cn/apache//commons/pool/binaries/commons-pool2-2.8.0-bin.zip
commons-logging:https://mirrors.tuna.tsinghua.edu.cn/apache//commons/logging/binaries/commons-logging-1.2-bin.zip (必须引入否则报错java.lang.NoClassDefFoundError: org/apache/commons/logging/LogFactory)

环境依赖:下载完成后导入jar包 commons-dbcp.jar,commons-pool.jar;

实际案例:
1.不使用配置文件方式/User/src/top/weiyigeek/DBCP/OBCPDemo1.java

package top.weiyigeek.DBCP;
/**
 * 
 * @Desc: OBCP Apache 开源数据连接管理框架的使用
 * @author WeiyiGeek
 * @CreatTime 下午12:45:22
 */
public class OBCPDemo1 {
  @SuppressWarnings("resource")
  @Test
  public void testOBCP () {
    //1.构建数据源对象
    BasicDataSource dataSource = new BasicDataSource();
    
    //2.得到连接对象
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    //3.数据库配置（手动）
    dataSource.setDriverClassName("org.mariadb.jdbc.Driver");
    //主协议 子协议 数据库IP:Port 数据库
    dataSource.setUrl("jdbc:mariadb://127.0.0.1:3306/student");
    dataSource.setUsername("root");
    dataSource.setPassword("");
    
    try {
      conn = dataSource.getConnection();
      //4.解析SQL语句
      ps = conn.prepareStatement("SELECT * FROM user ORDER BY id DESC LIMIT 0,?");
      ps.setInt(1, 10);
      rs = ps.executeQuery();
      while(rs.next()) {
        System.out.println(rs.getInt("id") + " ---- " + rs.getString("name") +  " ---- " + rs.getInt("grade"));
      }
      rs.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      //db.release(conn, ps, rs);
      //释放资源建议重写close方法
    }
  }
}

2.使用配置文件方式。
dbcp.properties:

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

/**
 * 
 * @Desc: OBCP读取配置文件来配置数据库连接
 * @author WeiyiGeek
 * @CreatTime 下午1:26:18
 */
public class OBCPDemo2 {
  
  @Test
  public void testDemo2() throws Exception {
    //1.利用Properties读取配置文件中数据
    Properties prop = new Properties();
    InputStream is = this.getClass().getClassLoader().getResourceAsStream("dbcp.properties");  //将项目中的jdbc.properties读取进输入流
    prop.load(is);

    //2.构建连接对象
    BasicDataSourceFactory factory = new BasicDataSourceFactory();
    DataSource datasource = factory.createDataSource(prop);  //引入properties文件
    
    //3.得到连接对象
    Connection conn = datasource.getConnection();
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM user LIMIT 0,10");
    ResultSet rs = ps.executeQuery();
    
    //4.打印出来从数据库中读取的数据
    while(rs.next()) {
      System.out.println(rs.getInt("id") + " ---- " + rs.getString("name") +  " ---- " + rs.getInt("grade"));
    }
    
    //5.关闭连接
    System.out.println("连接关闭");
    rs.close();
    ps.close();
    conn.close();
  }
}

执行结果:

2.C3P0（使用较多）

描述:C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。
目前使用它的开源项目有Hibernate，Spring等。

下载地址:https://sourceforge.net/projects/c3p0/

下载完成后向上面的一样导入工程之中(Build Path)，我们学习一个新的技术的时候最好能看看他的一些帮助文档;

代码实例（1）:手动配置

import top.weiyigeek.Util.db;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo1 {
  @Test
  public void Demo1() throws PropertyVetoException,Exception {
    //1.创建DataSource
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    
    //2.设置数据库连接信息
    cpds.setDriverClass("org.mariadb.jdbc.Driver");
    cpds.setJdbcUrl("jdbc:mariadb://127.0.0.1:3306/student");
    cpds.setUser("root");
    cpds.setPassword("");

    //3.得到连接对象
    conn = cpds.getConnection();
    ps = conn.prepareStatement("SELECT * FROM user LIMIT 0,?");
    ps.setInt(1, 20);

    //4.执行SQL查询
    rs = ps.executeQuery();
    System.out.println("序号 - 姓名 - 年级");
    while (rs.next()) {
      System.out.println(rs.getInt("id") + " - " + rs.getString("name") + " - " + rs.getShort("grade"));
    }
    
    //5.关闭连接
    db.release(conn, ps, rs);
  }
}

代码实例(2):配置文件进行配置c3p0-config.xml默认是采用字节码进行读取该文件;

<c3p0-config>
  <default-config>
    <property name="driverClass">org.mariadb.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mariadb://127.0.0.1:3306/student</property>
    <property name="user">root</property>
    <property name="password"></property>

    <property name="automaticTestTable">con_test</property>
    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>

    <user-overrides user="test-user">
      <property name="maxPoolSize">10</property>
      <property name="minPoolSize">1</property>
      <property name="maxStatements">0</property>
    </user-overrides>
  </default-config>
<!--可以配置多种数据库-->
  <named-config name="mysql">
    <property name="driverClass">org.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/student</property>
    <property name="user">root</property>
    <property name="password"></property>
  </named-config>

<!--可以配置多种数据库-->
  <named-config name="oracle">
    <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
    <property name="jdbcUrl">jdbc:oracle:thin:@192.168.10.25:1521:student</property>
    <property name="user">master</property>
    <property name="password"></property>
  </named-config>
</c3p0-config>

演示代码:

package top.weiyigeek.C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import top.weiyigeek.Util.db;
/**
 * 
 * @Desc: C3P0 通过xml文件配置读取数据库连接以及连接池配置(实际与Demo1差不不大)
 * @author WeiyiGeek
 * @CreatTime 下午2:38:55
 */
public class C3P0Demo2 {
  
  @Test
  public void demo2() throws SQLException {
    //1.创建DataSource并且读取设置数据库连接信息（c3p0-config.xml）
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    //ComboPooledDataSource cpds = new ComboPooledDataSource("oracle"); //可以指定配置文件中连接指定的数据库
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    //2.得到连接对象
    conn = cpds.getConnection();
    ps = conn.prepareStatement("SELECT count(*) FROM user");

    //3.执行SQL查询
    rs = ps.executeQuery();
    System.out.println("序号 - 姓名 - 年级");
    while (rs.next()) {
      System.out.println("学生总数: "+rs.getInt(1) );
    }
    //4.关闭连接
    db.release(conn, ps, rs);
  }
}

执行结果:

三月 19, 2020 2:44:34 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> con_test, breakAfterAcquireFailure -> false, checkoutTimeout -> 30000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgevwha81v0n140s2kcd6|33b37288, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.mariadb.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgevwha81v0n140s2kcd6|33b37288, idleConnectionTestPeriod -> 30, initialPoolSize -> 10, jdbcUrl -> jdbc:mariadb://127.0.0.1:3306/student, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 200, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
序号 - 姓名 - 年级
学生总数: 84
已关闭数据库连接并释放资源

采用C3P0极大的简化我们自己写的Tools类:


import com.mchange.v2.c3p0.ComboPooledDataSource;
/***
 * @Desc: 数据库连接工具类采用c3p0的方式（精简我们的代码）
 * @author WeiyiGeek
 * @CreatTime 下午1:28:57
 */
public class db1 {
  static ComboPooledDataSource cpds = null;
  static {
    cpds = new ComboPooledDataSource();
  }
  
  /**
	 * 获取C3P0中的链接对象
	 * @return Connection
	 */
  public static Connection getConn() throws SQLException {
    return cpds.getConnection();
  }
  
  /**
	 * Fun:关闭数据库连接并释放资源 （注意点:关闭的顺序）
	 * @param conn
	 * @param st
	 * @param rs
	 */
  public static void release(Connection conn,Statement st, ResultSet rs) {
    closeRs(rs);
    closeSt(st);
    closeConn(conn);
    System.out.println("\n已关闭数据库连接并释放资源\n");
  }
  
  //私有静态方法-释放查询结果集
  private static void closeRs(ResultSet rs) {
      try {
        if(rs != null)
          rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        rs = null;
      }
    }
    
  //私有静态方法-释放statement对象
  private static void closeSt(Statement st) {
      try {
        if(st != null)
          st.close();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        st = null;
      }
    }
    
  //私有静态方法-关闭数据库连接
  private static void closeConn(Connection conn) {
    try {
      if(conn != null)
        conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      conn = null;
    }
  }
}

3.DBUtils（重点）

什么是DBUtils?
答:Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能;

简单的说:DBUtils只是帮我们简化了CRUD的代码，创建数据库连接获取工作不在他考虑范围，您只需要传入DataSource连接对象给他即可;

官网下载地址:http://commons.apache.org/dbutils/download_dbutils.cgi

补充说明:

1.通过类的字节码得该类的实例（通过反射来实现）

1 2	Account a = new Account(); Account al = Account.class.newInstance();

2.ResultSetHandler接口实现类
- BeanHander - 将查询到的单个数据封装成为一个对象（常用）;
- BeanListHander - 将查询到的多个个数据封装成为一个装有对象的List集合（常用）;
  1
  2
  Person res = qr.query("select * from person", new BeanHandler<Person>(Person.class));
  List<Person> lp = runner.query("SELECT * FROM person ", new BeanListHandler<Person>(Person.class));
- ArrayHandler - 将查询到的单个数据封装成为一个数组
- ArrayListHandler - 将查询到的多个数据封装成为一个集合里面的元素是数组
- MapHandler - 将查询到的单个数据封装成为一个Map
- MapListHandler - 将查询到的多个数据封装成为一个集合里面的元素是Map
- ColumnsListHandler (不常用)
- KeyedHander (不常用) - 多条记录封装到一个Map集合的Map集合中。并且外面的Map集合是可以指定的（指定内层Map的其中一个属性名）。
- ScalarHander (不常用) - 将单个值封装、例如select count（*），求内容的条数
  1
  2
  Long count = (Long)runner.query("SELECT COUNT(*) FROM person",new ScalarHandler());
  return count.intValue();
  WeiyiGeek.

Query查询出结果存放的类:
(1) 存放查询数据的类

package top.weiyigeek.DBUtil;
public class Account {
  private int id;
  private String name;
  private float money;
  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 float getMoney() {
    return money;
  }
  public void setMoney(float money) {
    this.money = money;
  }
  
  @Override
  public String toString() {
    return "Account [序号=" + id + ", 姓名=" + name + ", 工资=" + money + "]";
  } 
}

(2) DBUtil实现的类

package top.weiyigeek.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/*
 * Function: DButil CURD 案例实现 
 */
public class DBUtilDemo1 {
  //调用实现
  public static void main(String[] args) throws SQLException {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    //示例1.插入测试
    if(insert(cpds)>0) {
      System.out.println("插入成功");
    }else {
      System.err.println("插入失败");
    }
    
    //示例2.测试删除
    if(delete(cpds)>0) {
      System.out.println("删除成功");
    }else {
      System.err.println("删除失败");
    }
    
    //示例3.测试更新
    if(update(cpds)>0) {
      System.out.println("更新成功");
    }else {
      System.err.println("更新失败");
    }
    
    
    //示例4.测试匿名实现类进行返回查询结果
    Account account = queryone(cpds);
    System.out.println(account.toString());
    
    
    //示例5.通过DBuntil中ResultSetHandler实现接口来返回结果;
    List<Account> ls = querytwo(cpds);
    for(Account worker:ls) {
      System.out.println(worker.toString());
    }

  }
  
  //（1）Function:如方法名称测试SQL插入语句
  public static int insert(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("INSERT INTO account VALUES (null,?,?)","张伟",1024);
    return flag;
  }
  
  //（2）Function:如方法名称测试SQL删除语句
  public static int delete(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("DELETE FROM account where name = ?","张伟");
    return flag;
  }
  
  //（3）Function:如方法名称测试SQL更新语句
  public static int update(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    int flag = qr.update("UPDATE account SET money=money+100 where id = ? and name = ?",1,"WeiyiGeek");
    return flag;
  }
  
  //（4）Function:如方法名称测试SQL查询语句
  public static Account queryone(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    //匿名类实现
    Account account = qr.query("SELECT * FROM account WHERE id = ?", new ResultSetHandler<Account>() {
      @Override
      public Account handle(ResultSet rs) throws SQLException {
        // TODO Auto-generated method stub
        Account account = new Account();
        while(rs.next()) {
          int id = rs.getInt("id");
          String name = rs.getString("name");
          float money = (float)rs.getInt("money");
          account.setId(id);
          account.setName(name);
          account.setMoney(money);
        }
        return account;
      }
    }, 1);
    
    return account;
  }
  
  //（5）采用ResultSetHandler中的，resultset转换为其他对象进行实现。
  public static List<Account> querytwo(DataSource cpds) throws SQLException {
    QueryRunner qr = new QueryRunner(cpds);
    //- BeanHander - 返回一个对象;
    //- BeanListHander - 返回一个装有对象的集合;
    List<Account> list= qr.query("SELECT * FROM account", new BeanListHandler<Account>(Account.class));
    return list;
  }
}

欢迎各位志同道合的朋友一起学习交流，如文章有误请在下方留下您宝贵的经验知识，个人邮箱地址【master#weiyigeek.top】或者个人公众号【WeiyiGeek】联系我。

更多文章来源于【WeiyiGeek Blog - 为了能到远方，脚下的每一步都不能少】, 个人首页地址( https://weiyigeek.top )

扫描Follow(关注)WeiyiGeek公众号与Visit(浏览)极客全栈修炼小程序

专栏书写不易，如果您觉得这个专栏还不错的，请给这篇专栏 【点个赞、投个币、收个藏、关个注、转个发、赞个助】，这将对我的肯定，我将持续整理发布更多优质原创文章！。

最后更新时间：2023-01-31 10:29:10
文章原始路径：_posts/编程世界/JavaWeb/3.JDBC/数据库事务处理与资源池.md
转载注明出处，原文地址：https://blog.weiyigeek.top/2020/2-8-308.html
本站文章内容遵循知识共享署名 - 非商业性 - 相同方式共享 4.0 国际协议

WeiyiGeek