• 欢迎访问开心洋葱网站,在线教程,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入开心洋葱 QQ群
  • 为方便开心洋葱网用户,开心洋葱官网已经开启复制功能!
  • 欢迎访问开心洋葱网站,手机也能访问哦~欢迎加入开心洋葱多维思维学习平台 QQ群
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏开心洋葱吧~~~~~~~~~~~~~!
  • 由于近期流量激增,小站的ECS没能经的起亲们的访问,本站依然没有盈利,如果各位看如果觉着文字不错,还请看官给小站打个赏~~~~~~~~~~~~~!

Java实现简单的数据库连接池代码

Mysql 水墨上仙 2692次浏览

支持超时自动断开因为异常导致的数据库连接没有关闭的情况

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实现简单的数据库连接池代码
喜欢 (0)
加载中……