Easily test Oracle Database applications with Testcontainers

Testcontainers is a powerful way to test Oracle apps, leveraging containers to quickly spin up disposable Oracle AI Database Free instances for your application unit and integration test cases.

With Testcontainers, you can create isolated, reproducible test environments for your Oracle apps, improving test suite reliability and consistency, and eliminating the need for complex local database setups or dedicated test databases. Today, create a lightning-fast test that launches an Oracle AI Database container and verifies the connection. All you need is a docker-compatible environment!

Side note: if you haven’t tried Oracle AI Database Free yet, check it out. I use these free tools to test, run POCs, and experiment every day.

Setup Testcontainers Dependencies

Add the following Maven dependencies to your project — At time of writing, the latest Testcontainers version is 2.0.3, and the Oracle Database dependencies are version 23.26.0.0.0:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>${oracle.version}</version>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>
    <version>${oracle.version}</version>
</dependency>
<dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter</artifactId>
    <version>${junit.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-oracle-free</artifactId>
    <version>${testcontainers.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-junit-jupiter</artifactId>
    <version>${testcontainers.version}</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>${oracle.version}</version>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>
    <version>${oracle.version}</version>
</dependency>
<dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter</artifactId>
    <version>${junit.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-oracle-free</artifactId>
    <version>${testcontainers.version}</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-junit-jupiter</artifactId>
    <version>${testcontainers.version}</version>
    <scope>test</scope>
</dependency>

Or, if you’re using Gradle:

implementation "com.oracle.database.jdbc:ojdbc11:${oracleVersion}"
implementation "com.oracle.database.jdbc:ucp:${oracleVersion}"
testImplementation "org.junit.jupiter:junit-jupiter:${junit_version}"
testImplementation "org.testcontainers:testcontainers-junit-jupiter:${testcontainers_version}"
testImplementation "org.testcontainers:testcontainers:${testcontainers_version}"
testImplementation "org.testcontainers:testcontainers-oracle-free:${testcontainers_version}"

implementation "com.oracle.database.jdbc:ojdbc11:${oracleVersion}"
implementation "com.oracle.database.jdbc:ucp:${oracleVersion}"
testImplementation "org.junit.jupiter:junit-jupiter:${junit_version}"
testImplementation "org.testcontainers:testcontainers-junit-jupiter:${testcontainers_version}"
testImplementation "org.testcontainers:testcontainers:${testcontainers_version}"
testImplementation "org.testcontainers:testcontainers-oracle-free:${testcontainers_version}"

Writing a test with Testcontainers

Before we jump in, make sure you have:

A Docker-compatible environment set up on your machine.
Pre-pull the Oracle AI Database Free container image (highly recommended for speedy test execution): docker pull gvenzl/oracle-free:23.26.0-slim-faststart

If you’re using Colima as your docker-compatible environment, see the following issue for Testcontainers compatibility: https://github.com/testcontainers/testcontainers-java/issues/5034#issuecomment-1036155978

Let’s write a basic test that spins up an Oracle Database container and verifies the DB connection with JUnit, using the gvenzl/oracle-free:23.26.0-slim-faststart image.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import org.junit.jupiter.api.Test;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.oracle.OracleContainer;

@Testcontainers
public class OracleDatabaseTest {
    static String image = "gvenzl/oracle-free:23.26.0-slim-faststart";
    @Container
    static OracleContainer oracleContainer = new OracleContainer(image)
            .withStartupTimeout(Duration.ofMinutes(3))
            .withUsername("testuser")
            .withPassword("testpwd");


    @Test
    void getConnection() throws SQLException {
        PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
        pds.setUser(oracleContainer.getUsername());
        pds.setPassword(oracleContainer.getPassword());
        pds.setURL(oracleContainer.getJdbcUrl());
        pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        pds.setConnectionPoolName("OracleDatabaseTest");

        try (Connection conn = pds.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeQuery("select * from v$version");
        }
    }
}

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import org.junit.jupiter.api.Test;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.oracle.OracleContainer;

@Testcontainers
public class OracleDatabaseTest {
    static String image = "gvenzl/oracle-free:23.26.0-slim-faststart";
    @Container
    static OracleContainer oracleContainer = new OracleContainer(image)
            .withStartupTimeout(Duration.ofMinutes(3))
            .withUsername("testuser")
            .withPassword("testpwd");


    @Test
    void getConnection() throws SQLException {
        PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
        pds.setUser(oracleContainer.getUsername());
        pds.setPassword(oracleContainer.getPassword());
        pds.setURL(oracleContainer.getJdbcUrl());
        pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        pds.setConnectionPoolName("OracleDatabaseTest");

        try (Connection conn = pds.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeQuery("select * from v$version");
        }
    }
}

Running SQL scripts before the test

Often we’ll have SQL scripts we want to run in the database container, before the tests start. Using JUnit’s @BeforeAll annotation, we can copy SQL scripts into the database container, and run them as sysdba:

@BeforeAll
public static void setUp() throws Exception {
    oracleContainer.start();
    // Load the init.sql script from the classpath. Any file may be used.
    MountableFile sqlFile = MountableFile.forClasspathResource("init.sql");
    oracleContainer.copyFileToContainer(sqlFile, "/tmp/init.sql");
    // Run the init.sql script as sysdba on the database container.
    oracleContainer.execInContainer("sqlplus", "sys / as sysdba", "@/tmp/init.sql");
}

@BeforeAll
public static void setUp() throws Exception {
    oracleContainer.start();
    // Load the init.sql script from the classpath. Any file may be used.
    MountableFile sqlFile = MountableFile.forClasspathResource("init.sql");
    oracleContainer.copyFileToContainer(sqlFile, "/tmp/init.sql");
    // Run the init.sql script as sysdba on the database container.
    oracleContainer.execInContainer("sqlplus", "sys / as sysdba", "@/tmp/init.sql");
}

Can we test Spring Boot apps too?

Oracle Database Free for Testcontainers works great with Spring Boot application tests — We can revise the prior example, adding Spring Boot dependencies and updating the test to create a Spring Boot database app test!

Let’s add the following Spring Boot dependencies to our project:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    <version>${spring-boot.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <version>${spring-boot.version}</version>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    <version>${spring-boot.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <version>${spring-boot.version}</version>
    <scope>test</scope>
</dependency>

Or, the Gradle version:

implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
testImplementation "org.springframework.boot:spring-boot-starter-test:${springBootVersion}"

implementation "org.springframework.boot:spring-boot-starter:${springBootVersion}"
testImplementation "org.springframework.boot:spring-boot-starter-test:${springBootVersion}"

Writing the Spring Boot app test

Let’s create the Spring application properties to configure and connect to Oracle Database: we’ll override the database username, password, and JDBC URL during test startup.

# application.yaml
spring:
  datasource:
    username: ${USERNAME}
    password: ${PASSWORD}
    url: ${JDBC_URL}

    # Set these to use UCP over Hikari.
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: UCPSampleApplication
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource

# application.yaml
spring:
  datasource:
    username: ${USERNAME}
    password: ${PASSWORD}
    url: ${JDBC_URL}

    # Set these to use UCP over Hikari.
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: UCPSampleApplication
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource

Next, we’ll write our SpringBootTest implementation, using autowiring to inject a Datasource connected to the Oracle Database container.

Note the use of the @ServiceConnection annotation, which we use to automatically inject the container database properties into our test’s Spring context.

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.oracle.OracleContainer;

@Testcontainers
@SpringBootTest
public class SpringBootDatabaseTest {
    /**
     * Use a containerized Oracle Database instance for testing.
     */
    @Container
    @ServiceConnection
    static OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-free:23.26.0-slim-faststart")
            .withStartupTimeout(Duration.ofMinutes(5))
            .withUsername("testuser")
            .withPassword("testpwd");

    @Autowired
    DataSource dataSource;

    @Test
    void springDatasourceConnection() throws SQLException {
        // Query Database version to verify Spring DataSource connection
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeQuery("select * from v$version");
        }
    }

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.testcontainers.service.connection.ServiceConnection;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.oracle.OracleContainer;

@Testcontainers
@SpringBootTest
public class SpringBootDatabaseTest {
    /**
     * Use a containerized Oracle Database instance for testing.
     */
    @Container
    @ServiceConnection
    static OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-free:23.26.0-slim-faststart")
            .withStartupTimeout(Duration.ofMinutes(5))
            .withUsername("testuser")
            .withPassword("testpwd");

    @Autowired
    DataSource dataSource;

    @Test
    void springDatasourceConnection() throws SQLException {
        // Query Database version to verify Spring DataSource connection
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeQuery("select * from v$version");
        }
    }

Now we should be able to run our tests and see the Oracle Database container in action! I hope this write-up helps you write excellent, container-driven tests for your Oracle Database applications.

andersswanson.dev