[TOC]

0x00 数据库事务

什么是事务?

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

为什么要有事务?

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看autocommit自动提交是否关闭
> SHOW VARIABLES LIKE 'autocommit';
"autocommit" "ON"

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

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

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

测试SQL语句:

1
2
3
4
5
6
7
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中事务处理

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

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


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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);
}
}
}

WeiyiGeek.事务处理

WeiyiGeek.事务处理


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) 写时问题
丢失更新:指一个事务去修改数据库同时另一个事务也修改数据库,最后的那个事务,不管是提交还是回滚都会造成前面一个事务的数据更新丢失;

WeiyiGeek.丢失更新

WeiyiGeek.丢失更新

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

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

WeiyiGeek.悲观锁

  • 乐观锁:指从来不会觉得丢失更新会发生。
    那么它的具体做法是什么呢?
    要求程序员在数据库中添加字段然后在后续更新的时候,对该字段进行判定比对如果一致才允许更新。
    • 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】机制是一样的
WeiyiGeek.乐观锁

WeiyiGeek.乐观锁


4.隔离级别

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 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;

WeiyiGeek.读未提交

WeiyiGeek.读未提交


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- 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)
WeiyiGeek.读已提交

WeiyiGeek.读已提交


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- 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)
WeiyiGeek.可重复读

WeiyiGeek.可重复读


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

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

SQL示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- 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)

WeiyiGeek.SERIALIZABLE

WeiyiGeek.SERIALIZABLE


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

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

按拦截程度 ,从高到底

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

数据库默认隔离级别:

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

0x01 数据库连接池

什么是连接池?

连接池指:创建一个池子(容器) 实际是在内存中开辟一块空间(集合), 往池子里面放置多个连接对象,专门用来管理连接对象;

WeiyiGeek.

WeiyiGeek.

连接池有什么作用?

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

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

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

如何解决?

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


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

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

基础示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
// 调用连接池使用:/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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/**
* @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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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);
}

.....
}

执行结果:

WeiyiGeek.连接池

WeiyiGeek.连接池

总结:

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

0x02 开源连接池

1.OBCP

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

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

WeiyiGeek.

WeiyiGeek.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#连接设置
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
/**
*
* @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();
}
}

执行结果:

WeiyiGeek.

WeiyiGeek.


2.C3P0(使用较多)

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

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

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

代码实例(1):手动配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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);
}
}

WeiyiGeek.

WeiyiGeek.


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<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>

演示代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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);
}
}

执行结果:

1
2
3
4
5
三月 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类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

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.

      WeiyiGeek.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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实现的类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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;
}
}

WeiyiGeek.

WeiyiGeek.