前言
在JDBC编程中,每次创建和断开Connection对象都会消耗一定的时间和IO资源。 这是因为在Java程序与数据库之间建立连接时,数据库端要验证用户名和密码并为该连接分配资源,而程序则要把代表连接Connection对象等加载到内存中,所以建立数据库连接的开销很大。尤其是在大量的并发访问时,频繁地创建、断开数据库 连接势必会影响数据库的访问效率,甚至导致数据库崩溃。
为了解决该类问题的发生诞生了数据库连接池技术。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用现有的数据库连接,而不是每次都重新建立连接,在本次博客中将简绍两种最常见的数据库连接池——DBCP和C3P0
一、数据库连接池——DBCP
1.DBCP简绍
DBCP(DataBase Connection Pool)由Apache研发,而且Tomcat的连接池也正是采用DBCP实现的,该数据库连接池既可与应用服务器整合使用,也可由应用程序独立使用
2.DBCP的使用步骤
2.1 添加jar包
commons-dbcp.jar
commons-pool.jar
mysql-connector-java-5.0.8-bin.jar
导包位置如下:
2.2 在src下创建DBCP的配置文件dbcpconfig.properties
如下图所示dbcpconfig.properties配置的级别与src同级,文件名建议直接复制
dbcpconfig.properties里面的内容如下(只需要修改当前使用的数据库及数据库的use和password)
#<!-- 连接设置 -->
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名字
username=root
password=你的密码
#<!-- 初始化连接 -->
initialSize=10
#<!-- 最大连接数量 -->
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间(单位毫秒) -->
maxWait=50000
#<!-- 编码方式 -->
connectionProperties=useUnicode=true;characterEncoding=utf8
##<!-- 指定由连接池所创建的连接自动提交 -->
defaultAutoCommit=true
#<!-- 指定由连接池所创建的连接的事务级别 -->
defaultTransactionIsolation=REPEATABLE_READ
2.3 编写操作DBCP的工具类DBCPUtil
代码如下(DBCPUtil工具类):
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBCPUtil {
private static DataSource dataSource = null;
//创建数据库连接池
static{
Properties properties = new Properties();
try {
ClassLoader classLoader=DBCPUtil.class.getClassLoader();
properties.load(classLoader.getResourceAsStream("dbcpconfig.properties"));
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new ExceptionInInitializerError("DBCP始化错误,请检查配置文件");
}
}
//创建连接
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (Exception e) {
throw new RuntimeException("数据库连接错误");
}
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
2.4 在数据库中建立学生表并插入数据
代码如下(创建学生表的MySQL命令):
-- 创建表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
studentid INT,
studentname VARCHAR(50)
);
-- 插入数据
INSERT INTO student (studentid,studentname) VALUES (1,"张三");
INSERT INTO student (studentid,studentname) VALUES (2,"李四");
INSERT INTO student (studentid,studentname) VALUES (3,"王五");
INSERT INTO student (studentid,studentname) VALUES (4,"赵六");
INSERT INTO student (studentid,studentname) VALUES (5,"小明");
2.5 创建一个学生类(里面的元素与学生表的字段信息类型相同)
代码如下(学生类):
public class Student {
private int studentID;// 根据学生表创建学生ID
private String studentName; // 根据学生表创建学生姓名
public Student() {
}
public Student(int studentID, String studentName) {
this.studentID = studentID;
this.studentName = studentName;
}
public int getStudentID() {
return studentID;
}
public void setStudentID(int studentID) {
this.studentID = studentID;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
// 重写toString方法方便后续测试结果直观展示
@Override
public String toString() {
return "Student [studentID=" + studentID + ", studentName=" + studentName + "]";
}
}
2.6 使用DBCP测试
代码如下(DBCP测试):
public class Test01 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBCPUtil.getConnection();// 调用工具类DBCPUtil创建连接
preparedStatement = connection.prepareStatement("select * from student");// 创建preparedStatement及sql预编译
resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
Student student = new Student();
int id = resultSet.getInt("studentid");
String name = resultSet.getString("studentname");
student.setStudentID(id);
student.setStudentName(name);
System.out.println(student);
}
} catch (Exception e) {
}finally {
DBCPUtil.release(connection, preparedStatement, resultSet);// 调用工具类DBCPUtil里面的方法释放资源
}
}
}
代码如下(DBCP测试输出):
Student [studentID=1, studentName=张三]
Student [studentID=2, studentName=李四]
Student [studentID=3, studentName=王五]
Student [studentID=4, studentName=赵六]
Student [studentID=5, studentName=小明]
二、数据库连接池——C3P0
1.C3P0简绍
C3P0是目前最流行的开源数据库连接池之一,它支持 JDBC2和JDBC3的标准规范,易于扩展并且性能优越,著名的开源框架Hibernate和 Spring使用的数据源正是C3P0。
2.C3P0的使用步骤
2.1 添加jar包
c3p0-0.9.1.2.jar
mysql-connector-java-5.0.8-bin.jar
导包位置如下:
2.2 在src下创建C3P0的配置文件c3p0-config.xml
如下图所示c3p0-config.xml配置的级别与src同级,文件名建议直接复制
c3p0-config.xml里面的内容如下(只需要修改当前使用的数据库及数据库的use和password)
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/你当前使用的数据库名</property>
<property name="user">使用者名字</property>
<property name="password">密码</property>
<property name="initialPoolSize">15</property>
<property name="maxIdleTime">40</property>
<property name="maxPoolSize">150</property>
<property name="minPoolSize">20</property>
</default-config>
</c3p0-config>
2.3 编写操作C3P0的工具类C3P0Util
代码如下(C3P0Util工具类):
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Util {
//创建数据库连接池
private static DataSource dataSource = new ComboPooledDataSource();
//创建连接
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
connection = null;
}
}
}
2.4 在数据库中建立学生表并插入数据
代码如下(创建学生表的MySQL命令):
-- 创建表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
studentid INT,
studentname VARCHAR(50)
);
-- 插入数据
INSERT INTO student (studentid,studentname) VALUES (1,"张三");
INSERT INTO student (studentid,studentname) VALUES (2,"李四");
INSERT INTO student (studentid,studentname) VALUES (3,"王五");
INSERT INTO student (studentid,studentname) VALUES (4,"赵六");
INSERT INTO student (studentid,studentname) VALUES (5,"小明");
2.5 创建一个学生类(里面的元素与学生表的字段信息类型相同)
代码如下(学生类):
public class Student {
private int studentID;// 根据学生表创建学生ID
private String studentName; // 根据学生表创建学生姓名
public Student() {
}
public Student(int studentID, String studentName) {
this.studentID = studentID;
this.studentName = studentName;
}
public int getStudentID() {
return studentID;
}
public void setStudentID(int studentID) {
this.studentID = studentID;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
// 重写toString方法方便后续测试结果直观展示
@Override
public String toString() {
return "Student [studentID=" + studentID + ", studentName=" + studentName + "]";
}
}
2.6 使用C3P0进行测试
代码如下(C3P0测试):
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Test01 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = C3P0Util.getConnection();
preparedStatement = connection.prepareStatement("select * from student");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
Student student = new Student();
int id = resultSet.getInt("studentid");
String name = resultSet.getString("studentname");
student.setStudentID(id);
student.setStudentName(name);
System.out.println(student);
}
} catch (Exception e) {
}finally {
C3P0Util.release(connection, preparedStatement, resultSet);
}
}
}
代码如下(C3P0测试输出):
三月 08, 2021 7:40:23 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
三月 08, 2021 7:40:24 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
三月 08, 2021 7:40:24 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge2veag4w6lqb1ochemm|4fca772d, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge2veag4w6lqb1ochemm|4fca772d, idleConnectionTestPeriod -> 0, initialPoolSize -> 15, jdbcUrl -> jdbc:mysql://localhost:3306/db5, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 40, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 150, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 20, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
Student [studentID=1, studentName=张三]
Student [studentID=2, studentName=李四]
Student [studentID=3, studentName=王五]
Student [studentID=4, studentName=赵六]
Student [studentID=5, studentName=小明]
总结
其实不难看出这两种数据库连接池无论是使用过程、步骤甚至连导包都非常相识,基本只要掌握一个另一个也基本就懂了