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>());
    /**
     * 計時統(tǒng)計
     */
    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 + "進行定時清除超時的數(shù)據(jù)庫連接");
                        }
                        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("當前數(shù)據(jù)庫可用連接" + freeConnections.size());
                            System.out.println("----------[清除超時的線程進行清除...----------");
                            System.out.println("----------[清除超時的線程成功]----------");
                        }

                    }
                    // 30秒后執(zhí)行定時操作:每個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;

    // 構(gòu)建定時器：自動關(guān)閉超時的連接.

    /**
     * 獲取連接
     */
    public static int Try_Time = 0;

    // 只有這個構(gòu)造方法
    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("創(chuàng)建了一個新的鏈接");
            }

            if (conn != null) {
                return conn;
            }
        } catch (SQLException e) {
            if (LogUtil.isInfo()) {
                logger.info("獲取數(shù)據(jù)庫連接失敗" + e);
            }

        }
        // 使用連接數(shù)有可能數(shù)據(jù)庫已經(jīng)達到最大的連接
        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("[系統(tǒng)報告]:已用 " + isUsed + " 個連接，空閑連接個數(shù) " + 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++;
            // 記住內(nèi)存地址
            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 {
                    // 再次自身調(diào)用自己:可能已經(jīng)有空的連接存在
                    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("***[從新獲取數(shù)據(jù)庫連接的失敗次數(shù)過多]***");
                    // 多次不能獲得連接則返回null
                    if (LogUtil.isInfo()) {
                        System.out.println("重復嘗試獲取數(shù)據(jù)庫連接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("***[沒有可獲取的鏈接，正在重試...]***");
                    }

                    // 再次自身調(diào)用自己
                    Connection conn = getConnection();
                    if (conn != null) {
                        busyConnectionsMap.put(conn.toString(), new ConnectionAndStartTime(conn, new Date().getTime()));
                        return conn;
                    } else {
                        // 再次自身調(diào)用自己
                        return getConnection();
                    }
                }
            }

        }
        return null;

    }

    private synchronized void initConnection() {
        try {
            Class.forName(driverClass); // 加載驅(qū)動
            for (int i = 0; i < minConns; i++) {
                Connection conn = createNewConnection();
                if (conn != null) {
                    freeConnections.add(conn);
                } else {
                    throw new RuntimeException("獲取的數(shù)據(jù)庫連接為null");
                }

            }
            if (LogUtil.isInfo()) {
                logger.info("初始化數(shù)據(jù)庫" + minConns + "個連接放入連接池\n");
            }

        } catch (ClassNotFoundException e) {
            if (LogUtil.isInfo()) {
                logger.info("驅(qū)動無法加載，請檢查驅(qū)動是否存在，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("關(guān)閉鏈接失敗" + 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;
            }
        }
    }

    // =========================================================
    /**
     * 快速關(guān)閉資源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);
        }

    }

    /**
     * 快速關(guān)閉資源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;
            }
        }

    }

    /**
     * 快速關(guān)閉資源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);
        }

    }

    // =========================================================

    // =========================================================
    /**
     * 事務(wù)處理
     */
    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);
        }

    }
}

標簽： Mysql 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。