[JAVA] [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
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
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.
- Loading of advance preparation data
- Status in the program under test
- 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
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
■ DB status
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
■ After executing UPDATE
■ DB status after execution
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
■ After executing the assertion
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