C3P0与DBCP连接池测试-优快云博客

本文链接：https://blog.youkuaiyun.com/sugarStop/article/details/73229013

本文通过配置C3P0和DBCP连接池并进行循环测试，记录了数据库连接的状态，旨在探究数据库连接中断的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景：最近数据库连接会不定时断开，找不到原因，可能是程序中使用的连接池问题，现在使用C3P0和DBCP两种连接池来进行测试

一、C3P0连接池

配置c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>  
<c3p0-config>  
    <named-config name="mySqlDataBase">  
        <property name="driverClass">com.mysql.jdbc.Driver</property>  
        <property name="jdbcUrl">jdbc:mysql://172.x.x.x:3306/test</property>  
        <property name="user">xxx</property>
        <property name="password">123456</property>  
        <property name="initialPoolSize">1</property>  
        <property name="maxIdleTime">70</property>  
        <property name="maxPoolSize">3</property>  
        <property name="minPoolSize">1</property>  
        <property name="maxStatements">200</property>  
    </named-config>  
</c3p0-config>

再创建一个jdbc.properties来存一些配置信息 (logpath是记录日志的路径，sql是测试的查询语句)

logpath = D://test/C3P0.txt
sql=select count(1) from dual

测试程序代码MysqlPool.java

package com.sugar;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * mySql数据库连接池
 * @author sugar
 *
 */
public class MysqlPool {
	static ComboPooledDataSource cpds=null;
	private static String LOGPATH;
	private static String SQL;
	static{
		cpds = new ComboPooledDataSource("mySqlDataBase");
		
		Properties prop=new Properties();         
    	try {
    		prop.load(new InputStreamReader(ComboPooledDataSource.class.getClassLoader().getResourceAsStream("jdbc.properties"), "UTF-8"));     
		} catch (IOException e) {
			e.printStackTrace();
		}
    	LOGPATH = prop.getProperty("logpath");
    	SQL = prop.getProperty("sql");
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConnection(){
		Connection conn = null;
		try {
			conn = cpds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 * @param ps
	 * @param rs
	 * @throws SQLException
	 */
	public static void close(Connection conn,PreparedStatement ps,ResultSet rs) throws SQLException{
		conn.close();
		ps.close();
		rs.close();
	}
	
	
	public static void main(String[] args) throws SQLException, IOException {
		final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		long t1 = System.currentTimeMillis();
		Connection conn = MysqlPool.getConnection();
		long t2 = System.currentTimeMillis();
		System.out.println(t2 - t1 +"毫秒");
		conn.close();
		
		File file = new File(LOGPATH);
     	Writer writer = null;
		writer = new FileWriter(file,true);
     	final BufferedWriter bw = new BufferedWriter(writer);
     	System.out.println("*******C3P0连接池*******");
     	System.out.println(sdf.format(new Date())+"连接成功，开始测试\r\n");
     	bw.write(sdf.format(new Date())+"连接成功，开始测试\r\n");
     	bw.flush();
		
		new Timer().schedule(new TimerTask(){
			public void run() {
				try {
					Connection conn = MysqlPool.getConnection();
					PreparedStatement ps = conn.prepareStatement(SQL);
					ResultSet rs = ps.executeQuery();
					while(rs.next()){
						try {
							System.out.println(sdf.format(new Date())+"查询结果："+rs.getInt(1));
							bw.write(sdf.format(new Date())+" 查询结果："+rs.getInt(1)+"\r\n");
							bw.flush();
						} catch (IOException e) {
						}
					}
					ps.close();
					rs.close();
					conn.close();
				} catch (SQLException e) {
					System.out.println(sdf.format(new Date())+"数据库连接断开");
					try {
						bw.write(sdf.format(new Date())+" 数据库连接断开\r\n");
						bw.write(sdf.format(new Date())+ e.getMessage() +" \r\n");
						bw.flush();
					} catch (IOException e1) {
					}
					e.printStackTrace();
				}
			}}, new Date(),300000);
	}
}

二、DBCP连接池

1、配置文件dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://172.x.x.x:3306/test
username = x
password =x
maxActive=300
maxIdle=150
minIdle=50
maxWait=3000
initialSize=1
removeAbandoned=true
removeAbandonedTimeout=180
logAbandoned=true

sql=select count(1) from testgather
#logpath = D://dbcp.txt
logpath = E://tangting_space/kingbaselog/DBCP_4.txt

2、测试代码

package com.sugar;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class ConnectionPool {
	private static DataSource  dataSource = null;
	private static String LOGPATH = null;
	private static String SQL = null;
	static{
		Properties prop = new Properties();
		try {
			prop.load(new InputStreamReader(ConnectionPool.class.getClassLoader().getResourceAsStream("dbcp.properties"), "UTF-8"));
			LOGPATH = prop.getProperty("logpath");
			SQL = prop.getProperty("sql");
			dataSource = BasicDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接
	 * @return
	 */
	public static Connection getConnection(){
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
		}
		return conn;
	}
	
	/**
	 * 关闭连接和数据集
	 * @param conn
	 * @param ps
	 * @param rs
	 */
	public static void close(Connection conn,PreparedStatement ps,ResultSet rs){
		try {
			conn.close();
			ps.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws SQLException, IOException {
		final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		long t1 = System.currentTimeMillis();
		Connection conn = ConnectionPool.getConnection();
		long t2 = System.currentTimeMillis();
		conn.close();
		System.out.println(t2 - t1 +"毫秒");
		File file = new File(LOGPATH);
     	Writer writer = null;
		writer = new FileWriter(file,true);
     	final BufferedWriter bw = new BufferedWriter(writer);
     	bw.write(sdf.format(new Date())+" 连接成功，开始测试\r\n");
     	System.out.println("*******DBCP连接池*******");
     	System.out.println(sdf.format(new Date())+"连接成功，开始测试\r\n");
     	bw.flush();
		
		new Timer().schedule(new TimerTask(){
			public void run() {
				try {
					Connection conn = ConnectionPool.getConnection();
					PreparedStatement ps = conn.prepareStatement(SQL);
					ResultSet rs = ps.executeQuery();
					while(rs.next()){
						try {
							System.out.println(sdf.format(new Date())+" 查询结果："+rs.getInt(1));
							bw.write(sdf.format(new Date())+" 查询结果："+rs.getInt(1)+"\r\n");
							bw.flush();
						} catch (IOException e) {
						}
					}
					ps.close();
					rs.close();
					conn.close();
				} catch (SQLException e) {
					System.out.println(sdf.format(new Date())+"数据库连接断开");
					try {
						bw.write(sdf.format(new Date())+" 数据库连接断开\r\n");
						bw.write(sdf.format(new Date())+ e.getMessage() +" \r\n");
						bw.flush();
					} catch (IOException e1) {
					}
					e.printStackTrace();
				}
			}}, new Date(),300000);
	}
}

上面两个程序的作用，使用c3p0和dbcp两个连接池管理连接，然后循环测试数据库查询，并将日志记录到指定的目录中，测试数据库连接在连接池中会不会中断

源码不知道怎么传