Enabling PreparedStatement pooling in DBCP

Question

I want to know how to set dbcp to use pool PreparedStatements. I seem to have connection pooling working but can't find much in the way of examples for prepared statements. There's a related question which goes into using the PreparedStatement pool but skips over the details of getting it set up in the first place. Apparently it involves passing a KeyedObjectPoolFactory to the PoolableConnectionFactory, but it's not at all clear to me which implementation I should use or how to create the objects needed by their constructors.

Here's how I'm currently setting up connection pooling:

private PoolingDataSource setupDataSource() {
    ConnectionFactory connection_factory = new ConnectionFactoryImpl();
    ObjectPool connection_pool = new GenericObjectPool();
    PoolableConnectionFactory poolable_connection_factory = new PoolableConnectionFactory(connection_factory, connection_pool, null, "select 1", false, true);
    PoolingDataSource data_source = new PoolingDataSource(connection_pool);

    return data_source;
}

private static class ConnectionFactoryImpl implements ConnectionFactory {
    private Properties connection_properties = new Properties();

    public ConnectionFactoryImpl() {
        connection_properties.put("user", USER);
        connection_properties.put("password", PASSWORD);
        connection_properties.put("zeroDateTimeBehavior", "convertToNull");
        connection_properties.put("jdbcCompliantTruncation", "false");
    }

    @Override
    public Connection createConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://" + SERVER + "/" + DEFAULT_DB, connection_properties);
    }
}

It's the third parameter to the PoolableConnectionFactory that controls prepared statement pooling, but I'm not sure what to use there.

Answer 1

If you are using spring, it has a helper been that sets it all up. However, statement pooling is disabled by default, so you need to add the last 2 settings in:

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="${database.driverClassName}"/>
    <property name="url" value="${database.url}"/>
    <property name="username" value="${database.username}"/>
    <property name="password" value="${database.password}"/>
    <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
    <property name="testOnBorrow" value="true"/>
    <property name="initialSize" value="1"/>
    <property name="minIdle" value="1"/>
    <property name="maxActive" value="10"/>
    <property name="poolPreparedStatements" value="true"/>
    <property name="maxOpenPreparedStatements" value="20"/>
</bean>

Here is an example of creating a BasicDataSource for standalone apps: http://www.kodejava.org/examples/803.html

once you have that, do the following:

dataSource.setPoolPreparedStatements(true)
dataSource.setMaxOpenPreparedStatements(20);