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 Application on CapRover Using GitHub Actions

BySuresh Gadupu Platform, Spring Boot

In this blog post, I will explain how we can deploy the Spring Boot application on CapRover using GitHub Actions. In my previous blog post, I have shown how you can deploy Spring Boot application from GitHub repo automatically when ever we push the new code. In the deployment method we were building docker image…

|

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…

|

Build Spring Boot multi arch docker images with GitHub actions.

BySuresh Gadupu CI/CD, Spring Boot

In this blog post, I will show you how to build multi arch docker images of Spring Boot applications with GitHub actions. This is useful for building images that can be run on system with different architectures. In one of my previous blog post, I have shown you how to build spring boot application multi…

Enrich Your Spring Applications by Leveraging Python Functionality with GraalVM

BySuresh Gadupu Spring Boot

We already know that GraalVM compiles your Java applications ahead of time into standalone binaries that start instantly, provide peak performance with no warmup, and use fewer resources. GraalVM also supports polyglot programming where you can use multiple language features in single project. Polyglot programming refers to the practice of using multiple programming languages within…

DevContainers Support with Docker Compose in SpringBoot 3.1

BySuresh Gadupu Spring Boot

In this blog post we will see how we can use docker containers during development time using docker compose files in Spring Boot 3.1 which simplifies the development and testing efforts by providing infrastructure. In my previous blog posts (part-1, part-2), I have already shown how can we use docker containers during development for testing…