连接池、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
- 界面
如图所示