/*
 Navicat Premium Data Transfer

 Source Server         : MySQL80_3306
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : powernode_jdbc

 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001

 Date: 25/01/2023 20:48:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

drop database if exits powernode_jdbc;
CREATE database powernode_jdbc;
use powernode_jdbc;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 24 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (6, '小明3', 4, '女', '武汉3');
INSERT INTO `student` VALUES (7, '小明4', 51, '女', '武汉4');
INSERT INTO `student` VALUES (8, '小明5', 39, '女', '武汉5');
INSERT INTO `student` VALUES (9, '小明6', 38, '女', '武汉6');
INSERT INTO `student` VALUES (10, '小明7', 67, '男', '武汉7');
INSERT INTO `student` VALUES (11, '小明8', 73, '女', '武汉8');
INSERT INTO `student` VALUES (12, '小明9', 64, '男', '武汉9');
INSERT INTO `student` VALUES (13, '小明10', 74, '男', '武汉10');
INSERT INTO `student` VALUES (14, '小明11', 56, '男', '武汉11');
INSERT INTO `student` VALUES (15, '小明12', 50, '男', '武汉12');
INSERT INTO `student` VALUES (16, '小明13', 68, '男', '武汉13');
INSERT INTO `student` VALUES (17, '小明14', 47, '男', '武汉14');
INSERT INTO `student` VALUES (18, '小明15', 96, '女', '武汉15');
INSERT INTO `student` VALUES (19, '小明16', 86, '女', '武汉16');
INSERT INTO `student` VALUES (20, '小明17', 73, '女', '武汉17');
INSERT INTO `student` VALUES (21, '小明18', 31, '女', '武汉18');
INSERT INTO `student` VALUES (22, '小明19', 61, '女', '武汉19');
INSERT INTO `student` VALUES (23, '小明20', 30, '女', '武汉20');

SET FOREIGN_KEY_CHECKS = 1;

1.4.1编写说明

我们现在编写的时候一个使用连接池，一个不使用连接池，到时候测试的时候做下时间对比

1.4.2 创建jdbc.properties

jdbc.properties放在resources目录下

对应的数据库的各种信息，以及数据库连接池初始化时数量，最大连接数量以及自动增长数量

参数	说明
initConnectCount	数据库连接池初始化时数量
maxConnects	数据库连接池最大连接数量
incrementCount	数据库连接池自动增长数量

jdbcDriver = com.mysql.cj.jdbc.Driver
jdbcUrl = jdbc:mysql://localhost:3306/powernode_jdbc?&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
username = root
password = root

initConnectCount = 20
maxConnects = 100
incrementCount = 3

1.4.3 封装一个连接类

该类中包含一个数据库连接，一个是否使用的标记以及一个close方法，用来将该连接置为可用状态，从而达到数据库连接的可复用，减少持续创建新连接的资源消耗

package com.bjpowernode.jdbc._08连接池.domain;

import java.sql.Connection;

public class PoolConnection {
    /**
     * 数据库连接
     */
    private Connection conn = null;

    /**
     * 标记该连接是否使用
     */
    private boolean isUse = false;

    /**
     * 构造方法
     * @param conn  连接对象
     * @param isUse  是否正在使用 【模拟关闭】
     */
    public PoolConnection(Connection conn, boolean isUse) {
        this.conn = conn;
        this.isUse = isUse;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public boolean isUse() {
        return isUse;
    }

    public void setUse(boolean use) {
        isUse = use;
    }

    /**
     * 将该连接置为可用状态
     */
    public void close() {
        this.isUse = false;
    }
}

1.4.4 创建一个连接池接口

对外提供的连接池的接口

package com.bjpowernode.jdbc._08连接池.service;

import com.bjpowernode.jdbc._08连接池.domain.PoolConnection;

import java.sql.Connection;

public interface IPool {
    /**
     * 获取连接池中可用连接
     */
    PoolConnection getConnection();

    /**
     * 获取一个数据库连接（不使用连接池）
     */
    Connection getConnectionNoPool();
}

1.4.5 创建一个连接池实现类以及对应方法

首先加载对应配置文件中信息，初始化数据库连接池，然后用synchronized来实现多线程情况下线程安全的获取可用连接

package com.bjpowernode.jdbc._08连接池.service.impl;

import com.bjpowernode.jdbc._08连接池.domain.PoolConnection;
import com.bjpowernode.jdbc._08连接池.service.IPool;

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 {
    //驱动
    private static String jdbcDriver;
    //连接地址
    private static String jdbcUrl;
    //数据库用户名
    private static String username;
    //数据库密码
    private static String password;
    //初始化连接数
    private static Integer initConnectCount;
    //最大连接数
    private static Integer maxConnects;
    //当连接不够时自动增长的数
    private static Integer incrementCount;
    //因为Vector是线程安全的，所有暂时选择它
    private static Vector<PoolConnection> connections = new Vector<>();

    /*
     * 通过实例初始化块来初始化
     */
    {
        //读取对应的配置文件，加载入properties中，并设置到对应的参数中
        InputStream is = JdbcPool.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(is);
        } catch (IOException e)  {
            e.printStackTrace();
        }
        jdbcDriver = properties.getProperty("jdbcDriver");
        jdbcUrl = properties.getProperty("jdbcUrl");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        initConnectCount = Integer.valueOf(properties.getProperty("initConnectCount"));
        maxConnects = Integer.valueOf(properties.getProperty("maxConnects"));
        incrementCount = Integer.valueOf(properties.getProperty("incrementCount"));

        try {
            /*
             * 注册jdbc驱动
             * */
            Driver driver = (Driver) Class.forName(jdbcDriver).newInstance();
            DriverManager.registerDriver(driver);
            /*
             * 根据initConnectCount来初始化连接池
             * */
            createConnections(initConnectCount);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取可用连接
     */
    @Override
    public PoolConnection getConnection() {
        if (connections.isEmpty()) {
            System.out.println("连接池中没有连接");
            throw new RuntimeException("连接池中没有连接");
        }

        return getActiveConnection();
    }
    /**
     * 同步方法来获取连接池中可用连接，在多线程情况下，只有一个线程访问该方法来获取连接，防止由于多线程情况下多个线程获取同一个连接从而引起出错
     */
    private synchronized PoolConnection getActiveConnection() {
        /*
         * 通过循环来获取可用连接，若获取不到可用连接，则依靠无限循环来继续获取
         * */
        while (true) {
            for (PoolConnection con : connections) {
                if (!con.isUse()) {
                    Connection trueConn = con.getConn();
                    try {
                        //验证连接是否失效 0表示不校验超时
                        if (!trueConn.isValid(0)) {
                            con.setConn(DriverManager.getConnection(jdbcUrl, username, password));
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    con.setUse(true);
                    return con;
                }
            }
            /*
             * 根据连接池中连接数量从而判断是否增加对应的数量的连接
             * */
            if (connections.size() <= maxConnects - incrementCount) {
                createConnections(incrementCount);
            } else if (connections.size() < maxConnects && connections.size() > maxConnects - incrementCount) {
                createConnections(maxConnects - connections.size());
            }
        }
    }

    /*
     * 创建对应数量的连接并放入连接池中
     * */
    private void createConnections(int count) {
        for (int i = 0; i < count; i++) {
            if (maxConnects > 0 && connections.size() >= maxConnects) {
                System.out.println("连接池中连接数量已经达到最大值");
                throw new RuntimeException("连接池中连接数量已经达到最大值");
            }
            try {
                Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
                /*
                 * 将连接放入连接池中，并将状态设为可用
                 * */
                connections.add(new PoolConnection(connection, false));

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

    /*
     * 获取连接池中连接数量
     * */
    public int getSize() {
        return connections.size();
    }
    @Override
    public Connection getConnectionNoPool() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

1.4.6 创建一个连接池的维护类

通过静态内部类来实现连接池的单例模式

package com.bjpowernode.jdbc._08连接池;

import com.bjpowernode.jdbc._08连接池.service.impl.JdbcPool;

public class PoolManager {
    /**
     * 静态内部类实现连接池的单例
     * */
    private static class CreatePool{
        private static JdbcPool pool = new JdbcPool();
    }

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

1.4.7 案例测试

最后，上测试方法：起2000个线程，通过new两个CountDownLatch，其中一个来实现线程的同时并发执行，

　　熟话说，没有对比就没有伤害，我们先来用普通没有连接池的测试一下2000个连接并行执行的时间，代码如下：

package com.bjpowernode.jdbc._08连接池.test;

import com.bjpowernode.jdbc._08连接池.PoolManager;
import com.bjpowernode.jdbc._08连接池.service.impl.JdbcPool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;


public class JdbcNoPoolMain {
    static final int threadSize = 2000;

    static JdbcPool jdbcPool = PoolManager.getInstance();

    static CountDownLatch countDownLatch1 = new CountDownLatch(1);
    static CountDownLatch countDownLatch2 = new CountDownLatch(threadSize);

    public static void main(String[] args) throws InterruptedException {
        threadTest();
    }

    public static void threadTest() throws InterruptedException {
        long time1 = System.currentTimeMillis();
        for (int i = 0; i < threadSize; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    try {
                        //使得线程阻塞到coutDownLatch1为0时才执行
                        countDownLatch1.await();
                        selectNoPool();
                        //每个独立子线程执行完后，countDownLatch2减1
                        countDownLatch2.countDown();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        }
        //将countDownLatch1置为0，从而使线程并发执行
        countDownLatch1.countDown();
        //等待countDownLatch2变为0时才继续执行
        countDownLatch2.await();
        long time2 = System.currentTimeMillis();
        System.out.println("thread size: " + threadSize + " no use pool :" + (time2 - time1));
    }

    public static void selectNoPool() throws SQLException {

        Connection conn = jdbcPool.getConnectionNoPool();
        Statement sm = null;
        ResultSet rs = null;
        try {
            sm = conn.createStatement();
            rs = sm.executeQuery("select * from student");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            while (rs.next()) {
                System.out.println(Thread.currentThread().getName() + " ==== " + "name: " + rs.getString("name") + " age: " + rs.getInt("age"));
            }
            Thread.sleep(100);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        rs.close();
        sm.close();
        conn.close();
    }
}