java-mysql数据库连接池

闲来无事，写了一个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