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

绝地灬酷狼 2023-10-03 16:17 23阅读 0赞

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

### 1、DBCP ###

#### 1.1、依赖jar包 ####

[官网：https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2][https_mvnrepository.com_artifact_org.apache.commons_commons-dbcp2]

[mysql-connector-java-5.1.49.jar 百度云：https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA][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][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][commons-dbcp2-2.8.0 _https_pan.baidu.com_s_10bcq3Fzo36MnFFMppICi3A]提取码：1tkz

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

[commons-logging-1.2 百度云：https://pan.baidu.com/s/1ZFZZcmCjZtRwumh\_qisKIw][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、配置项 ####

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

### 2、Druid ###

#### 2.1、依赖jar包 ####

[官网：https://mvnrepository.com/artifact/com.alibaba/druid][https_mvnrepository.com_artifact_com.alibaba_druid]

[mysql-connector-java-5.1.49.jar 百度云：https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA][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][mysql-connector-java-8.0.25.jar _https_pan.baidu.com_s_1b8n7650uMKJtwidoptOjNQ]提取码：wtvn

[druid1.2.6 百度云：https://pan.baidu.com/s/1f90LwWhAtPaAOASKf1UfPA][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);
        }
    }

[https_mvnrepository.com_artifact_org.apache.commons_commons-dbcp2]: https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2
[mysql-connector-java-5.1.49.jar _https_pan.baidu.com_s_17J2VfkGS2h44j69eB8TuFA]: https://pan.baidu.com/s/17J2VfkGS2h44j69eB8TuFA
[mysql-connector-java-8.0.25.jar _https_pan.baidu.com_s_1b8n7650uMKJtwidoptOjNQ]: https://pan.baidu.com/s/1b8n7650uMKJtwidoptOjNQ
[commons-dbcp2-2.8.0 _https_pan.baidu.com_s_10bcq3Fzo36MnFFMppICi3A]: https://pan.baidu.com/s/10bcq3Fzo36MnFFMppICi3A
[commons-pool2-2.8.0 _https_pan.baidu.com_s_1sZLSi0nRZqeYbhDuO5Mkxw]: https://pan.baidu.com/s/1sZLSi0nRZqeYbhDuO5Mkxw
[commons-logging-1.2 _https_pan.baidu.com_s_1ZFZZcmCjZtRwumh_qisKIw]: https://pan.baidu.com/s/1ZFZZcmCjZtRwumh_qisKIw
[https_mvnrepository.com_artifact_com.alibaba_druid]: https://mvnrepository.com/artifact/com.alibaba/druid
[druid1.2.6 _https_pan.baidu.com_s_1f90LwWhAtPaAOASKf1UfPA]: https://pan.baidu.com/s/1f90LwWhAtPaAOASKf1UfPA