自己写JDBC连接池

1.为什么需要连接池：

在J2EE开发中，性能损耗之一就是不断的去开辟IO流，在获取Conection对象连接数据库后，用户查询出Result程序就关闭连接了，这样一开一关是相当消耗性能的

2.连接池所需要的属性：

1.JDBC开发中的基本属性：

private String driverName = "com.mysql.jdbc.Driver";//JDBC驱动名称
private String url = "jdbc:mysql://127.0.0.1/databaseName?characterEncoding=utf8";//JDBC地址
private String userName = "root";//数据库用户名
private String password = "root";//数据库密码

2.连接池属性：

private String poolName = driverName+"_"+url;//连接池名称(一般由DriverName+"_"+url)
private Integer minConnections = 5;//连接池空闲时最小连接数
private Integer maxConnections = 20;//连接池空闲时最大连接数
private Integer initConnections = 10;//连接池初始化连接数
private Integer connTimeOut = 1000L;//无法获取连接下等待时间
private Integer maxActiveConnections = 100;//最大允许连接数，mysql默认100
private Boolean isCurrentConnection = true;//是否获取当前连接
private Boolean isCheckPool = true;//是否定时检查连接池
private Long lazyCheck = 1000*60*60L;//延迟检查时间
private Long periodCheck = 1000*60*60L;//检查频率

3.code:

package com.youxuan.databases;

public class DBbean {
    private String driverName = "com.mysql.jdbc.Driver";    //JDBC驱动
    private String url = "jdbc:mysql://127.0.0.1:3306/youxuan?characterEncoding=utf-8"; //JDBC地址
    private String userName = "root";   //数据库用户名
    private String password = "";   //数据库密码

    private String poolName = "testPool";   //连接池名字
    private Integer minConnections = 3; //空闲时最小连接数
    private Integer maxConnections = 10;    //空闲时最大连接数
    private Integer initConnection = 5; //初始化连接数量
    private Long connTimeOut = 1000L;   //重复获得连接的频率
    private Integer maxActiveConnections = 100; //最大允许的连接数
    private Long ConnectionTimeOut = 1000*60*20L;   //连接超时时间
    private Boolean isCurrentConnection = true;//是否获得当前连接
    private Boolean isCheckPool = true;//是否定时检查连接池
    private Long lazyCheck = 1000*60*60L;   //延迟多少时间后开始检查
    private Long periodCheck = 1000*60*60L; //检查频率

    private static DBbean db;
    private DBbean(){}//单例模式
    public static DBbean getBean(){
        if(db == null){
            db = new DBbean();
            return db;
        }else{
            return db;
        }
    }

    public String getDriverName() {
        return driverName;
    }

    public void setDriverName(String driverName) {
        this.driverName = driverName;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPoolName() {
        return poolName;
    }

    public void setPoolName(String poolName) {
        this.poolName = poolName;
    }

    public Integer getMinConnections() {
        return minConnections;
    }

    public void setMinConnections(Integer minConnections) {
        this.minConnections = minConnections;
    }

    public Integer getMaxConnections() {
        return maxConnections;
    }

    public void setMaxConnections(Integer maxConnections) {
        this.maxConnections = maxConnections;
    }

    public Integer getInitConnection() {
        return initConnection;
    }

    public void setInitConnection(Integer initConnection) {
        this.initConnection = initConnection;
    }

    public Long getConnTimeOut() {
        return connTimeOut;
    }

    public void setConnTimeOut(Long connTimeOut) {
        this.connTimeOut = connTimeOut;
    }

    public Integer getMaxActiveConnections() {
        return maxActiveConnections;
    }

    public void setMaxActiveConnections(Integer maxActiveConnections) {
        this.maxActiveConnections = maxActiveConnections;
    }

    public Long getConnectionTimeOut() {
        return ConnectionTimeOut;
    }

    public void setConnectionTimeOut(Long connectionTimeOut) {
        ConnectionTimeOut = connectionTimeOut;
    }

    public Boolean getIsCurrentConnection() {
        return isCurrentConnection;
    }

    public void setIsCurrentConnection(Boolean isCurrentConnection) {
        this.isCurrentConnection = isCurrentConnection;
    }

    public Boolean getIsCheckPool() {
        return isCheckPool;
    }

    public void setIsCheckPool(Boolean isCheckPool) {
        this.isCheckPool = isCheckPool;
    }

    public Long getLazyCheck() {
        return lazyCheck;
    }

    public void setLazyCheck(Long lazyCheck) {
        this.lazyCheck = lazyCheck;
    }

    public Long getPeriodCheck() {
        return periodCheck;
    }

    public void setPeriodCheck(Long periodCheck) {
        this.periodCheck = periodCheck;
    }


}

3.连接池工具类接口及其实现

1.接口：

    package com.youxuan.databases;

    import java.sql.Connection;
    import java.sql.SQLException;

    public interface ConnectionPoolInter {
        public Connection getConnection();  //获得连接
        public Connection getCurrentConnection();   //获得当前连接
        public void close(Connection conn) throws SQLException; //回收连接
        public void destroy();  //销毁连接池
        public Boolean isActive();  //获取连接池状态
        public void checkPool();    //检查连接池状态
    }

2.实现类:

package com.youxuan.databases;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;

public class ConnectionPool implements ConnectionPoolInter {

    private DBbean dbBean;
    private Boolean isActive = false;   
    private Integer contActive = 0; //创建记录总数

    private List<Connection> freeConnections = new Vector<Connection>();    //空闲链接
    private List<Connection> activeConnections = new Vector<Connection>();  //活动连接
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    private ConnectionPool(DBbean dbBean) {
        this.dbBean = dbBean;
        init();
    }

    private static ConnectionPool pool;
    public static ConnectionPool getPool(){
        if(pool == null){
            pool = new ConnectionPool(DBbean.getBean());
            return pool;
        }else{
            return pool;
        }
    }
    public void init(){
        try{
            Class.forName(dbBean.getDriverName());
            for(int i = 0;i < dbBean.getInitConnection();i++){
                Connection conn = newConnection();
                if(conn!=null){
                    freeConnections.add(conn);
                    contActive++;
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }


    public Connection newConnection() throws ClassNotFoundException, SQLException{
        Connection conn = null;
        if(dbBean != null){
            Class.forName(dbBean.getDriverName());
            conn = DriverManager.getConnection(dbBean.getUrl(),dbBean.getUserName(),dbBean.getPassword());
        }
        return conn;
    }

    @Override
    public synchronized Connection getConnection() {
        // TODO Auto-generated method stub
        Connection conn = null;
        try {
            if(contActive < this.dbBean.getMaxActiveConnections()){
                if(freeConnections.size() > 0){
                    conn = freeConnections.get(0);
                    if(conn!=null){
                        threadLocal.set(conn);
                    }
                    freeConnections.remove(0);
                }else{
                    conn = newConnection();
                }
            }else{
                wait(this.dbBean.getConnTimeOut());
                conn = getConnection();
            }
            if(isVaild(conn)){
                activeConnections.add(conn);
                contActive++;
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return conn;
    }

    public Boolean isVaild(Connection conn){
        try{
            if(conn == null || conn.isClosed())
            {
                return false;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return true;
    }

    @Override
    public  Connection getCurrentConnection() {
        // TODO Auto-generated method stub
        Connection conn = threadLocal.get();
        if(!isVaild(conn)){
            conn = getConnection();
        }
        return conn;
    }

    @Override
    public synchronized void close(Connection conn) throws SQLException {
        // TODO Auto-generated method stub
        if(isVaild(conn) && !(freeConnections.size()> dbBean.getMaxConnections())){
            freeConnections.add(conn);
            activeConnections.remove(conn);
            contActive--;
            threadLocal.remove();
            notifyAll();
        }
    }

    @Override
    public void destroy() {
        // TODO Auto-generated method stub
        for (Connection conn : freeConnections) {
            try {
                if(isVaild(conn))conn.close();
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
        }

        for(Connection conn : activeConnections){
            try {
                if(isVaild(conn))conn.close();
            } catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }
        }

        isActive = false;
        contActive = 0;
    }

    @Override
    public Boolean isActive() {
        // TODO Auto-generated method stub
        return isActive;
    }

    @Override
    public void checkPool() {
        // TODO Auto-generated method stub
        if(dbBean.getIsCheckPool()){
            new Timer().schedule(new TimerTask() {
                public void run() {
                    System.out.println("空闲连接数"+freeConnections.size());
                    System.out.println("活动连接数"+activeConnections.size());
                    System.out.println("总连接数"+contActive);
                }
            }, dbBean.getLazyCheck(),dbBean.getPeriodCheck());
        }

    }

}

实现类中的方法会 比接口多，是因为在面向接口编程中，你定义的类型为接口，而实现类中某些方法是不能被手动调用的。