[For super beginners] DBUnit super introduction

Introduction

This time, it is a big success in program development! Introducing how to use "DBUnit", a framework that can be used in unit tests.

DBUnit is one of the testing frameworks used in Java, but from the feeling I studied, it seemed that the articles were old and there were few web sites that were completely sufficient, so I wrote a reminder. , I will summarize it here for sharing with those who read this article.

There are many kind people who have posted the materials on the web as a reference, but orz

1. What is DBUnit?

As mentioned earlier, DBUnit is one of the Java program testing frameworks. When you hear the word testing framework, you can imagine XUnit. Since the target of this time is a program implemented in Java, I often use JUnit, but I also use DBUnit together with JUnit.

In JUnit, you can check the return value of the method processed in the program and the passed parameter, but in DBUnit, you can check the value updated in the DB. In addition, test data can be loaded into the DB before the test starts.

It seems that there are many people who do not know its existence itself, but if you can master it, unit tests (hereinafter referred to as UT) will be much "easier and more accurate", so if you are interested, please read this article to the end. ..

2. Build a test project

First, build a test project to use for testing your DBUnit. Testing the testing framework sounds a bit weird, but I don't care about that.

I've been studying Maven before and now I'm pushing Maven, so this test project will be built using Maven and Eclipse.

For the construction of the Maven project, I would appreciate it if you could refer to the article here that I wrote earlier. Change the project name and configuration as you like. This time, I named it "dbunit-test" and added the following libraries as dependencies.

Describe the project tree and the settings of pom.xml.

Project tree


Project root
├─src
│  ├─main
│  │  ├─java
│  │  │  └─test
│  │  │      └─main
│  │  │              TestMain.java
│  │  │
│  │  └─resources
│  │
│  └─test
│      ├─java
│      │  └─test
│      │      └─main
│      │              TestMainTest.java
│      │
│      └─resources
│              logback.xml
│
├─target
│  ├─classes
│  │  │  logback.xml
│  │  │
│  │  └─test
│  │      └─main
│  │              TestMain.class
│  │
│  └─test-classes
│      │  logback.xml
│      │
│      └─test
│          └─main
│                  TestMainTest.class
│
├─data
│      Before.xml		---Preparation data
│      After.xml		---Result verification data
└─pom.xml

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>dbunit.test</groupId>
  <artifactId>dbunit-test</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <!--Character code and Java version settings-->
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <!--Logger settings-->
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.2.3</version>
    </dependency>
    <dependency>
      <!--
Since Oracle is used for the DB this time, the JDBC driver used is that of Oracle.
Oracle's JDBC driver is not in Maven's central repository, so use the one installed in your local repository.
Not directly related to DBUnit, but how to install the library in your local repository
Finally, I will describe it as a reference.
      -->
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc7</artifactId>
      <version>11.2.0</version>
    </dependency>

    <!--Library used for testing-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.dbunit</groupId>
      <artifactId>dbunit</artifactId>
      <!--Latest at the time of writing the article-->
      <version>2.5.3</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.6.1</version>
        <configuration>
          <source>${java.version}</source>
          <target>${java.version}</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

This article uses Maven, but you don't have to be Maven as long as you add JUnit and DBUnit to your build path. JUnit can be an Eclipse plugin, so change the project settings to your liking.

3. Build a test table

Build a test table to be implemented in the test program. This time, we will use the DB and the traditional SCOTT user, which are automatically generated during Oracle installation. The table used is also the EMP table, which is familiar in textbooks. If the default is built when Oracle is installed, it is created automatically from the beginning, but the DDL of the table is described.

EMP table DDL


CREATE TABLE SCOTT.EMP (
  ID            NUMBER        NOT NULL,
  NAME          VARCHAR2(255) NOT NULL,
  AGE           NUMBER        NOT NULL,
  SALARY        NUMBER,
  JOB_TYPE      VARCHAR2(20),
  HIREDATE      TIMESTAMP(6),
  DEPARTMENT_ID NUMBER
);

We will build the table, but this time we want to load the test data as a DBUnit, so leave the table empty.

4. Implementation of test program

Now that you have built the project skeleton, implement the test program. It's just a sample, so it's a program that only UPDATEs the records in one table.

4.1. Implementation of the program under test

The sample program to be tested is "TestMain.java". The test program uses logback, but this is a bonus so you don't need to use it. If you use logback, you can see the debug log of DBUnit, which is quite convenient.

TestMain.java


package test.main;

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.
 * @author tarosa0001
 */
public class TestMain {
  /**Logger*/
  private static Logger logger = LoggerFactory.getLogger(TestMain.class);

  /**SQL to execute*/
  private static final String SQL
      = " update "
      + " 		EMP "
      + " 	set "
      + "		name = 'tiger' "
      + " 	where "
      + " 		id = 1";

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

    // ---------------------------------
    //Update DB
    // ---------------------------------

    try(Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
        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");
  }
}

4.2. Implementation of JUnit + DBUnit test source

From here is the production. Implement a test source using JUnit and DBUnit. The implementation target is "TestMainTest.class". Describes the contents of the test program. Here, the test data is loaded in the pre-processing and the result data is verified by the test method, but the contents of each data will be described later.

TestMainTest.class


package test.main;

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>
 * @author tarosa0001
 */
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("oracle.jdbc.driver.OracleDriver",
        "jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger", "scott");

    // --------------------------------------
    //Test data input
    // --------------------------------------
    IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File("./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("./data/After.xml"));
      ITable expectedTable = expectedDataSet.getTable("EMP");

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

      //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.");
  }
}

4.3. Loading of preparation data Use DBUnit to load the preparatory data. Both the preparatory data and the result verification data use XML.

I hate XML, so I basically want to make something like this in Excel or CSV, but in CSV you can only define one file and one table, in Excel the sheet name is for the table name DBUnit test data uses XML because there are some inconvenient factors such as lack of length.

Describes the XML contents of the preparation data and the result verification data. The preparation data is "Before.xml", and the result verification data is "After.xml".

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
  -->
  <EMP
      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.
  -->
  <EMP
      ID="1"
      NAME="tiger"
      AGE="22"
      SALARY="200000"
      JOB_TYPE="employee"
      HIREDATE="2017-12-31 24:12:36"
      DEPARTMENT_ID="1"
  />
</dataset>

In the above, the date and time of the DATE type is fixed, but it is also possible to set the current date and time like SYSDATE. In that case, [here](https://hondou.homedns.org/pukiwiki/pukiwiki.php?xUnit%20DbUnit%20%A4%C7%A1%A2%BD%E9%B4%FC%A5%C7% A1% BC% A5% BF% A4% CB% B8% BD% BA% DF% BB% FE% B9% EF% A4% F2% C0% DF% C4% EA% A4% B7% A4% BF% A4% Please see A4) for reference.

5. Run the test program

Now that the preparatory work has been completed, let's run the test program. However, since we want to see the changes in the DB, let's record the state before execution.

5.1. Pre-execution state

The pre-execution table has empty records. It doesn't matter if there are records, but we will start from the state where it is easy to understand.

■ Table state before execution picture1.JPG

5.2. Run the test

Now let's run the test. Run the constructed project as a JUnit test. The result is as follows, I think that it ended normally without any error.

■ JUnit execution result picture2.JPG

You have now built and run a test with JUnit + DBUnit. This time, we have prepared only one preparation data and one result data, but if we prepare multiple data and change the XML to be read for each test, new data will be automatically loaded for each test case, and we expected. The results can be verified automatically.

6. Test disassembly

So far, we have introduced how to perform automatic testing with JUnit + DBUnit, but how about it? When asked if automated testing is good, that's not the case, but it's quite a hassle to redo tests and get test results over and over, so many tests can be automated. In some cases, it will be a big advantage.

So far, we've only talked about running DBUnit in a hurry, but from now on, we'll break down the run tests step by step. Specifically, we will break it down into the next steps, and in each step we will look at the behavior of the program and the state of the DB.

  1. Loading of advance preparation data
  2. Status in the program under test
  3. Verification of test results

Now, let's see how the test program works while debugging it on Eclipse.

6.1. Loading of advance preparation data

First, let's check the movement when loading the preparation data by DBUnit.

6.1.1. Pre-execution state

The table has been emptied once.

■ Table state before execution picture1.JPG

6.1.2. Immediately after the end of preprocessing

Immediately after the pre-processing is completed, you can see that the data read from Before.xml is exactly included. Since the test target program UPDATEs the DB during processing, it is a mother diagram that does not work well if there is no record, but it works normally because it starts in a state where no record is inserted in this preprocessing is.

■ Program execution steps picture4.jpg

■ DB status picture3.jpg

6.1.3. Status during test program

Let's see how the test program is running. Since the record to be updated is inserted in the preprocessing, you can see that UPDATE has finished executing normally and the DB status has changed.

■ Before executing UPDATE picture5.jpg

■ After executing UPDATE a4886ebe-6a13-abf8-1d56-8a5b4f694f3f.jpeg

■ DB status after execution picture7.jpg

6.1.4. Verification of test results

Finally, let's take a look at the verification of the test results. Since the DB status is as expected, the DBUnit assertion does not fail the test and the assertion process is running normally. The final result after executing the assertion is as described above.

■ Before executing the assertion picture8.jpg

■ After executing the assertion picture9.jpg

7. Finally

This is the last of this long article. I've been introducing DBunit for a long time, but how about it? DBUnit is a tool for automating unit tests, but if used properly, it can be a useful tool for loading test data other than unit tests.

However, while it is convenient, it takes a lot of time and effort, so it cannot be used all the time. In this article, there was only one test data, so it wasn't too much trouble, but if you have a lot of tables to prepare or a program that processes a lot of records at once, let's automate the verification. However, it is a lot of work because a huge amount of test data must be prepared.

With that in mind, if you use it well, I wouldn't be so happy.

8. Reference

I will describe the reference that was not described directly in this article. In writing the article, I referred to the following Web. Thank you to the managers and authors for writing the article.

■ How to install the library in Maven's local repository http://sinsengumi.net/blog/2012/12/maven%E3%83%AA%E3%83%9D%E3%82%B8%E3%83%88%E3%83%AA%E3%81%A7%E6%8F%90%E4%BE%9B%E3%81%95%E3%82%8C%E3%81%A6%E3%81%84%E3%81%AA%E3%81%84%E3%82%B5%E3%83%BC%E3%83%89%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3jar/

■ With DBUnit http://jyukutyo.hatenablog.com/entry/20060630/1151636345

Recommended Posts

[For super beginners] DBUnit super introduction
[For super beginners] Ant super introduction
[For super beginners] Maven super introduction
[For super beginners] Mirage SQL super introduction
[For super beginners] Struts2 Super Primer --2018 Edition
[For super super beginners] What is object orientation?
Ractor super introduction
Book introduction: Spring Boot Recommended reference book for beginners!
[For beginners] I tried using DBUnit in Eclipse
[For super beginners] How to use autofocus: true
[Introduction to Java] Basics of java arithmetic (for beginners)
Let's use Java New FileIO! (Introduction, for beginners)
Groovy super easy introduction
Scraping for beginners (Ruby)
Introduction to Java for beginners Basic knowledge of Java language ①
How to use GitHub for super beginners (team development)
Java debug execution [for Java beginners]
[Java] Basic statement for beginners
(For beginners) [Rails] Install Devise
More usable Enumerable for beginners
Java for beginners, data hiding
Java application for beginners: stream
Binary Tree Memorandum for Beginners (Part 1)
[For beginners] Summary of java constructor
[Rails] Introduction of Rubocop by beginners
Rock-paper-scissors game for beginners in Java
Environment construction with Docker for beginners
Java for beginners, expressions and operators 1
[For beginners] Run Selenium in Java
Links for creating Android apps (for beginners)
Java for beginners, expressions and operators 2
[Folio LSP] Roughly Docker (for beginners)
Notes for Android application development beginners
[Super Introduction] About Symbols in Ruby
Object-oriented course for beginners sent by beginners
Use DBUnit for Spring Boot test
Starting heroku through heroku CLI (for beginners)
Introduction to Programming for College Students: Introduction
Recommended learning method for programming beginners
[For Java beginners] About exception handling
Classes and instances Java for beginners
(For super beginners) Getter / setter and property to think in D language