Spring JDBC Template with BoneCP Connection Pooling Configuration

Question

I have one java spring application with MySQL as a database. The MySQL connection limit is approx 12k. But my current application contain only basic configuration so my MySQL hang up while client connection reached up to 1500 to 1600 connection only. Does anyone have an idea how to configure BoneCP connection pool for my current MySQL.

Configuration

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value=“{URL}”/>    
<property name="username" value=“{USER}”/>
<property name="password" value=“{PASSWORD}”/>
<property name="idleConnectionTestPeriodInMinutes" value="60"/>
<property name="idleMaxAgeInMinutes" value="240"/>
<property name="maxConnectionsPerPartition" value="30"/>
<property name="minConnectionsPerPartition" value="10"/>
<property name="partitionCount" value="3"/>
<property name="acquireIncrement" value="5"/>
<property name="statementsCacheSize" value="100"/>
<property name="releaseHelperThreads" value="3"/>
<property name="connectionTestStatement" value="Select    1"/>
</bean>

Answer 1

Please see this updated configuration thanks.

  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("org.hsqldb.jdbcDriver");
    } catch (Exception e) {
        e.printStackTrace();
        return;
    }

    try {

        // setup the connection pool
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl("jdbc:hsqldb:mem:test");
        // url
        // specific to
        // your database
        config.setUsername("sa");
        config.setPassword("");
        config.setMinConnectionsPerPartition(50);
        config.setMaxConnectionsPerPartition(200);
        config.setPartitionCount(50);
        config.setLazyInit(false);
        connectionPool = new BoneCP(config); // setup the connection pool
        for (int i = 0; i < 200000; i++) {
            connection = connectionPool.getConnection(); // fetch a //connection
            System.out.println("Connection" + connection);

            if (connection != null) {
                System.out.println("Number of Connection successful " + i);
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS"); // do
                // something
                // with
                // the
                // connection.
                while (rs.next()) {
                    System.out.println(rs.getString(1)); // should print out
                    // "1"'
                }

            }
        }
        connectionPool.shutdown(); // shutdown connection pool.
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

}