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

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.

Modifying the properties is isn't going to work, those aren't the properties you want to modify... — M. Deinum
– M. Deinum, Commented Jan 18, 2016 at 14:26
@M.Deinum I thought these were the properties passed while connecting to the JDBC driver. What makes them different from what I thought ? — Marged
– Marged, Commented Jan 18, 2016 at 14:33
No they aren't passed while connecting. These are the internal properties used by the DataSourceBuilder and contain only a small number of useable properties for internal use. — M. Deinum
– M. Deinum, Commented Jan 18, 2016 at 14:36
You are correct, I stepped this through in the debugger and saw that even if DataSourceBuilder would honor these properties the tomcat jdbc DataSource / ~Proxy does not provide a direct setter for this. — Marged
– Marged, Commented Jan 18, 2016 at 14:42

Cyril · Accepted Answer · 2016-01-19 09:16:13Z

12

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

edited Jan 19, 2016 at 9:16

answered Jan 18, 2016 at 19:13

Cyril

2,47619 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marged Over a year ago

This seems to work for properties that get passed in the jdbc url. In my scenario defaultRowPrefetch needs to be passed in a set of Properties which is the second parameter used in getConnection(String url, Properties prop) and follows right after the jdbc url. But nonetheless thanks for your edit.

Dieter Hubau Over a year ago

As mentioned here by the lead developer on Spring Boot, as of version 1.4, that property no longer exists.

Marged · Accepted Answer · 2019-04-12 09:41:17Z

6

As Spring Boot is EOL for a long time I switched to Spring Boot 2.1 with its new default connection pool Hikari. Here the solution is even more simply and can be done in the application.properties or (like shown here) application.yml:

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

(In a real-life config there would be several other configuration items but as they are not of interest for the question asked I simply left them out in my example)

answered Apr 12, 2019 at 9:41

Marged

11.1k13 gold badges65 silver badges107 bronze badges

Comments

Marged · Accepted Answer · 2016-01-19 09:24:48Z

3

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.

edited Jan 19, 2016 at 9:24

answered Jan 19, 2016 at 8:13

Marged

11.1k13 gold badges65 silver badges107 bronze badges

7 Comments

M. Deinum Over a year ago

It isn't mentioned because it isn't a general available property. It works because you are using Tomcat JDBC, if you would use for instance Commons DBCP or a HikariCP based pool that property wouldn't be available nor work. It is due to the binding that it works (maybe that is a feature that should be documenten).

Marged Over a year ago

You are correct, for the other connection pools we need an approach you showed in my other question. But as Tomcat JDBC pool is the default it wouldn't hurt mentioning specific configuration options ;-) I created an issue at github because of this.

M. Deinum Over a year ago

As I tried to make clear there is no default... It depends on the dependencies you have.

Marged Over a year ago

@M.Deinum Perhaps we are just talking about the definition of "default". My definition of "default" in this case is what get's used when I don't select another connection pool dependency. As the default container in Spring Boot is Tomcat you will automatically get Tomcat JDBC pool (unless you change this)

M. Deinum Over a year ago

Code wise there is no default, that is what for me is leading... Detection is always done...

|

Ahmed Ashour · Accepted Answer · 2018-03-12 10:51:24Z

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;
    }
}

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related