Oracle Connection Pooling With Spring

1. Overview

Oracle is one of the most popular databases in large production environments. So, as Spring developers, it’s very common to work with these databases.

In this tutorial, we will talk about how we can make this integration.

2. The Database

The first thing we need is, of course, the database. If we don’t have one installed, we can install any databases available on the Oracle Database Software Downloads. But if we don’t want to do any installation, we can also build any Oracle database images for Docker.

In this case, we will use an Oracle Database 12c Release 2 (12.2.0.2) Standard Edition Docker image. Consequently, this keeps us from having to install new software on our computers.

3. Connection Pooling

Now we have the database ready for incoming connections. Next, let’s learn different ways to make connection pooling in Spring.

3.1. HikariCP

The easiest way for connection pooling with Spring is using autoconfiguration. The spring-boot-starter-data-jpa dependency includes HikariCP as the preferred pooling data source. Therefore, if we take a look at our pom.xml, we’ll see the following:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

The spring-boot-starter-data-jpa dependency includes the spring-boot-starter-jdbc dependency transitively for us.

Now we only have to add our configuration into the application.properties file:

# OracleDB connection settings
spring.datasource.url=jdbc:oracle:thin:@//localhost:11521/ORCLPDB1
spring.datasource.username=books
spring.datasource.password=books
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# HikariCP settings
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.poolName=HikariPoolBooks

# JPA settings
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.hibernate.ddl-auto=create

As you can see, we have three different section configuration settings:

The OracleDB connection settings section is where we configured the JDBC connection properties as we always do
The HikariCP settings section is where we configure the HikariCP connection pooling. In case we need advanced configuration, we should check the HikariCP configuration property list
The JPA settings section is some basic configuration for using Hibernate

That is all we need. It couldn’t be easier, could it?

3.2. Tomcat and Commons DBCP2 Connection Pooling

Spring recommends HikariCP for its performance. On the other hand, it also supports Tomcat and Commons DBCP2 in Spring Boot autoconfigured applications.

It tries to use the HikariCP. If it isn’t available, then try to use the Tomcat pooling. If neither of those is available, it tries to use Commons DBCP2.

We can also specify the connection pool to use. In that case, we need to add a new property to our application.properties file:

spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource

If we need to configure specific settings, we have available their prefixes:

spring.datasource.hikari.* for HikariCP configuration
spring.datasource.tomcat.* for Tomcat pooling configuration
spring.datasource.dbcp2.* for Commons DBC2 configuration

And we can set *spring.*datasource.type to any other DataSource implementation. It isn’t necessary to be any of the three mentioned above.

But in that case, we will have a basic out-of-the-box configuration. There will be many cases where we will need some advanced configurations. Let’s see some of them.

3.3. Oracle Universal Connection Pooling

If we want to use advanced configurations, we can declare the UCP datasource and set the remaining properties in the application.properties file. Currently, the best way to enable UCP in a spring-boot application is through the Oracle-provided spring-boot-starter library.

Oracle Universal Connection Pool (UCP) for JDBC provides a full-featured implementation for caching JDBC connections. It reuses the connections instead of creating new ones. It also gives us a set of properties for customizing pool behaviour.

If we want to use UCP, we need to add the following Maven dependency:

<dependency>
   <groupId>com.oracle.database.spring</groupId>
   <artifactId>oracle-spring-boot-starter-ucp</artifactId>
   <version>3.1.0</version> <!-- 2.7.7 for Spring Boot 2.x -->
   <type>pom</type>
</dependency>

Now we only have to add our configuration into the application.properties file:

# UCP settings
spring.datasource.type=oracle.oracleucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource 
spring.datasource.oracleucp.sql-for-validate-connection=select * from dual 
spring.datasource.oracleucp.connection-pool-name=UcpPoolBooks 
spring.datasource.oracleucp.initial-pool-size=5 
spring.datasource.oracleucp.min-pool-size=5 
spring.datasource.oracleucp.max-pool-size=10

In the above example, we’ve customized some pool properties:

spring.datasource.oracleucp.initial-pool-size specifies the number of available connections created after the pool is initiated
spring.datasource.oracleucp.min-pool-size specifies the minimum number of available and borrowed connections that our pool is maintaining, and
spring.datasource.oracleucp.max-pool-size specifies the maximum number of available and borrowed connections that our pool is maintaining

If we need to add more configuration properties, we should check the UCPDataSource JavaDoc or the developer’s guide.

4. Older Oracle Versions

For versions before 11.2, like Oracle 9i or 10g, we should create an OracleDataSource instead of using Oracle’s Universal Connection Pooling.

In our OracleDataSource instance, we turn on connection caching via setConnectionCachingEnabled:

@Configuration
@Profile("oracle")
public class OracleConfiguration {
    @Bean
    public DataSource dataSource() throws SQLException {
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser("books");
        dataSource.setPassword("books");
        dataSource.setURL("jdbc:oracle:thin:@//localhost:11521/ORCLPDB1");
        dataSource.setFastConnectionFailoverEnabled(true);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setConnectionCachingEnabled(true);
        return dataSource;
    }
}

In the above example, we created the OracleDataSource for connection pooling and configuring some parameters. We can check all the configurable parameters on the OracleDataSource JavaDoc.

5. Conclusion

Nowadays, configuring Oracle database connection pooling using Spring is a piece of cake.

We’ve seen how to do it just using autoconfiguration and programmatically. Even though Spring recommends the use of HikariCP, other options are available. We should be careful and choose the correct implementation for our current needs.

And as always, the complete example can be found on GitHub.

Persistence

REST

Security