Integration Testing with Spring Boot,Testcontainers,Oracle

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

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