数据库连接池DBCP

• 参考https://github.com/apache/commons-dbcp
• 参考https://commons.apache.org/proper/commons-dbcp/

---

• maven依赖

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-dbcp2</artifactId>
      <version>2.6.0</version>
    </dependency>

---

• 使用

package com.pool;

import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.*;

public class App {

    // 创建数据库使用
    final static String JDBC_URL_INIT = "jdbc:mysql://192.168.1.5:3306/?useSSL=false&useUnicode=true&characterEncoding=UTF-8";

    // 操作数据表使用
    final static String JDBC_URL = "jdbc:mysql://192.168.1.5:3306/jdbc?useSSL=false&useUnicode=true&characterEncoding=UTF-8";

    public static BasicDataSource basicDataSource = new BasicDataSource();

    public static void main(String[] args) throws SQLException {
        basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        basicDataSource.setUrl(JDBC_URL);
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("root");

//        basicDataSource.setInitialSize(5);  //初始化5个连接
//        basicDataSource.setMaxTotal(20);    //设置最大连接数为5
//        basicDataSource.setMaxWaitMillis(20L);  //设置最大等待时间
//        basicDataSource.setMinIdle(1);      //设置最小保持的连接数

        // 定期检查连接是否存在
//        basicDataSource.setTestWhileIdle(true); //开启功能
//        basicDataSource.setMinEvictableIdleTimeMillis(800L);    //最小时间运行一次
//        basicDataSource.setTimeBetweenEvictionRunsMillis(800L); //检查时间的间隔

        createDB();
        createTable();
        insertTable();
        queryTable();
    }

    //创建数据库
    public static void createDB() throws SQLException {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        basicDataSource.setUrl(JDBC_URL_INIT);
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("root");
        Connection connection = null;
        Statement statement = null;
        //  创建名为JDBC的数据库
        try {
            //  连接数据库
            connection = basicDataSource.getConnection();
            statement = connection.createStatement();
            boolean bool = statement.execute("CREATE DATABASE IF NOT EXISTS jdbc DEFAULT CHARSET utf8 COLLATE utf8_general_ci");
            System.out.println(bool);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null)connection.close();
            if(statement != null)statement.close();
        }
    }

    //创建数据表
    public static void createTable() throws  SQLException {
        Connection connection = null;
        Statement statement = null;
        //  创建user表
        try {
            //  连接数据库
            connection = basicDataSource.getConnection();
            statement = connection.createStatement();
            boolean bool = statement.execute("CREATE TABLE `user`  (\n" +
                    "  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                    "  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
                    "  `age` int(11) NULL DEFAULT NULL,\n" +
                    "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                    "  PRIMARY KEY (`id`) USING BTREE\n" +
                    ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic");
            System.out.println(bool);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null)connection.close();
            if(statement != null)statement.close();
        }
    }

    //插入(更新\删除)数据
    public static void insertTable() throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            //  连接数据库
            connection = basicDataSource.getConnection();
            statement = connection.createStatement();
            statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('雷OK', 'XM', 200 )",Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES (\"李What's\", 'BD', 300 )",Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('马氪金', 'TX', 100 )",Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('马买买', 'ALBB', 500 )",Statement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null)connection.close();
            if(statement != null)statement.close();
        }
    }

    //查询数据
    public static void queryTable() throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //  连接数据库
            connection = basicDataSource.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select id,user_name,password,age from user");
            //4.输出查询结果
            while (resultSet.next()){
                System.out.println(
                        "id=" + resultSet.getString("id") +
                                " user_name=" + resultSet.getString("user_name") +
                                " password=" + resultSet.getString("password") +
                                " age=" + resultSet.getString("age")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null)connection.close();
            if(statement != null)statement.close();
            if(resultSet != null)resultSet.close();
        }
    }

}