Java数据库连接池

在执行数据库SQL语句时，我们先要进行数据连接；而每次创建新的数据库的连接要消耗大量的资源，这样，大家就想出了数据库连接池技术。它的原理是，在运行过程中，同时打开着一定数量的数据库连接，形成数据连接池，当需要用到数据连接时，就从中取出一个连接，完成某些SQL操作后，系统自动回收，以供其它用户（或进程）调用。

连接池和连接池管理类

DBConnectionManager.java

//以下为数据库连接池管理类的代码清单，为了保证在整个应用中的数据库连接池管理类的实例只有一个，因此构造函数是私有的，通过静态函数getInstance（）的到这个唯一的管理类实例。

package com.range0505;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Enumeration;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * used to manage DBConnectionPool
 * and the number of this class's instances is limited to be only one
 * so the constructor is private
 * getInstance() function is used to get the only instance of this class
 *
 * @author range0505
 * Aug 29, 2008 3:32:28 PM
 *
 * any question, please contact 'http://blog.csdn.net/range0505'
 */
public class DBConnectionManager {
   
    /**
     * log
     */
    private static final Log log = LogFactory.getLog(DBConnectionManager.class);

    /**
     * the very only DBConnectionManager instance
     */
    private static DBConnectionManager instance;
   
    /**
     * how many process get the DBConnectionManager instance and use the connectionPool
     */
    private static int clients = 0;
   
    /**
     * db driver
     */
    private Vector<Driver> drivers;
   
    /**
     * connection pool
     */
    private DBConnectionPool pool;
   
    /**
     * the props read from the configuration file which are used to create db connection
     */
    private Properties props;
   
    /**
     * the constructor
     * for that there must be only one DBConnectionManager instance, so the constructor is private
     */
    private DBConnectionManager() {
        init();
    }
   
    /**
     * get DBConnectionManager instance
     *
     * @return DBConnectionManager instance
     */
    public synchronized static DBConnectionManager getInstance() {
        if(instance == null) {
            instance = new DBConnectionManager();
        }
       
        ++clients;
        return instance;
    }
   
    /**
     * init DBConnectionManager
     * load db driver and create pool
     */
    private void init() {
        InputStream is = this.getClass().getResourceAsStream(
                "db.properties");
        try {
            props = new Properties();
            props.load(is);
        } catch (Exception e) {
            log.error("Cannot load db.properties", e);
        }
       
        loadDriver();
        createPool();
    }
   
    /**
     * load db driver
     */
    private void loadDriver() {
        drivers = new Vector<Driver>();
       
        String driverClasses = props.getProperty("driver");
        StringTokenizer st = new StringTokenizer(driverClasses);
        while (st.hasMoreElements()) {
            String driverClassName = st.nextToken().trim();
            try {
                Driver driver = (Driver)Class.forName(driverClassName).newInstance();
                DriverManager.registerDriver(driver);
                drivers.addElement(driver);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }
   
    /**
     * create db connectionPool
     *
     */
    private void createPool() {
        int minConn = Integer.parseInt(props.getProperty("minConnection"));
        int maxConn = Integer.parseInt(props.getProperty("maxConnection"));
       
        pool = new DBConnectionPool(props.getProperty("username"),
                props.getProperty("password"), props.getProperty("connectionURL"),
                minConn, maxConn);
    }
   
    /**
     * get a dbconnection from pool
     * @return db connection
     */
    public Connection getConnection() {
        int timeout = Integer.parseInt(props.getProperty("timeout"));
        int retry = Integer.parseInt(props.getProperty("retry"));
        Connection conn = null;
       
        if(pool != null) {
            conn = pool.getConnection();
        }
       
        if(conn == null) {
            for(int i = 0; i < retry; i++) {
                conn = getConnection(timeout);
                if(conn != null) {
                    break;
                }
            }           
        }
       
        return conn;
    }
   
    /**
     * if there's too much process to get connection from the pool
     * there might not be enough connections
     * so wait some time to reget it
     */
    private Connection getConnection(long timeout) {
        Connection conn = null;
       
        log.info("sleeping for " + timeout + " miniseconds to wait for an aviable connection");
        try {
            Thread.sleep(timeout);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
       
        conn = pool.getConnection();
       
        return conn;
    }
   
    /**
     * return connection to the pool
     * @param conn - connection about to return
     */
    public void freeConnection(Connection conn) {
        if(pool != null) {
            pool.freeConnection(conn);
        }
    }
   
    /**
     * release the pool
     * if there's still some process using the db connection, wait
     */
    public void release() {
        if(--clients != 0) {
            return;
        }       
       
        log.info("release DBConnectionPool and driver");
        if(pool != null) {
            pool.release();
            Enumeration allDrivers = drivers.elements();
            while (allDrivers.hasMoreElements()) {
                Driver driver = (Driver)allDrivers.nextElement();
                try {
                    DriverManager.deregisterDriver(driver);
                } catch (Exception ex) {
                    System.out.println("Can not deregister driver "
                            + driver.getClass().getName());
                }
            }
        }
    }
}


DBConnectionPool.java

//以下为数据库连接池类，它的很多方法，如获得连接，都是用synchronized关键字修饰的，是为了给函数加一个锁，使得两个进程不能同时运行这个函数，保证了数据库连接池相关数据的正确性。
构造函数中，首先创建min个连接，放入连接池。如果连接池里这些连接都用光了，那么再创建新的连接，最多到max个。min和max都可以在配置文件db.properties中进行设置。

package com.range0505;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * DBConnectionPool
 *
 * @author range0505
 * Aug 29, 2008 3:32:24 PM
 *
 * any question, please contact 'http://blog.csdn.net/range0505'
 */
public class DBConnectionPool {
   
    /**
     * log
     */
    private static final Log log = LogFactory.getLog(DBConnectionPool.class);
   
    /**
     * count how many connections are in use
     */
    private int inUsed = 0;
   
    /**
     * the container used to store the free connections
     */
    private ArrayList<Connection> freeConnections = new ArrayList<Connection>();
   
    /**
     * db username
     */
    private String username;
   
    /**
     * db password
     */
    private String password;
   
    /**
     * db url
     */
    private String url;
   
    /**
     * the min connection number permitted
     */
    private int minConn;
   
    /**
     * the max connection number premitted
     */
    private int maxConn;
   
    /**
     * constructor
     * store the db params
     * create minConn connections
     *
     * @param driver
     * @param username
     * @param password
     * @param url
     * @param minConn
     * @param maxConn
     */
    public DBConnectionPool(String username, String password, String url,
            int minConn, int maxConn) {
        this.username = username;
        this.password = password;
        this.url = url;
        this.minConn = minConn;
        this.maxConn = maxConn;
       
        for(int i = 0; i < this.minConn; i++) {
            freeConnections.add(newConnection());
        }
    }
   
    /**
     * getConnection
     *
     * @return a Connection got from pool
     */
    public synchronized Connection getConnection() {
        Connection conn = null;
       
        if(freeConnections.size() > 0) {
            conn = freeConnections.remove(0);
            if(conn == null) {
                conn = getConnection();
            }
        } else {
            conn = newConnection();
        }
       
        if(maxConn == 0 || maxConn < inUsed) {
            conn = null;
            log.error("there's no aviable connection in DBConnectionPool");
        }
       
        if(conn != null) {
            ++inUsed;
            log.debug("get a connection(" + conn + ") from DBConnectionPool, now there's " + inUsed + " connection(s) in use");
        }
       
        return conn;
    }
   
    /**
     * freeConnection
     *
     * @param conn - the connection about to return to the connection pool
     */
    public synchronized void freeConnection(Connection conn) {
        freeConnections.add(conn);
        --inUsed;
        log.debug("free a connection, now there's " + inUsed + " connection(s) in use");
    }
   
    /**
     * newConnection
     *
     * @return the newly created connection
     */
    private Connection newConnection() {
        Connection conn = null;
       
        try {
            conn = DriverManager.getConnection(url, username, password);
            log.debug("create a new connection");
        } catch(SQLException e) {
            log.error("new connection failed", e);
        }
       
        return conn;
    }
   
    /**
     * release the connection pool
     */
    public synchronized void release() {
        Iterator<Connection> allConns = freeConnections.iterator();
        while(allConns.hasNext()) {
            Connection conn = allConns.next();
            try {
                conn.close();
            } catch(SQLException e) {
                log.error("release DBConntionPool failed", e);
            }
        }
        freeConnections.clear();
        inUsed = 0;
    }
}


db.properties

driver=com.mysql.jdbc.Driver
username= test
password= test
connectionURL=jdbc:mysql://localhost:3306/test
minConnection=10
maxConnection=40
timeout=100
retry=5


测试

Test.java
import java.util.ArrayList;


public class Test {
    private static int size = 100;
   
    public static void main(String[] args) {
        ArrayList<TestThread> threads = new ArrayList<TestThread>();
        for(int i = 0; i < size; i++) {
            threads.add(new TestThread());
        }
        for(int i = 0; i < size; i++) {
            threads.get(i).start();
        }
    }
}


TestThread.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.range.dbconnection.DBConnectionManager;

public class TestThread extends Thread {
    private static final Log log = LogFactory.getLog(TestThread.class);
   
    public void run(){
        DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();
        Connection conn = dbConnectionManager.getConnection();
        Statement stat = null;
        ResultSet rs = null;
       
        try {
            stat = conn.createStatement();
            rs = stat.executeQuery("select * from user");
            while(rs.next()) {
                log.debug(rs.getString("fullname"));
            }
        } catch(SQLException e) {
            log.error("query failed", e);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
                stat.close();
            } catch(SQLException e) {
                log.error("close rs or stat failed", e);
            }
           
            dbConnectionManager.freeConnection(conn);
        }
       
        dbConnectionManager.release();
    }
}

以上为测试程序，采用多线程进行测试，可以从打印信息中看到当前连接池中的数据库连接的使用情况。当使用数达到配置文件中设置的40后，将会等待空闲连接， 重试次数也在配置文件中可以设置。
在我的机器上连接使用数先从1增到40，经过一段时间的等待，数字持续在40，最后降为0.
Test.java中的‘ private static int size = 100; ’，这里可以设置一共有多少线程，当数字大到一定程度，程序会抛异常，但是我相信在实际应用中100已经足够。