基于多线程的数据库连接池

最新推荐文章于 2024-07-19 05:17:15 发布

lihao8023

最新推荐文章于 2024-07-19 05:17:15 发布

阅读量3.5k

点赞数

文章标签：数据库连接池多线程 exception 数据库 null string

基于多线程的数据库连接池

并支持两种数据库 mssql 和 mysql：

1、ConnectPoool.java

-------------------------------------->> ConnectPoool.java>>>>>>>>>>>>>---------------------------------------------

package com.sinrow.epm.db;

import java.sql.*;
import java.util.*;
import java.io.*;

/**
* 
* Title:数据库连接池
* 
* 
* Description:本连接池支持对失效连接重连、重建连接池，并监控调用者及调用时间
* 本类需要在类路径根中存在db.properties数据库配置文件，文件内容示例：
* drivers=net.sourceforge.jtds.jdbc.Driver
* defaultpoolname.url=jdbc:jtds:sqlserver://127.0.0.1:1433/wapplat;charset=gb2312
* defaultpoolname.user=sa defaultpoolname.password=lutong
* defaultpoolname.maxconn=20
* 
* 
* Copyright: Copyright (c) 2005
* 
* 
* Company:lutong
* 
*
* @author <a href="mailto:qiang860818@163.com">DBoy</a>
* @version 1.0
*/

public class ConnectPool extends Thread {
/* 连接池，存放已创建但未使用的连接 */
private Stack pool = new Stack();

/* 被返回的数据库连接,将由线程检测并返回到池中 */
private Stack returnedPool = new Stack();

/* 已被取走、正在使用的连接 */
private Map using = new HashMap();

/* 已经创建连接池计数 */
private int created = 0;
/* 当前数据库类型 */
public static String dbtype;
/* JDBC Driver类 */
String drivers = null;

/* 数据库连接字符串 */
String url = null;

/* 数据库连接用户名 */
String user = null;

/* 数据库连接密码 */
String password = null;

/* 连接池最大数 */
int max = 100;

/* 连接池最小数（还未实现） */
// int min = 10;
/* 空闲连接等待释放的时间(秒) ，（还未实现） */
// int idleTime = 1800;
/* 是否需要停止线程 */
boolean stopThread = false;

/* 各种同步锁对象，据说byte[]对象创建时间最短，占资料最少 */
private byte[] createdLock = new byte[0];
private byte[] usingLock = new byte[0];
private byte[] poolLock = new byte[0];
private byte[] returnedPoolLock = new byte[0];

/* 单实例 */
private static ConnectPool instance = new ConnectPool();

/**
* 私有的构造方法，防止从外部直接实例化
*
*/
private ConnectPool() {
 /* 初始化数据库连接参数 */
init();
//
// /* 启动服务线程 */
 start();
}

/**
* 从外部取得本类实例的唯一方法
*
* @return ConnectPool
*/
public static ConnectPool getInstance() {
 return instance;
}

/**
* 从连接池中取得一个数据库连接如果池中已没有连接，则新创建一个连接被使用的连接放到using对象里
*
* @param caller
* @return
*/
public Connection getConnection(String poolname, String caller) {
 if (null == caller || caller.length() == 0) {
 StackTraceElement[] callerStackTrace = new Throwable()
 .getStackTrace();

 caller = callerStackTrace[1].toString();
 }
 Connection conn = null;
 try {
 synchronized (poolLock) {
 conn = (Connection) pool.pop();
 }
 } catch (EmptyStackException e) {
 conn = newConnection();
 }

 if (null != conn) {
 synchronized (usingLock) {
 using.put(conn, new UsingConnection(conn, caller));
 }
 }
 return conn;
}

/**
* 创建一个新的数据库连接
*
* @return
*/
private Connection newConnection() {
 Connection conn = null;
 try {
 if (created < max) {
 Class.forName(drivers);
 /* 张志强修改不同数据库获取不同链接 */
 if ("mysql".equals(this.dbtype)) {
 conn = DriverManager.getConnection(url);
 } else {
 conn = DriverManager.getConnection(url, user, password);
 }
 /* 不同数据库获取不同链接 */
 if (null != conn) {
 synchronized (createdLock) {
 created++;
 }
 }
 }
 } catch (Exception e) {
 e.printStackTrace();
 }
 return conn;
}

/**
* 返回一个数据库连接到池中，再由线程返回连接池
*
* @param conn
*/
public void freeConnection(Connection conn) {
 freeConnection(null, conn);
}

public void freeConnection(String poolName, Connection conn) {
 if (null != conn) {
 synchronized (returnedPoolLock) {
 returnedPool.push(conn);
 }
 }
}

/**
* 初始化数据库连接使用的参数
*
*/
private void init() {
 InputStream is = getClass().getResourceAsStream("/db.properties");
 Properties dbProps = new Properties();
 try {
 dbProps.load(is);
 } catch (Exception e) {
 System.err.println("不能读取properties文件. " + "请查看确保文件路径正确！");
 return;
 }
 this.dbtype = dbProps.getProperty("defaultdbtype");
 if ("mysql".equals(this.dbtype)) {
 drivers = dbProps.getProperty("mysql.drivers");
 url = dbProps.getProperty("mysql.defaultpoolname.url");
 } else if ("mssql".equals(this.dbtype)) {
 drivers = dbProps.getProperty("mssql.drivers").trim();
 url = dbProps.getProperty("mssql.defaultpoolname.url").trim();
 user = dbProps.getProperty("mssql.defaultpoolname.user").trim();
 password = dbProps.getProperty("mssql.defaultpoolname.password").trim();
 }
 max = Integer.parseInt(dbProps.getProperty("defaultpoolname.maxconn").trim());
}

/**
* 连接服务线程，主要作用：记录已取走的连接测试返回的连接是否可用
*/
public void run() {
 Connection conn = null;
 UsingConnection uc = null;
 while (true && !stopThread) {
 /*
 * 处理被返回的数据库连接判断连接是否由本池创建并发出的，如果不是则直接关闭
 * 如果是则测试连接是否有效，无效从池中删除，有效则返回池中
 */
 while (!returnedPool.empty()) {
 synchronized (returnedPoolLock) {
 conn = (Connection) returnedPool.pop();
 }
 synchronized (usingLock) {
 uc = (UsingConnection) using.get(conn);
 }
 if (null == uc) {
 try {
 conn.close();
 } catch (Exception e) {
 }
 }
 synchronized (usingLock) {
 using.remove(conn);
 }

 if (testOK(conn)) {
 synchronized (poolLock) {
 pool.add(conn);
 }
 } else {
 try {
 conn.close();
 } catch (Exception e) {
 }
 synchronized (createdLock) {
 created--;
 }
 }
 }

 conn = null;
 uc = null;
 /* 避免循环太快 */
 try {
 Thread.sleep(50);
 } catch (InterruptedException ie) {
 }
 }
 stopThread = false;
}

/**
* 测试连接是否正常
*
* @param conn
* @return
*/
public boolean testOK(Connection conn) {
 boolean result = false;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 pstmt = conn.prepareStatement("select 1");
 rs = pstmt.executeQuery();
 result = true;
 } catch (Exception e) {
 } finally {
 if (null != rs) {
 try {
 rs.close();
 } catch (Exception e) {
 }
 }
 if (null != pstmt) {
 try {
 pstmt.close();
 } catch (Exception e) {
 }
 }
 }
 return result;
}

/**
* 取得当前正使用的连接信息（HTML格式）
*
* @return
*/
public String getUsingHTMLInfo() {

 StringBuffer info = new StringBuffer();
 info.append("Driver:" + drivers + " ");
 info.append("Connect url:" + url + " ");
 synchronized (createdLock) {
 info.append("Created connection count:" + created + " ");
 }
 synchronized (usingLock) {
 info.append("Using connection count:" + using.values().size()
 + " ");
 info
 .append("<table border=1><tr><td>Caller</td><td>Using Time(ms)</td></tr>");
 Iterator it = using.values().iterator();

 UsingConnection uc = null;
 while (it.hasNext()) {
 uc = (UsingConnection) it.next();
 info.append("<tr><td>");
 info.append(uc.getCaller());
 info.append("</td><td>");
 info.append(uc.getUsingTime());
 info.append("</td></tr>");
 }
 }
 info.append("</table>");
 return info.toString();
}

/**
* 释放所有连接,创建新池
*/
public void release() {
 /* 要求停止线程 */
 stopThread = true;

 /* 停等待线程结束,线程结束时会把stopThread置为false */
 int timeout = 0;
 while (stopThread) {
 if (++timeout > 600) {
 break;
 }
 try {
 Thread.sleep(500);
 } catch (InterruptedException ie) {
 }
 }

 /* 创建新连接池实例 */
 instance = new ConnectPool();

 /* 释放所有连接，除了Using，Using是正在使用的且会在新的池里被释放 */
 synchronized (poolLock) {
 while (!pool.isEmpty()) {
 try {
 Connection conn = (Connection) pool.pop();
 conn.close();
 } catch (Exception e) {
 }
 }
 }

 synchronized (returnedPoolLock) {
 while (!returnedPool.isEmpty()) {
 try {
 Connection conn = (Connection) returnedPool.pop();
 conn.close();
 } catch (Exception e) {
 }
 }
 }
}
}

/**
* 用户存储当前被使用的数据库连接信息
*/
class UsingConnection {
private Connection conn = null;

private String caller = null;

long time = 0;

public UsingConnection(Connection conn, String caller) {
 this.conn = conn;
 this.caller = caller;
 time = System.currentTimeMillis();
}

public String getCaller() {
 return caller;
}

public long getUsingTime() {
 return System.currentTimeMillis() - time;
}
}
------------------------------------------------------------------------------------------------------------

2、DateSource.java

--------------------------------------------DateSource.java >>>>>>>>>---------------------------

/**
* 通过修改dbconfig.properties 获取 MYSQL 或 MSSQL 数据库的连接
* 此文件创建于20090325
*/
package com.sinrow.epm.db;

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

public class DataSource {
public static String type=ConnectPool.dbtype;

private DataSource() {

}

public static Connection getConnection() throws ClassNotFoundException,
 SQLException {
 ConnectPool cp=ConnectPool.getInstance();
 Connection conn =cp.getConnection(cp.getName(),
 cp.getName()+"caller");

 return conn;
}

public static boolean CloseDB(Connection conn,Statement pamStatement, ResultSet rs) {
 boolean flag = true;
 try {

 if (rs != null)
 rs.close();
 if (pamStatement != null)
 pamStatement.close();

 } catch (Exception e) {
 flag = false;
 e.printStackTrace();
 }
 finally{
 ConnectPool.getInstance().freeConnection(conn);

 }
 return flag;
}

}

3、配置文件db.properties 放置于 SRC包下。

--------------------------------db.properties》》》》》》》-----

mssql.drivers=net.sourceforge.jtds.jdbc.Driver
mysql.drivers=com.mysql.jdbc.Driver
mssql.defaultpoolname.url=jdbc:jtds:sqlserver://127.0.0.1:1433/mytest;charset=gb2312
mysql.defaultpoolname.url=jdbc:mysql://127.0.0.1:3306/test?user=root&password=root&useUnicode=true&characterEncoding=gbk
mssql.defaultpoolname.user=sa
mssql.defaultpoolname.password=sa
defaultpoolname.maxconn=40
defaultdbtype=mysql