数据库连接池的简单实现

>   数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

一、定义连接池接口IPool

package jdbcPool;


public interface IPool {

    /**
     * 从池中获取连接
     * @return
     */
    PooledConnection getConnection();

    /**
     * 创建连接到池中
     * @param count 创建数量
     */
    void createConnections(int count);
}

二、封装连接Connection的类PooledConnection

package jdbcPool;

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

public class PooledConnection {
        //jdbc连接
        private Connection connection;  
        //连接是否被占用
    private boolean isBusy;

    public PooledConnection(Connection connection, boolean isBusy) {
        this.connection = connection;
        this.isBusy = isBusy;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public boolean isBusy() {
        return isBusy;
    }

    public void setBusy(boolean isBusy) {
        this.isBusy = isBusy;
    }

    public ResultSet queryBySql(String sql) throws SQLException, InterruptedException {

         Statement sm = null;
            ResultSet rs = null;
            try {
                sm = connection.createStatement();
                rs = sm.executeQuery(sql);
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            return rs;
    }
    //释放连接
    public void close() {
        isBusy = false;
    }

}

二、实现IPool接口JdbcPool

package jdbcPool;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

public class JdbcPool implements IPool {

    //jdbc驱动
    private String jdbcDriver = "";
    //jdbc连接url
    private String dbUrl = "";
    //用户名
    private String dbUsername = "";
    //密码
    private String dbPassword = "";
    //初始化大小
    private int initConnCount;
    //最大连接数
    private int maxConnects;
    //每次增长大小
    private int incrementalCount;
    //连接集合
    private static Vector<PooledConnection> pool = new Vector<PooledConnection>();
    //初始化连接池
    public void init() {
        InputStream is = JdbcPool.class.getClassLoader().getResourceAsStream("jdbcPool/jdbc.properties");
        Properties p = new Properties();
        try {
            p.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        jdbcDriver = p.getProperty("jdbcDriver");
        dbUrl = p.getProperty("dbUrl");
        dbUsername = p.getProperty("dbUsername");
        dbPassword = p.getProperty("dbPassword");
        initConnCount = Integer.valueOf(p.getProperty("initConnCount"));
        maxConnects = Integer.valueOf(p.getProperty("maxConnnects"));
        incrementalCount = Integer.valueOf(p.getProperty("incrementalcount"));

        try {
            Class.forName(jdbcDriver);
            createConnections(initConnCount);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }

    public void createConnections(int count) {
          System.out.println("线程名：" + Thread.currentThread().getName() + "创建了"+count+"个连接");
        for (int i = 0; i < count; i++) {
              //我们创建连接的时候，必须要判断这个池中的连接要小于我们的最大连接
            if (maxConnects > 0 && pool.size() >= maxConnects) {
                System.out.println("连接池中的连接数量已经达到了最大值！");
                return;
            }
            try {
                Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
                pool.add(new PooledConnection(connection, false));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }
    public  PooledConnection  getActiveConnection() {
        for (PooledConnection conn : pool) {
            if (!conn.isBusy()) {
                Connection trueconn = conn.getConnection();
                try {
                    if (!trueconn.isValid(0)) {//连接是否有效
                        Connection newconn = DriverManager.getConnection(dbUrl,
                                dbUsername,
                                dbPassword);

                        conn.setConnection(newconn);
                    }
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }

                conn.setBusy(true);
                return conn;
            }
        }

        return null;
    }

    @Override
    public synchronized PooledConnection getConnection() {
        if (pool.size() <= 0) {
            System.out.println("连接池中还没有连接！");
            throw new RuntimeException("连接池中还没有连接！");
        }
        PooledConnection conn = getActiveConnection();
        if(null == conn){
             System.out.println("连接池中中已连接用完");
            createConnections(incrementalCount);
               conn = getActiveConnection();
               while (conn == null) {
                   try {
                       Thread.sleep(300);
                   }
                   catch (InterruptedException e) {
                       e.printStackTrace();
                   }
                   conn = getActiveConnection();
               }
        }
        return conn;
    }

    public static Vector<PooledConnection> getPool() {
        return pool;
    }


}

四、连接池管理DBManager

package jdbcPool;
public class DBManager {

       private static class CreatePool {
            private static JdbcPool pool = new JdbcPool();
        }

       public static JdbcPool getInstance(){
           return CreatePool.pool;
       }
}

五、测试连接池

package jdbcPool;
import java.sql.ResultSet;
import java.util.concurrent.CountDownLatch;
import org.junit.Before;
import org.junit.Test;
public class JdbcPoolTest {
    private int threadCount = 1000;

    private CountDownLatch latch = new CountDownLatch(threadCount);

    JdbcPool pool = DBManager.getInstance();

    @Before
    public void before() {
        pool.init();
    }

    public void select() throws Exception {
        PooledConnection conn = pool.getConnection();

        ResultSet rs = conn.queryBySql("select * from tbl_user");

        if (rs.next()) {
            System.out.println("线程名：" + Thread.currentThread().getName() + "====="+ rs.getString("account") );
        }
        rs.close();
        conn.close();

    }
    @Test
    public void threadTest() throws InterruptedException {
        for (int i = 0; i < threadCount; i++) {
            new Thread(new Runnable() {
                public void run() {
                    try {
                        latch.await();
                        select();

                    }
                    catch (Exception e) {
                        e.printStackTrace();
                    }

                }
            }).start();
            latch.countDown();
        }
        Thread.sleep(3000);
        System.out.println(JdbcPool.getPool().size());
    }
}

测试效果

线程名：main创建了10个连接
线程名：Thread-57=====PN135998
连接池中中已连接用完
线程名：Thread-98创建了2个连接
连接池中中已连接用完
线程名：Thread-49创建了2个连接
线程名：Thread-53=====PN135998
线程名：Thread-52=====PN135998
线程名：Thread-999=====PN135998
线程名：Thread-55=====PN135998
线程名：Thread-51=====PN135998
线程名：Thread-88=====PN135998
线程名：Thread-56=====PN135998
线程名：Thread-50=====PN135998
…….
14