How to set custom connection properties on DataSource in Spring Boot 1.3.x with default Tomcat connection pool

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.

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