[JAVA] How to perform UT with Excel as test data with Spring Boot + JUnit5 + DBUnit


In applications that use Spring Boot Described how to implement UT with JUnit5 + DBUnit.

Since the project structure is based on Spring Initializr, You can easily check the operation by referring to the following.


I used MyBatis + Oracle to check the operation. Please match the library used for each environment.


plugins {
    id 'org.springframework.boot' version '2.2.6.RELEASE'
    id 'io.spring.dependency-management' version '1.0.9.RELEASE'
    id 'java'
    id 'eclipse'

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '12'

repositories {

dependencies {

    implementation 'org.springframework.boot:spring-boot-starter'
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.0'

    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'

    //Please set for each DB
    runtime files('libs/ojdbc8.jar')

    // Lombok
    compileOnly 'org.projectlombok:lombok'

    // JUnit5
    testImplementation 'org.junit.jupiter:junit-jupiter:5.5.2'
    testImplementation 'org.junit.platform:junit-platform-commons:1.5.2'
    testImplementation 'com.github.springtestdbunit:spring-test-dbunit:1.3.0'

    // DBUnit
    testImplementation 'org.dbunit:dbunit:2.6.0'

test {

Sample to be tested

application.yml Since it will be the connection information for Oracle, please set it for each DB. "Schema" also exists in the datasource attribute, but please define it separately. When registering test data, the information in datasource.schema is not referenced and an exception occurs.


    url: jdbc:oracle:thin:@localhost:1521/XEPDB1
    username: sample
    password: sample
    driverClassName: oracle.jdbc.OracleDriver
schema: SAMPLE
  type-aliases-package: com.example.demo.entity
    map-underscore-to-camel-case: true

SAMPLE table

For Oracle ... (ry




public class SampleTable {

    private long sampleId;

    private String sampleName;

Repository ( Mapper )


public interface SampleTableRepository {
    SampleTable findOne(long sampleId);

Test class

Test DB configuration class

DBConfig for test execution. Use this configuration class to configure the data source and schema.


public class DbConfig {

    @Bean(name = "dbUnitDatabaseConnection")
    public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(DataSource dataSource, @Value(value = "${schema}") String schema) {
        DatabaseDataSourceConnectionFactoryBean connectionFactory = new DatabaseDataSourceConnectionFactoryBean();
        return connectionFactory;

Loader class for Excel


public class XlsDataSetLoader extends AbstractDataSetLoader {

    protected IDataSet createDataSet(Resource resource) throws IOException, DataSetException {
        try (InputStream inputStream = resource.getInputStream()) {
            return new XlsDataSet(inputStream);

Test class

@DbUnitConfiguration: Please set the data source of the above "Test DB setting class" and "Loader class for Excel". @DatabaseSetup: Set for each test and set the data source, test data, and registration method set in @DbUnitConfiguration.


@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, DirtiesContextTestExecutionListener.class, TransactionDbUnitTestExecutionListener.class, DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = XlsDataSetLoader.class, databaseConnection = { "dbUnitDatabaseConnection" })
public class SampleTableRepositoryTest {

    SampleTableRepository sampleTableRepository;

    @DatabaseSetup(connection = "dbUnitDatabaseConnection", value = "/testdata/findOneTest.xlsx", type = DatabaseOperation.CLEAN_INSERT)
void Data can be acquired normally() {
        SampleTable actual = sampleTableRepository.findOne(3L);
        Assertions.assertEquals("NAME_C", actual.getSampleName(), "SAMPLE_The value of NAME is as expected");

test data

Sheet name: Table name 1st line: Column name Second and subsequent lines: Data


in conclusion

When the test is executed with the above configuration, the Excel test data will be registered in the DB. The difficulty is that the speed is slow. (Maybe the configuration is not good In the future, I will try to improve performance.

Recommended Posts

How to perform UT with Excel as test data with Spring Boot + JUnit5 + DBUnit
Perform transaction confirmation test with Spring Boot
How to test private scope with JUnit
[Java] How to test for null with JUnit
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to test interrupts during Thread.sleep with JUnit
How to use built-in h2db with spring boot
How to write a unit test for Spring Boot 2
[JUnit 5 compatible] Write a test using JUnit 5 with Spring boot 2.2, 2.3
[JUnit 5] Write a validation test with Spring Boot! [Parameterization test]
How to boot by environment with Spring Boot of Maven
I wrote a test with Spring Boot + JUnit 5 now
Flow until output table data to view with Spring Boot
How to use the same Mapper class in multiple data sources with Spring Boot + MyBatis
How to use Spring Data JDBC
[How to install Spring Data Jpa]
How to set Spring Boot + PostgreSQL
Use DBUnit for Spring Boot test
How to use ModelMapper (Spring boot)
Test Spring framework controller with Junit
Sample code to unit test a Spring Boot controller with MockMvc
How to apply thymeleaf changes to the browser immediately with #Spring Boot + maven
How to read Body of Request multiple times with Spring Boot + Spring Security
How to filter JUnit Test in Gradle
How to test private methods with arrays or variadic arguments in JUnit
How to delete data with foreign key
How to split Spring Boot message file
Form class validation test with Spring Boot
How to create an Excel form using a template file with Spring MVC
Sample to batch process data on DB with Apache Camel Spring Boot starters
Part2 Part II. How to proceed with Getting Started Spring Boot Reference Guide Note ①
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
How to use Spring Boot session attributes (@SessionAttributes)
Try to implement login function with Spring Boot
How to add a classpath in Spring Boot
An introduction to Spring Boot + in-memory data grid
[Java] Hello World with Java 14 x Spring Boot 2.3 x JUnit 5 ~
How to write test code with Basic authentication
How to bind to property file in Spring Boot
Try to automate migration with Spring Boot Flyway
[Java] Article to add validation with Spring Boot 2.3.1.
I wanted to gradle spring boot with multi-project
[Spring Boot] How to refer to the property file
[Introduction to Spring Boot] Authentication function with Spring Security
Spring Boot --How to set session timeout time
Settings for connecting to MySQL with Spring Boot + Spring JDBC
How to set Dependency Injection (DI) for Spring Boot
Automatically map DTOs to entities with Spring Boot API
[Java] How to omit spring constructor injection with Lombok
How to unit test with JVM with source using RxAndroid
Test the contents of an Excel file with JUnit
How to create a Spring Boot project in IntelliJ
[Spring Boot] How to create a project (for beginners)
I tried to get started with Spring Data JPA
How to use CommandLineRunner in Spring Batch of Spring Boot
How to test file upload screen in Spring + Selenium
Attempt to SSR Vue.js with Spring Boot and GraalJS
Introduction to RSpec 4. Create test data with Factory Bot
Let's find out how to receive in Request Body with REST API of Spring Boot
How to use JUnit 5
Unit test with Junit.