Java JDBC篇4——数据库连接池

Java JDBC篇4——数据库连接池

1、DBCP

1.1、依赖jar包

官网：https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2

mysql-connector-java-5.1.49.jar 百度云：https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA提取码：nhnt

mysql-connector-java-8.0.25.jar 百度云：https://pan.baidu.com/s/1b8n7650uMKJtwidoptOjNQ提取码：wtvn

commons-dbcp2-2.8.0 百度云：https://pan.baidu.com/s/10bcq3Fzo36MnFFMppICi3A提取码：1tkz

commons-pool2-2.8.0 百度云：https://pan.baidu.com/s/1sZLSi0nRZqeYbhDuO5Mkxw提取码：hk3w

commons-logging-1.2 百度云：https://pan.baidu.com/s/1ZFZZcmCjZtRwumh_qisKIw提取码：2uaz

1.2、快速入门

url=jdbc:mysql://localhost:3306/test
user=root
password=blingbling123.
driver=com.mysql.jdbc.Driver

public class DBCPPoolUtils {
    private static String urls;
    private static String user;
    private static String password;
    private static String driver;
    private static BasicDataSource basicDataSource=null;
    static {
        Properties properties=new Properties();
        ClassLoader classLoader=JDBCtool.class.getClassLoader();
        URL url=classLoader.getResource("connection.properties");
        String path=url.getPath();
        try {
            properties.load(new FileReader(path));
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(properties);
        basicDataSource=new BasicDataSource();
        basicDataSource.setDriverClassName(properties.getProperty("driver"));
        basicDataSource.setUrl(properties.getProperty("url"));
        basicDataSource.setUsername(properties.getProperty("user"));
        basicDataSource.setPassword(properties.getProperty("password"));
    }
    public static Connection getConnection() throws SQLException {
        System.out.println(basicDataSource);
        //从连接池中获取连接
        Connection connection = basicDataSource.getConnection();
        return connection;
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Test {
    public static void main(String[] args) throws SQLException {
        Connection connection = DBCPPoolUtils.getConnection();
        String sql="select * from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getString("username"));
        }
        DBCPPoolUtils.close(connection,preparedStatement,resultSet);
    }
}

1.3、配置项

属性	描述
driverClassName	数据库驱动名称
url	数据库地址
username	用户名
password	密码
maxActive	最大连接数量
maxIdle	最大空闲连接
minIdle	最小空闲连接
initialSize	初始化连接

2、Druid

2.1、依赖jar包

官网：https://mvnrepository.com/artifact/com.alibaba/druid

mysql-connector-java-5.1.49.jar 百度云：https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA提取码：nhnt

mysql-connector-java-8.0.25.jar 百度云：https://pan.baidu.com/s/1b8n7650uMKJtwidoptOjNQ提取码：wtvn

druid1.2.6 百度云：https://pan.baidu.com/s/1f90LwWhAtPaAOASKf1UfPA提取码：iq2n

2.2、快速入门

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=blingbling123.
initialSize=5
maxActive=10
maxWait=3000

public class DruidPoolUtils {
    private static DataSource dataSource=null;
    static {
        Properties properties=new Properties();
        ClassLoader classLoader=JDBCtool.class.getClassLoader();
        URL url=classLoader.getResource("connection.properties");
        String path=url.getPath();
        try {
            properties.load(new FileReader(path));
            System.out.println(properties);
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Test {
    public static void main(String[] args) throws SQLException {
        Connection connection = DruidPoolUtils.getConnection();
        String sql="select * from user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getString("username"));
        }
        DruidPoolUtils.close(connection,preparedStatement,resultSet);
    }
}