- 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);
- }
- }
- }
来源: http://www.phpxs.com/code/1002344/