How to specify database specific properties while using HikariCP in Java?

Question

I need to add Oracle database properties likes below while initiating database connection by HikariCP library, but did not found any example online.

    oracle.jdbc.timezoneAsRegion=true
    oracle.jdbc.timestampTzInGmt=true

Answer 1

This works for me in Spring Boot 2.3.2.RELEASE:

application.properties:

spring.datasource.hikari.data-source-properties.oracle.jdbc.timezoneAsRegion=true
spring.datasource.hikari.data-source-properties.oracle.jdbc.timestampTzInGmt=true

Check:

@Autowired
private DataSource dataSource;
:
logger.info(dataSource.getConnection().unwrap(OracleConnection.class).getProperties().getProperty("oracle.jdbc.timezoneAsRegion"));
logger.info(dataSource.getConnection().unwrap(OracleConnection.class).getProperties().getProperty("oracle.jdbc.timestampTzInGmt"));

Output:

true
true

Answer 2

This is as simple as adding new addDataSourceProperty in your DataSource

public class DataSource {

    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        config.setJdbcUrl( "jdbc_url" );
        config.setUsername( "database_username" );
        config.setPassword( "database_password" );
        ....
        config.addDataSourceProperty( "oracle.jdbc.timezoneAsRegion" , "true" );
        config.addDataSourceProperty( "oracle.jdbc.timestampTzInGmt" , "true" );

        ds = new HikariDataSource( config );
    }

    private DataSource() {}

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

To verify the setting in the connection, you must first unwrap to get the Oracle conenction

def hkConn = DataSource.getConnection() 

def conn  = hkConn.unwrap(OracleConnection.class); 

println conn.getProperties().getProperty("oracle.jdbc.timezoneAsRegion");
println conn.getProperties().getProperty("oracle.jdbc.timestampTzInGmt");

it returns

true
true

tested with HikariCP-2.7.2 and Oracle 12.1

Answer 3

Are you using Spring Boot?

If you're using Spring Boot you can try setting up via properties:

spring.datasource.hikari.*= # Hikari specific settings

https://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html

One example of properties file using MySQL:

spring.datasource.hikari.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.mysql.jdbc-url=jdbc:mysql://10.0.9.198:3306/develop_report?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&rewriteBatchedStatements=true
spring.datasource.hikari.mysql.username=root
spring.datasource.hikari.mysql.password=123456
spring.datasource.hikari.mysql.minimum-idle=5
spring.datasource.hikari.mysql.idle-timeout=180000
spring.datasource.hikari.mysql.maximum-pool-size=20
spring.datasource.hikari.mysql.auto-commit=true
spring.datasource.hikari.mysql.pool-name=Mysql-spring.datasource.hikariCP
spring.datasource.hikari.mysql.max-lifetime=1800000
spring.datasource.hikari.mysql.connection-timeout=30000
spring.datasource.hikari.mysql.connection-test-query=SELECT 1
spring.datasource.hikari.mysql.validation-timeout=5000