如何使用默认的Tomcat连接池在Spring Boot 1.3.x中的DataSource上设置自定义连接属性 [英] How to set custom connection properties on DataSource in Spring Boot 1.3.x with default Tomcat connection pool

问题描述

我需要设置一些特定的Oracle JDBC连接属性，以便加快批量 INSERT s（ defaultBatchValue ）的速度。和质量 SELECT （ defaultRowPrefetch ）。
我得到了

但是设置未激活，在分析应用程序时可以看到。 defaultRowPrefetch 仍为 10 ，这导致我的 SELECT 比激活 1000 的速度要慢得多。

解决方案

设置池 connectionProperties 应该起作用。这些将被传递到JDBC驱动程序。将此添加到application.properties：

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

编辑（一些背景信息）：

还要注意，您可以通过spring.datasource。*配置任何数据源实现
的特定属性。*：有关
的更多详细信息，请参见所用连接池实现的
文档。

来源：弹簧启动文档

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.

解决方案

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

这篇关于如何使用默认的Tomcat连接池在Spring Boot 1.3.x中的DataSource上设置自定义连接属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！