闲来无事，写了一个MySql数据库连接池
工程结构如图
源码如下：
DBConnectioin.java如下
package com.database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class DBConnection {
private Connection conn = null; //数据库连接句柄
private Statement stm = null;
private boolean used = false; //该链接是否正被使用
private boolean inPools = true; //是否在连接池中
private ArrayList<String> batchSql = null; //批量处理sql
public DBConnection(){
}
/**
* Description 构造方法
* @param drivername 数据库驱动名称
* @param url 数据库地址
*/
protected DBConnection(String drivername, String url, String username,
String password){
try{
Class.forName(drivername);
conn = DriverManager.getConnection(url, username, password);
}
catch(Exception e){
e.printStackTrace();
}
}
/**
* Description 构造方法
* @param drivername 数据库驱动名称
* @param username 用户名
* @parma password 密码
* @param url 数据库地址
* @param inPools 是否在缓冲池里
*/
protected DBConnection(String drivername, String url, String username,
String password, boolean inPools){
try{
Class.forName(drivername).newInstance();
conn = DriverManager.getConnection(url, username, password);
this.inPools = inPools;
}
catch(Exception e){
e.printStackTrace();
}
this.inPools = inPools;
}
/**
* 关闭连接
*/
public void closeConn(){
try{
if(conn != null && !conn.isClosed()){
try{
conn.close();
conn = null;
}
catch(SQLException e){
e.printStackTrace();
}
}
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* @return the conn
*/
public Connection getConn() {
return conn;
}
/**
* @param conn the conn to set
*/
private void setConn(Connection conn) {
this.conn = conn;
}
/**
* FunName setAutoCommit
* Description 设置自动提交
* @param bool 是否自动提交
*/
public void setAutoCommit(boolean bool){
try{
if(conn.getAutoCommit() != bool){
conn.setAutoCommit(bool);
closeStm();
createStatement();
}
}
catch(Exception e){
e.printStackTrace();
}
}
/**
* @return the used
*/
protected boolean isUsed() {
return used;
}
/**
* @param used the used to set
*/
protected void setUsed(boolean used) {
this.used = used;
}
/**
* @return the inPools
*/
protected boolean isInPools() {
return inPools;
}
/**
* @param inPools the inPools to set
*/
protected void setInPools(boolean inPools) {
this.inPools = inPools;
}
/**
* FunName execute
* Description 执行sql语句
*/
public boolean execute(String sql){
boolean success = false;
try{
createStatement();
success = stm.execute(sql);
}
catch(Exception e){
e.printStackTrace();
success = false;
}
return success;
}
/**
* FunName addBatch
* Description 添加批量处理sql语句
*/
public void addBatch(String sql){
if(batchSql == null)
batchSql = new ArrayList();
batchSql.add(sql);
}
/**
* FunName batch
* Description 批量执行sql语句
*/
public boolean batch(){
try{
createStatement();
setAutoCommit(false);
stm.execute("START TRANSACTION;");
for(int i = 0; i < batchSql.size(); i++){
stm.addBatch(batchSql.get(i));
}
stm.executeBatch();
conn.commit();
}
catch(Exception e){
try{
conn.rollback();
}
catch(Exception el){
el.printStackTrace();
}
e.printStackTrace();
}
batchSql = null;
setAutoCommit(true);
return true;
}
/**
* FunName query
* Description 执行查询语句
* @param sql 查询语句
* @return 查询结果集ResultSet
*/
public ResultSet query(String sql){
ResultSet rs = null;
try{
createStatement();
rs = stm.executeQuery(sql);
}
catch(Exception e){
e.printStackTrace();
}
return rs;
}
/**
* FunName createStatement
* Description 创建Statement
*/
private void createStatement() throws Exception{
if(stm == null){
stm = conn.createStatement();
}
}
/**
* FunName closeStm
* Description 关闭Statement
*/
protected void closeStm(){
try{
if(stm != null)
stm.close();
}
catch(Exception e){
e.printStackTrace();
}
stm = null;
}
/**
* FunName closeRs
* Description 关闭Result
*/
public void closeRs(ResultSet rs){
try{
if(rs != null)
rs.close();
}
catch(Exception e){
e.printStackTrace();
}
}
}
DBConnectionPool.java如下
package com.database;
import java.sql.Connection;
import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;
/*
*
*/
public class DBConnectionPool {
/**
* 连接地址
*/
private static String url;
/**
* 字符编码
*/
private static String encoding;
/**
* 驱动程序
*/
private static String drivername;
/**
* 数据库名
*/
private static String database;
/*
* 用户名
*/
private static String username;
/*
* 密码
*/
private static String password;
/**
* 数据库连接池
*/
private static Vector<DBConnection> pools = new Vector();
/**
* 当前连接池可用连接句柄数据
*/
private static int currConnCount = 0;
/**
* 连接池最高连接数据
*/
private static int maxConnCount = 30;
/**
* 连接池最小连接数据
*/
private static int minConnCount = 10;
/**
* 连接池增长速度
*/
private static int createSpeed = 10;
/**
* 连接池
*/
private static DBConnectionPool dbConnectionPool = null;
/*
* FunName DBConnectionPool
* Description 构造方法
*/
private DBConnectionPool(){
ReadDBProperties readProperties = new ReadDBProperties();
Properties properties = readProperties.getProperties();
drivername = properties.getProperty("drivername"); //从资源文件中读取数据库驱动名称
database = properties.getProperty("database"); //从资源文件中读取数据库名称
username = properties.getProperty("username"); //从资源文件中读取用户名
password = properties.getProperty("password"); //从资源文件中读取密码
encoding = properties.getProperty("encoding"); //从资源文件中读取数据库编码
url = properties.getProperty("url")+"/"+database
+"?useUnicode=true&characterEncoding="+encoding;//从资源文件中读取数据库路径
int max = 5; //默认连接池中最大连接数
try{
max = Integer.parseInt(properties.getProperty("maxConnCount")); //从资源文件中读取最大连接数
maxConnCount = max;
}catch(Exception e){
e.printStackTrace();
}
int min = 1; //默认连接池中最小连接数
try{
min = Integer.parseInt(properties.getProperty("minConnCount")); //从资源文件中读取最小连接数
if(min > max)
min = max;
minConnCount = min;
}catch(Exception e){
e.printStackTrace();
}
int speed = 1; //默认数据库连接增长速度为1
try{
speed = Integer.parseInt(properties.getProperty("createSpeed"));//从资源文件中读取数据库连接增长速度
if(speed > max)
speed = max;
createSpeed = speed;
}catch(Exception e){
e.printStackTrace();
}
InitConnections();
}
/**
* FunName InitConnections
* Description 初始化连接池pools
*/
private void InitConnections(){
for(int i = 0; i < minConnCount; i++){
DBConnection dbConnection = new DBConnection(drivername, url, username, password);
pools.add(dbConnection);
}
currConnCount = minConnCount;
}
/**
* FunName createConnection
* Description 创建连接句柄
*/
private static void createConnection(){
int leaveCount = maxConnCount - pools.size(); //计算剩下多少个可创建的数据库连接
int createCount = createSpeed < leaveCount ? createSpeed : leaveCount; //计算此次创建多少个数据库连接
for(int i = 0; i < createCount; i++){
DBConnection dbConnection = new DBConnection(drivername, url, username, password);
pools.add(dbConnection);
}
currConnCount += createCount; //计算当前可用数据库连接
}
/**
* 得到一个连接句柄
* @return dbConnection
*/
public static DBConnection getDBConnection(){
//如果未初始化连接池，那么执行初始化
if(dbConnectionPool == null){
dbConnectionPool = new DBConnectionPool();
}
//连接池连接数量为0，配置文件出错
if(pools.size() == 0){
System.out.println("创建连接池失败，请检查配置文件!");
return null;
}
//遍历连接池，寻找一个可用连接
Iterator<DBConnection> it = pools.iterator();
while(it.hasNext()){
//从连接池中得到一个连接
DBConnection dbConnection = it.next();
//判断是否可用
if(!dbConnection.isUsed()){
//得到连接名柄
Connection connection = dbConnection.getConn();
try{
//如果连接名柄可用，从连接池中返回，否则销毁
if(connection!=null && !connection.isClosed()){
dbConnection.setUsed(true);
currConnCount--;
return dbConnection;
}
else{
pools.remove(dbConnection);
}
}catch(Exception e){
e.printStackTrace();
}
}
}
//遍历完连接池也找不到1个可用连接创建新的连接
//判断是否连接池中的连接数量是否还可增长，可增长则由连接池创建连接，否则返回额外的连接名柄
if(currConnCount <= 0 && pools.size() < maxConnCount){
createConnection();
}
else if(pools.size() >= maxConnCount){
return new DBConnection(drivername, url, username, password, false);
}
return new DBConnection(drivername, url, username, password, false);
}
/**
* FunName close
* Description 连接句柄dbConnection放回连接池
* @param dbConnection
*/
public static void close(DBConnection dbConnection){
//如果是连接池的连接，由连接池回收,否则自动销毁
if(dbConnection.isInPools()){
//设置连接处于空闲状态
dbConnection.setUsed(false);
dbConnection.closeStm();
currConnCount++;
}
else
dbConnection.closeConn();
}
/*
* Description 关闭连接池
*/
public static void closePools(){
Iterator<DBConnection> it = pools.iterator();
while(it.hasNext()){
DBConnection dbConnection = it.next();
dbConnection.closeConn();
}
pools.removeAllElements();
if(pools.size() == 0){
System.out.println("连接池关闭成功!");
}
}
/**
* FunName getCurrConnCount
* Description 返回连接池可用连接数
* @return 返回连接池可用连接数
*/
public static int getCurrConnCount() {
return currConnCount;
}
/**
* FunName getConnCount
* Description 返回连接池连接数
* @return 返回连接池连接数
*/
public static int getConnCount(){
return pools.size();
}
}
ReadDBProperties.java如下
package com.database;
import java.io.InputStream;
import java.util.Properties;
public class ReadDBProperties {
private Properties properties;
private String webUrl = "";
public ReadDBProperties(){
properties = new Properties();
InputStream in = null;
try{
Package pack = ReadDBProperties.class.getPackage(); //得到类的包对象
ClassLoader loader = ReadDBProperties.class.getClassLoader(); //得到类的加载路径对象
webUrl = loader.getResource("").getPath(); //得到类路径
webUrl = java.net.URLDecoder.decode(webUrl, "UTF-8"); //对路径进行编码
webUrl = webUrl.substring(1, webUrl.lastIndexOf("/classes/")); //得到web根路径
in = loader.getResourceAsStream("db_init.properties"); //得到数据库配置文件流
properties.load(in); //加载数据库配置文件
in.close(); //关闭文件流
in = null;
}
catch(Exception e){
e.printStackTrace();
}
}
public Properties getProperties() {
return properties;
}
public String getWebUrl(){
return webUrl;
}
}
DBTest.java如下
package com.database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class DBTest {
public static void main(String args[]){
DBConnection dbConn1 = DBConnectionPool.getDBConnection();
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
DBConnection dbConn2 = DBConnectionPool.getDBConnection();
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
DBConnection dbConn3 = DBConnectionPool.getDBConnection();
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
DBConnectionPool.close(dbConn1);
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
DBConnectionPool.close(dbConn2);
DBConnectionPool.close(dbConn3);
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
DBConnection dbConn = DBConnectionPool.getDBConnection();
ResultSet rs = dbConn.query("SELECT * FROM MEMBER");
try{
while(rs.next()){
System.out.println("用户ID：" + rs.getInt("mid"));
System.out.println("用户名：" + rs.getString("username"));
System.out.println("密码：" + rs.getString("password"));
}
}
catch(Exception e){
}
System.out.println("连接池：" + DBConnectionPool.getConnCount());
System.out.println("当前连接池：" + DBConnectionPool.getCurrConnCount());
System.out.println("-----------------");
dbConn.addBatch("INSERT INTO MEMBER (USERNAME, PASSWORD, EMAIL) VALUES ('TEST1', 'TEST1', 'TEST1@qq.com')");
dbConn.addBatch("INSERT INTO MEMBER (MID, USERNAME, PASSWORD, EMAIL) VALUES ('1', 'TEST2', 'TEST2', 'TEST1@qq.com')");
dbConn.batch();
DBConnectionPool.close(dbConn);
}
}
资源文件db_init.properties如下
url=jdbc:mysql://localhost:3306
database=mhome
encoding=UTF-8
drivername=com.mysql.jdbc.Driver
username=root
password=root
maxConnCount=2
minConnCount=1
createSpeed=1