Oracle JDBC 连接池

1、简介

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

2、为什么要用连接池

如果按照单个连接来进行数据库操作，在高并发的情况下会导致数据库连接数耗尽的问题，而且单个连接的频繁创建和关闭，极大地增加了数据库的开销。针对这些，数据库连接池技术就能很好的解决这些问题。

3、实现

定义连接对象

import java.sql.Connection;

/**
 *
 */
public class PoolConnection {

    private Connection connect;
    //false 繁忙，true 空闲
    private boolean status;
    
    public PoolConnection() {
    }

    public PoolConnection(Connection connect, boolean status) {
        this.connect = connect;
        this.status = status;
    }

    public Connection getConnect() {
        return connect;
    }

    public void setConnect(Connection connect) {
        this.connect = connect;
    }

    public boolean isStatus() {
        return status;
    }

    public void setStatus(boolean status) {
        this.status = status;
    }

    //释放连接池中的连接对象
    public void releaseConnect(){
        this.status = true;
    }
}

定义一个接口获取连接对象

public interface DataSource {
    PoolConnection getDataSource();
}

实现类

public class DataSourceImpl implements DataSource {

    private ReentrantLock lock = new ReentrantLock();
    //定义连接池中连接对象的存储容器
    private List<PoolConnection> list = Collections.synchronizedList(new LinkedList<>());
    //定义数据库连接属性
    private final static String DRIVER_CLASS = PropertiesUtils.getInstance().getProperty("jdbc.driver_class");
    private final static String URL = PropertiesUtils.getInstance().getProperty("jdbc.url");
    private final static String USERNAME = PropertiesUtils.getInstance().getProperty("jdbc.username");
    private final static String PASSWORD = PropertiesUtils.getInstance().getProperty("jdbc.password");

    //定义默认连接池属性配置
    private int initSize = 2;
    private int maxSize = 4;
    private int stepSize = 1;
    private int timeout = 2000;


    public DataSourceImpl() {
        initPool();
    }

    //初始化连接池
    private void initPool() {
        String init = PropertiesUtils.getInstance().getProperty("initSize");
        String step = PropertiesUtils.getInstance().getProperty("stepSize");
        String max = PropertiesUtils.getInstance().getProperty("maxSize");
        String time = PropertiesUtils.getInstance().getProperty("timeout");

        initSize = init == null ? initSize : Integer.parseInt(init);
        maxSize = max == null ? maxSize : Integer.parseInt(max);
        stepSize = step == null ? stepSize : Integer.parseInt(step);
        timeout = time == null ? timeout : Integer.parseInt(time);

        try {
            //加载驱动
            Driver driver = (Driver) Class.forName(DRIVER_CLASS).newInstance();
            //使用DriverManager注册驱动
            DriverManager.registerDriver(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Override
    public PoolConnection getDataSource() {
        PoolConnection poolConnection = null;
        try {
            lock.lock();
            //连接池对象为空时，初始化连接对象
            if (list.size() == 0) {
                createConnection(initSize);
            }

            //获取可用连接对象
            poolConnection = getAvailableConnection();

            //没有可用连接对象时，等待连接对象的释放或者创建新的连接对象使用
            while (poolConnection == null) {
                System.out.println("---------------等待连接---------------");
                createConnection(stepSize);
                poolConnection = getAvailableConnection();
                if (poolConnection == null) {
                    TimeUnit.MILLISECONDS.sleep(30);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            lock.unlock();
        }
        return poolConnection;
    }


    //创建数据库连接
    private void createConnection(int count) throws SQLException {
        if (list.size() + count <= maxSize) {
            for (int i = 0; i < count; i++) {
                System.out.println("初始化了" + (i + 1) + "个连接");
                Connection connect = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                PoolConnection pool = new PoolConnection(connect, true);
                list.add(pool);
            }
        }
    }

    // 获取可用连接对象
    private PoolConnection getAvailableConnection() throws SQLException {
        for (PoolConnection pool : list) {
            if (pool.isStatus()) {
                Connection con = pool.getConnect();
                // 验证连接是否超时
                if (!con.isValid(timeout)) {
                    Connection connect = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                    pool.setConnect(connect);
                }
                pool.setStatus(false);
                return pool;
            }
        }
        return null;
    }
}

相关的PropertiesUtils 工具类

public class PropertiesUtils extends Properties {

    private static final long serialVersionUID = 1L;

    //定义属性文件名称
    private final static String PROPERTY_FILE = "datasource.properties";

    private static PropertiesUtils propertiesHolder = null;

    private PropertiesUtils() {
        if (propertiesHolder != null) {
            throw new RuntimeException("此类是单例，已经存在实例化对象了。");
        }
    }

    public static PropertiesUtils getInstance() {
        if (propertiesHolder == null) {
            synchronized (PropertiesUtils.class) {
                if (propertiesHolder == null) {
                    propertiesHolder = new PropertiesUtils();
                    try (InputStream input = propertiesHolder.getClass().getClassLoader().getResourceAsStream(PROPERTY_FILE)) {
                        propertiesHolder.load(input);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return propertiesHolder;
    }
}

4、测试类

public class PoolTest
{
    public static void main( String[] args )
    {
        DataSource source = new DataSourceImpl();
        CountDownLatch latch = new CountDownLatch(3);
        int i = 0;

        for(; i < 3; i++){
            new Thread(new Runnable() {
                @Override
                public void run() {
                    PreparedStatement pre = null;
                    ResultSet result = null;
                    try {
                        PoolConnection connect = source.getDataSource();
                        String sql = "select * from LEVEL4 where LEVEL4_CODE like ?";
                        pre = connect.getConnect().prepareCall(sql);
                        pre.setString(1, "%3AL34812ABAA%");
                        // 执行查询，注意括号中不需要再加参数
                        result = pre.executeQuery();
                        while (result.next()) {
                            // 当结果集不为空时
                            System.out.println("LEVEL4_CODE: " + result.getString("LEVEL4_CODE"));
                        }

                        TimeUnit.SECONDS.sleep(1);
                        connect.releaseConnect();
                        latch.countDown();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
            }).start();
        }

        try {
            latch.await();
            System.out.println("-------结束-----------");
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

    }
}