Introduction

Vibur is a JDBC connection pooling library that is built entirely using standard Java concurrency utilities. It has concise and simple source code base and a modular design, which includes a separate and dedicated object pool. Under the hood it relies on a simple and robust connection pooling mechanism implemented on top of a Queue guarded by a Semaphore.

Vibur DBCP uses Java dynamic proxies to create the objects that implement the JDBC API interfaces. These proxies are a powerful tool that exists in Java since 1.3, and which does not depend on any third-party bytecode manipulation libraries such as Javassist or cglib. The dynamic proxies allow Vibur to choose which methods of the JDBC interfaces it needs to implement. For example, the 6 main JDBC interfaces for which Vibur DBCP provides proxies have a total of approximately 480 methods; however, the invocations of less than 100 are explicitly intercepted and handled, and all other method calls are simply forwarded to their default implementations. This significantly reduces the amount of boilerplate code that Vibur would otherwise incur if it had to provide a static implementation of all proxied JDBC API interfaces.

In addition to the usual for a JDBC connection pool functionality, Vibur DBCP offers several advanced features that help identify complex issues and answer questions like why and where "things went wrong" on a running production system that has a large number of moving parts. Also, since version 6.0, Vibur allows the application to intercept and handle all of the below events (see the connection and method invocation hooks settings) as well as to provide its own metrics and statistics of these events, if needed.

Detection and logging on the application side of all SQL queries which execution has taken more than a given time limit, for example, more than 200 ms, including the concrete SQL query parameters, and optionally the full Java stack trace from which the query was generated. This log can be further matched and compared with a similar log from the database server, if such log is available. A sample of an application generated log is shown here.

Detection and logging of all calls to DataSource.getConnection(), that has taken longer than a given limit, for example, longer than 1000 ms. Similar to above, the log may include the full stack trace of the calls. This log can help to identify if there were any periods during which the application has experienced some sort of a connection starvation, the root cause of which can be further analyzed. A sample of such log can be seen here.

​Detection and logging on the application side of all SQL queries which execution has generated a ResultSet that is longer than a given limit, for example, such that has more than 500 rows in it. Again, the log will include the concrete SQL query parameters, and optionally, the full Java stack trace of the query. While there are cases in which retrieving large ResultSets is a completely valid and desirable application behavior, there are other cases in which this could be a manifestation of some subtle bugs where large ResultSets are retrieved from the database, but then only the first few records of them are processed by the application, and all others are discarded. Similar bugs may have an adverse effect on the overall application performance and memory consumption.

Vibur 6.0, and later 14.0, introduced several programming hooks through which the application can intercept and provide customized behaviour for important connection lifecycle events, such as the initial connection creation or taking and returning a connection from the pool. The hooks have access not only to the occurring events but also to the timing information associated with these events. The application can also register hooks that intercept the methods invocations on the different JDBC objects/interfaces, if this is needed.

Vibur DBCP has been under active development since late 2012, has had over 30 releases to date, and has production deployments in many large-scale Web applications.

Main Features at a Glance

Guarantee that no thread will be starved out from accessing the JDBC pool connections. See the poolFair configuration parameter.

Detection and logging of slow SQL queries, larger than expected ResultSets, and long lasting getConnection() method calls. See the related configuration properties here and here.

Hibernate 3.6, 4.x and 5.x integration support.

Caching support for JDBC Statements (Prepared and Callable).

Built using standard Java concurrency utilities and dynamic proxies, does not use any synchronized blocks or methods.

Vibur DBCP requires Java 1.6+ and only the following external dependencies: its dedicated object pool, slf4j/log4j, and ConcurrentLinkedHashMap. The CLHM dependency is optional and needs to be provided by the application only if the JDBC Statement caching is enabled/used.

Other Features

Intelligent pool sizing - the number of idle connections in the JDBC pool can be reduced based on heuristics for the number of recently used connections.

Validation intervals support; i.e., that taken from the JDBC pool connection is not validated before every use but is validated only if a given amount of time has passed since the connection's last use.

The raw JDBC connection or Statement object can be retrieved from the respective proxy object via calling the proxy's unwrap method.

Providing records (via JMX or in a log file) for all JDBC connections that are currently taken, including the stack traces with which they were taken; useful if debugging lost/unclosed connections or if the application simply wants to know where are all connections currently being taken from.

JMX support - the pool registers an MBean via which various pool parameters can be observed and/or set.

Code Metrics and Performance Results

The below source code metrics do not take into account the projects testing directories, and the Apache License comment header that is at the top of each source file.

Project Source Files Lines of Code Vibur DBCP 32 ~4.8K Vibur Object Pool 14 ~1.2K

The performance results below were obtained via running this test with a Java 1.8.0_151 on a machine with an Intel i7-4702MQ 2.2GHz processor, running Ubuntu 16.04. The test was run with settings of 500 threads, each thread attempting 100 take/restore operations from a pool with initial size 50 and max size 200. Each thread simulated work for 2 or 5 milliseconds via calling Thread.sleep(), and the pool fairness parameter was set as shown in the table. The execution time was calculated as the average of three consecutive runs.

Pool Fairness Simulated Work Execution Time true 2 ms 955 ms false 2 ms 1003 ms true 5 ms 1714 ms false 5 ms 1816 ms

Maven Dependencies

Vibur Artifact Coordinates and How to Build from Source

<dependency> <groupId>org.vibur</groupId> <artifactId>vibur-dbcp</artifactId> <version>25.0</version> </dependency>

To get a local copy of the Vibur DBCP repository use this command:

git clone https://github.com/vibur/vibur-dbcp.git

If needed, checkout a particular tag via something like:

git checkout tags/25.0

Vibur DBCP uses an in-memory HyperSQL database for unit/integration testing purposes, and building the sources is simply a matter of executing:

mvn clean install

Hibernate 3.6/4.x/5.x Integration Artifacts

Vibur DBCP comes with Hibernate 3.6, Hibernate 4.x and Hibernate 5.x integrations (for 5.x see more details below). Depending on which Hibernate version the project is using, add one of the following Maven dependencies. Note that these dependencies will transitively include the above vibur-dbcp dependency, too.

Vibur provides Hibernate 5.0 integration via the shown below vibur-dbcp-hibernate5 module. Due to some Hibernate interface changes, Vibur did not provide integration with Hibernate 5.1 and 5.2. However, since Hibernate 5.3 integration with Vibur was included as part of the standard Hibernate ORM distribution via the hibernate-vibur module, see below. Users of Hibernate 5.1 and 5.2 who wish to use Vibur as underlying connection provider need to either upgrade to Hibernate 5.3 (or later) or need to adapt and include the Vibur integration source from Hibernate 5.3 into their own project.

<!-- For Hibernate 5.3+ projects: --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-vibur</artifactId> <!-- Note that this dependency will transitively include dependency on vibur-dbcp-25.0. If a different version of vibur-dbcp is needed, a concrete dependency on this version needs to be included in the application. --> <!-- Here X is the exact Hibernate 5.3 version used by the application and Designator is usually the word Final (or something like Beta1 or CR1). --> <version>5.3.X.Designator</version> </dependency>

PLEASE NOTE that after Vibur DBCP release 21.2 the below vibur-dbcp-hibernateXYZ Maven artifacts are not longer updated. These artifacts can still be used to integrate Vibur DBCP with Hibernate versions 3.6, 4.0-4.3, and 5.0, however, an explicit dependency on the concrete (different than 21.2 ) version of Vibur DBCP needs to be added to the user application.

<!-- For Hibernate 5.0 projects: --> <dependency> <groupId>org.vibur</groupId> <artifactId>vibur-dbcp-hibernate5</artifactId> <version>21.2</version> <!-- not longer updated, see above --> </dependency>

<!-- For Hibernate 4.3 projects: --> <dependency> <groupId>org.vibur</groupId> <artifactId>vibur-dbcp-hibernate4</artifactId> <version>21.2</version> <!-- not longer updated, see above --> </dependency>

<!-- For Hibernate 4.0-4.2 projects: --> <dependency> <groupId>org.vibur</groupId> <artifactId>vibur-dbcp-hibernate4-012</artifactId> <version>21.2</version> <!-- not longer updated, see above --> </dependency>

<!-- For Hibernate 3.6 projects: --> <dependency> <groupId>org.vibur</groupId> <artifactId>vibur-dbcp-hibernate3</artifactId> <version>21.2</version> <!-- not longer updated, see above --> </dependency>

Setting Up Connection Pooling - Vibur DBCP

Hibernate 3.6/4.x/5.x Configuration Snippet

<hibernate-configuration> <session-factory> <!-- Database connection settings: --> <property name="hibernate.connection.url">jdbc:hsqldb:mem:sakila;shutdown=false</property> <property name="hibernate.connection.username">sa</property> <property name="hibernate.connection.password"></property> <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property> <property name="hibernate.current_session_context_class">thread</property> <!-- Vibur DBCP specific properties: --> <property name="hibernate.connection.provider_class"> org.vibur.dbcp.integration.ViburDBCPConnectionProvider </property> <property name="hibernate.vibur.poolInitialSize">10</property> <property name="hibernate.vibur.poolMaxSize">100</property> <property name="hibernate.vibur.connectionIdleLimitInSeconds">30</property> <property name="hibernate.vibur.testConnectionQuery">isValid</property> <property name="hibernate.vibur.logQueryExecutionLongerThanMs">500</property> <property name="hibernate.vibur.logStackTraceForLongQueryExecution">true</property> <property name="hibernate.vibur.statementCacheMaxSize">200</property> </session-factory> </hibernate-configuration>

Spring with Hibernate 3.6/4.x/5.x Configuration Snippet

<!-- Vibur DBCP dataSource bean definition: --> <bean id="dataSource" class="org.vibur.dbcp.ViburDBCPDataSource" init-method="start" destroy-method="terminate"> <property name="jdbcUrl" value="jdbc:hsqldb:mem:sakila;shutdown=false"/> <property name="username" value="sa"/> <property name="password" value=""/> <property name="poolInitialSize">10</property> <property name="poolMaxSize">100</property> <property name="connectionIdleLimitInSeconds">30</property> <property name="testConnectionQuery">isValid</property> <property name="logQueryExecutionLongerThanMs" value="500"/> <property name="logStackTraceForLongQueryExecution" value="true"/> <property name="statementCacheMaxSize" value="200"/> </bean> <!-- For Hibernate5 set the sessionFactory class below to org.springframework.orm.hibernate5.LocalSessionFactoryBean --> <!-- For Hibernate4 set the sessionFactory class below to org.springframework.orm.hibernate4.LocalSessionFactoryBean --> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="packagesToScan" value="the.project.packages"/> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop> <prop key="hibernate.cache.use_second_level_cache">false</prop> <prop key="hibernate.cache.use_query_cache">true</prop> </props> </property> </bean> <!-- For Hibernate5 set the transactionManager class below to org.springframework.orm.hibernate5.HibernateTransactionManager --> <!-- For Hibernate4 set the transactionManager class below to org.springframework.orm.hibernate4.HibernateTransactionManager --> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <property name="sessionFactory" ref="sessionFactory"/> </bean>

Programming Configuration Snippet

public DataSource createDataSourceWithStatementsCache() { ViburDBCPDataSource ds = new ViburDBCPDataSource(); ds.setJdbcUrl("jdbc:hsqldb:mem:sakila;shutdown=false"); ds.setUsername("sa"); ds.setPassword(""); ds.setPoolInitialSize(10); ds.setPoolMaxSize(100); ds.setConnectionIdleLimitInSeconds(30); ds.setTestConnectionQuery("isValid"); ds.setLogQueryExecutionLongerThanMs(500); ds.setLogStackTraceForLongQueryExecution(true); ds.setStatementCacheMaxSize(200); ds.start(); return ds; }

Log4j Configuration Snippet

<logger name="org.vibur.dbcp" additivity="false"> <level value="debug"/> <appender-ref ref="console"/> </logger>

Configuration Settings - Vibur DBCP

Besides of the configuration settings listed in the next sections, ViburDBCPDataSource supports several non-standard connection manipulation and tracking methods defined in the ViburDataSource interface.

The connection manipulation methods include the getNonPooledConnection() and severConnection() methods. The former allows a non-pooled/raw connection to be retrieved from the pool. The later allows any pooled or non-pooled connection to be immediately closed; when called on a pooled connection severConnection() removes the connection from the underlying object pool, and closes the associated with it physical JDBC connection, too.

The connection tracking methods include the getTakenConnections() and getTakenConnectionsStackTraces() methods. The former allows to retrieve an array/list of all currently taken from the pool connections, including various timing and other debugging information associated with them. The later simply returns as a String information about all currently taken connections, including the stack traces of the threads that have taken them, plus the threads names and states.

For more details, see the javadoc of the aforementioned methods in ViburDataSource.

Pool Sizing and Fairness Settings

poolInitialSize

Type: int Default: 5 The pool initial size; i.e., the initial number of connections allocated in this JDBC pool.

poolMaxSize

Type: int Default: 50 The pool max size; i.e., the maximum number of connections that can be allocated in this JDBC pool.

poolFair

Type: boolean Default: true If true , guarantees that the threads invoking the pool's take methods will be selected to obtain a connection from it in FIFO order, and no thread will be starved out from accessing the JDBC pool connections.

poolEnableConnectionTracking

Type: boolean Default: false If true , the JDBC connections pool will keep information for the current stack trace of every taken from it connection.

name

Type: String Default: "p" + auto-generated id The DataSource/pool name, mostly useful for JMX identification and Vibur logs analysis. This name must be unique among all names for all configured Vibur pools. The default name is "p" + an auto-generated integer ID. If the configured name is not unique, the default name will be used instead.

Basic Connection Settings

driver

Type: Driver Default: null The preferred way to configure/inject the JDBC Driver through which the Connections will be generated. Vibur will try to determine which is the JDBC Driver to be used in this particular order: if the driver property is specified it will be the one that will be used; if this property is not specified then Vibur will check the driverClassName property and if it is specified the JDBC Driver instance will be created from it; if this property is also not specified then Vibur will retrieve the JDBC driver based on the configured jdbcUrl .

driverProperties

Type: Properties Default: null The driver properties that will be used in the call to Driver.connect().

driverClassName

Type: String Default: null The database driver class name. This is an optional parameter if the driver is JDBC 4 compliant. If specified, a call to Class.forName(driverClassName) .newInstance() will be issued during the Vibur DBCP initialization. This is needed when Vibur DBCP is used in an OSGi container and may also be helpful if Vibur DBCP is used in an Apache Tomcat web application that has its JDBC driver JAR file packaged in the app WEB-INF/lib directory. If this property is not specified, Vibur DBCP will fallback to the standard JavaSE Service Provider mechanism in order to find the driver.

jdbcUrl

Type: String Default: Supplied by user The database JDBC connection string.

username

Type: String Default: Supplied by user The user name to use when connecting to the database.

password

Type: String Default: Supplied by user The password to use when connecting to the database.

externalDataSource

Type: String Default: null If specified, this externalDataSource will be used as an alternative way to obtain the raw connections for the pool instead of calling DriverManager.getConnection() .

allowConnectionAfterTermination

Type: boolean Default: false In rare circumstances, the application may need to obtain a non-pooled connection from the pool after the pool has been terminated. This may happen as part of some post-caching or application shutdown execution path.

allowUnwrapping

Type: boolean Default: true Controls whether the pool's DataSource and the created from it JDBC objects (Connection, Statement, etc) support unwrapping/exposing of the underlying (proxied) JDBC objects. If disabled, the call to Wrapper.isWrapperFor() on any of these objects will always return false .

Connection Timeouts and Retries Settings

The connectionTimeoutInMs and loginTimeoutInSeconds settings below are strongly related to the connection validation settings from the next section. It is very important to note that none of these settings affects the general socket connect and read timeout values that apply between the user application and the remote database server. Usually, the socket read timeout is set on OS level and is often 10 minutes. This means that if a network partitioning occurs the application may get blocked on a database read operation (for example while retrieving data after executing a select query) for up to this timeout.

If the application wishes to guarantee that in the case of a network disconnect a smaller timeout will apply (say 30 seconds), the application developer can either manually call the setNetworkTimeout() method if it is supported by the concrete JDBC driver or configure the useNetworkTimeout setting from the next section. The application can also utilize the specific for the driver socket timeout configuration options that are usually specified with an ampersand on the jdbcUrl string set by the application. The difference is that the setNetworkTimeout() method needs to be called explicitly on each connection after its creation, while the timeouts configured through the driver options or via the useNetworkTimeout setting apply implicitly to all connections. Examples of similar driver options are the MySQL Connector/J connectTimeout and socketTimeout, and the PostgreSQL pgjdbc driver loginTimeout, connectTimeout and socketTimeout options.

connectionTimeoutInMs

Type: long Default: 15000 Time to wait before a call to DataSource .getConnection() times out and throws an SQLTimeoutException . More precisely, that is the time to wait to obtain a connection from the pool when there is a ready and valid connection in the pool. 0 means forever. If there is no ready and valid connection in the pool, and if the maximum pool capacity is not reached yet, the total time that the call to getConnection() can take may include the time to lazily create a new connection, and is defined as: maxTimeoutInMs = connectionTimeoutInMs + loginTimeoutInSeconds * 1000

loginTimeoutInSeconds

Type: int Default: 5 Login timeout, which will be passed to the DriverManager .setLoginTimeout() or getExternalDataSource() .setLoginTimeout() method during the initialization process of the DataSource.

acquireRetryDelayInMs

Type: long Default: 500 After attempting to lazily create a JDBC Connection as part of the DataSource .getConnection() flow and failing with an SQLException , wait for this long before attempting again.

acquireRetryAttempt

Type: int Default: 3 After attempting to lazily create a JDBC Connection as part of the DataSource .getConnection() flow and failing with an SQLException , retry maximum these many times before giving up.

Connection Validation Settings

connectionIdleLimitInSeconds

Type: int Default: 5 If the connection has stayed in the JDBC pool for at least connectionIdleLimitInSeconds , it will be validated using the testConnectionQuery before being given to the application. If set to 0 , it will always validate the connection taken from the pool. If set to a negative number, it will never validate the connection taken from the pool. Applications which have strict requirements and expectations that the JDBC Connection checked out from the pool is valid at the time of the check out may benefit from reducing the value of connectionIdleLimitInSeconds to 1 second. Setting the value to 0 should be done with care as each connection validation requires one additional return-trip to the database. Also see the introductory paragraph from the connection timeouts and retries settings section.

validateTimeoutInSeconds

Type: int Default: 3 The timeout that will be passed to the call to testConnectionQuery when a taken from the pool JDBC Connection is validated before use, or when initSQL is executed (if specified). 0 means no limit.

testConnectionQuery

Type: String Default: isValid Used to test the validity of a JDBC Connection. If the connectionIdleLimitInSeconds is set to a non-negative number, the testConnectionQuery should be set to a valid SQL query; for example, SELECT 1 , or isValid , in which case the Connection.isValid() method will be used. Similar to the spec for Connection.isValid(int), if a custom testConnectionQuery is specified, it will be executed in the context of the current transaction. Note that if the driver is JDBC 4 compliant, using the default isValid value is strongly recommended, as the driver can often use some ad-hoc and very efficient mechanism via which to positively verify whether the given JDBC connection is still valid or not.

initSQL

Type: String Default: null An SQL query that will be run only once when a JDBC Connection is first created. This property should be set to a valid SQL query, to a null value that means no query, or to isValid , which means the Connection.isValid() method will be used. A case in which this property can be useful is when the application is connecting to the database via some middleware; for example, connecting to PostgreSQL server(s) via PgBouncer.

useNetworkTimeout

Type: boolean Default: false This option applies only if testConnectionQuery or initSQL are enabled and if at least one of them has a value different than isValid . If enabled, the calls to the validation or initialization SQL query will be preceded by a call to setNetworkTimeout(), and after that the original network timeout value will be restored. Note that it is responsibility of the application to make sure that the used JDBC driver supports the setNetworkTimeout() method. Also, a suitable networkTimeoutExecutor needs to be configured, see the next configuration option. Also see the introductory paragraph from the connection timeouts and retries settings section.

networkTimeoutExecutor

Type: Executor Default: null This option applies only if useNetworkTimeout is enabled. This is the Executor that will be passed to the call of setNetworkTimeout(). Note that it is responsibility of the application to supply Executor that is suitable for the needs of the concrete JDBC driver. For example, some JDBC drivers may require a synchronous Executor .

Slow SQL Queries and Large ResultSets Logging Settings

logQueryExecutionLongerThanMs

Type: long Default: 3000 The underlying SQL queries (including their concrete parameters) from a JDBC Statement execute... calls taking longer than or equal to this time limit are logged at the WARN level. A value of 0 will log all such calls. A negative number disables it. Note that while a JDBC Statement execute... call duration is roughly equivalent to the execution time of the underlying SQL query, the overall call duration may also include some Java GC time, JDBC driver specific execution time, and context switching time (the last could be significant if the application has a large thread count).

logStackTraceForLongQueryExecution

Type: boolean Default: false Will apply only if logQueryExecutionLongerThanMs is enabled, and if set to true , will log at the WARN level the current JDBC Statement execute... call stack trace. A log sample generated by Vibur DBCP for slow SQL queries is shown here.

logLargeResultSet

Type: long Default: 500 The underlying SQL queries (including their concrete parameters) from a JDBC Statement execute... calls that generate ResultSets with length greater than or equal to this limit are logged at WARN level. A negative number disables it. Retrieving of a large ResultSet may have negative effect on the application performance and may sometimes be an indication of a very subtle application bug, where the whole ResultSet is retrieved, but only the first few records of it are subsequently read and processed. The logging is done at the moment when the application issues a call to the ResultSet.close() method. Applications that rely on the implicit closure of the ResultSet when the generated it Statement is closed, will not be able to benefit from this logging functionality. The calculation of the ResultSet size is done based on the number of calls that the application has issued to the ResultSet.next() method. In most of the cases this is a very accurate and non-intrusive method to calculate the ResultSet size, particularly in the case of a Hibernate or Spring Framework JDBC application. However, this calculation mechanism may give inaccurate results in some advanced cases where the application navigates through the ResultSet with methods such as first() , last() , or afterLast() .

logStackTraceForLargeResultSet

Type: boolean Default: false Will apply only if logLargeResultSet is enabled, and if set to true , will log at WARN level the current ResultSet.close() call stack trace.

includeQueryParameters

Type: boolean Default: true Enables or disables the collection of concrete SQL query parameters for StatementExecution hook and ResultSetRetrieval hook. Also see logQueryExecutionLongerThanMs and logLargeResultSet . Disabling parameters collection can be useful if there are specific compliance requirements for the user application.

Slow getConnection() Calls and Timeouts Logging Settings

logConnectionLongerThanMs

Type: long Default: 3000 The DataSource.getConnection() method calls that take longer than or equal to this time limit are logged at the WARN level. A value of 0 will log all such calls. A negative number disables it. If the value of logConnectionLongerThanMs is greater than connectionTimeoutInMs , then logConnectionLongerThanMs will be set to the value of connectionTimeoutInMs .

logStackTraceForLongConnection

Type: boolean Default: false Will apply only if logConnectionLongerThanMs is enabled, and if set to true, will log at the WARN level the current getConnection() call stack trace. A log sample generated by Vibur DBCP for slow getConnection() calls is shown here.

logTakenConnectionsOnTimeout

Type: boolean Default: false If set to true , and if the connectionTimeoutInMs is reached and the call to getConnection() fails with throwing an SQLTimeoutException , will log at WARN level information about all currently taken connections, including the stack traces of the threads that have taken them (at the moment when they have taken them), plus the threads names and states, as well as the current execution stack traces of these threads, if they are still alive. This options implies that the poolEnableConnectionTracking option is enabled, and if the last is not explicitly enabled it will be implicitly enabled as part of the processing of this option. Note that this option should be used for troubleshooting purposes only, as it may generate a very large log output. The exact format of the logged message is controlled by the takenConnectionsFormatter .

logAllStackTracesOnTimeout

Type: boolean Default: false This option will apply only if logTakenConnectionsOnTimeout is enabled, and if set to true , will add to the log generated by ViburDataSource .getTakenConnectionsStackTraces() the current stack traces of all live threads in the JVM that are not holding connections. In other words, the combination of these two options is equivalent to generating a full JVM thread dump, and thus it has to be used for troubleshooting purposes only, as it may generate a very large log output.

logLineRegex

Type: Pattern Default: null If different than null , this regex will be matched against the string representation of each stack trace line that needs to be logged according logStackTraceForLongConnection , logStackTraceForLargeResultSet , logStackTraceForLongQueryExecution , logTakenConnectionsOnTimeout , and logAllStackTracesOnTimeout . Stack trace lines which string representation matches this regex will be logged while all others will be skipped. This may help to significantly reduce the length of the logged stack traces. A value of null disables the regex matching and (by default) all stack trace lines will be logged. For example, if the logging is done in the context of a Tomcat application, and if the application wants to omit from logging all stack trace lines that contain doFilter in them, then the application can set the following compiled regex pattern: Pattern.compile("^((?!doFilter).)*$")

takenConnectionsFormatter

Type: TakenConnectionsFormatter Default: TakenConnectionsFormatter. Default The interface implemented by this configuration option allows the application to implement custom formatting to String of the TakenConnection[] array. Note that the default formatter uses the logLineRegex for filtering.

Connection and Method Invocation Hooks

Note that all programming hooks described in this section must be set once only during the pool configuration phase and must not be modified after the pool is started.

Multiple hooks of one and the same type can be registered and they will be executed in the order in which they were registered; i.e., if there are N registered hooks from a particular type, the first registered hook will be executed first, then the second, the third, and so on.

viburDS .getConnHooks() .addOnInit(...)

Type: Hook. InitConnection Default: n/a A programming hook that will be invoked only once after the raw JDBC Connection is first created. This is the place to execute an application specific one-off Connection setup logic, such as setting the default Connection network timeout or similar. The hook execution should take as short time as possible. This hook complements the DestroyConnection hook, which is invoked after the raw JDBC Connection is destroyed.

viburDS .getConnHooks() .addOnGet(...)

Type: Hook. GetConnection Default: n/a A programming hook that will be invoked on the raw JDBC Connection after it was taken from the pool as part of the DataSource.getConnection() flow. This is the place to execute an application specific per taken Connection logic, such as setting the default database schema or role, if they are different for the different getConnection() calls. The hook execution should take as short time as possible. This hook complements the CloseConnection hook, which is invoked before the raw JDBC Connection is restored back to the pool.

viburDS .getConnHooks() .addOnClose(...)

Type: Hook. CloseConnection Default: n/a A programming hook that will be invoked on the raw JDBC Connection before it is restored back to the pool as part of the Connection.close() flow. This is the place to execute an application specific per taken Connection winding down logic, if needed. The hook execution should take as short time as possible. This hook complements the GetConnection hook, which is invoked after the raw JDBC Connection is taken from the pool.

viburDS .getConnHooks() .addOnDestroy(...)

Type: Hook. DestroyConnection Default: n/a A programming hook that will be invoked only once after the raw JDBC Connection is closed/destroyed by the ConnectionFactory. The hook execution should take as short time as possible. This hook complements the InitConnection hook, which is invoked after the raw JDBC Connection is first created.

viburDS .getConnHooks() .addOnTimeout(...)

Type: Hook. GetConnectionTimeout Default: n/a A programming hook that will be invoked only when the call to DataSource.getConnection() timeouts. In fact, the invocation of this hook indicates that the current call to getConnection() will throw an SQLTimeoutException . The application can use this hook to format and redirect the generated taken connections log to a different than the usual log location, for example, to Amazon S3. Note that in order to enable the taken connections tracking the application must set poolEnableConnectionTracking to true. The default hook uses the takenConnectionsFormatter ; it can be disabled via setting logTakenConnectionsOnTimeout to false. Note that if the thread waiting on a getConnection() call is interrupted, this does not count as a timeout. The hook execution should take as short time as possible.

viburDS .getInvocationHooks() .addOnMethodInvocation(...)

Type: Hook. MethodInvocation Default: n/a A programming hook that will be invoked before a method on the proxied JDBC interfaces is invoked. It intercepts (almost) all such method calls. Methods inherited from the Object class, methods related to the closed state of the JDBC objects (e.g., close() , isClosed() ), as well as methods from the Wrapper interface are not intercepted. The hook execution should take as short time as possible.

viburDS .getInvocationHooks() .addOnStatementExecution(...)

Type: Hook. StatementExecution Default: n/a A programming hook that will be invoked around the call of each JDBC Statement "execute..." method. The hook is responsible for using the supplied StatementProceedingPoint to pass the call to the intercepted Statement "execute..." method or to the next registered StatementExecution hook. The hook execution should take as short time as possible. An example implementation for this hook may look like: (proxy, method, args, sqlQuery, sqlQueryParams, proceed) -> { // Java 8 try { // do something before the real method execution, for example, // increment a queriesInProcess counter or start a stopwatch Object result = proceed.on(proxy, method, args, sqlQuery, sqlQueryParams, proceed); return result; } finally { // do something after the real method execution, for example, // decrement a queriesInProcess counter or stop a stopwatch } }

viburDS .getInvocationHooks() .addOnResultSetRetrieval(...)

Type: Hook. ResultSetRetrieval Default: n/a A programming hook that will be invoked at the end of each ResultSet retrieval as part of the ResultSet.close() flow. For implementation details see the comments for logLargeResultSet. The hook execution should take as short time as possible.

Connection Default Behavior Settings

clearSQLWarnings

Type: boolean Default: false If set to true , will clear the SQL Warnings (if any) from the JDBC Connection after use and before returning it to the pool. Similarly, if statement caching is enabled, will clear the SQL Warnings (if any) from the JDBC Prepared or Callable Statement after use and before returning it to the statement cache.

resetDefaultsAfterUse

Type: boolean Default: false If set to true , will reset the four connection default values below, always after the connection is restored (returned) to the pool after use. If the calling application never changes these default values, resetting them is not needed.

defaultAutoCommit

Type: boolean Default: Driver's default The default auto-commit state of the created connections.

defaultReadOnly

Type: boolean Default: Driver's default The default read-only state of the created connections.

defaultTransactionIsolation

Type: String Default: Driver's default The default transaction isolation level for the created connections.

defaultCatalog

Type: String Default: Driver's default The default database catalog of the created connections.

JDBC Statement Caching Settings

statementCacheMaxSize

Type: int Default: 0 Defines the maximum statement cache size. 0 disables it, and the max size allowed is 2000 . If the statement's cache is not enabled, the client application may safely exclude the dependency on ConcurrentLinkedHashMap. It is worth noting that the CLHM implementation employs a LRU eviction strategy, and that the eviction of the LRU map entry happens in the context of the thread that has executed an insert ( putIfAbsent ) map operation - which has increased the CLHM size above its predefined maxSize.

Pool Size Reduction Settings (advanced)

poolReducerClass

Type: String Default: org.vibur.dbcp.pool. PoolReducer The fully qualified pool reducer class name. This pool reducer class will be instantiated via reflection, and will be instantiated only if reducerTimeIntervalInSeconds is greater than 0 . It must implement the ThreadedPoolReducer interface and must also have a public constructor accepting a single argument of type ViburDBCPConfig . This setting is useful for applications that need to provide their own custom monitoring of the number of currently allocated in the pool connections, including their own logic for reducing the number of currently unused connections.

reducerTimeIntervalInSeconds

Type: int Default: 30 The time period after which the poolReducer will try to (possibly) reduce the number of created but unused JDBC connections in this pool. 0 disables it. Note that enabling the poolReducer will cause a background daemon thread to be started for the needs of the configured poolReducerClass . This thread is the only service thread that will be ever started for a configured Vibur DBCP instance.

reducerSamples

Type: int Default: 15 How many times the poolReducer will wake up during the given reducerTimeIntervalInSeconds period in order to sample various statistics from the pool.

Critical SQL States (advanced)

criticalSQLStates

Type: String Default: 08001,08006,08007, 08S01,57P01,57P02, 57P03,JZ0C0,JZ0C1 The list of critical SQL states as a comma separated values, see this stackoverflow answer. If an SQL exception that has any of these SQL states is thrown, then all connections in the pool will be considered invalid and will be closed.

JMX Related Settings

enableJMX

Type: boolean Default: true Enables or disables the Vibur DBCP DataSource JMX exposure. Also see the pool name configuration option.

Development Team

Vibur DBCP is designed and developed by Simeon Malchev and contributors, and is currently maintained by Simeon. If you need to report any issues, to request features, or if you just wish to provide some general feedback, please use the project's issues tracking system or the author's email.

The source code of all Vibur projects is distributed under Apache License 2.0, and is free to use for commercial or non-commercial use.

This open-source project has received support from IntelliJ via providing an excellent Java IDE and from EJ-Technologies through their outstanding Java profiler.