[JAVA] 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.

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

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:

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.

Method name Explanation
JdbcDatabaseTester(String driverClass, String connectionUrl),JdbcDatabaseTester(String driverClass, String connectionUrl, String username, String password),JdbcDatabaseTester(String driverClass, String connectionUrl, String username, String password, String schema) DatabaseTester that creates a connection using the JDBC driver manager.
FlatXmlDataSetBuilder().build(File xmlInputFile) FlatXmlDataSet Sets the flat XML input source to build.
IDatabaseTester.setDataSet(IDataSet dataSet) Set the test dataset to use.
IDatabaseTester.setSetUpOperation() Gets the DatabaseOperation to call at the start of the test.
setTearDownOperation(DatabaseOperation tearDownOperation) Set Database Operation to call at the end of the test.
IDataSet.getTable(String tableName) Returns the specified table from the dataset.
IDatabaseTester.getConnection() Returns a test database connection.
IDatabaseConnection.createDataSet() Creates a dataset that contains only the specified tables from the database.
DefaultColumnFilter.excludedColumnsTable(ITable table, Column[] columns) Returns a table with the specified columns excluded.
Assertion.assertEquals(IDataSet expectedDataSet, IDataSet actualDataSet) Compare the expected ITable with the actual ITable

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

Recommended Posts

Getting Started with DBUnit
Getting Started with Ruby
Getting Started with Swift
Getting Started with Docker
Getting Started with Doma-Transactions
Getting Started with Doma-Annotation Processing
Getting Started with Java Collection
Getting Started with JSP & Servlet
Getting Started with Java Basics
Getting Started with Spring Boot
Getting Started with Ruby Modules
Getting Started with Java_Chapter 5_Practice Exercises 5_4
[Google Cloud] Getting Started with Docker
Getting started with Java lambda expressions
Getting Started with Docker with VS Code
Getting Started with Doma-Criteria API Cheat Sheet
Getting Started with Ruby for Java Engineers
Getting Started with Docker for Mac (Installation)
Getting Started with Parameterization Testing in JUnit
Getting Started with Java Starting from 0 Part 1
Getting Started with Ratpack (4)-Routing & Static Content
Getting started with the JVM's GC mechanism
Getting Started with Language Server Protocol with LSP4J
Getting Started with Creating Resource Bundles with ListResoueceBundle
Getting Started with Java_Chapter 8_About Instances and Classes
Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Java 1 Putting together similar things
Getting started with Kotlin to send to Java developers
Getting Started with Doma-Using Joins with the Criteira API
Getting Started with Doma-Introduction to the Criteria API
Get started with Gradle
I tried Getting Started with Gradle on Heroku
Getting started with Java programs using Visual Studio Code
Getting Started with Legacy Java Engineers (Stream + Lambda Expression)
Get started with Spring boot
Get started with DynamoDB with docker
Completable Future Getting Started (First Future)
Getting Started with Reactive Streams and the JDK 9 Flow API
Getting Started with GitHub Container Registry instead of Docker Hub
Let's get started with parallel programming
How to get started with slim
Use PostgreSQL inet type with DbUnit
Summarize the main points of getting started with JPA learned with Hibernate
Getting started with Swift / C bridges with porting Echo Server using libuv
CompletableFuture Getting Started 2 (Try to make CompletableFuture)
Message cooperation started with Spring Boot
Getting Started with Micronaut 2.x ~ Native Build and Deploy to AWS Lambda ~
Getting Started with Machine Learning with Spark "Price Estimate" # 1 Loading Datasets with Apache Spark (Java)
I tried to get started with WebAssembly
[Note] How to get started with Rspec
With dbunit 2.6.0, poi comes in without permission
[Deprecated] Getting started with JVM GC and memory management that I didn't understand
Part2 Part II. How to proceed with Getting Started Spring Boot Reference Guide Note ①