Getting Started with DBUnit

What is DBUnit

You will often have a lot of trouble creating a unit test program for a class that references and updates the database. The following problems tend to occur when checking the database for unit tests.

• It takes time to put the data for the test into the DB, or to delete the data after the test and restore the DB.
• "Injecting and cleaning up test data" complicates the program and reduces maintainability. Anyway, I can't read or fix test programs written by others.
• Because the program becomes complicated, it is difficult to understand whether the method is executed and evaluated correctly.

Therefore, I will introduce a framework called `` `DBUnit```. DBUnit is a framework for creating test programs for classes that operate databases, and extends JUnit.

• Ability to input test data to DB
• Function to clean up DB
• Ability to check the DB status after method execution

It has functions such as. This chapter describes how to use DBUnit to describe test classes that perform database processing.

DBUtil installation

If it is a maven project, you can use DBUtil by writing the following in pom.xml.

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.22</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.dbunit</groupId>
			<artifactId>dbunit</artifactId>
			<version>2.6.0</version>
		</dependency>

JUnit is using version 4.13.

If you want to use DBUtil instead of maven project, you can use it by downloading the jar file from the link below and passing it through the classpath. (Although not recommended) http://www.dbunit.org/

This time, we will use DBUnit to do the following:

• Delete DB data before testing, insert data from XML
• Get test data for update confirmation from XML, execute test class
• Delete test data

Creating a test table

Since we are using mysql database this time, create a table with the following SQL.

CREATE TABLE user (
  Id INT NOT NULL,
  name  VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  salary INT,
  job_type VARCHAR(20),
  hiredate  TIMESTAMP(6),
  department_id INT
);

Implementation to be tested

Create a class to UPDATE one record.

TestMain

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *A class with main for DBUnit testing.
 */
public class TestMain {
  /**Logger*/
  private static Logger logger = LoggerFactory.getLogger(TestMain.class);

  /**SQL to execute*/
  private static final String SQL
      = " update user set name = 'abc' where id = 1";

  /**
   * @param args
   */
  public static void main(String[] args) {
    logger.info("Start processing");

    // ---------------------------------
    //Update DB
    // ---------------------------------
    try {
    	Class.forName("com.mysql.jdbc.Driver");
    	Connection conn = DriverManager.getConnection(
    	        "jdbc:mysql://localhost:3306/[Database name]?serverTimezone=JST", "[User name]", "[password]");
    	        PreparedStatement stmt = conn.prepareStatement(SQL);

      conn.setAutoCommit(false);
      int i = stmt.executeUpdate();

      //Display the number of processing
      logger.info("Number of cases processed:[" + i + "]");

      conn.commit();
    } catch(Exception e) {
      logger.error("error", e);
    }

    logger.info("Processing Exit");
  }
}

Creating an XML file to use when running the test

Before.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
  <!--
Numerical data is also included in XML for datasets""Surround with
DATE and TIMESTAMP type dates are "-Specify by connecting
  -->
  <user
      ID="1"
      NAME="scott"
      AGE="22"
      SALARY="200000"
      JOB_TYPE="employee"
      HIREDATE="2017-01-01 12:34:56"
      DEPARTMENT_ID="1"
  />
</dataset>

After.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
  <!--
The date is not subject to verification, but I will list it for the time being.
Enter a different date on purpose to verify that it has been filtered.
  -->
  <user
      ID="1"
      NAME="abc"
      AGE="22"
      SALARY="200000"
      JOB_TYPE="employee"
      HIREDATE="2017-12-31 24:12:36"
      DEPARTMENT_ID="1"
  />
</dataset>

Before.xml is the data to be inserted before executing the test class to confirm that only one case has been updated. After.xml is the DB data after the update is performed. It is used when asserting the test class.

Test class implementation

TestMainTest

import static org.junit.Assert.*;

import java.io.File;

import org.dbunit.Assertion;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.filter.DefaultColumnFilter;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * [Test class]<br>
 *Test using JUnit and DBUnit.<br>
 * <br>
 */
public class TestMainTest {
  /**Logger*/
  private Logger logger = LoggerFactory.getLogger(TestMain.class);

  /**DBUnit tester*/
  private static IDatabaseTester databaseTester;

  /**
   * [Preprocessing]<br>
   *Prepare advance data in DB.<br>
   * <br>
   * @throws java.lang.Exception
   */
  @Before
  public void setUp() throws Exception {
    logger.info("Start preprocessing");

    // --------------------------------------
    //INSERT of preparation data
    // --------------------------------------
    //Specify the schema for INSERT of preparation data
    databaseTester = new JdbcDatabaseTester("com.mysql.jdbc.Driver",
        "jdbc:mysql://localhost:3306/[Database name]?serverTimezone=JST", "[username]", "[password]");

    // --------------------------------------
    //Test data input
    // --------------------------------------
    IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File("src/test/java/com/example/DBUnitDemo/data/Before.xml"));
    databaseTester.setDataSet(dataSet);
    //Prepare preparation data by DELETE → INSERT
    databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
        databaseTester.onSetup();

    logger.info("Pre-processing finished");
  }

  /**
   * [Post-processing]<br>
   *Perform post-test post-processing.<br>
   *Clean up the DB Unit.<br>
   * <br>
   * @throws java.lang.Exception
   */
  @After
  public void tearDown() throws Exception {
    databaseTester.setTearDownOperation(DatabaseOperation.NONE);
    databaseTester.onTearDown();
  }

  /**
   * [test]<br>
   *Use DBUnit to verify the DB update result.<br>
   */
  @Test
  public void test() {
    logger.info("JUnit +Start testing with DBUnit.");

    TestMain.main(null);

    try {
      // ----------------------------------
      //Data check after update with DBUnit
      // ----------------------------------
      IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new File("src/test/java/com/example/DBUnitDemo/data/After.xml"));
      ITable expectedTable = expectedDataSet.getTable("user");

      IDataSet databaseDataSet = databaseTester.getConnection().createDataSet();
      ITable actualTable = databaseDataSet.getTable("user");

      //Assertion for time almost certainly fails, so exclude it from verification
      ITable filteredExpectedTable = DefaultColumnFilter.excludedColumnsTable(
          expectedTable, new String[]{"HIREDATE"});
      ITable filteredActualTable;
      filteredActualTable = DefaultColumnFilter.excludedColumnsTable(
          actualTable, new String[]{"HIREDATE"});

      // ---------------------------------------------------------------
      //Use DBUnit Assertion instead of JUnit to validate update results
      // ---------------------------------------------------------------
      Assertion.assertEquals(filteredExpectedTable, filteredActualTable);
    } catch (Exception e) {
      logger.error("error", e);
      fail("The test failed with an unexpected error.");
    }

    logger.info("JUnit +Start testing with DBUnit.");
  }
}

The DBUnit method used this time is as follows.

reference

This time we introduced DBUnit, and this article is created by referring to the following articles. It is written in more detail, so if you want to know more, please refer to it. [For super beginners] DBUnit super introduction DBUnit Loading data from Excel into DB using DBUnit with Spring Boot