Generic Top

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

1. Overview

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

In this tutorial, we're going to 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 get and install any of the databases available on the Oracle Database Software Downloads. But in case we don't want to do any installation, we can also build any of the Oracle database images for Docker.

In this case, we're going to 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 computer.

3. Connection Pooling

Now we have the database ready for incoming connections. Next, then, let's learn some different ways to do connection pooling in Spring.

3.1. HikariCP

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

<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 tries to use the Tomcat pooling. If neither of those is available, then it tries to use Commons DBCP2.

We can also specify the connection pool to use. In that case, we just 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, actually, 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 just 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 need to explicitly define the DataSource bean and set the properties. Probably the easiest way of doing this is by using the @Configuration and @Bean annotations.

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 behavior.

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

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.ha</groupId>
    <artifactId>ons</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>
</dependency>

Now we're ready to declare and configure the UCP connection pool:

@Configuration
@Profile("oracle-ucp")
public class OracleUCPConfiguration {

    @Bean
    public DataSource dataSource() throws SQLException {
        PoolDataSource dataSource = PoolDataSourceFactory.getPoolDataSource();
        dataSource.setUser("books");
        dataSource.setPassword("books");
        dataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        dataSource.setURL("jdbc:oracle:thin:@//localhost:11521/ORCLPDB1");
        dataSource.setFastConnectionFailoverEnabled(true);
        dataSource.setInitialPoolSize(5);
        dataSource.setMinPoolSize(5);
        dataSource.setMaxPoolSize(10);
        return dataSource;
    }
}

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

  • setInitialPoolSize specifies the number of available connections created after the pool is initiated
  • setMinPoolSize specifies the minimum number of available and borrowed connections that our pool is maintaining, and
  • setMaxPoolSize 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 PoolDataSource JavaDoc or the developer's guide.

4. Older Oracle Versions

For versions prior to 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 were creating the OracleDataSource for connection pooling and configured 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 right implementation for our current needs.

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

Generic bottom

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE
2 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Dale
Dale
6 months ago
Loredana Crusoveanu
6 months ago
Reply to  Dale

Hi Dale,
Thanks for letting us know! We’ll update the article and our code samples.
Cheers

Comments are closed on this article!