HikariCP Best Practices for Oracle Database and Spring Boot

December 4, 2024 | 17 minute read
Kuassi Mensah
Director Product Management
Richard Exley
Consulting Member of Technical Staff, Oracle Database
Irina Granat
Senior Director
Text Size 100%:

This previously published blog now covers the new Oracle Database 23ai release and HikariCP 6.0.0 updates. The earlier version of the blog can be reviewed here.

HikariCP is a popular Java connection pool, commonly used with Spring Boot. This blog post furnishes the best practices for configuring HikariCP with Spring Boot for the Oracle Database. 

You will learn how to configure HikariCP to leverage the Oracle Databases’ High Availability (HA) capabilities for continuous service during planned and unplanned downtime. In this blog, we will cover HikariCP ver. 6.0.0 which includes support for request boundaries (beginRequest and endRequest API support) that are part of the JDBC 4.3 standard. The request boundaries enable HikariCP to transparently inject BeginRequest and EndRequest, similarly to UCP, thereby enabling the JDBC driver to perform replay as part of Transparent Application Continuity (TAC). 
All the recommendations in this blog post have been tested using the latest Long Term Support (LTS) Oracle Database release i.e., 19c (19.21) and 23ai and HikariCP version 6.0.0

About UCP

Before zooming into HikariCP, a few words about the Universal Connection Pool (UCP). The Oracle Database furnishes the Universal Connection Pool (UCP), a feature-rich Java connection pool — one of the Spring Boot choices — that supports out-of-the-box, all Oracle database configurations, and mission-critical capabilities such as Oracle Real Application Cluster (RAC), Data Guard, Oracle Sharding, Asynchronous API, Runtime Load Balancing, XA, and front-end to Database Resident Connection Pool (DRCP).

UCP is released quarterly and fully supported by Oracle.
See more details at: https://www.oracle.com/jdbc/

You may have standardized on HikariCP and do not necessarily need the advanced and mission-critical UCP features. However, if you’d like to migrate from HikariCP to UCP, this blog post is for you.

The Basics

The basic steps for using HikariCP with the Oracle Database consist of (i) configuring the Oracle JDBC driver, and (ii) configuring HikariCP.

Configure the Oracle JDBC Driver

Add Oracle Driver as a dependency in Spring Initializer or manually add it to your project’s pom.xml.

For Oracle database 23ai, the version of JDBC is 23.6.0.24.10:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
<properties>
    <oracle.jdbc.version>23.6.0.24.10</<oracle.jdbc.version>
</properties>

<dependencies>
...
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>

For Oracle database 19c, the version of JDBC is 19.21.0.0:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
<properties>
    <oracle.jdbc.version>19.21.0.0</<oracle.jdbc.version>
</properties>

<dependencies>
...
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>${oracle.jdbc.version}</version>
</dependency>
...
</dependencies>

Use this page for more information on Oracle RDBMS and JDK Version Compatibility for Oracle JDBC Drivers.

Configure HikariCP

1. Starting with version 2, Spring Boot uses HikariCP as the default connection pool and it is transitively imported with the following Spring Boot starters: spring-boot-starter-jdbc or spring-boot-starter-data-jpa.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>3.0.4</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>

<dependencies>
...
<dependency>
<! -- Assume Spring Data JDBC -->
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
...
</dependencies>

2. However, in order to use the latest HikariCP release (i.e., version 6.0.0 as of this writing) which implements request boundaries option, we also need to add the Hikari dependency in the pom.xml explicitly:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
<dependencies>
...
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.0.0</version>
</dependency>
...
</dependencies>

3. Then configure the application properties using one of the following methods: (i) using application.properties file (Spring Boot automatically reads these files and applies the configuration), (ii) creating a configuration class annotated with @Configuration and using @Bean to define the DataSource bean with HikariCP settings — we are using this method hereafter, (iii) using application.yaml file, and (iv) using a Kubernetes secret, for a more secure method. See this UCP-related blog post for more details about the approaches. Pick the approach that best fits your project and preferences. The first method might be simpler and is often sufficient for basic setups, while the second method provides more flexibility and control over the configuration in a Java class.

HikariCP has default settings that perform well in most deployments without additional tweaking. Spring Boot exposes Hikari-specific properties using the spring.datasource.hikari namespace. Every property is optional except for the few essential ones that must be set. HikariCP’s official GitHub page explains various configuration options. In addition, you can review the Common Application Properties page listing all Spring Boot application.properties options.

Here are the examples of application.properties file and DataSourceConfig class:

i. An application.properties file with the minimum set of properties required for using the Oracle Database.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
#Oracle Database - Connection Details
spring.datasource.url=${JDBC_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# HikariCP settings
spring.datasource.hikari.maximum-pool-size=4
spring.datasource.hikari.minimum-idle=4
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL

In this example we enabled the following HikariCP properties:

  • spring.datasource.hikari.maximum-pool-size → sets the maximum number of connections that can be held in the connection pool
  • spring.datasource.hikari.minimum-idle → defines the minimum number of idle connections that the pool should try to maintain. An idle connection is one that is not in use but is kept ready and in open state. Maintaining idle connections can improve the performance of the applications as it will reduce the time taken to acquire a new connection.
  • oracle.jdbc.defaultConnectionValidation →  specifies how much effort to put into validating a connection on borrow. The values range from NONE (least effort) to COMPLETE (most effort). Default is NETWORK, which is very expensive and so it should be changed unless it is the best for your application. Setting the oracle.jdbc.defaultConnectionValidation property to LOCAL or SOCKET enables lightweight connection validation by the JDBC driver when you call the isValid(timeout) method. We strongly recommend setting up such a property.

Validation comes at a cost, and you should decide what value works best for your application. Here is an analysis showcasing the processing time associated with each value:

connection validation analysis

You may add other HikariCP properties based on your application requirements however, when setting multiple data source properties beware of the syntax and side effects.

We also recommend enabling additional logging to verify that your properties where correctly registered:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
logging.level.com.zaxxer.hikari.HikariConfig=DEBUG

Please refer to the HikariCP documentation for more details.

ii. An example of a DataSourceConfig class defining the minimum set of properties that the DataSource requires to work with the Oracle Database.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    public HikariDataSource getDataSource () {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));
        config.setMinimumIdle(4);
        config.setMaximumPoolSize(4);
        config.addDataSourceProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
        return new HikariDataSource(config);
    }
}

At this stage, you have successfully configured your Spring Boot application to work with the Oracle Database using HikariCP.

The next sections cover the steps for configuring HikariCP to leverage Oracle Databases’ high availability capabilities.

Oracle's High Availability

With the Oracle Real Application Cluster system (RAC), any Oracle database service is accessible equally from several server nodes. If a node or a subset of the RAC cluster fails or is taken offline for maintenance, the database is still accessible through the remaining active nodes.

The building blocks for implementing Application High Availability with the Oracle database are: using a database service, configuring a URL or a connection string for High Availability, enabling Fast Application Notification (FAN), implementing draining, and enabling continuous database availability for Java applications.
You may choose the level of HA that suits your requirements. A detailed explanation of HA protection levels can be found in Oracle’s “High Availability Overview and Best Practices” under the Application High Availability Levels chapter.

The following steps allow you to configure the Oracle RAC system, the Oracle JDBC driver, and HikariCP for sustaining planned and unplanned outages. The detailed steps and explanations for the continuous availability of your applications are covered in Oracle’s “High Availability Overview and Best Practices” documentation.

Configuring for Basic High Availability — Level 1

Implement a level of high availability that allows applications to immediately react to instance, node, or database failures, and quickly establish new connections to surviving database instances.

With application HA Level 1, downtime is minimized for unplanned and planned outages.

Configure High Availability Database Services

With Oracle RAC, the Oracle database service may be deployed over several nodes in the cluster. Planned outage allows performing maintenance operations on a subset of the service nodes/hosts. The database admin issues a command that generates a planned down event. As HikariCP is not instrumented to handle such event type, it will be handled by the Oracle JDBC. The driver will drain i.e., transparently and smoothly close all active connections thereby allowing the nodes scheduled for maintenance, to be shut down without impacting the applications (the remaining active nodes will absorb the workload).

Create a dedicated service to support HA features as follows. A detailed explanation of how to create a dedicated service and a connect string can be found in Oracle’s “High Availability Overview and Best Practices” under the Application High Availability Levels chapter.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ srvctl add service -db mydb -service MYSERVICE -pdb mypdb
-notification TRUE

Configure the JDBC Connection String for High Availability

Configure your Java application to use the following connect string.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
jdbc:oracle:thin:@(DESCRIPTION = 
  (CONNECT_TIMEOUT= 3)(RETRY_COUNT=4)(RETRY_DELAY=2)
  (TRANSPORT_CONNECT_TIMEOUT=3) (ADDRESS_LIST = (LOAD_BALANCE=on) 
  (ADDRESS = (PROTOCOL = TCP)(HOST=clu_site_scan)(PORT=1521))) 
  (CONNECT_DATA=(SERVICE_NAME = my_service)))

Enable Fast Application Notification (FAN) at the Driver Level

As HikariCP has not been instrumented to handle FAN events, the planned maintenance will be managed by the Oracle JDBC driver. To configure the driver to handle FAN events, add simplefan and ons jar files to your project’s pom.xml.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
<dependencies>
...
    <dependency>
      <groupId>com.oracle.database.ha</groupId>
      <artifactId>ons</artifactId>
      <version>${oracle.jdbc.version}</version>
    </dependency>
    <dependency>
      <groupId>com.oracle.database.ha</groupId>
      <artifactId>simplefan</artifactId>
      <version>${oracle.jdbc.version}</version>
    </dependency>
...
  </dependencies>

At this stage, the basic High Availability has been configured and furnishes the following benefits:

  1. FAN event notification of unplanned connection failures. This is especially useful when the failure may have resulted in an application hang, such as when a database node is failing, and the TCP/IP sockets are not cleaned up.
  2. A connection string that results in connection requests being load-balanced across multiple RAC nodes using Single Client Access Name (SCAN) with retry on failure.

If your application has been designed to handle planned and unplanned outages via retry logic, then you don’t need anything else.

Configuring for Planned Maintenance — Level 2

To prepare applications for planned maintenance, enable the request boundaries and add the following connection validation settings to your application code.

Enable Request Boundaries

Please note that the major change in Hikari 6.0.0 is a support for request boundaries and this feature is disabled by default. Enabling the request boundaries will make draining more efficient - drain can occur at both checkout's (i.e., at connection-validation) and checkin's (i.e., at endRequest). It also enables a complete support for both AC-manual and TAC. Request boundaries should be enabled by setting the following system property:

  • System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");

Please be aware that a system property has JVM scope, so all HikariCP pools in that JVM instance will be affected by such a setting.

Enable Predictable Connection Validation

By design, HikariCP validates connections that have been idle for more than 500ms (in the latest version). If a connection is constantly being checked out and checked in it won’t be validated, and hence won’t be drained by the Oracle JDBC driver.
Set the following system property either as a JVM system property or programmatically in your DataSourceConfig class (as shown below) to force connection validation at checkout.

  • System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");

A systematic connection validation on borrow may have a slight performance impact; Oracle recommends setting a lightweight connection validation property oracle.jdbc.defaultConnectionValidation to "LOCAL" or "SOCKET" (SOCKET has a small performance penalty while LOCAL might limit the driver's ability to detect the outage) to mitigate the impact:

  • setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");

Setting the data source class name using the application.properties or the applications.yaml files, throws the following error message: “Caused by: java.lang.IllegalStateException: both driverClassName and dataSourceClassName”. We need to set it programmatically using DataSourceConfig class as follows (at this point, we recommend to use only DataSourceConfig class file for any additional settings and not to combine it with application.properties file).

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {   

    @Bean
    public HikariDataSource getDataSource () {    

        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
        HikariConfig config = new HikariConfig();
        config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));  
        config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
        config.setMinimumIdle(4);
        config.setMaximumPoolSize(4);
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
        config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

For Oracle database 19c, change the value of config.setDataSourceClassName for the data source creation to oracle.jdbc.datasource.impl.OracleDataSource:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
config.setDataSourceClassName("oracle.jdbc.pool.OracleDataSource");

Perform Planned Maintenance

The following command initiates the draining of active sessions.
The "-drain_timeout 60" parameter, allows active sessions to finish their requests within a predefined timeout (60 sec).

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
srvctl stop instance -db mydb -node node1 -stopoption immediate 
–drain_timeout 60 -force -failover

Planned Outage Success Test

For the planned outage test, we will be using a two-node Oracle RAC environment. During normal operation, both RAC nodes are up and running and serving the application. Depending on your load-balancing strategy, the number of sessions might be different or equally distributed across both nodes. Under load (i.e., database activities), observe that the connections drain away from that instance and that the instance/node can be shut down.

We will monitor the draining process using the following SQL statement:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
select systimestamp time, machine, inst_id, count(*) from gv$session where username='XXXX' group by systimestamp, machine, inst_id;

During normal operation, both RAC nodes db1 and db2 (inst_id 1 and inst_id2) are up and running. The following SQL output demonstrates how sessions for app1 and app2 are being distributed (count * column). Each application is using a connection pool that is configured to hold 4 sessions.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL>
TIME                        MACHINE      INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.23.964298 AM +00:00 app1         2    3
04-NOV-24 12.33.23.964298 AM +00:00 app2         1    4
04-NOV-24 12.33.23.964298 AM +00:00 app2         1    1

To begin the planned maintenance test, we first stop the database services on node 1 using the following command:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
srvctl stop instance -db $(srvctl config database) -node db1 -stopoption immediate -drain_timeout 10 -force -failover

Fast Application Notification (FAN) sends a stop event. The connection pool reacts to FAN and starts closing connections on node 1. At the same time, new connections are being opened on node 2. The active session on node 1 is still executing its work. If it finishes within the drain timeout (10 sec) and returns the connection to the pool, then the session is closed, and a new one is established on node 2 when requested.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL>
TIME                        MACHINE      INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.29.945024 AM +00:00 app1         2    3
04-NOV-24 12.33.29.945024 AM +00:00 app2         2    4
04-NOV-24 12.33.29.945024 AM +00:00 app2         1    1

If the drain timeout completes but the remaining session is still connected to node 1, it will be disconnected. With Application Continuity in place, the session reconnects to node 2, and the interrupted work will be replayed transparently to the end user. See the next section for replay configuration details.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
SQL>
TIME                        MACHINE   INST_ID   COUNT(*)
----------------------------------- --------- ---------- ----------
04-NOV-24 12.33.31.964371 AM +00:00 app1         2    4
04-NOV-24 12.33.31.964371 AM +00:00 app2         2    4

Configuring for Unplanned Outages — Level 3

For planned maintenance, draining is used to allow active sessions to finish their work (drain) before the database instance is shut down. However, in unplanned events such as an instance, node, or database service failures, all sessions connected to the faulty node/instance will be terminated immediately — there is no time for draining. The Fast Application Notification (FAN) mechanism will detect those unplanned events and notify the JDBC driver or any connection pool such as UCP that has been instrumented to receive and process the FAN messages.

As HikarCPi is not capable of handling FAN events, the Oracle JDBC driver transparently cleans up the orphan connections and replays in-flight requests on a surviving database instance (using new connections); the failures will therefore be transparent to the applications that were using those connections.

1. Add the following parameters to the database service definition:

 -failovertype AUTO →  enables Transparent Application Continuity (TAC).

 -failover_restore AUTO  →  restores client states automatically

 -replay_init_time 600  →  specifies the time in seconds upon which the replay will not be performed.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
$ srvctl add service -db mydb -service MYSERVICE -pdb mypdb
-notification TRUE -failover_restore AUTO -failovertype AUTO -replay_init_time 600

2. We recommend setting "oracle.jdbc.defaultConnectionValidation" to "SOCKET"  to have a better coverage for unplanned outages.

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
package maa.spring;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;

import java.util.Properties;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {   

    @Bean
    public HikariDataSource getDataSource () {    

        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
        HikariConfig config = new HikariConfig();
        config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));  
        config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
        config.setMinimumIdle(4);
        config.setMaximumPoolSize(4);
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
        config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

For Oracle database 19c, change the value of config.setDataSourceClassName for the data source creation to oracle.jdbc.replay.OracleDataSourceImpl:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
config.setDataSourceClassName("oracle.jdbc.replay.OracleDataSourceImpl");

Additional Configuration Options

Depending on your application functionality and performance needs, you might also consider adding the following properties to the HikariCP configuration and/or enable Oracle's Database Resident Connection Pooling (DRCP):

1. The spring.datasource.hikari.connection-timeout property →  controls the maximum number of milliseconds an application is willing to wait for a connection from the pool. If pool is exhausted and this time is reached, then an exception is thrown with a message “connection acquisition timed out”. Lowest acceptable connection timeout is 250 ms (Oracle's UCP supports much lower minimum). Default: 30000 (30 seconds).

Note: If the maximum number of connections exhausted from the pool and a new request comes in, it will behave depending on the connection-timeout. The new request will wait until the connection is available or it is timed out.

2. The oracle.jdbc.implicitStatementCacheSize property→  by design HikariCP doesn’t implement a statement cache as explained by the author. Oracle JDBC thin driver provides an implicit statement cache that will take care of optimizing how statements and cursors are allocated and freed. To enable it just set the property "oracle.jdbc.implicitStatementCacheSize" to a value for example "10" which is usually a good size for the statement cache.

Here are the examples of application.properties file and DataSourceConfig class including these properties.

i. application.properties

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
# Oracle Database DataSource Configuration
spring.datasource.url=${HATEST_JDBC_URL}
spring.datasource.username=${HATEST_DB_MAIN_USER}
spring.datasource.password=${HATEST_DB_MAIN_PASSWORD}

# HikariCP settings
spring.datasource.hikari.maximum-pool-size=4
spring.datasource.hikari.minimum-idle=4
spring.datasource.hikari.connection-timeout=250
spring.datasource.hikari.data-source-properties.oracle.jdbc.defaultConnectionValidation=LOCAL
spring.datasource.hikari.data-source-properties.oracle.jdbc.implicitStatementCacheSize=10

ii. DataSourceConfig class:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
package maa.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;
import oracle.jdbc.OracleConnection;
import java.util.Properties;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    
    @Bean
    public HikariDataSource getDataSource () {
        
        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
        HikariConfig config = new HikariConfig();
        config.addDataSourceProperty("url",System.getenv("JDBC_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));   
        config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
        config.setMinimumIdle(4);
        config.setMaximumPoolSize(4);
        config.setConnectionTimeout(250);
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
        props.setProperty("oracle.jdbc.implicitStatementCacheSize", "10");
        config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

3.  To enable Database Resident Connection Pooling (DRCP), you can refer to Oracle’s “Database Resident Connection Pooling” documentation for detailed configuration steps. DRCP is a feature of Oracle Database that allows applications and mid-tier services to efficiently manage multiple data requests while optimizing database resource utilization.

Along with enabling DRCP on the server side, you also need to configure it on the client side. This involves setting a new connection string and enabling the required properties in the DataSourceConfig class.

  • enable DRCP with SERVER=POOLED in a Network Connect Descriptor string to pass to JDBC_POOLED_URL
  • ensure that HikaripCP version 6.0.0. request boundaries property is enabled:
    • System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");

  • oracle.jdbc.DRCPConnectionClass → this property is used in Spring Boot applications with HikariCP connection pool to configure Oracle Database Resource Pool (DRCP)
  • oracle.jdbc.DRCPMultiplexingInRequestAPIs → this property is used to enable or disable DRCP multiplexing in request APIs when using HikariCP with an Oracle database in a Spring Boot application

Here are the examples of  DataSourceConfig class including these properties:

Copied to Clipboard
Error: Could not Copy
Copied to Clipboard
Error: Could not Copy
package maa.spring;

import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariConfig;
import oracle.jdbc.OracleConnection;
import java.util.Properties;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    
    @Bean
    public HikariDataSource getDataSource () {
        
        System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
        System.setProperty("com.zaxxer.hikari.enableRequestBoundaries", "true");
        HikariConfig config = new HikariConfig();
        config.addDataSourceProperty("url",System.getenv("JDBC_POOLED_URL"));
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));   
        config.setDataSourceClassName("oracle.jdbc.datasource.impl.OracleDataSource");
        config.setMinimumIdle(4);
        config.setMaximumPoolSize(4);
        config.setConnectionTimeout(250);
        Properties props = new Properties();
        props.setProperty("oracle.jdbc.defaultConnectionValidation", "SOCKET");
        props.setProperty("oracle.jdbc.implicitStatementCacheSize", "10");
        props.setProperty("oracle.jdbc.DRCPMultiplexingInRequestAPIs", "true");
        props.setProperty("oracle.jdbc.DRCPConnectionClass", "DRCP_POOL_NAME");
        config.addDataSourceProperty("connectionProperties", props);
        return new HikariDataSource(config);
    }
}

JDBC Diagnosability

New in Oracle Database 23ai: A single ojdbc jar (e.g., ojdbc8.jar, ojdbc11.jar) for all use cases (production, debug, metrics). In other words, no more ojdbc8_g.jar or ojdbc11_g.jar for debugging.

Check this blog post — What’s in ‘Oracle DB 23c Free Developer Release’ for Java Developers — to get more information about the new JDBC Self-Driven Diagnosability in Oracle databse 23ai.

Wrap-up

The steps described in this blog post are based on an Oracle RAC system and Oracle JDBC Drivers v19.21.0.0 and 23.6.0.24.10. We hope you found this blog post helpful. Thanks for reading!

Further Readings

Kuassi Mensah

Director Product Management

Kuassi is Director of Product Management at Oracle, in charge of

•Java database access, performance , scalability, availability and frameworks for the Oracle database (OJVM, JDBC, UCP, App Cont, TG, etc)

•Hadoop, Spark and Flink integration with the Oracle database

•JavaScript with Oracle database (Nashorn with JDK, JRE and OJVM)

Graduate MS in CS from the Programming Institute of University of Paris VI (Jussieu)

Frequent speaker @ JavaOne, Oracle Open World, Data Summit, Node Summit, Collaborate/IOUG, RMOUG, BIWA , UKOUG, DOAG, OUGN, BGOUG, OUGF, OTN LAD, OTN APAC.

Author: Oracle Database Programming using Java and Web Services

Social network: @kmensah, http://db360.blogspot.com/, https://www.linkedin.com/in/kmensah

Show more

Richard Exley

Consulting Member of Technical Staff, Oracle Database

Irina Granat

Senior Director

Irina Granat is a member of MAA group in Oracle Database Development organization focusing on Microservices and SaaS applications.

Irina (Rena) has over 20 years of experience in the Software Industry with Leadership, Solution Consultant and Management abilities that span ERP, CRM, HCM, SaaS, PaaS, Applications, and Technology Platforms.

Show more

Previous Post

Loading MLE modules into the database got a lot easier with SQLcl 24.3.2

Martin Bach | 4 min read

Next Post


HikariCP (pre 6.0.0 version) Best Practices for Oracle Database and Spring Boot

Irina Granat | 12 min read