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

Introduction

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.

Installation

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

build.gradle

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 {
    mavenCentral()
}

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 {
    useJUnitPlatform()
}

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.

• Maybe only Oracle

application.yml

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

SAMPLE table

For Oracle ... (ry

CREATE TABLE SAMPLE_TABLE 
( SAMPLE_ID NUMBER NOT NULL,
  SAMPLE_NAME VARCHAR2(255 CHAR) NOT NULL, 
  CONSTRAINT "PK_SAMPLE_TABLE" PRIMARY KEY (SAMPLE_ID)
);

Entity

SampleTable.java

@Data
public class SampleTable {

    private long sampleId;

    private String sampleName;
}

Repository ( Mapper )

SampleTableRepository.java

@Mapper
public interface SampleTableRepository {
    @Select("SELECT SAMPLE_ID, SAMPLE_NAME FROM SAMPLE_TABLE WHERE SAMPLE_ID = #{sampleId}")
    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.

DbConfig.java

@Configuration
public class DbConfig {

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

Loader class for Excel

XlsDataSetLoader.java

public class XlsDataSetLoader extends AbstractDataSetLoader {

    @Override
    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.

• Please refer to here for Database Operation.

python

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

    @Autowired
    SampleTableRepository sampleTableRepository;

    @Test
    @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.