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

Question

I need to set some specific JDBC connection properties to speed up the INSERT batch ( defaultBatchValue ) and the SELECT mass ( defaultRowPrefetch ). I received suggestions on how to do this using DBCP (thanks to M. Deinum), but I would like to:

• save Tomcat jdbc default connection pool
• save application.yml for configuration

I was thinking about requesting the support function spring.datasource.custom_connection_properties or the like in the future, and because of this I tried to pretend that it was already possible. I did this by passing the relevant information when creating the DataSource and manipulating the creation of the DataSource as follows:

 @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 see that I am creating the correct DataSource:

 2016-01-18 14:40:32.924 DEBUG 31204 --- [ main] daeacconfig.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] daeacconfig.DatabaseConfiguration : properties after: {password=wonttell, driverClassName=oracle.jdbc.OracleDriver, defaultRowPrefetch=1000, defaultBatchValue=1000, url=jdbc:oracle:thin:@xyz:1521:abc, username=ihave} 

The drive shows that my code replaced the data source:

But the settings are not activated, which I see when profiling the application. defaultRowPrefetch is still at 10 , which causes my SELECT be much slower than if it had been activated by 1000 .

Answer 1

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

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

Edit (some background information):

Note that you can configure any of the DataSource implementations using spring.datasource. *: refer to the documentation about the implementation of the connection pool, which you use for more detailed information.

source: spring-boot documentation

Answer 2

More info to complement @Cyril's answer. If you want to use your answer, not mine.

I was a little puzzled by how easy it is to set additional connection properties that are ultimately used when creating a database connection. So I did a little work.

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

The trait doesn't matter because of the relaxed binding , but if you interpret it literally, the property name is spring.datasource.connection-properties .

The correct setup in application.yml looks like this:

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

This deserves attention, as confirmed by my perf4j measurements of SELECT s mass.

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] [147] tag [get elements]

SQL statement execution time decreases from 1377 to 147, which is a huge increase in performance.

Answer 3

After digging into the Tomcat code for the bits, I found that dataSource.getPoolProperties().getDbProperties() is a Properties object that will actually be used to create connections for the pool.

If you use the BeanPostProcessor approach mentioned in @ m-deinum, but instead use it to populate dbProperties like this, you should be able to add properties so that they are locked and 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 4

Since Spring Boot has been EOL for a long time, I upgraded to Spring Boot 2.1 with its new Hikari default connection pool. The solution here is even simpler and can be done in application.properties or (as shown here) application.yml:

 spring: datasource: hikari: data-source-properties: defaultRowPrefetch: 1000 

(In a real configuration, there would be several other configuration elements, but since they are not of interest to the question asked, I just skipped them in my example)