Optimizing JDBC fetch size by use of Spring Boots application.properties

Question

My Spring Boot 1.3.1 based application relies on an Oracle 11.2 database and I want to tune the fetching of SELECT statement results.

JdbcTemplate offers public void setFetchSize(int fetchSize) to tune the fetch size, which for Oracle is preset to 10 by the driver:

Set the fetch size for this JdbcTemplate. This is important for processing large result sets: Setting this higher than the default value will increase processing speed at the cost of memory consumption; setting this lower can avoid transferring row data that will never be read by the application. Default is -1, indicating to use the JDBC driver's default (i.e. to not pass a specific fetch size setting on the driver).

The Oracle JDBC driver (I use ojdbc7.jar because it is downwards compatible) offers a defaultRowPrefetch parameter to increase the fetch size for the complete database connection.

According to the docs this parameter could be set this way:

java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password","tiger");
info.put ("defaultRowPrefetch","15");
getConnection ("jdbc:oracle:oci8:@",info);

But my application is configured using application.yml:

datasource:
    url: jdbc:oracle:thin:@xyz:1521:abc
    username: ${name}
    password: ${password}
    driver-class-name: oracle.jdbc.driver.OracleDriver
    ...

And even if I wanted to change that configuration to use spring.datasource.url=jdbc:... instead there is no way to set the fetch size globally according to this post.

Is there a more "Spring Boot style" approach or do I need to configure each template manually ?

Why would you have more then 1 template? It is thread safe and can be shared, you don't need to create on in each dao (Sprng Boot already configures one for you). You could create a `BeanPostProcessor` which sets some additional config on the `JdbcTemplate` classes. — M. Deinum, Jan 14 '16 at 12:10
I let Spring Boot inject that already predefined bean. Unfortunately there is more than one JdbcTemplate. If you use namedParameterJdbcTemplate it receives its own instance. I "fear" that there might remain a template that was not setup correctly. — Marged, Jan 14 '16 at 12:56
That depends on how you setup the `NamedParameterJdbcTemplate` you can construct it using a `DataSource` which in turn will create an internal `JdbcTemplate` yu can also pass it a preconfigured `JdbcTemplate`. — M. Deinum, Jan 14 '16 at 12:59
I just inject it using @Autowired. What I want to avoid is having to remember that another copy of JdbcTemplate might get used. I already found an approach based on `afterPropertiesSet`, but I consider it as not very "elegant" — Marged, Jan 14 '16 at 13:03
@M.Deinum I found another parameter (defaultBatchValue) that needs to be set. Unfortunately neither of the templates supports this, so I need to find a way to tell Spring / the "master" JdbcTemplate to create a connection with a certain set of `Properties` or special `DataSource` (assuming that I can create this one with the correct Oracle specific settings) :-( — Marged, Jan 14 '16 at 14:24
Regarding a different copy hence the suggestion of a `BeanPostProcessor`... Either for the templates or the `DataSource` that way you can add additional configuration. — M. Deinum, Jan 14 '16 at 15:10
@M.Deinum I think I don't get your point. Would you mind posting an example / answer ? — Marged, Jan 14 '16 at 22:18

score 3 · Accepted Answer · answered Jan 15 '16 at 07:05

3

A BeanPostProcessor will process all the beans in the ApplicationContext and that way you can add additional configuration or replace it totally if you would like.

You could create a BeanPostProcessor that would add the properties to the configured DataSource. The sample below assumes the use of commons-dbcp 1 or 2 if you use a different DataSource modify accordingly.

public class DataSourceConfiguringBeanPostProcessor implements BeanPostProcessor {
    private final Map<String,String> properties = new HashMap<>;

    public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {
        if (bean instance BasicDataSource ) { 
            for (Map.Entry<String, String> prop : properties.entrySet()) {
                ((BasicDataSource) bean).addConnectionProperty(prop.getKey(), prop.getValue());
            }
        }
        return bean;
    }

    public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
        return bean;
    }

    public void setProperties(Map<String, String> properties) {
        this.properties.putAll(properties);
    }
}

Now you can add this to your configuration and it will add the properties to DataSource beans.

@Bean
public BeanPostProcessor dataSourcePostProcessor() {
    DataSourceConfiguringBeanPostProcessor processor = new DataSourceConfiguringBeanPostProcessor();
    Map<String, String> properties = new HashMap<>();
    properties.put("defaultRowPrefetch", "15");
    properties.put("defaultBatchValue", "25");
    processor.setProperties(properties);
    return processor;
}

That should do the trick for configuring the datasource.

answered Jan 15 '16 at 07:05

M. Deinum

115,695
22
220
224

I did not get this to work with the default DataSource used in Spring Boot: `org.apache.tomcat.jdbc.pool.DataSource`. The `DataSourceProxy` provides a get/setConnectionProperties method but I am unable to detect it in the post processor – Marged Jan 18 '16 at 10:30
I am not sure if my problem is related to `Bean 'dataSource' of type [class org.apache.tomcat.jdbc.pool.DataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)` – Marged Jan 18 '16 at 10:38
Have you fixed the `BeanPostProcessor` to detect the proper type? – M. Deinum Jan 18 '16 at 10:54
Yes, I even had it print all classes that contain "DataSource" and besides `org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration` and `...DataSourceProperties` there is not much to be seen :-( – Marged Jan 18 '16 at 11:36
There is no default datasource as that depends on your dependencies. Try doing the instance `DataSource` and see which classes get scanned. But it could be that it is registered to early... – M. Deinum Jan 18 '16 at 11:39
I tried, both with the interface and the concrete tomcat pool implementation: it is not in the list. I am a little bit lost, my only idea is the fiddle with the `DataSourceBuilder` and use reflection to make the private properties member available for writing ... – Marged Jan 18 '16 at 11:44
Yes, I adopted your example – Marged Jan 18 '16 at 12:16
I took another approach but this did not work either. If you want to have a look here is a link to my new question: http://stackoverflow.com/questions/34856811/how-to-set-custom-connection-properties-on-datasource-in-spring-boot-1-3-x-with – Marged Jan 18 '16 at 14:16

Optimizing JDBC fetch size by use of Spring Boots application.properties

1 Answers1

Linked