Vibur DBCP is a concurrent, fast and fully functional JDBC connection pool, which provides advanced performance monitoring functions. This paper mainly introduces   The basic use of vibur DBCP, the software version used in this paper: Java 1.8.0_191,vibur-dbcp 25.0,Spring Boot 2.3.12.RELEASE.

1. Configuration parameters

1.1. Connection pool size and fairness configuration

parameter	type	Default value	describe
poolInitialSize	int	5	Number of initial connections
poolMaxSize	int	50	maximum connection
poolFair	boolean	true	If true, ensure that the thread also obtains connections in first in first out order
poolEnableConnectionTracking	boolean	false	If true, the connection pool will record the stack information of each lending connection
name	String	"p" + auto-generated id	Connection pool name

1.2 basic configuration

parameter	type	Default value	describe
driver	Driver	null	jdbc driver; The order in which the vibus looks for the driver: 1.driver parameters 2.driverClassName parameter 3. Determine the driver through JDBC URL
driverProperties	Properties	null	The parameters of the driver will be used to call   Driver.connect() method
driverClassName	String	null	Drive name
jdbcUrl	String		jdbc connection url
username	String		user name
password	String		password
externalDataSource	DataSource	null	External data source. If set, the connection will be obtained from the data source
allowConnectionAfterTermination	boolean	false	Allow connection acquisition after connection pool interruption
allowUnwrapping	boolean	true	Whether to allow getting the underlying object of jdbc

1.3 connection timeout and retry

parameter	type	Default value	describe
connectionTimeoutInMs	long	15000	Get the timeout of the connection (DataSource.getConnection()); 0 means unlimited
loginTimeoutInSeconds	int	5	Timeout for creating a connection, which will be used to call   DriverManager.setLoginTimeout() method
acquireRetryDelayInMs	long	500	Time to wait for retry when connection creation fails
acquireRetryAttempt	int	3	Maximum number of retries when creating a connection failed

1.4 connection verification

parameter	type	Default value	describe
connectionIdleLimitInSeconds	int	5	When obtaining a connection from the connection pool, if the idle time of the obtained connection exceeds this setting, the connection will be verified. 0 means that the connection is verified every time, and a negative number means that it will never be verified.
validateTimeoutInSeconds	int	3	Execute when getting connection   Timeout of testConnectionQuery, or create connection execution   Timeout for initSQL.
testConnectionQuery	String	isValid	Check the connection query statement. If it is not set, use Connection.isValid(int) to check the connection; If the driver supports JDBC 4, it is strongly recommended to use the default value, which will make the verification efficient.
initSQL	String	null	The query statement executed when the connection is first created; isValid indicates that the   Connection.isValid(int) method.
useNetworkTimeout	boolean	false	If true, in   testConnectionQuery or   initSQL enabled and not   isValid, when executing   testConnectionQuery or   When initSQL, the   Connection.setNetworkTimeout() method; Driver support needs to be guaranteed   Connection.setNetworkTimeout() method.
networkTimeoutExecutor	Executor	null	When useNetworkTimeout is enabled, execute   The connection.setnetworktimeout (executor, int milliseconds) method requires   Executor object.

1.5. Query slow and large result set log settings

parameter	type	Default value	describe
logQueryExecutionLongerThanMs	long	3000	Whether to log the long-term query statements; A positive number indicates that the log is recorded when the query time of the SQL statement is greater than or equal to this setting, 0 indicates that it is always recorded, and a negative number indicates that it is not recorded
logStackTraceForLongQueryExecution	boolean	false	If true, in   When logQueryExecutionLongerThanMs is enabled and logged, the stack information of SQL statements is recorded at the same time.
logLargeResultSet	long	500	Whether to log large result sets; A positive number means that if the number of results obtained from the result set (the number of executions of ResultSet.next() exceeds this setting, the log will be recorded; A negative number means no record.
logStackTraceForLargeResultSet	boolean	false	If true, when logLargeResultSet is enabled and logging is performed, the stack information of the SQL statement is recorded at the same time.
includeQueryParameters	boolean	true	Whether to collect parameter information of query SQL

1.6. Get slow connection and timeout log settings

parameter	type	Default value	describe
logConnectionLongerThanMs	long	3000	If the time of obtaining the connection (DataSource.getConnection()) exceeds this setting, a log will be recorded; 0 means always log; A negative number means no record; If   logConnectionLongerThanMs greater than   connectionTimeoutInMs, logConnectionLongerThanMs will be set to   The value of connectionTimeoutInMs.
logStackTraceForLongConnection	boolean	false	If true, when logConnectionLongerThanMs starts and logs, the stack information of the obtained connection is recorded at the same time.
logTakenConnectionsOnTimeout	boolean	false	The connection from the connection pool timed out. Whether to print the log; If true, the timeout will be greater than the status information and stack information of all lending connections. The format of the log can be   takenConnectionsFormatter settings.
logAllStackTracesOnTimeout	boolean	false	If true, when logtakenconnectionstimeout is enabled and logged, the stack information of all other threads that do not get a connection is recorded at the same time
logLineRegex	Pattern	null	When recording stack information (based on the following parameters: logstacktraceforlongconnection, logstacktraceforlargeresultset, logstacktraceforlongqueryexecution, logtakenconnectionstimeout, logallstacktracesontimeout), only the information conforming to the regular expression is recorded. null means full stack information is recorded.
takenConnectionsFormatter	TakenConnectionsFormatter	TakenConnectionsFormatter.Default	When logtakenconnectionstimeout is enabled and logs are recorded, the log format can be set through this parameter.

1.7 callback function

The callback function can only be set in the configuration phase and cannot be modified after startup.

function	type	Default value	describe
viburDS.getConnHooks().addOnInit(...)	Hook.InitConnection	null	Function executed after connection initialization
viburDS.getConnHooks().addOnGet(...)	Hook.GetConnection	null	Function executed after obtaining a connection from the connection pool
viburDS.getConnHooks().addOnClose(...)	Hook.CloseConnection	null	The function executed before the connection returns to the connection pool
viburDS.getConnHooks().addOnDestroy(...)	Hook.DestroyConnection	null	Function executed after the original connection is closed
viburDS.getConnHooks().addOnTimeout(...)	Hook.GetConnectionTimeout	null	stay   Function executed after logtakenconnectionstimeout is enabled and logged
viburDS.getInvocationHooks().addOnMethodInvocation(...)	Hook.MethodInvocation	null	Execute the function before executing the JDBC interface method; This function is not executed when inheriting Object methods, methods related to close, and java.sql.Wrapper are executed.
viburDS.getInvocationHooks().addOnStatementExecution(...)	Hook.StatementExecution	null	stay   When the JDBC Statement is executed, the function will wrap around   Execution of Statement.
viburDS.getInvocationHooks().addOnResultSetRetrieval(...)	Hook.ResultSetRetrieval	null	Execute the method after the result set is closed.

1.8. Connection default behavior settings

parameter	type	Default value	describe
clearSQLWarnings	boolean	false	If true, the SQL warning is cleared before the connection returns to the connection pool.
resetDefaultsAfterUse	boolean	false	If true, reset the connection after it returns to the connection pool.
defaultAutoCommit	boolean	Driver's default	Auto submit by default
defaultReadOnly	boolean	Driver's default	Read only by default
defaultTransactionIsolation	String	Driver's default	Default isolation level
defaultCatalog	String	Driver's default	Default catalog

1.9. Statement cache settings

parameter	type	Default value	describe
statementCacheMaxSize	int	0	Maximum number of statement caches; 0 means not enabled   Statement cache, max. 2000.

1.10. Connection pool reduction setting

parameter	type	Default value	describe
poolReducerClass	String	org.vibur.dbcp.pool.PoolReducer	Classes that manage connection pooling
reducerTimeIntervalInSeconds	int	30	How long will the number of free connections in the connection pool be reduced
reducerSamples	int	15	stay   During the reducerTimeIntervalInSeconds time, how long does poolReducer wake up to count the connection pool information.

1.11 severe SQL status

parameter	type	Default value	describe
criticalSQLStates	String	08001,08006,08007,08S01,57P01,57P02,57P03,JZ0C0,JZ0C1	If an SQL exception appears in this setting, all connections will be invalidated and the connection pool will be closed.

1.12. Enable JMX

parameter	type	Default value	describe
enableJMX	boolean	true	Start JMX

For details, please refer to the official website: http://www.vibur.org/

2. Use

2.1. Direct use

2.1.1. Introducing dependency

<dependency>
    <groupId>org.vibur</groupId>
    <artifactId>vibur-dbcp</artifactId>
    <version>25.0</version>
</dependency>
<dependency>
    <groupId>com.googlecode.concurrentlinkedhashmap</groupId>
    <artifactId>concurrentlinkedhashmap-lru</artifactId>
    <version>1.4.2</version>
</dependency>

2.1.2 application examples

package com.abc.demo.general.dbpool;

import org.vibur.dbcp.ViburDBCPDataSource;

import java.sql.*;

public class ViburDbcpCase {
    public static void main(String[] args) {
        ViburDBCPDataSource viburDBCPDataSource = new ViburDBCPDataSource();
        viburDBCPDataSource.setName("vibur-dbcp Connection pool");
        viburDBCPDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        viburDBCPDataSource.setJdbcUrl("jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8");
        viburDBCPDataSource.setUsername("root");
        viburDBCPDataSource.setPassword("123456");
        viburDBCPDataSource.setPoolInitialSize(2);
        viburDBCPDataSource.setPoolMaxSize(10);
        viburDBCPDataSource.setConnectionIdleLimitInSeconds(30);
        viburDBCPDataSource.setTestConnectionQuery("select 1");
        viburDBCPDataSource.setLogQueryExecutionLongerThanMs(5000);
        viburDBCPDataSource.setLogStackTraceForLongQueryExecution(true);
        viburDBCPDataSource.setStatementCacheMaxSize(200);
        viburDBCPDataSource.start();

        Connection connection = null;
        try {
            connection = viburDBCPDataSource.getConnection();
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery("select version()");
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection);
        }

        //In practice, the data source is initialized when the application starts, and the application obtains the connection from the data source; The data source is not closed.
        viburDBCPDataSource.close();
    }

    private static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.2. Use in SpringBoot

2.1.1. Introducing dependency

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.12.RELEASE</version>
    <relativePath />
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
    </dependency>

    <dependency>
        <groupId>org.vibur</groupId>
        <artifactId>vibur-dbcp</artifactId>
        <version>25.0</version>
    </dependency>
    <dependency>
        <groupId>com.googlecode.concurrentlinkedhashmap</groupId>
        <artifactId>concurrentlinkedhashmap-lru</artifactId>
        <version>1.4.2</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

2.1.2 single data source

application.yml configuration:

spring:
  datasource:
    vibur:
      name: vibur-dbcp Connection pool
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://10.110.74.187:3306/egmp?useUnicode=true&characterEncoding=UTF-8
      username: root
      password: InsYR0ot187!
      pool-initial-size: 2
      pool-max-size: 10
      connection-idle-limit-in-seconds: 30
      test-connection-query: select 1
      log-query-execution-longer-than-ms: 5000
      log-stack-trace-for-long-query-execution: true
      statement-cache-max-size: 200

Data source configuration class:

package com.abc.demo.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.vibur.dbcp.ViburDBCPDataSource;


@Configuration
public class DataSourceConfig {
    @Bean(name = "dataSource", initMethod = "start")
    @ConfigurationProperties(prefix = "spring.datasource.vibur")
    public ViburDBCPDataSource dataSource() {
        ViburDBCPDataSource dataSource = DataSourceBuilder.create().type(ViburDBCPDataSource.class).build();
        return dataSource;
    }
}

use:

@Autowired
private DataSource dataSource;

2.1.3 multiple data sources

application.yml configuration:

spring:
  datasource:
    vibur:
      db1:
        name: vibur-dbcp Connection pool 1
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.110.74.187:3306/egmp?useUnicode=true&characterEncoding=UTF-8
        username: root
        password: InsYR0ot187!
        pool-initial-size: 2
        pool-max-size: 10
        connection-idle-limit-in-seconds: 30
        test-connection-query: select 1
        log-query-execution-longer-than-ms: 5000
        log-stack-trace-for-long-query-execution: true
        statement-cache-max-size: 200
      db2:
        name: vibur-dbcp Connection pool 2
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.110.74.187:3306/egmp?useUnicode=true&characterEncoding=UTF-8
        username: root
        password: InsYR0ot187!
        pool-initial-size: 2
        pool-max-size: 10
        connection-idle-limit-in-seconds: 30
        test-connection-query: select 1
        log-query-execution-longer-than-ms: 5000
        log-stack-trace-for-long-query-execution: true
        statement-cache-max-size: 200

Data source configuration class:

package com.abc.demo.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.vibur.dbcp.ViburDBCPDataSource;


@Configuration
public class DataSourceConfig {
    @Primary
    @Bean(name = "dataSource1", initMethod = "start")
    @ConfigurationProperties("spring.datasource.vibur.db1")
    public ViburDBCPDataSource dataSourceOne(){
        return DataSourceBuilder.create().type(ViburDBCPDataSource.class).build();
    }

    @Bean(name = "dataSource2", initMethod = "start")
    @ConfigurationProperties("spring.datasource.vibur.db2")
    public ViburDBCPDataSource dataSourceTwo(){
        return DataSourceBuilder.create().type(ViburDBCPDataSource.class).build();
    }
}

use:

@Autowired
@Qualifier("dataSource1")
private DataSource dataSource1;

@Autowired
@Qualifier("dataSource2")
private DataSource dataSource2;