连接池、ApacheDBUtils 和 BasicDAO
学习内容来自B站韩顺平老师的Java基础课
连接池
传统连接的问题
如果同时有大量程序连接数据库，就会报错，比如：
// 连接数据库 5000 次
@Test
public void testConnection() {
for (int i = 0; i < 5000; i++) {
Connection connection = JDBCUtils.getConnection();
}
}
代码中的连接方法可以参考这里
会报错：
那如果连接后及时关闭呢？
// 连接数据库 5000 次
@Test
public void testConnection() {
long start = System.currentTimeMillis();
System.out.println("开始连接");
for (int i = 0; i < 5000; i++) {
// 连接方法
Connection connection = JDBCUtils.getConnection();
// 关闭连接的方法
JDBCUtils.close(null, null, connection);
}
long end = System.currentTimeMillis();
System.out.println("共耗时：" + (end - start));
}
输出结果：
开始连接
共耗时：23096
可以看到相当耗时
问题分析：
- 传统的 JDBC 连接数据库使用 DriverManage 来获取，每次建立连接都要将 Connection 加载到内存中，再验证 IP 地址，用户名和密码（花费 0.05s - 1s）。每次需要连接就像数据库要求一个，频繁的进行数据库连接操作将占用很多的系统资源，容易造成服务器崩溃
- 每一次数据库连接使用后都要断开，如果程序出现异常未正常关闭，将导致数据库内存泄漏，最终可能导致重启数据库
- 传统获取连接的方式，不能控制创建的连接数量，连接过多，也可能导致内存泄漏或者 MYSQL 崩溃
解决传统连接问题，可以采用数据库连接池技术
连接池基本介绍
- 预先在数据库“缓冲池”中放入一定数量的连接，当需要建立数据库连接时，只需要从“缓冲池”中取出一个，使用完毕后再放回去，即连接可以复用。这个“缓冲池”就是连接池
- 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立
- 当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将会被加入到等待队列中
连接池种类
JDBC 的数据库连接池用 javax.sql.DataSource 来表示，DataSource 是一个接口，该接口一般由第三方实现：
- C3P0 数据库连接池，速度相对较慢，稳定性不错，hibernate 和 spring 底层用的就是这种
- DBCP 数据库连接池，速度相对 C3P0 较快，但不稳定
- Proxool 数据库连接池，有监控连接池状态的功能，稳定性比 C3P0 差一点
- BonCP 数据库连接池，速度快
- Druid（德鲁伊）是阿里提供的数据库连接池，集 C3P0、DBCP、Proxool 优点于一身
接下来主要介绍 C3P0 和 Druid
C3P0
首先需要下载对应 jar 包，然后放入项目的包文件夹中，并添加为库文件（add as library）
方式 1
在程序中指定 user, password, url 然后连接
public void testC3P0_01() throws Exception{
// 创建 DataSource 对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 通过配置文件获取相关信息
Properties properties = new Properties();
properties.load(new FileInputStream("src\\mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 给数据源 comboPooledDataSource 设置相关参数
// 连接是由 comboPooledDataSource 管理的
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
// 设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
// 设置最大连接数，表示最多同时有 50 个连接，如果有超过的，则需要排队等待
comboPooledDataSource.setMaxPoolSize(50);
System.out.println("开始连接");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p 0 连接 5000 次共耗时：" + (end - start));
}
输出结果：
开始连接
连接 5000 次共耗时：2022
方式 2
使用配置文件模板来完成
首先需要有配置文件 c3p0-config.xml，文件放置在 src 目录下
<c3p0-config>
<!-- 数据源名称代表连接池 -->
<named-config name="jerryHome">
<!-- 驱动类 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- url-->
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/hsp_db02</property>
<!-- 用户名 -->
<property name="user">root</property>
<!-- 密码 -->
<property name="password">hsp</property>
<!-- 每次增长的连接数-->
<property name="acquireIncrement">5</property>
<!-- 初始的连接数 -->
<property name="initialPoolSize">10</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 最大连接数 -->
<property name="maxPoolSize">50</property>
<!-- 可连接的最多的命令对象数 -->
<property name="maxStatements">5</property>
<!-- 每个连接对象可连接的最多的命令对象数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
上述需要配置为自己需要连接的数据库对应的参数
然后写代码：
public void testC3P0_02() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("jerryHome");
System.out.println("开始连接");
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("c3p0 使用配置文件连接 5000 次共耗时：" + (end - start));
}
输出结果：
开始连接
c3p0 使用配置文件连接 5000 次共耗时：1077
可以看到，速度优化了 50%
Druid
在使用前，需要先加入对应的 jar 包，然后需要在 src 目录下创建配置文件
druid.properties
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/hsp_db02?rewriteBatchedStatements=true
username=root
password=hsp
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000
代码：
public void testDruid() throws Exception {
// 创建 Properties 对象
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
// 创建一个指定参数的数据库连接池，即 Druid 连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
System.out.println("开始连接");
for (int i = 0; i < 5000; i++) {
// 获取连接
Connection connection = dataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("Druid 连接 5000 次，耗时：" + (end - start));
}
输出结果：
开始连接
Druid 连接 5000 次，耗时：831
可以看到，和之前 c3p0 方式连接并没有优化
但是，如果将连接次数改为 500000，那么：
c3p0 使用配置文件连接 500000 次的耗时为：
c3p0 使用配置文件连接 500000 次共耗时：3865
Druid 耗时为：
Druid 连接 500000 次，耗时：1128
这次就可以看到明显的提升，这些都是我自己笔记本跑出来的，大家可以自行测试
所以，当并发量高的时候，还是使用 Druid 性能更好
JDBCUtilsByDruid
既然 Druid 性能好，那就实现一个基于 Druid 的数据库连接池工具类，如下：
public class JDBCUtilsByDruid {
private static DataSource dataSource;
// 在静态文件中完成初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
// 关闭连接
// 注意：在连接池技术中，关闭连接并不是真的断掉连接，而是把连接对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection){
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
测试：
public void testJDBCByDruidQuery() {
Connection connection = null;
// sql
String sql = "select * from actor";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 得到连接
connection = JDBCUtilsByDruid.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 执行
resultSet = preparedStatement.executeQuery();
// 遍历结果集
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
String phone = resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + birthday + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
}
ApacheUtils
问题
关闭 Connection 后，ResultSet 无法使用，即结果集与连接是关联的，也就是结果集不能复用，这样不利于数据管理，同时结果集的数据使用不方便（无法判断数据属于哪个字段）
如何解决这个问题？
- 可以实现一个类，对应要查询的数据表，表中的各个字段都为该类的属性，这样每条记录就是对应类的一个对象
- 读取到 ResultSet 后，将记录封装到该类的集合中去
简单实现：
首先创建对应类
public class Actor {
private Integer id;
private String name;
private String sex;
private Date birthday;
private String phone;
// 一定要给一个无参构造器【反射需要】
public Actor() {
}
public Actor(Integer id, String name, String sex, Date birthday, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.birthday = birthday;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", phone='" + phone + '\'' +
'}' + '\n';
}
}
然后测试：
public void testQueryToList() {
Connection connection = null;
// sql
String sql = "select * from actor where id >= ?";
// 创建 PreparedStatement 对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
// 创建存储记录的集合
List<Actor> resultList = new ArrayList<>();
try {
// 得到连接
connection = JDBCUtilsByDruid.getConnection();
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
// 执行
resultSet = preparedStatement.executeQuery();
// 遍历结果集
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date birthday = resultSet.getDate("birthday");
String phone = resultSet.getString("phone");
// 把当前记录封装为 Actor 对象，然后存到集合中
resultList.add(new Actor(id, name, sex, birthday, phone));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
System.out.println("集合数据：" + resultList);
}
}
输出结果：
集合数据：[Actor{id=2, name='jerry', sex='男', birthday=2019-12-01, phone='66666'}
, Actor{id=3, name='tom', sex='男', birthday=2021-04-01, phone='66666'}
, Actor{id=4, name='杰瑞狗', sex='男', birthday=2021-12-01, phone='66666'}
]
可以看到，连接关闭后仍然可以使用数据
ApacheUtils 查询
在上面例子中，可以看到，封装结果集的工作是重复的，也可以抽象出一个工具类来方便封装，所以就有了 ApacheUtils 工具类
基本介绍：
- commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库，是对 JDBC 的封装，能极大简化 JDBC 编码的工作量
DBUtils 常用的有：
- QueryRunner 类：线程安全，封住了 SQL 的执行，可以实现增删改查和批处理
- ResultSetHandler 接口：用于处理 ResultSet，可以把数据按照要求转换为指定格式
在使用前，需要先添加 jar 包作为类库
查询多条记录
使用 DBUtils + Druid 方式，完成对表 actor 的 crud
public void testQueryMany() throws SQLException { // 返回多行数据的查询
// 得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 使用 QueryRunner 对象查询
String sql = "select * from actor where id >= ?"; // 查询所有列
// String sql = "select id, name from actor where id >= ?"; // 查询部分列
// query() 方法就是执行 sql 语句，得到 ResultSet，然后返回到 ArrayList 中
/* 参数依次为：
connection 连接;
sql 要执行的 sql 语句;
new BeanListHandler<>(Actor.class) 用来将 ResultSet 封装到 Actor 对象中，再放入 ArrayList
底层使用反射机制获取 Actor 类;
1 就是给 sql 语句的 ？ 赋值，底层是可变参数;
*/
// 该方法底层会创建 PreparedStatement 和 ResultSet，并在使用后关闭
/**
* 分析 queryRunner.query方法源码:
* public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
* PreparedStatement stmt = null;//定义PreparedStatement
* ResultSet rs = null;//接收返回的 ResultSet
* Object result = null;//返回ArrayList
*
* try {
* stmt = this.prepareStatement(conn, sql);//创建PreparedStatement
* this.fillStatement(stmt, params);//对sql 进行 ? 赋值
* rs = this.wrap(stmt.executeQuery());//执行sql,返回resultset
* result = rsh.handle(rs);//返回的resultset --> arrayList[result] [使用到反射，对传入class对象处理]
* } catch (SQLException var33) {
* this.rethrow(var33, sql, params);
* } finally {
* try {
* this.close(rs);//关闭resultset
* } finally {
* this.close((Statement)stmt);//关闭preparedstatement对象
* }
* }
*
* return result;
* }
*/
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
for (Actor actor : list) {
System.out.println(actor);
}
// 关闭连接
JDBCUtilsByDruid.close(null, null, connection);
}
查询单条记录
在上面的例子中， sql 查询返回的是多条记录，如果返回单条语句是怎么样？如下：
public void testQuerySingle() throws SQLException {
// 得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// sql
String sql = "select * from actor where id = ?";
// 使用方法查询
// 因为返回单行记录，所以使用 Handler 是 BeanHandler 而不是 BeanListHandler
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 2);
System.out.println(actor);
// 关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
查询单行单列
再来一个例子，返回记录为单行单列
public void testQueryScalar() throws SQLException {
// 得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// sql
String sql = "select name from actor where id = ?";
// 使用方法查询
// 因为返回单个对象，所以使用的 Handler 是 ScalarHandler
Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 2);
System.out.println(obj);
// 关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
总结
于是，可以知道，根据查询返回结果的不同使用的 Handler 也相应不同
- 如果返回多条记录，使用 BeanListHandler<>(记录类.class)
- 如果返回单条记录，使用 BeanHandler<>(记录类.class)
- 如果返回单行单列记录，即一个对象，使用 ScalarHandler()
ApacheUtils DML
使用 ApacheDBUtils + Druid 完成 DML（update、insert、delete）
public void testDML() throws SQLException {
// 得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// sql
// String sql = "update actor set name = ? where id = ?";
// String sql = "insert into actor values(null, ?, ?, ?, ?)";
String sql = "delete from actor where id = ?";
// 使用 update 方法执行 sql，返回的是受影响的行数
/*
参数：
connection 连接
sql 要执行的 sql
剩下的为 sql 语句对应的 ？ 设置的值，为可变参数
*/
// int affectedRow = queryRunner.update(connection, sql, "杰瑞", 2);
// int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1966-10-10", "116");
int affectedRow = queryRunner.update(connection, sql, 5);
System.out.println(affectedRow > 0 ? "执行成功" : "执行未造成数据库改变");
// 关闭
JDBCUtilsByDruid.close(null, null, connection);
}
数据库表类型和 JavaBean 类型的映射关系
上表表示，设计数据库查询结果集的封装类时，各种类型数据应该封装的基本类型，只有两个类型需要特别注意：
- int 封装为 Integer
- double 封装为 Double
因为数据库记录的值可能为 null，而 Java 只有包装类才可以为 null，基本类型不可以
DAO 和增删改查通用方法-BasicDAO
Apache-DBUtils 简化了 JDBC 开发，但是仍有不足
- SQL 语句固定，不能通过参数传入，通用性不好，需要进行改进，更方便执行 crud
- 对于 select 操作，如果有返回值，返回类型不能固定，需要使用泛型
- 若表很多时，业务需求复杂，不能只依赖一个 Java 类完成
正常的开发逻辑应该是：
- 对于数据库的每张表，都对应一个实体类，这些类称为 domain
- 而对每张表的操作，应该由对应的 DAO 来实现，而所有的 DAO 又会有重复的部分，于是应该抽象出一个 BasicDAO，让其它的 DAO 都继承自 BasicDAO，在共有的方法基础上再实现各自特有的功能
DAO 的全称是 data access object，即数据访问对象
BasicDAO 就是专门和数据库交互的通用类，完成对数据库的 crud 操作
在 BasicDAO 的基础上，对于每张表实现一个 DAO，用来完成独特的功能，比如 actorbiao - Actor.java 类 - ActorDAO
简单实现
完成一个简单实现：
由下面四个包组成
- utils 包 – 工具类
- domain 包 – javaBean
- dao 包 – 存放 xxxDao 和 BasicDao
- test --测试类
utils
仍然使用之前的 JDBCUtilsByDruid.java
domain
使用之前的 Actor.java 类
dao
BasicDAO
public class BasicDAO<T> { // 泛型指定具体的类
private QueryRunner qr = new QueryRunner();
// 开发通用的 dml 方法
public int update(String sql, Object... params) {
Connection connection = null;
try {
// 获取连接
connection = JDBCUtilsByDruid.getConnection();
// 执行
int update = qr.update(connection, sql, params);
return update;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 查询多行记录的方法
/**
*
* @param sql 查询的 sql 语句
* @param clazz 对应类的 Class 对象
* @param params 传入 ? 具体的值
* @return 根据 Class 对象，返回对应的集合
*/
public List<T> quertMulti(String sql, Class<T> clazz, Object... params) {
Connection connection = null;
try {
// 获取连接
connection = JDBCUtilsByDruid.getConnection();
// 执行并返回结果
return qr.query(connection, sql, new BeanListHandler<>(clazz), params);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 查询单行记录
public T querySingle(String sql, Class<T> clazz, Object... params) {
Connection connection = null;
try {
// 获取连接
connection = JDBCUtilsByDruid.getConnection();
// 执行并返回结果
return qr.query(connection, sql, new BeanHandler<>(clazz), params);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 查询单行单列，即单值
public Object queryScalar(String sql, Object... params) {
Connection connection = null;
try {
// 获取连接
connection = JDBCUtilsByDruid.getConnection();
// 执行并返回结果
return qr.query(connection, sql, new ScalarHandler(), params);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
ActorDAO
继承自 BasicDAO
public class ActorDAO extends BasicDAO<Actor> {
// 根据需求，可以编写特有的方法
}
test
public class TestDAO {
// 测试 ActorDAO 对 actor 表的 crud
@Test
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
// 查询多行记录
List<Actor> actors = actorDAO.quertMulti("select * from actor where id >= ?", Actor.class, 1);
for (Actor actor : actors) {
System.out.println("actor = " + actor);
}
// 查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 2);
System.out.println("actor = " + actor);
// 查询单值
Object o = actorDAO.queryScalar("select name from actor where id = ?", 2);
System.out.println("o = " + o);
// dml 操作
int update = actorDAO.update("insert into actor values (null, 'jerry', '男', '2019-12-01', '66666')");
System.out.println(update > 0 ? "执行成功" : "本次执行未修改数据库");
}
}
总结
上面提到的就是目前通用的项目开发逻辑，但是还不够完善，一般来说还需要加上业务层（service）和界面（前端），也就是一共分为四层：
- 持久层：数据库，domain，工具包 utils
- dao 层：BasicDAO 和各种表的 DAO
- 业务层：操作各个表完成业务的 service
- 界面
如图所示
