Java實現簡單的數據庫連接池代碼

javapp 9年前發布 | 5K 次閱讀 Java
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);
    }

}

}
</pre>
本文由用戶 javapp 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
本文地址：http://www.baiduhome.net/code/view/1425371451728
Java
Java實現簡單的數據庫連接池代碼

相關代碼

相關文檔

相關經驗

目錄
