2020-09-04
422
原创
数据库连接池---C3P0与Druid
1.什么是数据库连接池？
【问题】：当有多个线程，每个线程都需要连接数据库执行SQL语句的话，那么每个线程都会创建一个连接，并且在使用完毕后，关闭连接。创建连接和关闭连接的过程也是比较消耗时间的，当多线程并发的时候，系统就会变得很卡顿。同时，一个数据库同时支持的连接总数也是有限的，如果多线程并发量很大，那么数据库连接的总数就会被消耗光，后续线程发起的数据库连接就会失败。
数据库连接池：其实就是一个容器(集合)，存放数据库连接的容器。系统初始化后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。
使用数据库连接池的好处：节约资源(避免重复创建连接)、用户访问高效(提升程序执行效率)
实现：
- 标准接口DataSource：javax.sql包下
- 获取连接：getConnection()
- 归还连接：如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了，而是归还连接。
- 一般我们不去实现它，有数据库厂商来实现
- C3P0：数据库连接池技术（比较老的技术）
- Druid：数据库连接池实现技术，由阿里巴巴提供（最好的数据库连接池之一）
2.C3P0数据库连接池
2.1 C3P0使用步骤
- 导入jar包：c3p0-0.9.5.2.jar、mchange-commons-java-0.2.12.jar
- 定义配置文件：文件名必须为c3p0.properties 或者 c3p0-config.xml，路径将文件放在src目录下
- 创建数据库连接池对象：ComPooledDataSource
- 获取连接：getConnection
【注意】：不要忘记导入数据库的驱动jar包：mysql-connector-java-5.0.8-bin.jar
2.2 导入jar包
在项目中新键libs文件夹，将两个jar包复制进去并添加进入。
2.3 定义配置文件
直接将配置文件c3p0-config.xml复制到src根目录下，更改里面的参数情况，如下所示：
<c3p0-config>
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">19991204</property>
<!-- 连接池参数 -->
<!-- 初始化申请的连接数量 -->
<property name="initialPoolSize">5</property>
<!-- 最大的连接数量 -->
<property name="maxPoolSize">10</property>
<!-- 超时时间：3s -->
<property name="checkoutTimeout">3000</property>
</default-config>
<named-config name="otherc3p0">
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">19991204</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
</c3p0-config>
2.4 创建数据库连接池对象、获取连接
利用ComPooledDataSource()与getConnection()
package Datasource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Demo01 {
public static void main(String[] args) throws SQLException {
// 1.创建数据库连接池对象
DataSource ds = new ComboPooledDataSource();
// 2.获取连接对象
Connection conn = ds.getConnection();
// 3.打印
System.out.println(conn);
}
}
九月 04, 2020 8:25:11 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
九月 04, 2020 8:25:12 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
九月 04, 2020 8:25:12 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> z8kfltacopru28lbmcrv|5a2e4553, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltacopru28lbmcrv|5a2e4553, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/test, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@224aed64 [wrapping: com.mysql.jdbc.Connection@c39f790]
【说明】：第1~6行是日志信息，第7行是正确打印的对象。
2.5 测试配置文件中的参数
直接根据之前的配置文件进行测试：
package Datasource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Demo02 {
public static void main(String[] args) throws SQLException {
// 1.创建数据库连接池对象
DataSource ds = new ComboPooledDataSource();
// 2.获取连接对象
//测试配置文件中的最大获取10个连接对象
for(int i = 1; i <= 10; i++){
Connection conn = ds.getConnection();
//打印
System.out.println(i + ":" + conn);
}
}
}
九月 04, 2020 8:38:43 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
九月 04, 2020 8:38:43 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> z8kfltacoq97o514gmwzm|28c97a5, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltacoq97o514gmwzm|28c97a5, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/test, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
1:com.mchange.v2.c3p0.impl.NewProxyConnection@224aed64 [wrapping: com.mysql.jdbc.Connection@c39f790]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@2ac1fdc4 [wrapping: com.mysql.jdbc.Connection@5f150435]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@50cbc42f [wrapping: com.mysql.jdbc.Connection@75412c2f]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@13b6d03 [wrapping: com.mysql.jdbc.Connection@f5f2bb7]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@64c64813 [wrapping: com.mysql.jdbc.Connection@3ecf72fd]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@21a06946 [wrapping: com.mysql.jdbc.Connection@77f03bb1]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@25618e91 [wrapping: com.mysql.jdbc.Connection@7a92922]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@2cfb4a64 [wrapping: com.mysql.jdbc.Connection@5474c6c]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@2fc14f68 [wrapping: com.mysql.jdbc.Connection@591f989e]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@61443d8f [wrapping: com.mysql.jdbc.Connection@445b84c0]
【说明】：配置文件中最大的连接数量为10，经过测试可以打印出10个。
如果让程序中申请11个连接，那么会出现什么现象？
for(int i = 1; i <= 11; i++){
Connection conn = ds.getConnection();
System.out.println(i + ":" + conn);
}
九月 04, 2020 8:41:14 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
九月 04, 2020 8:41:15 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
九月 04, 2020 8:41:15 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> z8kfltacoqchjvp2kar9|5a2e4553, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltacoqchjvp2kar9|5a2e4553, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/test, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
1:com.mchange.v2.c3p0.impl.NewProxyConnection@224aed64 [wrapping: com.mysql.jdbc.Connection@c39f790]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@2ac1fdc4 [wrapping: com.mysql.jdbc.Connection@5f150435]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@50cbc42f [wrapping: com.mysql.jdbc.Connection@75412c2f]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@13b6d03 [wrapping: com.mysql.jdbc.Connection@f5f2bb7]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@64c64813 [wrapping: com.mysql.jdbc.Connection@3ecf72fd]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@21a06946 [wrapping: com.mysql.jdbc.Connection@77f03bb1]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@25618e91 [wrapping: com.mysql.jdbc.Connection@7a92922]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@2cfb4a64 [wrapping: com.mysql.jdbc.Connection@5474c6c]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@2fc14f68 [wrapping: com.mysql.jdbc.Connection@591f989e]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@61443d8f [wrapping: com.mysql.jdbc.Connection@445b84c0]
Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
【说明】：只能打印10个，第11个会报错，超过了10个最大申请数量。
如果我们让第5个提前归还，那么会成功吗？
package Datasource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Demo02 {
public static void main(String[] args) throws SQLException {
// 1.创建数据库连接池对象
DataSource ds = new ComboPooledDataSource();
// 2.获取连接对象
// 测试配置文件中的最大获取10个连接对象
for(int i = 1; i <= 11; i++){
Connection conn = ds.getConnection();
System.out.println(i + ":" + conn);
if(i == 5){
conn.close(); //第5个归还到连接池
}
}
}
}
九月 04, 2020 8:44:45 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
九月 04, 2020 8:44:46 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.5 [built 11-December-2019 22:07:46 -0800; debug? true; trace: 10]
九月 04, 2020 8:44:46 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> z8kfltacoqh06nb2q84w|5a2e4553, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltacoqh06nb2q84w|5a2e4553, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/test, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
1:com.mchange.v2.c3p0.impl.NewProxyConnection@224aed64 [wrapping: com.mysql.jdbc.Connection@c39f790]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@2ac1fdc4 [wrapping: com.mysql.jdbc.Connection@5f150435]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@50cbc42f [wrapping: com.mysql.jdbc.Connection@75412c2f]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@13b6d03 [wrapping: com.mysql.jdbc.Connection@f5f2bb7]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@64c64813 [wrapping: com.mysql.jdbc.Connection@3ecf72fd]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@326de728 [wrapping: com.mysql.jdbc.Connection@25618e91]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@71f2a7d5 [wrapping: com.mysql.jdbc.Connection@3ecf72fd]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@5474c6c [wrapping: com.mysql.jdbc.Connection@4b6995df]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@591f989e [wrapping: com.mysql.jdbc.Connection@66048bfd]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@445b84c0 [wrapping: com.mysql.jdbc.Connection@61a52fbd]
11:com.mchange.v2.c3p0.impl.NewProxyConnection@63d4e2ba [wrapping: com.mysql.jdbc.Connection@7bb11784]
【说明】：如果前面10个中有提前归还的连接，第11个便可以正常。
【注意】：我们还有可以通过指定DataSource中的参数：名称配置
public static void main(String[] args) throws SQLException {
// 1.创建数据库连接池对象
DataSource ds = new ComboPooledDataSource("otherc3p0"); //名称配置
// 2.获取连接对象
for(int i = 1; i <= 11; i++){
Connection conn = ds.getConnection();
System.out.println(i + ":" + conn);
}
}
3.Druid数据库连接池
3.1 Druid使用步骤
- 导入jar包：druid-1.0.9.jar
- 定义配置文件：.properties形式、可以叫任意名称、可以放置在任意的目录下
- 加载配置文件：Properties
- 创建数据库连接池对象：DruidDataSourceFactory
- 获取连接：getConnection
3.2 导入jar包
在项目中新键libs文件夹，将jar包复制进去并添加进入。
3.3 定义配置文件
命名为：druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=19991204
initialSize=5
maxActive=10
maxWait=3000
3.4 加载配置文件、创建数据库连接池对象、获取连接
package DataSource_Driod;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidDemo01 {
public static void main(String[] args) throws Exception {
// 1.导入jar包
// 2.定义配置文件
// 3.加载配置文件
Properties pro = new Properties();
InputStream is = DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
// 4.获取连接池对象
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
// 5.获取连接
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
九月 04, 2020 9:01:12 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
com.mysql.jdbc.Connection@3caeaf62
3.5 定义工具类
(1)定义一个类：JDBCUtils
(2)提供静态方法块加载配置文件，初始化连接池对象
(3)提供方法
- 获取连接方法：通过数据库连接池获取连接
- 释放资源
- 获取连接池的方法
编写JDBCUtils工具类：
package utils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.util.JdbcUtils;
public class JDBCUtils {
// 1.定义成员变量 DataSource
private static DataSource ds;
// 静态代码块
static{
try {
//1.加载配置文件
Properties pro = new Properties();
pro.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
//2.获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取链接
public static Connection getConnection() throws SQLException{
return ds.getConnection();
}
// 释放资源：2参数
public static void close(Statement stmt, Connection conn){
close(null, stmt, conn);
}
// 释放资源：3参数
public static void close(ResultSet rs, Statement stmt, Connection conn){
if(rs != null){
try {
rs.close(); //归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close(); //归还连接
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 获取连接池的方法
public static DataSource getDatSource(){
return ds;
}
}
测试JDBCUtils工具类，简化代码，添加一条数据：
package DataSource_Driod;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import utils.JDBCUtils;
public class DruidDemo02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try{
//1.获取连接
conn = JDBCUtils.getConnection();
//2.定义SQL
String sql = "insert into sales values(?, ?)";
//3.获取pstmt对象
pstmt = conn.prepareStatement(sql);
//4.给？赋值
pstmt.setString(1, "toM");
pstmt.setInt(2, 30000);
//5.执行SQL
int count = pstmt.executeUpdate();
System.out.println("一共影响" + count + "行");
} catch(SQLException e){
e.printStackTrace();
} finally{
JDBCUtils.close(pstmt, conn);
}
}
}
九月 04, 2020 9:07:52 下午 com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
信息: {dataSource-1} inited
一共影响1行
Java
MySql
- 作者：李延松(联系作者)
- 发表时间：2020-09-04 21:25
- 版本声明:自由转载-非商用-非衍生-保持署名(创意共享3.0许可证)
- 公众号转载:请在文末添加作者公众号二维码
评论