How to set custom connection properties on DataSou

Question

I need to set some specific Oracle JDBC connection properties in order to speed up batch INSERTs (defaultBatchValue) and mass SELECTs (defaultRowPrefetch). I got suggestions how to achieve this with DBCP (Thanks to M. Deinum) but I would like to:

• keep the default Tomcat jdbc connection pool
• keep application.yml for configuration

I was thinking about a feature request to support spring.datasource.custom_connection_properties or similar in the future and because of this tried to pretent this was already possible. I did this by passing the relevant information while creating the DataSource and manipulated the creation of the DataSource like this:

@Bean
public DataSource dataSource() {
    DataSource ds = null;

    try {
        Field props = DataSourceBuilder.class.getDeclaredField("properties");
        props.setAccessible(true);
        DataSourceBuilder builder = DataSourceBuilder.create();
        Map<String, String> properties = (Map<String, String>) props.get(builder);

        properties.put("defaultRowPrefetch", "1000");
        properties.put("defaultBatchValue", "1000");

        ds = builder.url( "jdbc:oracle:thin:@xyz:1521:abc" ).username( "ihave" ).password( "wonttell" ).build();

        properties = (Map<String, String>) props.get(builder);

        log.debug("properties after: {}", properties);
    } ... leaving out the catches ...
    }
    log.debug("We are using this datasource: {}", ds);
    return ds;
}

In the logs I can see that I am creating the correct DataSource:

2016-01-18 14:40:32.924 DEBUG 31204 --- [           main] d.a.e.a.c.config.DatabaseConfiguration   : We are using this datasource: org.apache.tomcat.jdbc.pool.DataSource@19f040ba{ConnectionPool[defaultAutoCommit=null; ...

2016-01-18 14:40:32.919 DEBUG 31204 --- [           main] d.a.e.a.c.config.DatabaseConfiguration   : properties after: {password=wonttell, driverClassName=oracle.jdbc.OracleDriver, defaultRowPrefetch=1000, defaultBatchValue=1000, url=jdbc:oracle:thin:@xyz:1521:abc, username=ihave}

The actuator shows me that my code replaced the datasource:

But the settings are not activated, which I can see while profiling the application. The defaultRowPrefetch is still at 10 which causes my SELECTs to be much slower than they would be if 1000 was activated.

Answer 1

Some additional information to complement the answer by @Cyril. If you want to upvote use his answer, not mine.

I was a little bit puzzled how easy it is to set additional connection properties that in the end get used while creating the database connection. So I did a little bit of research.

spring.datasource.connectionProperties is not mentioned in the reference. I created an issue because of this. If I had used the Spring Boot YML editor, I would have seen which properties are supported. Here is what STS suggests when you create an application.yml and hit Ctrl+Space:

The dash does not matter because of relaxed binding but if you interpret it literally the propertys name is spring.datasource.connection-properties.

The correct setup in application.yml looks like this:

spring:
    datasource:
        connection-properties: defaultBatchValue=1000;defaultRowPrefetch=1000
        ...

This gets honored which is proven by my perf4j measurements of mass SELECTs.

Before:

2016-01-19 08:58:32.604 INFO 15108 --- [ main] org.perf4j.TimingLogger : start[1453190311227] time[1377] tag[get elements]

After:

2016-01-19 08:09:18.214 INFO 9152 --- [ main] org.perf4j.TimingLogger : start[1453187358066] time[147] tag[get elements]

The time taken to complete the SQL statement drops from 1377ms to 147, which is an enormous gain in performance.

Answer 2

After digging around in the Tomcat code for a bit, I found that the dataSource.getPoolProperties().getDbProperties() is the Properties object that will actually get used to generate connections for the pool.

If you use the BeanPostProcessor approach mentioned by @m-deinum, but instead use it to populate the dbProperties like so, you should be able to add the properties in a way that makes them stick and get passed to the Oracle driver.

import java.util.Properties;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolConfiguration;

@Component
public class OracleConfigurer implements BeanPostProcessor {
    @Override
    public Object postProcessBeforeInitialization(Object bean, String name) throws BeansException {
        if (bean instanceof DataSource) {
            DataSource dataSource = (DataSource)bean;
            PoolConfiguration configuration = dataSource.getPoolProperties();
            Properties properties = configuration.getDbProperties();
            if (null == properties) properties = new Properties();
            properties.put("defaultRowPrefetch", 1000);
            properties.put("defaultBatchValue", 1000);
            configuration.setDbProperties(properties);
        }
        return bean;
    }

    @Override
    public Object postProcessAfterInitialization(Object bean, String name) throws BeansException {
        return bean;
    }
}

Answer 3

Setting the pools connectionProperties should work. Those will be passed to the JDBC driver. Add this to application.properties:

spring.datasource.connectionProperties: defaultRowPrefetch=1000;defaultBatchValue=1000

Edit (some background information):

Note also that you can configure any of the DataSource implementation specific properties via spring.datasource.*: refer to the documentation of the connection pool implementation you are using for more details.

source: spring-boot documentation