支持超时自动断开因为异常导致的数据库连接没有关闭的情况
package org.apple.connectionpool;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
import java.util.logging.Logger;
public class DbConnectionManager {
private static DbConnectionManager dbConnectionManager = new DbConnectionManager();
private static Properties properties = new Properties();
private static DbConnectionPool pool = null;
static {
try {
properties.load(DbConnectionManager.class.getResourceAsStream("/org/apple/connectionpool/connectionpoll.properties"));
pool = new DbConnectionPool(properties.getProperty("driverClass").trim(), properties.getProperty("url").trim(), properties.getProperty("username").trim(), properties.getProperty("password").trim(), Integer.parseInt(properties.getProperty("minConns").trim()), Integer.parseInt(properties.getProperty("maxConns").trim()));
} catch (IOException e) {
e.printStackTrace();
}
}
public static DbConnectionManager getInstance() {
if (dbConnectionManager != null) {
return dbConnectionManager;
} else {
return new DbConnectionManager();
}
}
public static void main(String[] args) throws SQLException {
for (int i = 0; i < 23; i++) {
Connection connection = DbConnectionManager.getInstance().getConnection();
System.out.println(connection);
DbConnectionManager.getInstance().close(connection);
}
for (int i = 0; i < 10; i++) {
Connection connection = DbConnectionManager.getInstance().getConnection();
System.out.println(connection);
DbConnectionManager.getInstance().close(connection);
}
}
private DbConnectionManager() {
}
public void close(Connection conn) throws SQLException {
if (conn != null) {
pool.freeConnection(conn);
}
}
// ----------对外提供的方法----------
// ----------对外提供的方法----------
public Connection getConnection() {
return pool.getConnection();
}
public void releaseAll() {
pool.releaseAll();
}
}
class DbConnectionPool {
private final static Logger logger = Logger.getLogger(DbConnectionPool.class.getName());
private static Vector<Connection> freeConnections = new Vector<Connection>();
private static Map<String, ConnectionAndStartTime> busyConnectionsMap = Collections.synchronizedMap(new HashMap<String, ConnectionAndStartTime>());
/**
* 计时统计
*/
private static Timer timer = new Timer();
private static long timerCount = 0;
private static int timeOut = 30;
static {
// 另起一个线程
new Thread(new Runnable() {
public void run() {
timer.schedule(new TimerTask() {
@Override
public void run() {
if (LogUtil.isDebug()) {
logger.info("----------[清除超时的线程进行清除...----------");
}
if (LogUtil.isInfo()) {
System.out.println("----------[清除超时的线程进行清除...----------");
}
timerCount++;
if (timerCount >= 100000000) {
timerCount = 0;
}
if (LogUtil.isDebug()) {
System.out.println("第" + timerCount + "进行定时清除超时的数据库连接");
}
if (LogUtil.isDebug()) {
System.out.println("----------[清除超时的线程进行清除...----------");
}
Set<String> set = busyConnectionsMap.keySet();
Iterator<String> iterator = set.iterator();
String connectionAndTimeKeyArray = "";
int index = 0;
while (iterator.hasNext()) {
String connectionClassString = iterator.next();
ConnectionAndStartTime connectionAndTime = busyConnectionsMap.get(connectionClassString);
if (new Date().getTime() - connectionAndTime.getStartTime() > timeOut * 1000) {// 大于2分钟
if (index == 0) {
connectionAndTimeKeyArray += connectionClassString;
} else {
connectionAndTimeKeyArray += "," + connectionClassString;
}
index++;
}
}
// 清除
if (connectionAndTimeKeyArray != null && connectionAndTimeKeyArray != "") {
String[] connectionClassStringArray = connectionAndTimeKeyArray.split(",");
for (int i = 0; i < connectionClassStringArray.length; i++) {
if (busyConnectionsMap.get(connectionClassStringArray[i]) != null) {
System.out.println("connectionClassStringArray[i]" + connectionClassStringArray[i]);
busyConnectionsMap.remove(connectionClassStringArray[i]);
if (LogUtil.isDebug()) {
System.out.println("清除超时的Connection:" + connectionClassStringArray[i]);
}
isUsed--;
}
}
}
if (LogUtil.isDebug()) {
System.out.println("当前数据库可用连接" + freeConnections.size());
System.out.println("----------[清除超时的线程进行清除...----------");
System.out.println("----------[清除超时的线程成功]----------");
}
}
// 30秒后执行定时操作:每个10秒检查是否超时
}, 30 * 1000, 10 * 1000);
}
}).start();
if (LogUtil.isInfo()) {
System.out.println("超时处理Connection线程启动");
}
if (LogUtil.isInfo()) {
}
}
private String driverClass;
private String url;
private String username;
private String password;
private int minConns = 5;
private int maxConns = 20;
private static int isUsed = 0;
private int timeout = 1000;
// 构建定时器：自动关闭超时的连接.
/**
* 获取连接
*/
public static int Try_Time = 0;
// 只有这个构造方法
public DbConnectionPool(String driverClass, String url, String username, String password, int minConns, int maxConns) {
this.driverClass = driverClass;
this.url = url;
this.username = username;
this.password = password;
this.minConns = minConns;
this.maxConns = maxConns;
initConnection();
}
private Connection createNewConnection() {
try {
Connection conn = null;
conn = DriverManager.getConnection(url, username, password);
if (LogUtil.isInfo()) {
logger.info("创建了一个新的链接");
}
if (conn != null) {
return conn;
}
} catch (SQLException e) {
if (LogUtil.isInfo()) {
logger.info("获取数据库连接失败" + e);
}
}
// 使用连接数有可能数据库已经达到最大的连接
return null;
}
/**
* 释放连接入连接池
*/
public synchronized void freeConnection(Connection conn) throws SQLException {
if (conn != null && !conn.isClosed()) {
freeConnections.add(conn);
busyConnectionsMap.remove(conn.toString().trim());
if (isUsed >= 1) {
isUsed--;
}
notifyAll();
if (LogUtil.isInfo()) {
logger.info("释放连接!");
}
}
}
public synchronized Connection getConnection() {
if (LogUtil.isInfo()) {
System.out.println("[系统报告]:已用 " + isUsed + " 个连接，空闲连接个数 " + freeConnections.size());
}
// ==========第一种情况
if (freeConnections.size() >= 1) {
if (LogUtil.isInfo) {
System.out.println("[it has free connections]");
}
Connection conn = freeConnections.firstElement();
try {
if (conn.isClosed() || conn == null) {
// 新的连接代替无效连接
conn = createNewConnection();
}
} catch (SQLException e) {
conn = createNewConnection();
}
freeConnections.removeElementAt(0);
isUsed++;
// 记住内存地址
busyConnectionsMap.put(conn.toString().trim(), new ConnectionAndStartTime(conn, new Date().getTime()));
return conn;
}
if (freeConnections.size() <= 0) {
if (LogUtil.isInfo()) {
System.out.println("[now it is getting connection from db]");
}
// ==========第二种情况.1
if (isUsed < maxConns) {
Connection conn = createNewConnection();
if (conn != null) {
isUsed++;
busyConnectionsMap.put(conn.toString().trim(), new ConnectionAndStartTime(conn, new Date().getTime()));
return conn;
} else {
// 再次自身调用自己:可能已经有空的连接存在
return getConnection();
}
}
// ==========第二种情况.2
if (isUsed >= maxConns) {
if (LogUtil.isInfo) {
System.out.println("it has no more connections that is allowed for use");
}
Try_Time++;
if (LogUtil.isInfo) {
System.out.println("***[第" + Try_Time + "尝试从新获取连接]***");
}
if (Try_Time > 10) {
// throw new RuntimeException("***[从新获取数据库连接的失败次数过多]***");
// 多次不能获得连接则返回null
if (LogUtil.isInfo()) {
System.out.println("重复尝试获取数据库连接10次...???等待解决问题");
}
return null;
}
// 连接池已满
long startTime = System.currentTimeMillis();
try {
wait(timeout);
} catch (InterruptedException e) {
// e.printStackTrace();
}
if (new Date().getTime() - startTime > timeout) {
if (LogUtil.isInfo()) {
logger.info("***[没有可获取的链接，正在重试...]***");
}
// 再次自身调用自己
Connection conn = getConnection();
if (conn != null) {
busyConnectionsMap.put(conn.toString(), new ConnectionAndStartTime(conn, new Date().getTime()));
return conn;
} else {
// 再次自身调用自己
return getConnection();
}
}
}
}
return null;
}
private synchronized void initConnection() {
try {
Class.forName(driverClass); // 加载驱动
for (int i = 0; i < minConns; i++) {
Connection conn = createNewConnection();
if (conn != null) {
freeConnections.add(conn);
} else {
throw new RuntimeException("获取的数据库连接为null");
}
}
if (LogUtil.isInfo()) {
logger.info("初始化数据库" + minConns + "个连接放入连接池\n");
}
} catch (ClassNotFoundException e) {
if (LogUtil.isInfo()) {
logger.info("驱动无法加载，请检查驱动是否存在，driver: " + driverClass + e + "\n");
}
}
}
public synchronized void releaseAll() {
Enumeration<Connection> enums = freeConnections.elements();
while (enums.hasMoreElements()) {
try {
enums.nextElement().close();
} catch (SQLException e) {
if (LogUtil.isInfo()) {
logger.info("关闭链接失败" + e);
}
}
}
freeConnections.removeAllElements();
busyConnectionsMap.clear();
if (LogUtil.isInfo()) {
logger.info("释放了所有的连接");
}
}
}
/**
*
* 记录连接使用的时间
*
*/
class ConnectionAndStartTime {
private Connection conn;
private long startTime;
public ConnectionAndStartTime(Connection conn, long startTime) {
super();
this.conn = conn;
this.startTime = startTime;
}
public Connection getConn() {
return conn;
}
public long getStartTime() {
return startTime;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public void setStartTime(long startTime) {
this.startTime = startTime;
}
}
/**
*
* 记录日志
*
*/
class LogUtil {
public static boolean isDebug = true;
public static boolean isInfo = true;
public static boolean isDebug() {
return isDebug;
}
public static boolean isInfo() {
return isInfo;
}
}
/src/org/apple/connectionpool/connectionpoll.properties
driverClass=oracle.jdbc.driver.OracleDriver
url=jdbc\:oracle\:thin\:@172.18.2.95\:1521\:MYSQL
username=wjt
password=wjt
minConns=1
maxConns=3
package com.etc.oa.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apple.connectionpool.DbConnectionManager;
public class DBUtil {
// ==================================================
public static Connection getConnection() {
Connection conn = null;
conn = DbConnectionManager.getInstance().getConnection();
//conn = DriverManager.getConnection("jdbc:oracle:thin:@172.18.2.95:1521:MYSQL", "wjt", "wjt");
return conn;
}
// ==================================================
/**
* 建立PreparedStatement实例
*/
public static PreparedStatement createPreparedStatement(Connection conn, String sql) throws SQLException {
try {
if (sql != null && conn != null) {
PreparedStatement pstmt = conn.prepareStatement(sql);
if (pstmt != null) {
return pstmt;
}
}
} catch (SQLException e) {
throw e;
}
return null;
}
/**
* pstmt更新操作
*/
public static int pstmtExcuteUpdate(PreparedStatement pst) throws SQLException {
try {
if (pst != null) {
return pst.executeUpdate();
}
} catch (SQLException e) {
throw e;
}
return 0;
}
// ==================================================
// ==================================================
/**
* pstmt查询操作
*/
public static ResultSet pstmtExcuteQuery(PreparedStatement pst) throws SQLException {
try {
if (pst != null) {
ResultSet rs = pst.executeQuery();
if (rs != null) {
return rs;
}
}
} catch (SQLException e) {
throw e;
}
return null;
}
// ====================================================
// ====================================================
public static void close(Connection conn) throws SQLException {
DbConnectionManager.getInstance().close(conn);
}
public static void close(PreparedStatement pst) throws SQLException {
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
throw e;
}
}
}
public static void close(ResultSet rs) throws SQLException {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw e;
}
}
}
// =========================================================
/**
* 快速关闭资源ResultSet rs, PreparedStatement pstmt, Connection conn
*/
public static void close(ResultSet rs, PreparedStatement pst, Connection conn) throws SQLException {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw e;
}
}
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
throw e;
}
}
if (conn != null) {
DbConnectionManager.getInstance().close(conn);
}
}
/**
* 快速关闭资源ResultSet rs, PreparedStatement pstmt
*/
public static void close(ResultSet rs, PreparedStatement pst) throws SQLException {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw e;
}
}
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
throw e;
}
}
}
/**
* 快速关闭资源PreparedStatement pstmt, Connection conn
*/
public static void close(PreparedStatement pst, Connection conn) throws SQLException {
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
throw e;
}
}
if (conn != null) {
DbConnectionManager.getInstance().close(conn);
}
}
// =========================================================
// =========================================================
/**
* 事务处理
*/
public static void rollback(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
throw e;
}
}
}
public static void commit(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.commit();
} catch (SQLException e) {
throw e;
}
}
}
public static void setCommit(Connection conn, Boolean value) throws SQLException {
if (conn != null) {
try {
conn.setAutoCommit(value);
} catch (SQLException e) {
throw e;
}
}
}
public static void main(String[] args) throws SQLException {
Connection connection4 = DbConnectionManager.getInstance().getConnection();
DbConnectionManager.getInstance().close(connection4);
Connection connectiona = DbConnectionManager.getInstance().getConnection();
Connection connectionb = DbConnectionManager.getInstance().getConnection();
Connection connectionc = DbConnectionManager.getInstance().getConnection();
for (int i = 0; i < 10; i++) {
Connection connection8 = DbConnectionManager.getInstance().getConnection();
DbConnectionManager.getInstance().close(connection8);
}
}
}
开心洋葱 , 版权所有丨如未注明 , 均为原创丨未经授权请勿修改 , 转载请注明Java实现简单的数据库连接池代码！
![[原创] MYSQL 事务隔离级别实验](https://blog.75271.com/wp-content/themes/com75271/timthumb.php?src=https://blog.75271.com/wp-content/themes/com75271/css/img/pic/8.jpg&h=110&w=185&q=90&zc=1&ct=1)
