【JDBC单例】和【C3P0连接池基本用法】的对比

最近需要从一张表中查询大量数据，做了如下实验：

平台：

MySQL5.1.52（最大连接数设为1000）

CentOS 6.2

HP台式机（酷睿i5-2400 / 8G内存/ 500GB Sata3 硬盘）

在本机通过JDBC连接数据库

将基本的单例模式和基本的C3P0连接池的使用做了如下对比。

1、单例

代码如下：

package t2.dbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import t2.util.Configuration;

public class SingletonConn
{
	private Connection connection = null;
	private Statement statement = null;
	private ResultSet resultSet = null;
	private static String DBClassName = null;
	private static String DBUrl = null;
	private static String DBUser = null;
	private static String DBPassword  = null;
	
	private static SingletonConn instance = null;
	
	protected SingletonConn()
	{
	}
	
	static
	{
		try
		{
			Configuration configuration = new Configuration("db.conf");
			DBClassName = configuration.getItemValue("ClassName");
			DBUrl = configuration.getItemValue("URL");
			DBUser = configuration.getItemValue("User");
			DBPassword = configuration.getItemValue("Pass");
			
			Class.forName(DBClassName);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public static synchronized SingletonConn getInstance()
	{
		if(instance == null)
		{
			instance = new SingletonConn();
		}
		return instance;
	}
	
	private synchronized void getConnection() throws SQLException
	{
		//仅当connection失效时才重新获取
		if (connection == null || connection.isValid(10) == false)
		{
			connection = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
		}
	}
	
	private synchronized void getStatement() throws SQLException
	{
		getConnection();
		//仅当statement失效时才重新创建
		if (statement == null || statement.isClosed() == true)
		{
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		}
	}
	
	public synchronized void close() throws SQLException
	{
		if (resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		if (statement != null)
		{
			statement.close();
			statement = null;
		}
		if (connection != null)
		{
			connection.close();
			connection = null;
		}
	}
	
	public synchronized ResultSet executeQuery(String sql) throws SQLException
	{
		getStatement();
		if (resultSet != null && resultSet.isClosed() == false)
		{
			resultSet.close();
		}
		resultSet = null;
		resultSet = statement.executeQuery(sql);
		return resultSet;
	}

	public synchronized int executeUpdate(String sql) throws SQLException
	{
		int result = 0;
		getStatement();
		result = statement.executeUpdate(sql);
		return result;
	}

}

2、基本的C3P0代码：

package t2.dbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

import t2.util.Configuration;

public class PooledConn
{
	private static String DBClassName = null;
	private static String DBUrl = null;
	private static String DBUser = null;
	private static String DBPassword  = null;
	
	private static ComboPooledDataSource cpds = null;;
	
	private static PooledConn instance = null;
	
	protected PooledConn() { }
	
	static
	{
		try
		{
			Configuration configuration = 
					new Configuration("db.conf");
			DBClassName = configuration.getItemValue("ClassName");
			DBUrl = configuration.getItemValue("URL");
			DBUser = configuration.getItemValue("User");
			DBPassword = configuration.getItemValue("Pass");
			
			cpds = new ComboPooledDataSource();
			cpds.setDriverClass(DBClassName);
			cpds.setJdbcUrl(DBUrl);
			cpds.setUser(DBUser);
			cpds.setPassword(DBPassword);
			
			cpds.setInitialPoolSize(20);
			cpds.setMinPoolSize(10);
			cpds.setAcquireIncrement(5);
			cpds.setMaxPoolSize(50);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public static synchronized PooledConn getInstance()
	{
		if(instance == null)
		{
			instance = new PooledConn();
		}
		return instance;
	}
	
	public synchronized void close()
	{
		try
		{
			DataSources.destroy(cpds);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public synchronized ResultSet executeQuery(String sql)
	{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn = cpds.getConnection();
			st = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(sql);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return rs;
	}

	public synchronized int executeUpdate(String sql)
	{
		int result = 0;
		Connection conn = null;
		Statement st = null;
		try
		{
			conn = cpds.getConnection();
			st = conn.createStatement();
			result = st.executeUpdate(sql);
			st.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return result;
	}

}

3、测试程序：

package t2.dbc;

public class Conn extends SingletonConn
{
	private Conn() {}
}

package t2.test;

import java.sql.ResultSet;
import java.sql.SQLException;

import t2.dbc.Conn;


public class BDAccessTest
{
	public static void main (String[] args) throws SQLException
	{
		int i = 100000;
		long timer = System.currentTimeMillis();
		while (i-- > 0)
		{
			ResultSet rs =  Conn.getInstance().executeQuery("select * from t_query where f_id=" + i);
			while (rs.next());
		}
		timer = System.currentTimeMillis() - timer;
		System.out.println("处理时间：" + timer);
	}
}

4、结果

t_query表中有一个f_id字段、建立了主键和哈希索引，还有一个f_token字段为平均长度50的varchar。引擎类型为innodb

单例只会占用1个数据库连接，可以达到每秒7000-8000条的查询速度，并且不会有异常。

C3P0平均会占用最大连接数*80%的连接数，当查询速度达到每秒100条时，持续一分钟左右就会出现JDBC异常。

但是单例没有保持连接的能力，一旦超过一定时间没有使用（大约几百毫秒），连接就会被自动释放掉，每次新建连接都需要140毫秒左右的时间

而C3P0连接池会池化连接，随时取用，平均每次取用只需要10-20毫秒。

所以：

如果只有少量的进程并发（单例在每个进程中都会有唯一的实例），并且需要连续执行查询（一般的应用可能不会有这种情况，但是在做数据处理、分析的时候可能会有），单例就很好，一旦建立连接速度很快。

如果是很多客户端并发，并且随机地访问数据库（比如一般的动态网站），那么用数据库连接池更好一些。