※连接池
传统连接方式的弊端:出现异常Too many connections,其它程序无法连接,哪怕及时关闭也存在耗时长的问题。
/**
* 演示传统连接的问题
*/
public class ConQuestion {
@Test
public void testCon() throws SQLException {
long start = System.currentTimeMillis();
for(int i = 0; i < 5000; i++) {
//使用传统jdbc连接方式,得到连接
Connection connection = JDBCUtils.getConnection();
//做一些操作,比如得到PreparedStatement,发送sql
//......
//未及时关闭:异常"Too many connections"
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("传统连接方式耗时" + (end - start));//15503
}
}
原理:传统的JDBC数据库连接使用DriverManager获取,每次向数据库建立连接时都要将Connection加载到内存中,再验证IP地址,用户名和密码(0.05~1s),频繁连接过多占用系统资源,容易造成服务器崩溃。
每次连接完数据库后都要断开,如果出现异常未能关闭,将导致数据库内存泄漏,最终导致重启数据库。
不能控制连接数量,连接过多也可能导致内存泄漏,mysql崩溃。
数据库连接池计数(connection pool):预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,从“缓冲池”中取出一个,使用完毕后放回去;
数据库连接池负责分配、管理和释放数据库连接,允许程序重复使用一个现有的数据库连接,而不是重新建立一个。
当程序向连接池请求的连接数超过最大连接数量时,请求将被加入到等待队列。
C3P0
@Test
//方式2:使用配置文件模板来完成
//1 将C3P0提供的配置文件c3p0-config.xml拷贝到src目录下,提供连接数据库的相关方方式
public void testC3P0_02() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");
long start = System.currentTimeMillis();
for(int i = 0; i < 500000; i++) {
Connection connection = comboPooledDataSource.getConnection();
// System.out.println("连接成功~");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("C3P0 方式2执行500000次耗时" + (end - start)); //1760
}
}
※Druid德鲁伊
德鲁伊连接池
C3P0是老牌方式,工程项目建议使用Druid
public class Druid_ {
@Test
public void testDruid() throws Exception {
//1 添加jar包至libs,add as librarys
//2 添加配置文件模板至src
//3 创建Properties对象,用来读取文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//4 创建指定参数数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for(int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
// System.out.println("连接成功");
connection.close();
}
long end = System.currentTimeMillis();
System.out.println("Druid连接500000次耗时" + (end - start)); //571
}
}
德鲁伊工具类
/**
* 基于druid数据库连接池的工具类
*/
public class JDBCUtilsByDruid {
private static DataSource ds;
//在静态代码块完成ds初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//编写getConnection
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//关闭连接,数据库连接池计数中,close方法并非真的断掉连接,而是把使用的connection连接对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
try{
if(resultSet != null) {
resultSet.close();
}
if(statement != null) {
statement.close();
}
if(statement != null) {
statement.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
使用
package com.hspedu.jdbc.datasource;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class JDBCUtilsByDruid_USE {
@Test
public void testSelect(){
Connection connection = null;
String sql = "select * from actor where id = ?";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtilsByDruid.getConnection();
System.out.println(connection.getClass());//运行类型:com.alibaba.druid.pool.DruidDataSource info
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 borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
}
}
Apache-DBUtils
需求:关闭connection后,resultSet结果集无法使用;resultSet不利于数据管理(使用完就要关闭);使用不变,用的是getString(),而不是getName(), getSex()
package com.hspedu.jdbc.datasource;
import java.util.Date;
/**
* Actor对象代表actor表中的一个记录
*/
public class Actor {
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() { //底层反射需要无参构造器
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
//...generate getter and setter
@Override
public String toString() {
return "\nActor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
创建类Actor,称为JavaBean/PoJo/Domain,属性对应结果集中各字段,一个actor对象对应表中一条记录;将结果集封装到ArrayList<Actor>
中。
//自己写代码将ResultSet封装到ArraySet中
@Test
public ArrayList<Actor> testSelectToArraySet(){
Connection connection = null;
String sql = "select * from actor where id >= ?";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
ArrayList<Actor> list = new ArrayList<>();
try {
connection = JDBCUtilsByDruid.getConnection();
System.out.println(connection.getClass());//运行类型:com.alibaba.druid.pool.DruidDataSource info
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 borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
// System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
//把得到的resultSet封装到Actor对象,在放入到list集合
list.add(new Actor(id, name, sex, borndate, phone));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
System.out.println(list.toString());
return list;
}
用工具完成封装过程
使用DBUtils+数据库连接池(德鲁伊)方式,完成对表actor的crud
public class DBUtils_USE {
//使用apache-DBUtil 工具类+druid完成对表的crud操作
@Test
public void testQueryMary() throws SQLException { //返回结果是多行的情况
//1 得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//2 使用DBUtils类和接口,先引入DBUtils相关jar包,加入到Project
//3 创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
//4 可以执行相关方法,返回ArrayList结果集
String sql = "select * from actor where id >= ?";
// sql语句也可以查询部分列
//String sql = "select id, name from actor where id >= ?";
//解读:query方法是执行sql语句,得到resultSet-->封装到-->ArrayList集合中,返回集合
//new BeanListHandler<>(Actor.class:在将resultSet->对象->封装到ArrayList(底层使用反射机制获取actor里的各属性)
// 1是给sql语句中的?赋值,可以有多个值,是可变形参Object... params
//底层resultSet,会在query关闭, 也会关闭preparedStatement
List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
System.out.println("输出集合信息");
for(Actor actor: list) {
System.out.println(actor);
}
//释放资源
JDBCUtilsByDruid.close(null, null, connection);
}
}
不同返回使用不同Bean方法
//返回单个记录,使用的处理器 Hander 是 BeanHandler
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
//返回单行单列值对象
Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 2);
// sql语句完成DML操作,使用queryRunner.update,返回受影响行数
//修改
String sql = "update actor set name = ? where id = ?";
int affectedRow = queryRunner.update(connection, sql, "张三丰", 2);
//增加
String sql = "insert into actor values (null, ?, ?, ?, ?)";
int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1966-10-10", "117");
//删除
String sql = "delete from actor where id = ?";
int affectedRow = queryRunner.update(connection, sql, 3);
System.out.println(affectedRow > 0 ? "执行成功" : "未影响表");
BasicDao
Apache-DBUtils+Druid 简化JDBC开发,仍有不足:
SQL语句固定,不能通过参数传入,不变执行增删改查通用操作;
Select操作若有返回值,返回类型不能固定,需要使用泛型;
业务复杂表多,不能只使用一个类。
DAO(Data Access Object)数据访问对象
通用类BasicDao,专门用于和数据库交互,即完成对数据库(表)的crud操作;在BasicDao基础上,实现一张表对应一个Dao。
BasicDAO.java
public class BasicDAO<T> { //泛型指定具体的类型
//开发通用dml方法,针对任意表
private QueryRunner qr = new QueryRunner();
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
// 返回多个对象(查询结果多行),针对任意表
public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
...
return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
...
}
//查询单行结果的通用方法
public T querySingle(String sql, Class<T> clazz, Object... parameters) {
...
return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
...
}
//查询单行单列的方法,即返回单值得方法
public Object queryScalar(String sql, Object... parameters) {
...
return qr.query(connection, sql, new ScalarHandler(), parameters);
...
}
}
Actor.java
ActorDAO.java
public class ActorDAO extends BasicDAO<Actor> {
}
TestDAO.java
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
//1 查询
List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
for(Actor actor: actors) {
System.out.println(actor);
}
//2 查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 1);
System.out.println(actor);
//3 查询单行单列
Object o = actorDAO.queryScalar("select name from actor where id = ?", 2);
System.out.println(o);
//4 dml操作
int update = actorDAO.update("insert into actor values (null, ?, ?, ?, ?)", "林青霞", "女", "1967-07-01", "117" );
System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
}
}
实际开发中还有一业务层:组织sql语句,调用相应XxxDAO完成综合需求(涉及多张表)
应用层TestDAO 、 界面层AppView -> 业务层ActorService ->DAO层 XxxDAO extends BasicDAO -> 数据层mysql + Xxx类、工具类utils。