[JAVA] I tried using Spring + Mybatis + DbUnit

I had the opportunity to test around DB in Spring + Mybatis environment and investigated how to use DbUnit. (I will post it so that I will not forget it) Since transactions are managed on the Spring side, a little setting is required for cooperation with DbUnit.

Operating environment

Bean file settings

To link spring and DbUnit, add the following definition to the beans definition file of spring. DbUnit and spring seem to manage transactions with each other, as shown below If you do not wrap the dataSource in the TransactionAwareDataSourceProxy class, the data set in DbUnit will not roll back.

applicationContext.xml

    <!--Data source settings(reference) -->
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="org.postgresql.Driver"/>
      <property name="url" value="jdbc:postgresql://localhost:5432/<DB name>" />
      <property name="username" value="<Environment dependent>" />
      <property name="password" value="<Environment dependent>" />
   </bean>

   <!--When using dbunit with spring, wrap dataSource as follows-->
   <bean id="dataSourceTest"
    class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <constructor-arg ref="dataSource" />
   </bean>

Test source

About setting the test source of Junit.

Annotations such as @RunWith have the same settings as when using spring. The class inherits from DataSourceBasedDBTestCase to use DbUnit.

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
@Transactional
public class DocumentMapperTest extends DataSourceBasedDBTestCase {

When inheriting the Mapper class to be tested and DataSourceBasedDBTestCase, set dataSourceTest to be DI by @Autowired to be used as the return value of getDataSource () that needs to be implemented.

    @Autowired
    DocumentMapper mapper;

    @Autowired
    DataSource dataSourceTest;

    @Rule
    public TestName name = new TestName();

If you call super.setUp () in @Before, it will be set up before the test.

@Before
    public void setUp() throws Exception {
        // super.setUp()DB can be set up before test execution by calling.
        // setUp()If you don't call with, setup is required in the test method
        // super.setUp();
    }

As mentioned in the previous sentence, it is getDataSource () that needs to be implemented by inheriting the DataSourceBasedDBTestCase class. Here, the dataSourceTest set by @Autowired is returned.

    @Override
    protected DataSource getDataSource() {
        return this.dataSourceTest;
    }

GetDataSet () that needs to be implemented in the inheritance of the DataSourceBasedDBTestCase class. The data set in the DB will be returned here. getDataSet () is called in super.setUp (). Here, the Excel file (xls format) that matches the test method name is passed as DB data from the classpath. If you write null in the Excel file, it will be treated as a character string instead of null, so it is replaced with null using the ReplacementDataSet class provided by DbUnit (the part that is null in the Excel file is defined by [null]). .. In the latest DbUnit, not only xls format but also xlsx format can be used.

    @Override
    protected IDataSet getDataSet() throws Exception {

        String pathName = name.getMethodName();

        String xlsname = pathName + ".xls";
        InputStream is = getClass().getClassLoader().getResourceAsStream(xlsname);
        XlsDataSet dataSet = new XlsDataSet(is);

        ReplacementDataSet replacement = new ReplacementDataSet(dataSet);
        // [null]Replaced with null
        replacement.addReplacementObject("[null]", null);
        // [systemtime]Replace with the current date and time
        replacement.addReplacementObject("[systemtime]", toDateTimeStr(nowTime));

        return replacement;
    }

With the above settings, you can test with Spring + Mybatis + DbUnit. Actually, there is already Spring Test DBUnit, so it may be better to use this without wasting effort.

Reference (source)

pom.xml

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>sample</groupId>
  <artifactId>SpringMybatisDbUnit</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>4.3.7.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.2</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.7.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.3.7.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.dbunit/dbunit -->
    <dependency>
      <groupId>org.dbunit</groupId>
      <artifactId>dbunit</artifactId>
      <version>2.5.3</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.7.RELEASE</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.0.0.jre7</version>
    </dependency>
  </dependencies>
</project>

Document.java

package mng.doc.dao;

import java.lang.reflect.Field;
import java.util.Date;

public class Document {

    private Integer id;
    private String name;
    private String registrant;
    private Date registrationtime;
    private String editor;
    private Date edittime;

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getRegistrant() {
        return registrant;
    }

    public String getEditor() {
        return editor;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setRegistrant(String registrant) {
        this.registrant = registrant;
    }

    public void setEditor(String editor) {
        this.editor = editor;
    }

    public Date getRegistrationtime() {
        return registrationtime;
    }

    public Date getEdittime() {
        return edittime;
    }

    public void setRegistrationtime(Date registrationtime) {
        this.registrationtime = registrationtime;
    }

    public void setEdittime(Date edittime) {
        this.edittime = edittime;
    }

    public String toString() {

        StringBuilder sb = new StringBuilder();
        sb.append("Document [");
        Field[] fields = this.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {

            Field field = fields[i];
            String name = field.getName();
            field.setAccessible(true);
            Object value = null;
            try {
                value = field.get(this);
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

            sb.append(name + " = " + value);
            if (i != (fields.length - 1)) {
                sb.append(", ");
            }
        }
        sb.append("]");
        return sb.toString();
    }
}

DucumentMapper.java

package mng.doc.mapper;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Param;

import mng.doc.dao.Document;

public interface DocumentMapper {

    public List<Document> selectAll();
    public Integer insertDocument(Document doc);
    public Integer updateDocument(@Param("editor") String editor, @Param("edittime") Date edittime);
    public Integer deleteDocument(Integer id);
}

DocumentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mng.doc.mapper.DocumentMapper">

  <resultMap id="baseMap" type="mng.doc.dao.Document">
    <id property="id" jdbcType="INTEGER" column="id" />
    <result property="name" jdbcType="VARCHAR" column="name" />
    <result property="registrant" jdbcType="VARCHAR" column="registrant" />
    <result property="registrationtime" jdbcType="TIMESTAMP" column="registration_time" />
    <result property="editor" jdbcType="VARCHAR" column="editor" />
    <result property="edittime" jdbcType="TIMESTAMP" column="edit_time" />
  </resultMap>

  <select id="selectAll" resultMap="baseMap">
    SELECT
    *
    FROM
    DOCUMENT
  </select>
  <insert id="insertDocument" parameterType="mng.doc.dao.Document">
    INSERT INTO
    DOCUMENT
    (name, registrant, registration_time)
    VALUES
    (
    #{name},
    #{registrant},
    #{registrationtime}

    )
  </insert>
  <update id="updateDocument">
    UPDATE
    DOCUMENT
    SET
    editor = #{editor},
    edit_time = ${edittime}
  </update>

  <delete id="deleteDocument" parameterType="java.lang.Integer">
    DELETE
    FROM
    DOCUMENT
    WHERE
    id = #{id}
  </delete>
</mapper>

DocumentMapperTest.java

package mng.doc.mapper;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import mng.doc.dao.Document;
import org.dbunit.DataSourceBasedDBTestCase;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ReplacementDataSet;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TestName;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
@Transactional
public class DocumentMapperTest extends DataSourceBasedDBTestCase {

    Long nowTime = System.currentTimeMillis();

    @Autowired
    DocumentMapper mapper;

    @Autowired
    DataSource dataSourceTest;

    @Rule
    public TestName name = new TestName();


    @Test
    public void testSelectAll() throws Exception {
        prepare();
        List<Document> documents = this.mapper.selectAll();
        for (Document c : documents) {
            System.out.println(c.toString());
        }
    }

    @Before
    public void setUp() throws Exception {
        // super.setUp()DB can be set up before test execution by calling.
        // setUp()If you don't call with, setup is required in the test method
        // super.setUp();
    }

    public void prepare() throws Exception {
        DatabaseConnection connection = new DatabaseConnection(this.dataSourceTest.getConnection());
        DatabaseOperation.CLEAN_INSERT.execute(connection, getDataSet());
    }

    public void resetSequenceName(String sequenceName) throws Exception {
        DatabaseConnection connection = new DatabaseConnection(this.dataSourceTest.getConnection());
        connection
        .getConnection()
        .createStatement()
        .executeQuery("select setval('" + sequenceName + "', 1, false)");
    }

    @Override
    protected DataSource getDataSource() {
        return this.dataSourceTest;
    }

    @Override
    protected IDataSet getDataSet() throws Exception {

        String pathName = name.getMethodName();

        String xlsname = pathName + ".xls";
        InputStream is = getClass().getClassLoader().getResourceAsStream(xlsname);
        XlsDataSet dataSet = new XlsDataSet(is);

        ReplacementDataSet replacement = new ReplacementDataSet(dataSet);
        // [null]Replaced with null
        replacement.addReplacementObject("[null]", null);
        // [systemtime]Replace with the current date and time
        replacement.addReplacementObject("[systemtime]", toDateTimeStr(nowTime));

        return replacement;
    }

    private String toDateTimeStr(Long time) {
        return toDateTimeStr(new Date(time));
    }

    private String toDateTimeStr(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
        String result = sdf.format(date);
        System.out.println(result);
        return result;
    }
}

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/jdbc
        http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

    <!--Data source settings-->
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="org.postgresql.Driver"/>
      <property name="url" value="jdbc:postgresql://localhost:5432/<DB name>" />
      <property name="username" value="<Environment dependent>" />
      <property name="password" value="<Environment dependent>" />
   </bean>

  <!--Transaction Manager settings-->
  <bean id="transactionManager"
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
  </bean>

  <!--SqlSessionFactory settings-->
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
  </bean>

  <!--Mapper interface registration-->
  <bean id="sampleMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="mng.doc.mapper.DocumentMapper" />
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
  </bean>

  <bean id="dataSourceTest"
    class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <constructor-arg ref="dataSource" />
  </bean>

</beans>

document.sql

-- Table: public.document

-- DROP TABLE public.document;

CREATE TABLE public.document
(
  id integer NOT NULL DEFAULT nextval('document_id_seq'::regclass),
  name text NOT NULL,
  registrant character varying(50),
  registration_time timestamp without time zone,
  editor character varying(50),
  edit_time timestamp without time zone,
  CONSTRAINT document_pkey PRIMARY KEY (id, name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.document
  OWNER TO postgres;