JDBC——解析properties文件，创建自定义连接池，操作mysql

一、创建idea工程

二、添加依赖包

<!-- 连接mysql工具包-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.38</version>
</dependency>

三、创建资源目录，并在其中创建properties文件

resource可以在工程目录或src下创建

resource/db.properties

db.properties配置如下：

mysqldriver=com.mysql.jdbc.Driver
url=jdbc:mysql://single01:3306/school?useSSL=false
user=root
password=ok

四、解析properties文件，创建自定义连接池，操作mysql

1、解析properties文件，创建连接测试类

public class JdbcUtils {
    private static String driver;
    private static String url ;
    private static String user ;
    private static String password ;

    //加载配置文件，解析配置文件信息
    static {
        ClassLoader classLoader = JdbcUtils.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("db.properties");//通过流获取配置文件
        Properties properties = new Properties();
        try {
            properties.load(is);//加载properties配置文件
        } catch (IOException e) {
            e.printStackTrace();
        }
        //获取配置文件信息
        driver = properties.getProperty("mysqldriver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");
    }

    //创建连接
    public static Connection getConnection(){
        Connection connection=null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭连接池：方法重载，实现多种需求
    public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
        try {
            if (rs!=null) {
                rs.close();
            }
            if (pstmt!=null){
                pstmt.close();
            }
            if (conn!=null){
                conn.close();
            }

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

    }
    public static void close(Connection conn, PreparedStatement pstmt){
        close(conn,pstmt,null);
    }
    public static void close(Connection conn){
        close(conn,null,null);
    }
    // 关闭连接——可变参数实现
    public  static void close(AutoCloseable...closes){
        if (null !=closes ){
            for (AutoCloseable close : closes) {
                try {
                    close.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) {
        //测试连接
        Connection connection = JdbcUtils.getConnection();
        System.out.println(connection);
        close(connection);
    }
}

2、创建自定义连接池

* 连接池配置参数
* 连接池构造方法：生产connection对象，添加到连接池中：检查连接池是否达到最大值
* 创建连接池单例对象
* 获取连接池中Connection对象
* 查询连接池数量
* 关闭连接池
* main方法测试连接池

public class MysqlConnectionPool {
    private Integer initNum=5; //初始化Connection数量
    private Integer increase=5; //每次请求Connection时获取的数量
    private Integer min=3;  //允许连接池拥有的Connection的最小数量
    private Integer max=30;//允许连接池拥有的Connection的最大数量

    private Byte b =1;//同步锁参数
    private Boolean tag=false;//false：当前没有子线程被启动   true：有子线程启动
    private LinkedList<Connection> pool=new LinkedList<>();//创建连接池对象，存储Connection
    
    //连接池构造方法
    private MysqlConnectionPool (){
        produceConnection(initNum);
    }
    //生产connection对象，添加到连接池中
    private void produceConnection(Integer num){
        System.out.println(Thread.currentThread().getName());//打印当前线程名
        for (int i = 0; i < num; i++) {
            if (!checkPoolMaxNum()){//连接池没有达到最大值，就生产Connection对象
                Connection connection = JdbcUtils.getConnection();
                pool.add(connection);
            }
        }
        tag=false;
    }
    //检查连接池是否达到最大值
    private boolean checkPoolMaxNum(){
        if (pool.size()>max){
            return true;
        }else {
            return false;
        }
    }

    //创建连接池单例对象（一个工程一个连接池）
    private static MysqlConnectionPool poolInstance= new MysqlConnectionPool();
    //饿汉（static）  懒汉
    public static MysqlConnectionPool getPool(){
        return poolInstance;
    }
    
    //获取连接池中Connection对象
    public Connection getConnection(){
        while (pool.size()==0){
            System.out.println("warning:连接池已用尽........");
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        synchronized (b){  //连接池中连接数量达到最小值，开线程生产新的Connection
            if (pool.size()<=min && !tag){
                tag=true;
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        produceConnection(increase);
                    }
                }).start();//子线程不影响主线程运行

            }

            return pool.pop();
        }

    }

    //查询连接池数量
    public Integer count(){
        return pool.size();
    }

    //关闭连接池：方法重载，实现多种需求
    public void close(Connection connection){
        close(connection,null,null);
    }
    public void close(Connection connection, PreparedStatement pstmt){
        close(connection,pstmt,null);
    }
    public void close(Connection connection, PreparedStatement pstmt, ResultSet rs){
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (connection!=null){
                connection.close();
                /*pool.add(connection);//会导致连接池数量不变
                connection=null;*/
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    // 关闭连接——可变参数实现
    public  static void close(AutoCloseable...closes){
        if (null !=closes ){
            for (AutoCloseable close : closes) {
                try {
                    close.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    public static void main(String[] args) {
        //测试连接池
        MysqlConnectionPool pool = MysqlConnectionPool.getPool();
        int count =0;
        while(true){
            count++;
            Connection connection = pool.getConnection();
            System.out.println(connection+"  "+count+" 线程池连接数量："+ pool.count());
            try {
//                connection.close();
                pool.close(connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

    }
}