CODEWITHSUNDEEP
springoracle-databasejdbcspring-bootjdbctemplate
Marged
Marged

Reputation: 10953

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

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 ?

Upvotes: 2

Views: 9403

Answers (1)

M. Deinum
M. Deinum

Reputation: 124526

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.

Upvotes: 3

Related Questions