通过使用Spring Boots application.properties优化JDBC的获取大小 [英] Optimizing JDBC fetch size by use of Spring Boots application.properties

问题描述

基于Spring Boot 1.3.1的应用程序依赖于Oracle 11.2数据库，并且我想调整SELECT语句结果的获取.

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提供了public void setFetchSize(int fetchSize)来调整获取大小，对于Oracle，该大小由驱动程序预设为10:

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

设置此JdbcTemplate的获取大小.这对于 处理大型结果集:将其设置为高于默认值 值将以内存为代价提高处理速度 消耗;将此值设置得较低可以避免传输行数据 永远不会被应用程序读取.默认值为-1，表示 使用JDBC驱动程序的默认值(即不传递特定的提取大小 在驱动程序上设置).

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).

Oracle JDBC驱动程序(我使用ojdbc7.jar是因为它向下兼容)提供了一个defaultRowPrefetch参数来增加整个数据库连接的获取大小.

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

但是我的应用程序是使用application.yml配置的:

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
    ...

即使我想更改该配置以使用spring.datasource.url=jdbc:...，也无法根据

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.

还有更多的"Spring Boot样式"方法吗?还是需要手动配置每个模板?

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

推荐答案

A

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.

您可以创建一个BeanPostProcessor，该属性会将属性添加到已配置的DataSource中.如果使用相应的DataSource修改，下面的示例假定使用commons-dbcp 1或2.

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

现在您可以将其添加到您的配置中，它将把属性添加到DataSource bean中.

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.

这篇关于通过使用Spring Boots application.properties优化JDBC的获取大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！