代码链接:https://download.csdn.net/download/qq_52354698/86495369?spm=1001.2014.3001.5503
5k 次连接数据库,如果没有及时关闭连接的话,数据库是无法承受住如此至多的;连接的
如果及时的关闭了连接,也会造成大量资源和时间的浪费
因此出现了数据库连接池

首先要导入C3P0的jar包,导入方式和,mysql.jar一样
第一种方式:相关参数,在程序中指定user、url、password
@Test
public void testC3P0_01() throws IOException, PropertyVetoException, SQLException {
//创建一个数据对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//通过配置文件mysql.properties获取相关的连接信息
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);
//设置最大连接数
comboPooledDataSource.setMaxPoolSize(50);
//测试连接效率
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
第二种:使用配置文件模板来完成
将C3P0提供的c3p0.config.xml拷贝到src目录下
<c3p0-config>
<named-config name="qdu">
<property name="driverClass">com.mysql.jdbc.Driverproperty>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/qdu_01?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=trueproperty>
<property name="user">rootproperty>
<property name="password">rootproperty>
<property name="acquireIncrement">5property>
<property name="initialPoolSize">10property>
<property name="minPoolSize">5property>
<property name="maxPoolSize">50property>
<property name="maxStatements">5property>
<property name="maxStatementsPerConnection">2property>
named-config>
c3p0-config>
注意:这个地方的url同样需要根据自己mysql版本的不同而修改,与mysql配置文件不同的是,在xml文件中 “&” 要用 “& amp;” (没有空格)来代替
@Test
public void testC3P0_02() throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("qdu");
long start = System.currentTimeMillis();
System.out.println("开始执行");
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
同样也需要加入 Druid(德鲁伊)的 jar 包,放入 libs 文件夹下,然后引入
Druid 配置文件(与mysql的配置文件类似的配置文件)
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qdu_01?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true
username=root
password=root
#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
package com.qdu.jdbc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;
/**
* @author dell
* @version 1.0
*/
public class Druid_ {
@Test
public void testDruid() throws Exception {
//创建Properties对象,用来读入Druid配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//创建一个指定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println("连接成功");
connection.close();
}
}
package com.qdu.jdbc.datasource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author dell
* @version 1.0
* 基于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) {
throw new RuntimeException(e);
}
}
//得到连接
public static Connection getConnection() throws SQLException {
return dataSource.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 (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}

commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库,它是对 JDBC 的封装,使用 dbutils 能极大简化 jdbc 编码的工足量。
ArrayHandler:把结果集中的第一行数据转成对象数组。
ArrayListHandler:把结果集中的每一行数据都转成一 个数组, 再存放到List中。
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler:将结果集中的每一行数据都封装到一 个对应的JavaBean实例中,存放到List里。
ColumnListHandler:将结果集中某一列的数据存放到List中。
KeyedHandler(name):将结果集中的每行数据都封装到Map里,再把这些map再存到一个map里,其key为指定的key.
MapHandler:将结果集中的第一行数据封装到一 个Map里,key是列名, value就是对应的值。
MapListHandler:将结果集中的每一行数据都封装到一 个Map里,然后再存放到List
使用 DBUtils + 数据连接池(德鲁伊)方式,完成 actor 的 crud
先引入 DBUtils 相关的 jar
package com.qdu.jdbc.datasource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* @author dell
* @version 1.0
*/
public class DBUtils_USE {
@Test
public void testQueryMany() throws SQLException {
//得到连接
Connection connection = JDBCUtilsByDruid.getConnection();
//使用DBUtils类和接口,引入 DBUtils 相关的 jar
//创建 QueryRunner
QueryRunner queryRunner = new QueryRunner();
//执行相关的方法,返回 ArrayList 结果集
String sql = "select * from actor where id >= ?";
//query 方法就是执行 sql 语句,得到 resultset -- 封装到 -- ArrayList 集合中
//connection:连接
//sql:执行的sql语句
//new BeanListHandler<>(Actor.class):在将result -> Actor 对象 -> 封装到 ArrayList
//1:给sql语句中的?赋值,可以有多个值
//底层得到的resulttest,会在query关闭,关闭PreparedStatment
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);
}
}

apache- dbutils+ Druid简化了JDBC开发,但还有不足:

完成一个简单设计
com.qdu.dao_
JDBCUtilsByDruid 类
package com.qdu.dao_.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author dell
* @version 1.0
* 基于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) {
throw new RuntimeException(e);
}
}
//得到连接
public static Connection getConnection() throws SQLException {
return dataSource.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 (connection != null) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Actor 类
package com.qdu.dao_.domain;
import java.util.Date;
/**
* @author dell
* @version 1.0
*/
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;
}
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 getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "\nActor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
BasicDAO 类
package com.qdu.dao_.dao;
import com.qdu.dao_.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.util.List;
/**
* @author dell
* @version 1.0
* 开发BasicDAO,是其他DAO的父类
*/
public class BasicDAO<T> {//泛型指定具体的类型
private QueryRunner queryRunner = new QueryRunner();
//开发通用的dml方法,针对任意的表
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = queryRunner.update(connection, sql,parameters);
return update;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
/**
*
* @param sql sql语句,可以有 ?
* @param clazz 传入一个类的Class对象 比如 Actor.class
* @param parameters 传入 ? 的具体的值,可以是多个
* @return 根据 Actor.class 返回对应的 ArrayList 集合
*/
//返回多个对象(即查询的结果是多行),针对任意表
public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行结果的通用方法
public T querySingle(String sql, Class<T> clazz, Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
//查询单行单列的方法,即返回单值的方法
public Object queryScalar(String sql, Object... parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return queryRunner.query(connection, sql, new ScalarHandler(), parameters);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(null, null, connection);
}
}
}
ActorDAO 类
package com.qdu.dao_.dao;
import com.qdu.dao_.domain.Actor;
/**
* @author dell
* @version 1.0
*/
public class ActorDAO extends BasicDAO<Actor>{
}
TestDAO 类
package com.qdu.dao_.test;
import com.qdu.dao_.dao.ActorDAO;
import com.qdu.dao_.domain.Actor;
import org.junit.jupiter.api.Test;
import java.util.List;
/**
* @author dell
* @version 1.0
*/
public class TestDAO {
@Test
public void testActorDAO(){
ActorDAO actorDAO = new ActorDAO();
//查询
List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
System.out.println("========查询结果========");
for (Actor actor : actors) {
System.out.println(actor);
}
//查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 5);
System.out.println("========查询结果========");
System.out.println(actor);
//查询单行单列
Object o = actorDAO.queryScalar("select name from actor where id = ?", 4);
System.out.println("========查询结果========");
System.out.println(o);
//dml操作
int update = actorDAO.update("insert into actor values(null, ?, ?, ?, ?)", "张无忌", "男", "2022-8-25", "999");
System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
}
}
