Vibur DBCP

Main features and characteristics:

• Built using standard Java concurrency utilities and dynamic proxies. Does not use any synchronized blocks/methods during normal pool operations.
• Supports fairness parameter, which when set to 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 pool's underlying resources.
• Vibur DBCP requires Java 1.6+ and only the following external dependencies: its dedicated object pool, slf4j/log4j, and ConcurrentLinkedHashMap. The CLHM dependency can be excluded if the JDBC Statement caching is not needed.

• SQL queries logging and getConnection() calls logging if their execution time is longer than a given limit.
• Caching support for JDBC Statements (Prepared and Callable).
• Hibernate 3.x and 4.x integration support.

Other features:

• Validation intervals support, i.e. the taken from the pool connection is not validated before every use but is validated only if a given time has passed since the connection's last use.
• Intelligent pool sizing - the number of idle connections in the pool can be reduced based on heuristics for the number of recently used connections.

• The raw JDBC connection or Statement object can be retrieved from the respective proxy object via calling the proxy's unwrap method.
• Ability to provide records for all JDBC connections which are currently taken, including the stack traces with which they were taken. Useful if debugging lost (unclosed) connections.
• JMX support - the pool registers an MBean via which various pool parameters can be observed and/or set.

Todo:

• Different usernames and passwords are not supported yet. The call to DataSource.getConnection(username, password) will simply create and return connection using the configured default username and password.

Some Code Metrics and Performance Results

The below source code metrics are not accounting for the projects' testing directories and for the Apache License header which is at the top of each source file:

The below performance results were obtained via running this test with Java 1.7 on a machine with an Intel i7-3615QM 2.3 GHz processor. 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 10 or 20 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.

Usage Examples

Maven dependency and how to build:

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

A read-only copy of the source code can be checked out via:

svn checkout http://vibur-dbcp.googlecode.com/svn/tags/1.0.0 vibur-dbcp-1.0.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.x/4.x integration:

Vibur DBCP comes with Hibernate3 and Hibernate4 integrations. Depending on which Hibernate version your project uses, add one of the following Maven dependencies to it. Note that, these dependencies will transitively include the shown above vibur-dbcp dependency, too.

<!-- For Hibernate 3.x projects: -->
<dependency>
  <groupId>org.vibur</groupId>
  <artifactId>vibur-dbcp-hibernate3</artifactId>
  <version>1.0.0</version>
</dependency>

<!-- For Hibernate 4.x projects: -->
<dependency>
  <groupId>org.vibur</groupId>
  <artifactId>vibur-dbcp-hibernate4</artifactId>
  <version>1.0.0</version>
</dependency>

Hibernate 3.x/4.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.x/4.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 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 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 ViburDBCPDataSource 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="stdout"/>
    </logger>

Configuration Options

Basic database connection settings:

Database connection validation settings:

Basic pool size, growth and fairness settings:

Database connection timeout and retries settings:

JDBC statement caching settings:

Database connections and SQL queries logging settings

Database connection default behavior settings:

Pool size reduction settings (advanced):

Development Team

Vibur DBCP is designed, developed and provided to you by Simeon Malchev (firstname.lastname at gmail.com). 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.

Acknowledgements

I would like to thank to all my friends and colleagues who reviewed the original project documentation and source code. Your encouragement and feedback helped me to bring the project to its current state.

Vibur DBCP was developed from scratch, however while working on it the author has often looked at the documentation and/or source code of projects such as Spring Framework, BoneCP, Tomcat JDBC Connection Pool, between others.