数据库连接池

TimeUnit.MILLISECONDS.sleep(10);
TimeUnit.SECONDS.sleep(10);
TimeUnit.MINUTES.sleep(10);
Thread.sleep(10);
Thread.sleep(10*1000);

Thread.sleep(10*60*1000);

package com.ConnectionToPool;

/**
 * Created by 79782 on 2018/7/1.
 */
public abstract class AbstractDataSourcePool {
    /**
     * 定义一个获取连接池中连接的方法
     */
    public abstract PooledConnetion getConnection();
}

package com.ConnectionToPool;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * Created by 79782 on 2018/7/1.
 */
public class DataSourcePool extends AbstractDataSourcePool {
    //数据库连接信息
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int initSize = 2;
    private int maxSize = 10;
    private int incrSize = 5;
    private int timeOut = 1000;

    //创建一把锁
    private Object lockObject = new Object();
    //保存连接池中对象的集合 考虑线程安全的问题
    /*public Vector<PooledConnetion> connectionPool=new Vector<PooledConnetion>();*/
    //JDK1.5之后提供了Collections.synchronizedList 创建带线程安全的集合，用的是同步快
    public List<PooledConnetion> connectionPool =
            Collections.synchronizedList(new ArrayList<PooledConnetion>());

    public DataSourcePool() {
        initPool();

    }

    private void initPool() {
        Properties prop = new PropertiesPlaceHolder();
        //读取属性文件配置
        this.driverClassName = prop.getProperty("jdbc.driver_class");
        this.url = prop.getProperty("jdbc.url");
        this.username = prop.getProperty("jdbc.username");
        this.password = prop.getProperty("jdbc.password");
        initSize = prop.getProperty("initSize") == null ? initSize : Integer.parseInt(prop.getProperty("initSize"));
        incrSize = prop.getProperty("incrSize") == null ? incrSize : Integer.parseInt(prop.getProperty("incrSize"));
        maxSize = prop.getProperty("maxSize") == null ? maxSize : Integer.parseInt(prop.getProperty("maxSize"));
        timeOut = prop.getProperty("timeOut") == null ? timeOut : Integer.parseInt(prop.getProperty("timeOut"));
        //把对应的驱动注册到DriverManager中
        try {
            //手动注册来提高性能
            Driver driver = (Driver) Class.forName(driverClassName).newInstance();
            DriverManager.registerDriver(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public PooledConnetion getConnection() {
        PooledConnetion connetion = null;
        synchronized (lockObject) {
            if (connectionPool.size() == 0) {
                creatConnection(initSize);
            }
            //获取连接池中可用的连接对象
            connetion = getRealConnetion();
            while (connetion == null) {
                System.out.println("尝试获取连接");
                creatConnection(incrSize);
                connetion = getRealConnetion();
                try {
                    if (connetion == null) {
                        TimeUnit.MILLISECONDS.sleep(30);
                    }

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


        return connetion;
    }

    /**
     * 获取连接池中空闲的连接对象，审核连接池中获取连接对象是否可用
     * 提高连接的可靠性
     *
     * @return
     */
    private PooledConnetion getRealConnetion() {
        for (int i = 0; i < connectionPool.size(); i++) {
            PooledConnetion conn = connectionPool.get(i);
            if (!conn.isUsed()) {
                //在规定的时间内审核该连接是否能与数据库连接
                Connection connection = conn.getConnection();
                try {
                    if (!connection.isValid(timeOut)) {
                        //把不可用的连接使用一个新的连接补回来
                        connection = DriverManager.getConnection(url, username, password);
                        //补齐损坏的连接
                        conn.setConnection(connection);

                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn.setIsUsed(true);
                return conn;
            }
        }
        return null;

    }



    private void creatConnection(int initCount) {
        if(connectionPool.size()+incrSize<=maxSize){
            for(int i=0;i<initCount;i++){
                System.out.println("连接池中有："+connectionPool.size()+"个连接对象");
                try {
                    Connection connection=DriverManager.getConnection(url,username,password);
                    PooledConnetion conn=new PooledConnetion(connection,false);
                    //放入到连接池中
                    connectionPool.add(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }else{
            System.out.println("连接池已经满了...");
        }
    }


}

package com.ConnectionToPool;

import java.sql.Connection;

/**
 * Created by 79782 on 2018/7/1.
 * 封装连接池中连接的类
 */


public class PooledConnetion {
    private Connection connection;
    //是否使用
    private boolean isUsed;

    public PooledConnetion(Connection connection, boolean isUsed) {
        this.connection = connection;
        this.isUsed = isUsed;
    }

    public PooledConnetion() {
    }

    public boolean isUsed() {
        return isUsed;
    }

    public void setIsUsed(boolean isUsed) {
        this.isUsed = isUsed;
    }

    public Connection getConnection() {
        return connection;
    }

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

    public void close(){
        System.out.println("释放了连接。。。"+this);
        this.isUsed=false;
    }
}

package com.ConnectionToPool;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * Created by 79782 on 2018/7/1.
 */
public class PropertiesPlaceHolder extends Properties {


    public PropertiesPlaceHolder(){

        try(InputStream is = this.getClass().getClassLoader().getResourceAsStream("pool.properties")) {
            this.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        String property = new PropertiesPlaceHolder().getProperty("jdbc.username");
        System.out.println(property);
    }
}

package com.ConnectionToPool;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Created by 79782 on 2018/7/1.
 */
public class TestMan {
    /**
     * 1.为什么出现连接池
     *      传统的连接数据操作，都是访问一次数据开启一次连接，访问结束后关闭连接对象
     *      单线程：100次数据访问--》开启100次连接，关闭100次连接（开启和关闭对象非常耗时）
     *      多线程：30次数据访问（业务非常复杂）--》开启30次连接，关闭30次连接（开启和关闭对象非常耗时）
     *      解决方案：减少开启和关闭连接消耗时间，对连接对象进行复用
     * 2.连接池常用参数：
     *      初始化连接数
     *      最大连接数
     *      每次增加连接数
     *      超时时间
     * 3.连接池的核心作用：
     *      复用连接
     */
    private static AbstractDataSourcePool pool=new DataSourcePool();

    public static void main(String[] args) {
        //单线程
        /*for(int i=0;i<1000;i++){
            selectTest();
        }*/
        //测试多线程
        for(int i=0;i<100;i++){
            new Thread(new Runnable() {
                @Override
                public void run() {
                    selectTest();
                }
            }).start();
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
    public static void selectTest() {
        PooledConnetion conn=pool.getConnection();
        PreparedStatement pst=null;
        ResultSet rs=null;
        try {
            pst=conn.getConnection().prepareStatement("select * from city");
            rs=pst.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(rs!=null){
                    rs.close();
                }
                if(pst!=null){
                    pst.close();
                }
                if(conn!=null){
                    Thread.sleep(1000);
                    conn.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

}