java 简易版本数据库连接池

写了个 Java数据库连接池，具备基本的功能点：
　　1、对池中活动连接的重用。
　　2、池满时的适时等待。
　　3、对空闲连接的适时关闭。

　　抛砖引玉，走过路过，不吝赐教。

 

DBConnection.java如下：

 

package db;

import java.sql.Connection;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 * 封装的连接
 * @author Linkwork, [e-mail]
 * @since 2014年11月01日
 */
public class DBConnection {
    
    /**
     * 原生的连接
     */
    private Connection connection = null;
    
    /**
     * 是否空闲
     */
    private AtomicBoolean idle = null;
    
    /**
     * 最近一次的空闲开始时间
     */
    private volatile long idleStart = 0L;
    
    /**
     * 标识
     */
    private int index = -1;
    
    /**
     * 构造函数
     * @param index 标识
     * @param connection 连接
     * @param idle 是否空闲
     */
    public DBConnection(int index, Connection connection, boolean idle) {
        this.index = index;
        this.connection = connection;
        this.idle = new AtomicBoolean(idle);
    }
    
    /**
     * 释放
     */
    public void release() {
        if (this.idle.compareAndSet(false, true)) {
            this.idleStart = System.currentTimeMillis();
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public AtomicBoolean getIdle() {
        return idle;
    }
    
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public int getIndex() {
        return index;
    }

    public long getIdleStart() {
        return idleStart;
    }

    public void setIdleStart(long idleStart) {
        this.idleStart = idleStart;
    }
    
}

 DBConnectionPool.java如下：

 

 

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;

/**
 * 数据库连接池
 * @author Linkwork, [e-mail]
 * @since 2014年11月01日
 */
public class DBConnectionPool extends Thread {
    
    /**
     * 容量
     */
    private volatile int capacity = 1;
    
    /**
     * 驱动
     */
    private String driver = null;
    
    /**
     * 地址
     */
    private String url = null;
    
    /**
     * 用户名
     */
    private String user = null;
    
    /**
     * 密码
     */
    private String password = null;
    
    /**
     * 等待的轮询间隔
     */
    private volatile long waitInterval = 50L;
    
    /**
     * 等待的超时时间，默认 2分钟
     */
    private volatile long waitTimeout = 120000L;
    
    /**
     * 空闲的超时时间，默认 5分钟
     */
    private volatile long idleTimeout = 300000L;
    
    /**
     * 连接集
     */
    private Vector<DBConnection> dbconnectionLst = null;
    
    /**
     * 是否正在进行关闭
     */
    private volatile boolean closing = false;
    
    /**
     * 构造函数
     * @param capacity 容量
     * @param driver 驱动
     * @param url 地址
     * @param user 用户名
     * @param password 密码
     */
    public DBConnectionPool(
            int capacity,
            String driver,
            String url,
            String user,
            String password) {
        this.capacity = capacity;
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.password = password;
        this.dbconnectionLst = new Vector<DBConnection>();
    }
    
    /**
     * 获取 DB连接
     * @return
     */
    public DBConnection getDBConnectionQuietly() {
        try {
            return this.getDBConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 获取 DB连接
     * @return
     * @throws Exception
     */
    public DBConnection getDBConnection() throws Exception {
        long start = System.currentTimeMillis();
        // 当不是正在进行关闭
        while (! this.closing) {
            // 遍历连接集，获取空闲、可用的连接
            for (DBConnection dbconnection: this.dbconnectionLst) {
                if (dbconnection.getIdle().compareAndSet(true, false)) {
                    // 若连接未关闭
                    if (! this.isNullOrClose(dbconnection)) {
                        return dbconnection;
                    } else {
                        if (! this.closing) {
                            dbconnection.getIdle().set(true);
                        }
                    }
                }
            }
            // 若连接的总数未超出容量，则新建连接
            if ((this.dbconnectionLst.size() < this.capacity)
                    && (! this.closing)) {
                synchronized (this.dbconnectionLst) {
                    DBConnection dbconnection = this.createDBConnection(this.dbconnectionLst.size() + 1);
                    this.dbconnectionLst.add(dbconnection);
                    return dbconnection;
                }
            } 
            // 遍历连接集，重用空闲、不可用的连接
            for (DBConnection dbconnection: this.dbconnectionLst) {
                if (dbconnection.getIdle().compareAndSet(true, false)) {
                    // 若连接已关闭
                    if ((null == dbconnection.getConnection())
                            || dbconnection.getConnection().isClosed()) {
                        Connection connection = this.createConnection();
                        dbconnection.setConnection(connection);
                        return dbconnection;
                    } else if (! this.closing) {
                        dbconnection.getIdle().set(true);
                    }
                }
            }
            // 延迟轮询
            Thread.sleep(this.waitInterval);
            long end = System.currentTimeMillis();
            // 若等待超时
            if (end - start > this.waitTimeout) {
                throw new Exception("ERROR_WAIT_TIMEOUT");
            }
            start = end;
        } // while (! this.closing) {
        return null;
    }
    
    /**
     * 关闭连接池中的所有连接
     * @throws Exception
     */
    public void close() {
        this.closing = true;
        // 是否已经关闭
        boolean closed = false;
        // 当未关闭完成
        while (! closed) {
            closed = true;
            try {
                Thread.sleep(this.waitInterval);
            } catch (Exception e) {
                e.printStackTrace();
                return;
            }
            // 遍历连接集，关闭所有空闲连接
            for (DBConnection dbconnection: this.dbconnectionLst) {
                // 若连接空闲，则关闭该连接，并标记未关闭完成
                if (dbconnection.getIdle().compareAndSet(true, false)) {
                    closed = false;
                    try {
                        if (! this.isNullOrClose(dbconnection)) {
                            dbconnection.getConnection().close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        } // while (true) {
        System.out.println("has closed all!");
    }

    @Override
    public void run() {
        // 当不是正在进行关闭
        while (! this.closing) {
            try {
                // 延迟轮询
                Thread.sleep(this.waitInterval);
                // 遍历连接集，关闭空闲超时的连接
                for (DBConnection dbconnection: this.dbconnectionLst) {
                    // 若连接空闲，且空闲超时
                    if (dbconnection.getIdle().get()
                            && this.idleTimeout(dbconnection)
                            && dbconnection.getIdle().compareAndSet(true, false)) {
                        // 若连接空闲超时
                        if (this.idleTimeout(dbconnection)) {
                            dbconnection.setIdleStart(0L);
                            dbconnection.getConnection().close();
                            System.out.println("【dbconnection-" + dbconnection.getIndex() + "】idle timeout.");
                        }
                        if (! this.closing) {
                            dbconnection.getIdle().set(true);
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    
    public int getCapacity() {
        return capacity;
    }

    public void setCapacity(int capacity) {
        this.capacity = capacity;
    }

    public long getWaitTimeout() {
        return waitTimeout;
    }

    public void setWaitTimeout(long waitTimeout) {
        this.waitTimeout = waitTimeout;
    }

    public long getIdleTimeout() {
        return idleTimeout;
    }

    public void setIdleTimeout(long idleTimeout) {
        this.idleTimeout = idleTimeout;
    }

    public long getWaitInterval() {
        return waitInterval;
    }

    public void setWaitInterval(long waitInterval) {
        this.waitInterval = waitInterval;
    }

    /**
     * 创建 DB连接
     * @param index
     * @return
     * @throws Exception
     */
    private DBConnection createDBConnection(int index) throws Exception {
        return new DBConnection(index, this.createConnection(), false);
    }
    
    /**
     * 创建连接
     * @return
     * @throws Exception
     */
    private Connection createConnection() throws Exception {
        Class.forName(this.driver);
        return DriverManager.getConnection(this.url, this.user, this.password);
    }
    
    /**
     * DB连接是否空闲超时
     * @param dbconnection
     * @return
     */
    private boolean idleTimeout(DBConnection dbconnection) {
        return ((dbconnection.getIdleStart() > 0)
                && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout));
    }
    
    /**
     * DB连接是否为空、或已关闭
     * @param dbconnection
     * @return
     * @throws SQLException
     */
    private boolean isNullOrClose(DBConnection dbconnection) throws SQLException {
        return ((null == dbconnection.getConnection())
                || dbconnection.getConnection().isClosed());
    }

}

 DBConnectionPoolTest.java如下

 

 

package db;

import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 测试：数据库连接池
 * @author Linkwork, [e-mail]
 * @since 2014年11月0日
 */
public class DBConnectionPoolTest {
    
    public static void main(String[] args) {
        DBConnectionPool pool = new DBConnectionPool(3, "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/db", "lw2013qq", "omg2056db4qq");
        pool.setWaitTimeout(10000L);
        pool.setIdleTimeout(40L);
        pool.start();
        testPool(pool);
        pool.close();
    }
    
    /**
     * 测试连接池
     * @param pool
     */
    public static void testPool(DBConnectionPool pool) {
        DBConnection dbconnection = pool.getDBConnectionQuietly();
        if (null != dbconnection) {
            try {
                // 创建表
                dbconnection.getConnection().prepareStatement("create table if not exists test_pool (tp_value int);").execute();
                dbconnection.release();
                mockConcurrent(pool);
                Thread.sleep(1000);
                mockConcurrent(pool);
                Thread.sleep(60);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                dbconnection.release();
            }
        }
    }
    
    /**
     * 模拟多线程并发访问数据库，并发插入 10行数据
     * @param pool
     */
    public static void mockConcurrent(DBConnectionPool pool) {
        for (int index = 0; index < 10; ++ index) {
            final int value = index;
            Thread thread = new Thread() {
                public void run() {
                    DBConnection dbconnection = pool.getDBConnectionQuietly();
                    if (null != dbconnection) {
                        try {
                            // 插入数据
                            PreparedStatement statement = dbconnection.getConnection().prepareStatement("insert into test_pool(tp_value) values(?);");
                            statement.setInt(1, value);
                            statement.execute();
                            StringBuffer msg = new StringBuffer();
                            msg.append("dbconnection index=").append(dbconnection.getIndex())
                                .append(", insert=").append(value)
                                .append(", time=").append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss SSS").format(new Date()));
                            System.out.println(msg.toString());
                        } catch (Exception e) {
                            e.printStackTrace();
                        } finally {
                            dbconnection.release();
                        }
                    }
                }
            };
            thread.start();
        }
    }

}

 运行 DBConnectionPoolTest.java，控制台输出：

 

dbconnection index=1, insert=0, time=2014-11-29 11:20:46 901

dbconnection index=2, insert=2, time=2014-11-29 11:20:46 917

dbconnection index=3, insert=8, time=2014-11-29 11:20:46 917

dbconnection index=2, insert=1, time=2014-11-29 11:20:46 960

dbconnection index=1, insert=4, time=2014-11-29 11:20:46 960

dbconnection index=3, insert=6, time=2014-11-29 11:20:46 984

dbconnection index=2, insert=7, time=2014-11-29 11:20:47 008

dbconnection index=1, insert=9, time=2014-11-29 11:20:47 008

【dbconnection-3】idle timeout.

dbconnection index=1, insert=3, time=2014-11-29 11:20:47 092

dbconnection index=2, insert=5, time=2014-11-29 11:20:47 142

【dbconnection-1】idle timeout.

【dbconnection-2】idle timeout.

dbconnection index=2, insert=1, time=2014-11-29 11:20:47 907

dbconnection index=1, insert=0, time=2014-11-29 11:20:47 929

dbconnection index=3, insert=3, time=2014-11-29 11:20:47 929

dbconnection index=2, insert=4, time=2014-11-29 11:20:47 948

dbconnection index=1, insert=5, time=2014-11-29 11:20:47 982

dbconnection index=3, insert=8, time=2014-11-29 11:20:47 982

 

has closed all!

 

 

欢迎大家访问我的个人网站 萌萌的IT人