CODEWITHSUNDEEP
javasql-serverspring-bootcloud-foundryspring-cloud-connectors
andylau
andylau

Reputation: 3

Cannot set connectionProperties with Spring Cloud Connectors in PCF with SqlServer

I am setting up my DataSource in my project which has been deployed to PCF,and there is performance issue about sqlserver,so i want to add the property:sendStringParametersAsUnicode=false to fix this issue,just as the local jdbcUrl setting:url: jdbc:sqlserver://localhost:1433;databaseName=localdatasource;sendStringParametersAsUnicode=false,

But I tried both of the below code ,it seems that the connectionConfig does not work.

Code1 reference Spring Cloud Spring Service Connector guide

@Configuration
@Profile({"dev", "sit", "uat", "prod"})
@Slf4j
public class CloudConfig extends  AbstractCloudConfig {
    @Value("${datasourceinfo.min-idle}")
    private int dataSourceMinPoolSize = 20;
    @Value("${datasourceinfo.max-active}")
    private int dataSourceMaxPoolSize = 100;
    @Value("${datasourceinfo.max-wait}")
    private int dataSourceMaxWaitTime = -1;

    @Value("${datasourceinfo.azureDatabase}")
    private String azureDatabase;

    @Bean
    @Primary
    public DataSource dataSource() {
        PooledServiceConnectorConfig.PoolConfig poolConfig = new PooledServiceConnectorConfig.PoolConfig(dataSourceMinPoolSize, dataSourceMaxPoolSize, dataSourceMaxWaitTime);
        DataSourceConfig.ConnectionConfig connConfig = new DataSourceConfig.ConnectionConfig("sendStringParametersAsUnicode=false");
        DataSourceConfig dbConfig = new DataSourceConfig(poolConfig, connConfig);
        log.info("======================init dataSource connProperties {}",dbConfig);
        return connectionFactory().dataSource(azureDatabase, dbConfig);
    }
}

Code2:following the advice here: Spring Cloud Connectors issue

    @Primary
    public DataSource dataSource() {
        PooledServiceConnectorConfig.PoolConfig poolConfig = new PooledServiceConnectorConfig.PoolConfig(dataSourceMinPoolSize, dataSourceMaxPoolSize, dataSourceMaxWaitTime);
        Map<String, Object> connProperties = new HashMap<>();
        connProperties.put("connectionProperties","sendStringParametersAsUnicode=false");
        DataSourceConfig dbConfig = new DataSourceConfig(poolConfig,null,null,connProperties);
        log.info("======================init dataSource connProperties {}",dbConfig);
        return connectionFactory().dataSource(azureDatabase, dbConfig);
    }

The logs as below:

2020-05-26T20:13:48.724+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.724+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] HikariPool-1 - configuration:
2020-05-26T20:13:48.727+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.727+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] connectionTestQuery............."SELECT 1"
2020-05-26T20:13:48.727+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.727+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] connectionTimeout...............30000
2020-05-26T20:13:48.728+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.728+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] driverClassName................."com.microsoft.sqlserver.jdbc.SQLServerDriver"
2020-05-26T20:13:48.729+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.729+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] jdbc4ConnectionTest.............false
2020-05-26T20:13:48.729+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.729+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] jdbcUrl.........................**jdbc:sqlserver://myIp:1433;database=devDb;user=u4a1780c36;password=<masked>;Encrypt=true;TrustServerCertificate=false;HostNameInCertificate=*.database.windows.net;loginTimeout=30;**
2020-05-26T20:13:48.729+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.729+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] leakDetectionThreshold..........0
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.729+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] maxLifetime.....................1800000
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] maximumPoolSize.................100
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] metricRegistry..................none
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] metricsTrackerFactory...........com.zaxxer.hikari.metrics.micrometer.MicrometerMetricsTrackerFactory@75add13c
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] minimumIdle.....................20
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] password........................<masked>
2020-05-26T20:13:48.730+08:00 [APP/PROC/WEB/0] [OUT] DEBUG . [26-05-2020 20:13:48.730+0800] [main] [springAppName_IS_UNDEFINED,,,] [c.z.h.HikariConfig] poolName........................"HikariPool-1"

From the logs,I found that the PoolConfig [minimumIdle=20,maximumPoolSize=100] was update ,but the ConnectionConfig was ignored,and i don't know what happened or is there sonemthing wrong with my code. Thanks.

Upvotes: 0

Views: 1011

Answers (1)

Scott Frederick
Scott Frederick

Reputation: 5115

You're using the HikariCP connection pooling library, and I don't think what you're trying to do is possible with HikariCP and Spring Cloud Connectors. 

    DataSourceConfig.ConnectionConfig connConfig = new DataSourceConfig.ConnectionConfig("sendStringParametersAsUnicode=false");
    DataSourceConfig dbConfig = new DataSourceConfig(poolConfig, connConfig);

When using this configuration, Connectors will attempt to call a setter method named setConnectionProperties on the detected DataSource implementation. HikariDataSource does not have a setConnectionProperties method like the other supported connection pooling libraries do, so this configuration will have no effect. The same is true with the Map-based configuration option, since HikariDataSource does not expose any other method that allows such properties to be set.

In order to get this to work, you would need to switch to one of the other supported connection pooling libraries.

Note that Spring Cloud Connectors is in maintenance mode. Please consider switching to Java CFEnv instead. Java CFEnv is more flexible in terms of allowing you to use the JDBC URL directly (with modification as needed) or allowing connection details to be overridden with Spring Boot properties.

Upvotes: 1

Related Questions