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