查找到的简单工具类:c3p0配置文件,连接池工具类DataSourceUtils,需要的jar包:mysql-connector,c3p0,commons等jar包
c3p0-config.xml
<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="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!-- 命名的配置 -->
<named-config name="itcast">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/xxxx</property>
<property name="user">root</property>
<property name="password">1234</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">40</property>
<property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
DataSourceUtils.java
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 DataSourceUtils {
private static ComboPooledDataSource ds=new ComboPooledDataSource();
private static ThreadLocal<Connection> tl=new ThreadLocal<>();
/**
* 获取数据源
* @return 连接池
*/
public static DataSource getDataSource(){
return ds;
}
/**
* 从当前线程上获取连接
* @return 连接
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
Connection conn = tl.get();
if(conn==null){
//第一次获取 创建一个连接 和当前的线程绑定
conn=ds.getConnection();
//绑定
tl.set(conn);
}
return conn;
}
/**
* 释放资源
*
* @param conn
* 连接
* @param st
* 语句执行者
* @param rs
* 结果集
*/
public static void closeResource(Connection conn, Statement st, ResultSet rs) {
closeResource(st, rs);
closeConn(conn);
}
public static void closeResource(Statement st, ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
}
/**
* 释放连接
*
* @param conn
* 连接
*/
public static void closeConn(Connection conn) {
if (conn != null) {
try {
conn.close();
//和当前的线程解绑
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
/**
* 释放语句执行者
*
* @param st
* 语句执行者
*/
public static void closeStatement(Statement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st = null;
}
}
/**
* 释放结果集
*
* @param rs
* 结果集
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
}
/**
* 开启事务
* @throws SQLException
*/
public static void startTransaction() throws SQLException{
//获取连接//开启事务
getConnection().setAutoCommit(false);;
}
/**
* 事务提交
*/
public static void commitAndClose(){
try {
//获取连接
Connection conn = getConnection();
//提交事务
conn.commit();
//释放资源
conn.close();
//解除绑定
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 事务回滚
*/
public static void rollbackAndClose(){
try {
//获取连接
Connection conn = getConnection();
//事务回滚
conn.rollback();
//释放资源
conn.close();
//解除绑定
tl.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}