Database connection pool using the JAVA version BoneCP

Sep 21 posted in Database (RSS), comments.

Advertisements

BoneCP is a fast, free, open-source, Java database connection pool (JDBC Pool) library. If you are familiar with C3P0 and DBCP then you already know what this means. For the rest, this is a library that will manage a database connection for you to get faster database access in your application.
BoneCP is fast! For some tests, it's almost 25 times faster than the next fastest connection pool option, not to mention that BoneCP never spin-locks so it won't slow down your application.
Official Homepage: http://jolbox.com/
Download: http://jolbox.com/bonecp/downloads/maven/com/jolbox/bonecp/
The latest version is: 0.7
Dependent on the jar:

A database that accepts connections
A driver to go with it
Google Guava Library, available for free from here .
The SLF4J logging Library.

JDK1.5 or higher.
That
bonecp-0.7.0.jar
google-collections-1.0.jar
log4j-1.2.15.jar
mysql-connector-java-5.1.6-bin.jar (mysql driver)
slf4j-api-1.5.10.jar
slf4j-log4j12-1.5.10.jar
Above jar file can be downloaded here http://jolbox.com/bonecp/downloads/maven/

In the jdbc connection pool using BoneCP

package com.bonecp;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
 
/** 
 * @author sxyx2008
 *
 */
public class ExampleJDBC {
 
    public static void main(String[] args) {
        BoneCP connectionPool = null;
        Connection connection = null;
 
        try {
            // load the database driver (make sure this is in your classpath!)
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        
        try {
            // setup the connection pool
            BoneCPConfig config = new BoneCPConfig();
            config.setJdbcUrl("jdbc:mysql://localhost:3306/demo"); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
            config.setUsername("root"); 
            config.setPassword("root");
            // Setting checks every 60 seconds number of idle connections in database 
            config.setIdleConnectionTestPeriod(60);
            // Set the connection idle time 
            config.setIdleMaxAge(240);
            // Set maximum number of connections in each partition  30
            config.setMaxConnectionsPerPartition(30);
            // Minimum number of connection settings in each partition  10
            config.setMinConnectionsPerPartition(10);
            // When the connection from the connection pool is exhausted BoneCP while getting the number of connections 
            config.setAcquireIncrement(5);
            // Connection release 
            config.setReleaseHelperThreads(3);
            // Set partition partitions 3
            config.setPartitionCount(3);
            // Setting configuration parameters 
            connectionPool = new BoneCP(config); // setup the connection pool
            
            connection = connectionPool.getConnection(); // fetch a connection
            
            if (connection != null){
                System.out.println("Connection successful!");
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(" select * from person "); // do something with the connection.
                while(rs.next()){
                    System.out.println(rs.getString(1)); // should print out "1"'
                    System.out.println(rs.getString(2)); // should print out "1"'
                }
            }
            connectionPool.shutdown(); // shutdown connection pool.
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

xml-mode configuration bonecp

<?xml version="1.0" encoding="UTF-8"?>
<bonecp-config>  
  <default-config>  
    <property name="jdbcUrl">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property>  
    <property name="username">scott</property>  
    <property name="password">tiger</property>  
    <property name="partitionCount">3</property>  
    <property name="maxConnectionsPerPartition">30</property>  
    <property name="minConnectionsPerPartition">10</property>  
    <property name="acquireIncrement">3</property>  
  </default-config>   
</bonecp-config>

Connection code

package com.bonecp;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
 
/** 
 * @author sxyx2008
 *
 */
public class ExampleJDBC {
 
    public static void main(String[] args) {
        BoneCP connectionPool = null;
        
        Connection connection = null;
        try {
            // load the database driver (make sure this is in your classpath!)
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        
        try {
            // setup the connection pool
            BoneCPConfig config = null;
            try {
                config = new BoneCPConfig("bonecp-config.xml");
            } catch (Exception e) {
                e.printStackTrace();
            }
            /*
            config.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl"); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
            config.setUsername("scott"); 
            config.setPassword("tiger");
            // Setting checks every 60 seconds number of idle connections in database 
            config.setIdleConnectionTestPeriod(60);
            // Set the connection idle time 
            config.setIdleMaxAge(240);
            // Set maximum number of connections in each partition  30
            config.setMaxConnectionsPerPartition(30);
            // Minimum number of connection settings in each partition  10
            config.setMinConnectionsPerPartition(10);
            // When the connection from the connection pool is exhausted BoneCP while getting the number of connections 
            config.setAcquireIncrement(5);
            // Connection release 
            config.setReleaseHelperThreads(3);
            // Set partition partitions 3
            config.setPartitionCount(3);
            */
            // Setting configuration parameters 
            connectionPool = new BoneCP(config); // setup the connection pool
            
            long startTime=System.currentTimeMillis();
            // Create 100 connections 
            for (int i = 0; i < 100; i++) {
                connection = connectionPool.getConnection(); // fetch a connection
            }
            long endtTime=System.currentTimeMillis();
            
            System.out.println("-------->total seconds :"+(endtTime-startTime));
            
            if (connection != null){
                System.out.println("Connection successful!");
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(" select * from emp "); // do something with the connection.
                while(rs.next()){
                    System.out.println(rs.getString(1)); // should print out "1"'
                    System.out.println(rs.getString(2)); // should print out "1"'
                }
            }
            connectionPool.shutdown(); // shutdown connection pool.
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Use of a DataSource

package com.bonecp;

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

import com.jolbox.bonecp.BoneCPDataSource;

public class ExampleDataSource {
    
    public static void main(String[] args) {
        
        Connection connection = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        BoneCPDataSource dataSource=new BoneCPDataSource();
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/demo");
        dataSource.setMaxConnectionsPerPartition(10);
        dataSource.setMinConnectionsPerPartition(5);
        dataSource.setIdleConnectionTestPeriod(60);
        dataSource.setIdleMaxAge(240);
        dataSource.setAcquireIncrement(5);
        dataSource.setReleaseHelperThreads(3);
        try {
            connection=dataSource.getConnection();
            if (connection != null){
                System.out.println("Connection successful!");
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(" select * from person "); // do something with the connection.
                while(rs.next()){
                    System.out.println(rs.getString(1)); // should print out "1"'
                    System.out.println(rs.getString(2)); // should print out "1"'
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        
    }
    
}

Use in Hibernate BoneCP

Use in Hibernate BoneCP addition to the above mentioned jar files, they also need to download a file called bonecp-provider-0.7.0.jar jar of bonecp-provider's package, it's download location is: http://jolbox .com/bonecp/downloads/maven/com/jolbox/bonecp-provider/0.7.0/bonecp-provider-0.7.0.jar.

In addition, the need to do the following configuration:

<!-- Hibernate SessionFactory -->  
<bean autowire="autodetect">  
    <property name="hibernateProperties">  
        <props>  
            <prop key="hibernate.connection.provider_class">com.jolbox.bonecp.provider.BoneCPConnectionProvider</prop>  
            <prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>  
            <prop key="hibernate.connection.url">jdbc:mysql://127.0.0.1/yourdb</prop>  
            <prop key="hibernate.connection.username">root</prop>  
            <prop key="hibernate.connection.password">abcdefgh</prop>  
            <prop key="bonecp.idleMaxAge">240</prop>  
            <prop key="bonecp.idleConnectionTestPeriod">60</prop>  
            <prop key="bonecp.partitionCount">3</prop>  
            <prop key="bonecp.acquireIncrement">10</prop>  
            <prop key="bonecp.maxConnectionsPerPartition">60</prop>  
            <prop key="bonecp.minConnectionsPerPartition">20</prop>  
            <prop key="bonecp.statementsCacheSize">50</prop>  
            <prop key="bonecp.releaseHelperThreads">3</prop>  
        </props>  
    </property>  
</bean>

Reference: http://www.blogjava.net/sxyx2008/archive/2011/03/16/346384.html