ConnectionManager.java package xupt.se.util;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ResourceBundle;import com.mchange.v2.c3p0.ComboPooledDataSource;import
package xupt.se.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
import com.alibaba.druid.pool.DruidDataSource;
/**
* 数据库连接操作类
* @author 张荣
*/
public final class ConnectionManager
{
private static ConnectionManager instance;
private static ComboPooledDataSource ds;
private static DruidDataSource dds;
//Druid初始化
static
{
ResourceBundle rb = ResourceBundle.getBundle("druid");
dds = new DruidDataSource();
try {
dds.setDriverClassName(rb.getString("driver"));
}catch (Exception e){
e.printStackTrace();
}
dds.setUrl(rb.getString("url"));
dds.setUsername(rb.getString("username"));
dds.setPassword(rb.getString("password"));
dds.setInitialSize(5);
dds.setMinIdle(1);
dds.setMaxActive(10);
}
// 初始化,只执行一次
static
{
ResourceBundle rb = ResourceBundle.getBundle("c3p0");
ds = new ComboPooledDataSource();
try
{
ds.setDriverClass(rb.getString("driver"));
}
catch(Exception e)
{
e.printStackTrace();
}
ds.setJdbcUrl(rb.getString("url"));
ds.setUser(rb.getString("username"));
ds.setPassword(rb.getString("password"));
}
/**
* 获取数据库实例
* @return 连接对象ConnectionManager
*/
public synchronized static final ConnectionManager getInstance()
{
if(instance == null)
{
try
{
instance = new ConnectionManager();
}
catch(Exception e)
{
e.printStackTrace();
}
}
return instance;
}
/**
* 获取数据库连接
* @return 数据库连接对象Connection
*/
public synchronized final Connection getDruidConnection(){
try
{
// 查看活动链接数
// System.out.println("------->busy connections: " + ds.getNumBusyConnections());
return dds.getConnection();
}
catch(SQLException e)
{
e.printStackTrace();
}
return null;
}
/**
* 获取数据库连接
* @return 数据库连接对象Connection
*/
public synchronized final Connection getConnection()
{
try
{
// 查看活动链接数
// System.out.println("------->busy connections: " + ds.getNumBusyConnections());
return ds.getConnection();
}
catch(SQLException e)
{
e.printStackTrace();
}
return null;
}
/**
* 关闭数据库连接
* @return void
*/
public static void close(ResultSet rs, Statement stmt, Connection con)
{
try
{
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(con != null)
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
/**
* 释放数据库资源
* @return void
*/
@Override
protected void finalize() throws Throwable
{
// 关闭datasource
DataSources.destroy(ds);
DataSources.destroy(dds);
super.finalize();
}
}
TestEmployeeDAO.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import xupt.se.util.ConnectionManager;
public class TestEmployeeDAO extends Thread
{
// 为什么定义成静态的?
private static ConnectionManager manager = ConnectionManager.getInstance();
public void runDruidPool(){
try
{
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
int count = 0;
try
{
con = manager.getDruidConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT count(*) FROM employee");
if(rs.next())
count = rs.getInt(1);
}
finally
{
ConnectionManager.close(rs, stmt, con);
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
// 使用连接池
public void run()
{
try
{
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
int count = 0;
try
{
con = manager.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT count(*) FROM employee");
if(rs.next())
count = rs.getInt(1);
}
finally
{
ConnectionManager.close(rs, stmt, con);
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
// 未使用连接池
public void runNoPool(int i)
{
String dbUrl = "jdbc:mysql://localhost:3306/ttms?useUnicode=true&characterEncoding=utf-8&useSSL=false";
try
{
Connection con = DriverManager.getConnection(dbUrl, "root", "198498");
Class.forName("com.mysql.jdbc.Driver");
Statement stmt = null;
ResultSet rs = null;
int count = 0;
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT count(*) FROM employee");
if(rs.next())
count = rs.getInt(1);
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
// 对使用线程池和不使用的比较
private void compare()
{
long start, end;
// -----------使用连接池-----------
start = System.currentTimeMillis();
TestEmployeeDAO[] test = new TestEmployeeDAO[1000];
try
{
for(int i = 0; i < test.length; i++)
{
test[i] = new TestEmployeeDAO();
test[i].start();
test[i].join();
}
}
catch(InterruptedException e)
{
e.printStackTrace();
}
end = System.currentTimeMillis();
System.out.println("使用连接池总用时= " + (end - start) + " ms");
// -----------使用Druid连接池-----------
start = System.currentTimeMillis();
TestEmployeeDAO[] test1 = new TestEmployeeDAO[1000];
try
{
for(int i = 0; i < test.length; i++)
{
test1[i] = new TestEmployeeDAO();
test1[i].runDruidPool();
}
}
catch(Exception e)
{
e.printStackTrace();
}
end = System.currentTimeMillis();
System.out.println("使用Druid连接池总用时= " + (end - start) + " ms");
//
// // -----------不使用连接池-----------
// start = System.currentTimeMillis();
// TestEmployeeDAO[] test2 = new TestEmployeeDAO[1000];
// for(int i = 0; i < test2.length; i++)
// {
// test2[i] = new TestEmployeeDAO();
// test2[i].runNoPool(i);
// }
// end = System.currentTimeMillis();
// System.out.println("不使用连接池总用时= " + (end - start) + " ms");
//
// System.out.println("over");
}
public static void main(String[] args)
{
// 测试速度
TestEmployeeDAO test = new TestEmployeeDAO();
test.compare();
// 正常使用DAO
// EmployeeDAO dao = (EmployeeDAO) DAOFactory.creatEmployeeDAO();
// ArrayList
list = dao.findEmployeeAll();
// for(Employee e : list)
// {
// System.out.println(e.getEmp_name() + " : " + e.getEmp_addr());
// }
}
}
druid.properties
#DB login parameters driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/ttms?useUnicode=true&characterEncoding=utf-8&useSSL=true username=root password=198498 #druid配置 filters=stat maxActive=20 initialSize=1 maxWait=60000 minIdle=10 maxIdle=15 timeBetweenEvictionRunsMillis=60000 minEvictableIdleTimeMillis=300000 validationQuery=SELECT 'x' testWhileIdle=true testOnBorrow=false testOnReturn=false maxOpenPreparedStatements=20 removeAbandoned=true removeAbandonedTimeout=1800 logAbandoned=true