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); } } }
