Spring Boot Integration Testing with Testcontainers and DB2

BySuresh Gadupu Spring Boot

In this blog post I show how to do Spring Boot integration testing with Testcontainers using DB2 database.

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 DB2 database.

Adding Dependencies

Add DB2 specific dependencies for test containers and DB2 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.3</testcontainers.version>
	</properties>
	<dependencies>
		....
<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.ibm.db2</groupId>
			<artifactId>jcc</artifactId>
			<version>11.5.7.0</version>
		</dependency>
		
		<dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>junit-jupiter</artifactId>
			<scope>test</scope>
		</dependency>
                <dependency>
			<groupId>org.testcontainers</groupId>
			<artifactId>db2</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.

DB2 default date format is YYYY-MM-DD, 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') , TO_DATE('20041221','YYYYMMDD')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)

DB2 database image

I am going to use DB2 database image from here.

Primary Key Strategies

DB2 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 ALTER id RESTART WITH 1;

ALTER TABLE employee ALTER id RESTART 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 DB2 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 Db2Container db2 = new Db2Container ("ibmcom/db2:11.5.7.0a").acceptLicense();

    @DynamicPropertySource
    public static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url",db2::getJdbcUrl);
        registry.add("spring.datasource.username", db2::getUsername);
        registry.add("spring.datasource.password", db2::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 Db2Container db2 = new Db2Container ("ibmcom/db2:11.5.7.0a").acceptLicense();


    static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

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

        }

    }

}Code language: Java (java)

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

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 Db2Container db2;


    static {
        db2 =  new Db2Container ("ibmcom/db2:11.5.7.0").acceptLicense();

        db2.start();
    }


    @DynamicPropertySource
    public static void properties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url",db2::getJdbcUrl);
        registry.add("spring.datasource.username", db2::getUsername);
        registry.add("spring.datasource.password", db2::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 Db2Container db2;


    static {
        db2 =  new Db2Container ("ibmcom/db2:11.5.7.0").acceptLicense();

        db2.start();
    }


    static class TestEnvInitializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext applicationContext) {
            TestPropertyValues values = TestPropertyValues.of(
                    "spring.datasource.url=" + db2.getJdbcUrl(),
                    "spring.datasource.password=" + db2.getPassword(),
                    "spring.datasource.username=" + db2.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

You might also interested in

Run DB2 Database with Docker Compose

Similar Posts

|

Deploying Spring Boot Applications on CapRover

BySuresh Gadupu Platform, Spring Boot

In this blog post, I will show how can you can deploy the Spring Boot application on CapRover platform. In my previous blog post, I have shown you how to setup your own PAAS platform using CapRover on Oracle Cloud. Now we will see how to deploy Spring Boot application on Caprover. CapRover allows the…

Setup Local Development Environment with Kubernetes and Skaffold

BySuresh Gadupu Spring Boot

In this blog post, I will explain the ways to setup local development environment using Kubernetes and Skaffold. If you are deploying application in k8s environments, it would be better if you can deploy and test in similar environments in your local system. In this blog post we will deploy spring boot applications on k8s…

|

Build and Release Spring Boot 3 Native Image Binary with GitHub Actions

BySuresh Gadupu CI/CD, Spring Boot

In this post, how to build and release native image of Spring Boot 3 application with GraalVM using CI/CD pipeline of GitHub Actions. In one of my previous blog posts, I have shown you how to build native image of spring boot 3 framework application in local system. In this blog post, we will take…

Integration Testing in Spring Boot Application with Wiremock and Testcontainers

BySuresh Gadupu Spring Boot

In this blog post we will see how to use Wiremock for Spring Boot integration testing using Testcontainers. In one of my previous blog post, I have shown you how to mock external API using Wiremock library.In that blog post we have used annotation and programmatic approach to start the Wiremock server. Wiremock server also…