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