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
- Auto handling containers using @Container (Junit 5) or @ClassRule (Junit4) annotations
- 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