Integration Testing with Spring Boot,Testcontainers,Oracle

BySuresh Gadupu Spring Boot

In this blog post I show how to use oracle -xe database with Testcontainers for doing database integration tests in Spring Boot application.

I have already written a detailed blog post about database integration testing with Spring Boot and Testcontainers in following article

Spring Boot integration testing with Testcontainers

In above blog post, I have used PostgresSQL database.

In this blog post , I will only cover the changes you need to do if you are using Oracle database.

Adding Dependencies

Add Oracle specific dependencies for test containers and Oracle driver library.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	...
	<properties>
		<java.version>11</java.version>
		<testcontainers.version>1.17.2</testcontainers.version>
	</properties>
	<dependencies>
		....
<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		<dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>junit-jupiter</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>oracle-xe</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.testcontainers</groupId>
				<artifactId>testcontainers-bom</artifactId>
				<version>${testcontainers.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
	......

</project>Code language: HTML, XML (xml)

2) Change the import SQL statements.

Oracle default date format is DD-MMM-YYYY, If you are importing initial data for testing purpose make sure that either you convert date field using date function like TO_DATE('2004-12-21','YYYY-MM-DD') or use default date format

INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(9,'Ibrar1','Garcia1','F','01-DEC-1994',TO_DATE('2004-12-21','YYYY-MM-DD'),102);
Code language: Java (java)

Oracle Database image

I am going to use oracle database image from here.

Primary Key Strategies

Oracle does supports both identity ( auto increment of value) and sequence for primary key generation

Using Identity to generate primary key

For hibernate to generate table column with identity keyword, you should change the Entity class. id field should use GenerationType.IDENTITY strategy.

@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Entity
@Getter
@Setter
@ToString
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String first_name;
    private String last_name;

    @Enumerated(EnumType.STRING)
    private Gender gender;

    private LocalDate birth_date;
    private LocalDate hire_date;

    @ManyToOne
    @JoinColumn(name = "department_id")
    private Department department;



    public Employee() {

    }
}Code language: Java (java)

@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Entity
@Getter
@Setter
@ToString
public class Department implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    @OneToMany(
            mappedBy = "department",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    @JsonIgnore
    private List<Employee> employees;

    public Department(Integer id, String name) {
        super();
        this.id = id;
        this.name = name;
    }

    public Department() {
    }



}Code language: Java (java)

Sample SQL script you can use for testing


DELETE FROM employee;
DELETE FROM department;

ALTER TABLE department MODIFY id
    GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1);

ALTER TABLE employee MODIFY id
    GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1);

INSERT INTO department(name) VALUES('HR');
INSERT INTO department(name) VALUES('PDE');
INSERT INTO department(name) VALUES('Marketing');



INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Alex','Bonilla','M',TO_DATE('1990-12-07','YYYY-MM-DD'),TO_DATE('2011-04-01','YYYY-MM-DD'),1);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Duke','Stacey','M',TO_DATE('1989-07-11','YYYY-MM-DD'),TO_DATE('2012-08-21','YYYY-MM-DD'),1);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Hayley','Drake','M',TO_DATE('1992-10-01','YYYY-MM-DD'),TO_DATE('2007-07-06','YYYY-MM-DD'),2);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Nancie','Beasley','M',TO_DATE('1978-02-21','YYYY-MM-DD'),TO_DATE('2003-03-03','YYYY-MM-DD'),2);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Wyatt','Holder','M',TO_DATE('1994-12-01','YYYY-MM-DD'),TO_DATE('2016-02-15','YYYY-MM-DD'),2);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Tobey','Drake','M',TO_DATE('1992-10-01','YYYY-MM-DD'),TO_DATE('2014-06-11','YYYY-MM-DD'),3);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Marvin','Hope','F',TO_DATE('1978-02-21','YYYY-MM-DD'),TO_DATE('2003-09-10','YYYY-MM-DD'),3);
INSERT INTO employee(first_name,last_name,gender,birth_date,hire_date,department_id) VALUES('Ibrar','Garcia','F',TO_DATE('1994-12-01','YYYY-MM-DD'),TO_DATE('2004-12-21','YYYY-MM-DD'),3);

Code language: SQL (Structured Query Language) (sql)

Using Sequence to generate primary key 

@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Entity
@Getter
@Setter
@ToString
public class Department implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Integer id;
    private String name;
    @OneToMany(
            mappedBy = "department",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    @JsonIgnore
    private List<Employee> employees = new ArrayList<>();

    public Department(Integer id, String name) {
        super();
        this.id = id;
        this.name = name;
    }

    public Department() {
    }

}Code language: Java (java)

@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
@Entity
@Getter
@Setter
@ToString
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Integer id;
    private String first_name;
    private String last_name;

    @Enumerated(EnumType.STRING)
    private Gender gender;

    private LocalDate birth_date;
    private LocalDate hire_date;

    @ManyToOne
    @JoinColumn(name = "department_id")
    private Department department;



    public Employee() {

    }  

}Code language: Java (java)

Sample SQL script you can use for testing


ALTER SEQUENCE hibernate_sequence RESTART start WITH 10;

DELETE FROM employee;
DELETE FROM department;

INSERT INTO DEPARTMENT(id,name) VALUES(100,'HR');
INSERT INTO DEPARTMENT(id,name) VALUES(101,'PDE');
INSERT INTO DEPARTMENT(id,name) VALUES(102,'Marketing');

INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(1,'Alex','Bonilla','M',TO_DATE('1990-12-07','YYYY-MM-DD'),TO_DATE('2011-04-01','YYYY-MM-DD'),100);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(2,'Duke','Stacey','M',TO_DATE('1989-07-11','YYYY-MM-DD'),TO_DATE('2012-08-21','YYYY-MM-DD'),100);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(3,'Hayley','Drake','M',TO_DATE('1992-10-01','YYYY-MM-DD'),TO_DATE('2007-07-06','YYYY-MM-DD'),101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(4,'Nancie','Beasley','M',TO_DATE('1978-02-21','YYYY-MM-DD'),TO_DATE('2003-03-03','YYYY-MM-DD'),101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(5,'Wyatt','Holder','M',TO_DATE('1994-12-01','YYYY-MM-DD'),TO_DATE('2016-02-15','YYYY-MM-DD'),101);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(6,'Tobey','Drake','M',TO_DATE('1992-10-01','YYYY-MM-DD'),TO_DATE('2014-06-11','YYYY-MM-DD'),102);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(7,'Marvin','Hope','F',TO_DATE('1978-02-21','YYYY-MM-DD'),TO_DATE('2003-09-10','YYYY-MM-DD'),102);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(8,'Ibrar','Garcia','F',TO_DATE('1994-12-01','YYYY-MM-DD'),TO_DATE('2004-12-21','YYYY-MM-DD'),102);
INSERT INTO employee(id,first_name,last_name,gender,birth_date,hire_date,department_id) VALUES(9,'Ibrar1','Garcia1','F','01-DEC-1994',TO_DATE('2004-12-21','YYYY-MM-DD'),102);

Code language: SQL (Structured Query Language) (sql)

When using Oracle as your database you can use testcontainers in 2 ways

  1. Auto handling containers using @Container (Junit 5) or @ClassRule (Junit4) annotations
  2. Manual container starting

Using @Container

If you are using Junit5 and Springboot version >= 2.2.6.

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@Testcontainers
@DirtiesContext
public  class BaseIT {

    @Autowired
    protected TestRestTemplate testRestTemplate ;
	
	@Container
    public static OracleContainer oracleDB = new OracleContainer("gvenzl/oracle-xe:21-slim");


    @DynamicPropertySource
    public static void properties(DynamicPropertyRegistry registry) {        
        registry.add("spring.datasource.url",oracleDB::getJdbcUrl);
        registry.add("spring.datasource.username", oracleDB::getUsername);
        registry.add("spring.datasource.password", oracleDB::getPassword);

    }



	@Autowired
	protected TestRestTemplate testRestTemplate ;

}Code language: Java (java)

If you use the Junit5 and Springboot version < 2.2.6, you can use following code

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(initializers = BaseIT2.TestEnvInitializer.class)
@Testcontainers
@DirtiesContext
public  class BaseIT2 {

    @Autowired
    protected TestRestTemplate testRestTemplate ;

    @Container
    public static OracleContainer oracleDB = new OracleContainer("gvenzl/oracle-xe:21-slim");


    static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext applicationContext) {
            TestPropertyValues values = TestPropertyValues.of(
                    "spring.datasource.url=" + oracleDB.getJdbcUrl(),
                    "spring.datasource.password=" + oracleDB.getPassword(),
                    "spring.datasource.username=" + oracleDB.getUsername()
            );
            values.applyTo(applicationContext);

        }

    }

}Code language: Java (java)

Testcontainers while using oracle database image by default generates database named XEPDB1 and uses username/password as test/test.

If you want to create your own database, you can use below code block.

 @Container
    public static OracleContainer oracleDB = new OracleContainer("gvenzl/oracle-xe:21-slim")
                                                                                    .withDatabaseName("eis")
                                                                                    .withUsername("dbuser")
                                                                                    .withPassword("dbuser")
                                                                                    .withInitScript("ddl.sql");
Code language: Java (java)

Manual container starting

If you are using Junit5 and Springboot version >= 2.2.6

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@Testcontainers
@DirtiesContext
public  class BaseIT {

    @Autowired
    protected TestRestTemplate testRestTemplate ;

    public static OracleContainer oracleDB;

    static {
         oracleDB = new OracleContainer("gvenzl/oracle-xe:21-slim");
         oracleDB.start();
    }


    @DynamicPropertySource
    public static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url",oracleDB::getJdbcUrl);
        registry.add("spring.datasource.username", oracleDB::getUsername);
        registry.add("spring.datasource.password", oracleDB::getPassword);

    }

}Code language: Java (java)

If you use the Junit5 and Springboot version < 2.2.6, you can use following code

@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@ContextConfiguration(initializers = BaseIT2.TestEnvInitializer.class)
@Testcontainers
@DirtiesContext
public  class BaseIT2 {

    @Autowired
    protected TestRestTemplate testRestTemplate;

    public static OracleContainer oracleDB;

    static {
        oracleDB = new OracleContainer("gvenzl/oracle-xe:21-slim");
        oracleDB.start();
    }


    static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext applicationContext) {
            TestPropertyValues values = TestPropertyValues.of(
                    "spring.datasource.url=" + oracleDB.getJdbcUrl(),
                    "spring.datasource.password=" + oracleDB.getPassword(),
                    "spring.datasource.username=" + oracleDB.getUsername()
            );
            values.applyTo(applicationContext);

        }

    }
}Code language: Java (java)

You can download source code for ( primary using sequence) from GitHub here.

You can download source code for ( primary using identity) from GitHub here

Similar Posts

|

Spring Boot : Enabling HTTPS (SSL) Using Cloudflare Certificate

BySuresh Gadupu Cloud, Spring Boot

In this blog post, I will explain how to enable HTTPS (SSL) in Spring Boot applications using Cloudflare SSL certificate. For enabling SSL in Spring Boot applications we can use self signed certificate if you are running application in local development machine but in production you have to use certificate issued by Certificate Authority. In…

Simplify Local Development with Docker Compose – Part II

BySuresh Gadupu Spring Boot

In this blog post, I will explain the ways to setup local development environment using docker compose. This the second part in this series, you can read about first part here In my first part, I have shown you setting local development environment using docker and docker compose. I was creating docker image and using…

|

Build Mac and Linux ARM64 Spring Boot 3 Native Image with GitHub Selfhost Runners

BySuresh Gadupu CI/CD, Spring Boot

in this blog post I will explain how to build MacOS and Linux ARM64 native image for Spring boot 3 using GitHub Actions. In one of my previous blog post, I have shown you, how to build and release the spring boot 3 native binary for windows, Linux and Mac OS using GitHub actions. As…

Spring Boot – Testing RabbitMQ with Testcontainers

BySuresh Gadupu Spring Boot

In this tutorial, I will explain how to do integrating testing of spring boot applications which uses RabbitMQ server for messaging using Testcontainers. In my previous blog post I have covered how to write RabbitMQ producer and consumer application using Spring Boot framework. Now, we will write integration tests for testing the logic of RabbitMQ…

Building Native Image of Spring Boot 3 apps with GraalVM

BySuresh Gadupu Spring Boot

In this post, we will learn what is native image, how to build native image of Spring Boot 3 application with GraalVM. In general we deploy spring boot application by building jar file and run those jar file with jvm installed those systems. The Java code written is first compiled into bytecode during the build…

Spring Boot RabbitMQ Default Exchange Example

BySuresh Gadupu Spring Boot

In this tutorial, we’ll examine how to integrate RabbitMQ with Spring Boot applications to send and receive messages using the Default Exchange. This is the fourth post in this series. First part covers about Direct Exchange Second part covers about Topic Exchange Third part covers about Fanout Exchange Fourth part covers about Headers Exchange Default exchange The default…