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