参考梦阑的博客,网址:https://blog.csdn.net/qq_33356083/article/details/80300311
连接池的连接,最后一定要释放,即conn.close(); //此释放为释放到连接池中,假的释放。
如不释放,则当需要很多连接时,则会出现无连接可用的问题,所以一定要conn.close(); (连接池也需要conn.close())
需要包为:c3p0-0.9.5.2.jar、 mchange-commons-java-0.2.11.jar、mysql-connector-java-5.1.42.jar
package dbPool;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0ConnectionPool {
private ComboPooledDataSource cpds;
private static C3P0ConnectionPool c3pocp;
static{
System.out.println("1111111111111111");
c3pocp = new C3P0ConnectionPool();
}
public C3P0ConnectionPool() {
try {
System.out.println("2222222222222222");
cpds = new ComboPooledDataSource();
//加载配置文件
Properties props = new Properties();
props.load(C3P0Mysql.class.getClassLoader().getResourceAsStream("config.properties"));
cpds.setDriverClass(props.getProperty("DriverClass"));
cpds.setJdbcUrl(props.getProperty("JdbcUrl"));
cpds.setUser(props.getProperty("User"));
cpds.setPassword(props.getProperty("Password"));
cpds.setMaxPoolSize(Integer.parseInt(props.getProperty("MaxPoolSize")));
cpds.setMinPoolSize(Integer.parseInt(props.getProperty("MinPoolSize")));
cpds.setInitialPoolSize(Integer.parseInt(props.getProperty("InitialPoolSize")));
// 属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
// 如果maxStatements与maxStatementsPerConnection均为0,则缓存被关闭。Default: 0-->
// <property name="maxStatements">100</property>
// <!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
// <property name="maxStatementsPerConnection"></property>
cpds.setMaxStatements(Integer.parseInt(props.getProperty("MaxStatements")));
//<!--maxIdleTime:最大空闲时间,100秒内未使用则连接被丢弃。若为0则永不丢弃。-->
cpds.setMaxIdleTime(Integer.parseInt(props.getProperty("MaxIdleTime")));
} catch (Exception e) {
System.out.println("初始化线程池失败");
e.printStackTrace();
}
}
public static C3P0ConnectionPool getInstance(){
return c3pocp;
}
//获取连接对象
public Connection getConnection(){
Connection conn = null;
try {
conn = cpds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//获取连接对象
public ComboPooledDataSource getComboPooledDataSource(){
return cpds;
}
//获取
//连接测试
public static void test(){
Connection conn =null;
try {
conn = C3P0ConnectionPool.c3pocp.getConnection();
ComboPooledDataSource cpds1=C3P0ConnectionPool.c3pocp.getComboPooledDataSource();
System.err.println("总连接数为=" + cpds1.getNumConnectionsDefaultUser()+"忙连接为=" + cpds1.getNumBusyConnectionsDefaultUser()+"空闲连接为=" + cpds1.getNumIdleConnectionsDefaultUser());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close(); //连接池也需要,只是放回连接池
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
for(int i=0;i<10;i++){
test();
}
System.out.println("已经连接成功");
}
}