JDBC连接池工具类 （单例模式 枚举）

初用，还存在还大优化空间：
如：连接不够用，需要自动新增一定大小；
连接属性应该可以抽离，不要写死在代码里面；

JDBCDataSourceUtil

package com.wuzhou.utils;

import java.sql.*;
import java.util.LinkedList;

/**
 * JDBC 数据库连接池 工具类(采用枚举类型的单例模式)
 *
 * @author Y_Kevin
 * @date 2021-03-19 9:35
 */
public class JDBCDataSourceUtil {

    public enum innerEnum {
        INSTANCE;
        private JDBCDataSourceUtil jdbcDataSourceUtil;
        /**
         * LinkedList集合，存储连接的容器---连接池
         */
        private LinkedList<Connection> connectionList = new LinkedList<>();
        private Connection connection;

        innerEnum() {
            System.out.println("创建数据库连接池(size=150)。(只出现一次)");
            for (int i = 0; i < 150; i++) {
                try {
                    String url = "jdbc:clickhouse://192.168.1.1:8123/ship?socket_timeout=300000";
                    String driverName = "ru.yandex.clickhouse.ClickHouseDriver";
                    Class.forName(driverName);
                    connection = DriverManager.getConnection(url);
                    connectionList.addLast(connection);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new RuntimeException("连接失败！");
                }
            }
            jdbcDataSourceUtil = new JDBCDataSourceUtil();
        }

        private JDBCDataSourceUtil getJDBCDataSourceUtil() {
            return jdbcDataSourceUtil;
        }

        private LinkedList<Connection> getConnectionList() {
            return connectionList;
        }
    }

    /**
     * 获取实例
     */
    public static JDBCDataSourceUtil getInstance() {
        return innerEnum.INSTANCE.getJDBCDataSourceUtil();
    }

    /**
     * 获取连接池
     */
    public LinkedList<Connection> getConnectionList() {
        return innerEnum.INSTANCE.getConnectionList();
    }

    /**
     * 获取一条连接
     */
    public synchronized Connection getConnection() {
        // 取出连接池中第一个连接
        Connection connection = getConnectionList().removeFirst();
        System.out.println("获取一条连接" + connection);
        return connection;
    }

    /**
     * 归还连接
     */
    public synchronized void releaseConnection(Connection conn) {
        System.out.println("归还连接" + conn);
        // 把连接返还给连接池
        if (!getConnectionList().contains(conn)) {
            getConnectionList().add(conn);
        }
        System.out.println("归还JDBC后的 连接数 ==>" + getConnectionList().size());
    }

    /**
     * 关闭所有 Connection
     */
    public void closeConnection() {
        // 遍历关闭所有连接
        for (Connection conn : getConnectionList()) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        //清空 集合里面的连接
        getConnectionList().clear();
    }

    /**
     * 关闭SQL连接，释放资源
     *
     * @param st Statement
     * @param rs ResultSet
     */
    public void closeResource(Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("res关闭失败！");
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("st关闭失败！");
            }
        }
    }
}

使用

// 获取 连接池实例 和 连接
JDBCDataSourceUtil dataSourceUtil = JDBCDataSourceUtil.getInstance();
Connection conn = dataSourceUtil.getConnection();
PreparedStatement psmt = null;
ResultSet rs = null;

psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
// 遍历执行SQL返回的数据
while (rs.next()) {
    count = rs.getLong("count");
}

// 关闭资源
dataSourceUtil.closeResource(psmt, rs);
// 归还 conn
dataSourceUtil.releaseConnection(conn);