数据库连接池
文章目录
C3P0
1. 导入jar包
- 导入
c3p0-0.9.5.2.jar
,mchange-commons-java-0.2.12.jar
和mysql-connector-java-5.1.37-bin.jar
(数据库的驱动jar包)到lib中 - 导入配置文件
c3p0-config.xml
到src中,修改地址
property name="jdbcUrl">jdbc:mysql://localhost:3306/db4</property>
2.1 简单打印
package cn.itcast.datasource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* c3p0的演示
*/
public class C3P0Demo1 {
public static void main(String[] args) throws SQLException {
DataSource ds=new ComboPooledDataSource();
Connection connection = ds.getConnection();
System.out.println(connection);
}
}
运行结果
三月 06, 2021 10:30:13 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
三月 06, 2021 10:30:13 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
三月 06, 2021 10:30:13 下午 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 -> 1hgff29ag27dalnxwvz83|6d5380c2, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgff29ag27dalnxwvz83|6d5380c2, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/db4, 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@7a92922 [wrapping: com.mysql.jdbc.JDBC4Connection@71f2a7d5]
2.2 切换到xml文件中的另一种数据源otherc3p0
package cn.itcast.datasource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* c3p0演示
*/
public class C3P0Demo2 {
public static void main(String[] args) throws SQLException {
DataSource ds = new ComboPooledDataSource("otherc3p0");
for (int i = 1; i <= 8; i++) {
System.out.println(i + " " + ds.getConnection());
}
}
}
运行结果
三月 06, 2021 10:31:48 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
三月 06, 2021 10:31:49 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
三月 06, 2021 10:31:49 下午 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 -> 1000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> otherc3p0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgff29ag27fc6boei9k9|6d5380c2, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/db3, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 8, 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@7a92922 [wrapping: com.mysql.jdbc.JDBC4Connection@71f2a7d5]
2 com.mchange.v2.c3p0.impl.NewProxyConnection@5474c6c [wrapping: com.mysql.jdbc.JDBC4Connection@4b6995df]
3 com.mchange.v2.c3p0.impl.NewProxyConnection@591f989e [wrapping: com.mysql.jdbc.JDBC4Connection@66048bfd]
4 com.mchange.v2.c3p0.impl.NewProxyConnection@445b84c0 [wrapping: com.mysql.jdbc.JDBC4Connection@61a52fbd]
5 com.mchange.v2.c3p0.impl.NewProxyConnection@63d4e2ba [wrapping: com.mysql.jdbc.JDBC4Connection@7bb11784]
6 com.mchange.v2.c3p0.impl.NewProxyConnection@7006c658 [wrapping: com.mysql.jdbc.JDBC4Connection@34033bd0]
7 com.mchange.v2.c3p0.impl.NewProxyConnection@7cdbc5d3 [wrapping: com.mysql.jdbc.JDBC4Connection@3aa9e816]
8 com.mchange.v2.c3p0.impl.NewProxyConnection@3834d63f [wrapping: com.mysql.jdbc.JDBC4Connection@1ae369b7]
Druid
1. 导入jar包
- 导入
druid-1.0.9.jar
到lib中 - 导入配置文件
druid.properties
到src中
2.1 简单打印
与C3P0
不同,需要手动加载配置文件
package cn.itcast.datasource.druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* Druid演示
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//加载配置文件
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(is);
//获取数据库连接池中的连接
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
Connection conn = ds.getConnection();
//打印输出
System.out.println(conn);
}
}
运行结果
三月 06, 2021 10:44:07 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
com.mysql.jdbc.JDBC4Connection@17ed40e0
2.2 在account中添加一条记录
工具类JDBCUtils
,
package cn.itcast.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 根据配置文件的信息,初始化数据库连接池对象
* 并附有返回数据库连接池对象方法,返回数据库连接对象方法,释放资源方法(含重载)
*/
public class JDBCUtils {
private static DataSource ds;
static {
try {
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接池对象中的某个数据库连接
*
* @return 返回连接池
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源
*
* @param conn 数据库连接对象
* @param stmt sql执行对象
*/
public static void close(Connection conn, Statement stmt) {
close(conn, stmt, null);
}
/**
* 释放资源
*
* @param conn 数据库连接对象
* @param stmt sql执行对象
* @param rs 结果集对象
*/
public static void close(Connection conn, Statement stmt, ResultSet rs) {
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();
}
}
}
/**
* 获取连接池对象
*
* @return 数据库连接池对象
*/
public static DataSource getDataSource() {
return ds;
}
}
测试类
package cn.itcast.datasource.druid;
import cn.itcast.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 使用新的工具类
*/
public class DruidDemo2 {
public static void main(String[] args) {
/**
* 在account中添加一条记录
*/
Connection conn = null;
PreparedStatement pst = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into account values(null,?,?)";
pst = conn.prepareStatement(sql);
pst.setString(1, "wangwu");
pst.setDouble(2, 3000);
pst.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.close(conn, pst);
}
}
}
运行前
运行后