最近需要从一张表中查询大量数据,做了如下实验:
平台:
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毫秒。
所以:
如果只有少量的进程并发(单例在每个进程中都会有唯一的实例),并且需要连续执行查询(一般的应用可能不会有这种情况,但是在做数据处理、分析的时候可能会有),单例就很好,一旦建立连接速度很快。
如果是很多客户端并发,并且随机地访问数据库(比如一般的动态网站),那么用数据库连接池更好一些。