当前位置：Gxlcms > 数据库问题 > 10、JDBC-DBCP

10、JDBC-DBCP

时间：2021-07-01 10:21:17 帮助过：3人阅读

xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>yofc</groupId> <artifactId>jdbc</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter-api</artifactId> <version>5.4.0</version> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.6.0</version> </dependency> </dependencies> <build> <plugins>  <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build> </project>

获取连接，三种方式

1、BasicDataSource

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DBCPTest {
    @Test
    public void testDBCP() throws SQLException {
        BasicDataSource dataSource = new BasicDataSource();

        // 设置必须属性
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setUrl("jdbc:mysql://192.168.8.136:3306/jdbc");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // 设置可选属性

        // 指定数据库连接池中初始化连接数的个数
        dataSource.setInitialSize(5);
        // 指定最大的连接数: 同一时刻可以同时向数据库申请的连接数
        dataSource.setMaxTotal(5);
        // 指定小连接数: 在数据库连接池中保存的最少的空闲连接的数量
        dataSource.setMinIdle(2);
        // 等待数据库连接池分配连接的最长时间. 单位为毫秒. 超出该时间将抛出异常.
        dataSource.setMaxWaitMillis(1000 * 5);

        // 从数据源中获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();

        printDataSourceStats(dataSource);
        shutdownDataSource(dataSource);
    }

    private static void printDataSourceStats(DataSource ds) {
        BasicDataSource bds = (BasicDataSource) ds;
        System.out.println("NumActive: " + bds.getNumActive());
        System.out.println("NumIdle: " + bds.getNumIdle());
    }

    private static void shutdownDataSource(DataSource ds) throws SQLException {
        BasicDataSource bds = (BasicDataSource) ds;
        bds.close();
    }
}

配置文件方式

dbcp.properties

username=root
password=root
url=jdbc:mysql://192.168.8.136:3306/jdbc
driverClassName=com.mysql.cj.jdbc.Driver

initialSize=10
maxTotal=50
minIdle=5
maxWaitMillis=5000

@Test
public void testDBCPWithConfig() throws Exception {
    InputStream inStream = this.getClass().getClassLoader().getResourceAsStream("dbcp.properties");
    Properties properties = new Properties();
    properties.load(inStream);
    BasicDataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

    // 从数据源中获取数据库连接
    System.out.println(dataSource.getConnection());

    printDataSourceStats(dataSource);
    shutdownDataSource(dataSource);
}

2、PoolingDataSource

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PoolingDataSourceExample {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            DataSource dataSource = setupDataSource("jdbc:mysql://192.168.8.136:3306/jdbc");

            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery("select * from user");
            int numcols = rset.getMetaData().getColumnCount();
            while (rset.next()) {
                for (int i = 1; i <= numcols; i++) {
                    System.out.print("\t" + rset.getString(i));
                }
                System.out.println("");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rset != null) rset.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }
    }

    public static DataSource setupDataSource(String connectURI) {
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI, "root", "root");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
        ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
        poolableConnectionFactory.setPool(connectionPool);

        PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
        return dataSource;
    }
}

3、PoolingDriver

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;

import java.sql.*;

public class PoolingDriverExample {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            setupDriver("jdbc:mysql://192.168.8.136:3306/jdbc");
            conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");
            stmt = conn.createStatement();
            rset = stmt.executeQuery("select * from user");
            int numcols = rset.getMetaData().getColumnCount();
            while (rset.next()) {
                for (int i = 1; i <= numcols; i++) {
                    System.out.print("\t" + rset.getString(i));
                }
                System.out.println("");
            }
            printDriverStats();
            shutdownDriver();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rset != null) rset.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
            }
        }
    }

    public static void setupDriver(String connectURI) throws Exception {
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI, "root", "root");
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
        ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
        poolableConnectionFactory.setPool(connectionPool);

        Class.forName("org.apache.commons.dbcp2.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.registerPool("example", connectionPool);
    }

    public static void printDriverStats() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        ObjectPool<? extends Connection> connectionPool = driver.getConnectionPool("example");
        System.out.println("NumActive: " + connectionPool.getNumActive());
        System.out.println("NumIdle: " + connectionPool.getNumIdle());
    }

    public static void shutdownDriver() throws Exception {
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
        driver.closePool("example");
    }
}