1. Introduction
1.绪论
In this tutorial, we’ll take a look at DBUnit, a unit testing tool used to test relational database interactions in Java.
在本教程中，我们将看看DBUnit，这是一个单元测试工具，用于测试Java中的关系数据库交互。
We’ll see how it helps us get our database to a known state and assert against an expected state.
我们将看到它是如何帮助我们把数据库弄到一个已知的状态，并针对一个预期状态进行断言。
2. Dependencies
2.依赖性
First, we can add DBUnit to our project from Maven Central by adding the dbunit dependency to our pom.xml:
首先，我们可以通过在pom.xml中添加dbunit依赖项，从Maven中心向我们的项目添加DBUnit。
<dependency>
<groupId>org.dbunit</groupId>
<artifactId>dbunit</artifactId>
<version>2.7.0</version>
<scope>test</scope>
</dependency>We can look up the most recent version on Maven Central.
我们可以在Maven Central上查找最新的版本。
3. Hello World Example
3.Hello World实例
Next, let’s define a database schema:
接下来，让我们定义一个数据库模式：。
schema.sql:
schema.sql。
CREATE TABLE IF NOT EXISTS CLIENTS
(
`id` int AUTO_INCREMENT NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS ITEMS
(
`id` int AUTO_INCREMENT NOT NULL,
`title` varchar(100) NOT NULL,
`produced` date,
`price` float,
PRIMARY KEY (`id`)
);
3.1. Defining the Initial Database Contents
3.1.定义初始数据库内容
DBUnit lets us define and load our test dataset in a simple declarative way.
DBUnit让我们以一种简单的声明性的方式定义和加载我们的测试数据集。
We define each table row with one XML element, where the tag name is a table name, and attribute names and values map to column names and values respectively. The row data can be created for multiple tables. We have to implement the getDataSet() method of DataSourceBasedDBTestCase to define the initial data set, where we can use the FlatXmlDataSetBuilder to refer to our XML file:
我们用一个XML元素定义每个表行，其中标签名是表名，属性名和值分别映射到列名和列值。行数据可以为多个表创建。我们必须实现DataSourceBasedDBTestCase的getDataSet()方法来定义初始数据集，其中我们可以使用FlatXmlDataSetBuilder来引用我们的XML文件。
data.xml:
data.xml。
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<CLIENTS id='1' first_name='Charles' last_name='Xavier'/>
<ITEMS id='1' title='Grey T-Shirt' price='17.99' produced='2019-03-20'/>
<ITEMS id='2' title='Fitted Hat' price='29.99' produced='2019-03-21'/>
<ITEMS id='3' title='Backpack' price='54.99' produced='2019-03-22'/>
<ITEMS id='4' title='Earrings' price='14.99' produced='2019-03-23'/>
<ITEMS id='5' title='Socks' price='9.99'/>
</dataset>3.2. Initializing the Database Connection and Schema
3.2.初始化数据库连接和模式
Now that we’ve got our schema, we have to initialize our database.
现在我们已经有了我们的模式，我们必须初始化我们的数据库。
We have to extend the DataSourceBasedDBTestCase class and initialize the database schema in its getDataSource() method:
我们必须扩展DataSourceBasedDBTestCase类并在其getDataSource()方法中初始化数据库模式。
DataSourceDBUnitTest.java:
DataSourceDBUnitTest.java。
public class DataSourceDBUnitTest extends DataSourceBasedDBTestCase {
@Override
protected DataSource getDataSource() {
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL(
"jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;init=runscript from 'classpath:schema.sql'");
dataSource.setUser("sa");
dataSource.setPassword("sa");
return dataSource;
}
@Override
protected IDataSet getDataSet() throws Exception {
return new FlatXmlDataSetBuilder().build(getClass().getClassLoader()
.getResourceAsStream("data.xml"));
}
}Here, we passed a SQL file to an H2 in-memory database in its connection string. If we want to test on other databases we will need to provide our custom implementation for it.
在这里，我们在其连接字符串中向H2内存数据库传递了一个SQL文件。如果我们想在其他数据库上进行测试，我们将需要为它提供我们的自定义实现。
Keep in mind that, in our example, DBUnit will reinitialize the database with the given test data before each test method execution.
请记住，，在我们的例子中，DBUnit将在每个测试方法执行前用给定的测试数据重新初始化数据库。
There are multiple ways to configure this via getSetUpOperation and getTearDownOperation:
有多种方法可以通过getSetUpOperation和getTearDownOperation进行配置。
@Override
protected DatabaseOperation getSetUpOperation() {
return DatabaseOperation.REFRESH;
}
@Override
protected DatabaseOperation getTearDownOperation() {
return DatabaseOperation.DELETE_ALL;
}The REFRESH operation, tells DBUnit to refresh all its data. This will ensure that all caches are cleared up and our unit test gets no influence from another unit test. The DELETE_ALL operation ensures that all the data gets removed at the end of each unit test. In our case, we are telling DBUnit that during set up, using the getSetUpOperation method implementation we will refresh all caches. Finally, we tell DBUnit to remove all data during the teardown operation using the getTearDownOperation method implementation.
REFRESH操作，告诉DBUnit刷新所有的数据。这将确保所有缓存被清除，我们的单元测试不会受到另一个单元测试的影响。DELETE_ALL操作确保所有的数据在每个单元测试结束时被删除。在我们的案例中，我们告诉DBUnit在设置期间，使用getSetUpOperation方法实现，我们将刷新所有的缓存。最后，我们告诉DBUnit在拆分操作期间使用getTearDownOperation方法实现来删除所有数据。
3.3. Comparing the Expected State and the Actual State
3.3.比较预期状态和实际状态
Now, let’s examine our actual test case. For this first test, we’ll keep it simple – we’ll load our expected dataset and compare it to the dataset retrieved from our DB connection:
现在，让我们检查一下我们的实际测试案例。对于这第一个测试，我们将保持简单–我们将加载我们预期的数据集，并将其与从我们的数据库连接中获取的数据集进行比较。
@Test
public void givenDataSetEmptySchema_whenDataSetCreated_thenTablesAreEqual() throws Exception {
IDataSet expectedDataSet = getDataSet();
ITable expectedTable = expectedDataSet.getTable("CLIENTS");
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("CLIENTS");
assertEquals(expectedTable, actualTable);
}4. Deep Dive Into Assertions
4.深入研究断言
In the previous section, we saw a basic example of comparing the actual contents of a table with an expected data set. Now we’re going to discover DBUnit’s support for customizing data assertions.
在上一节中，我们看到了一个将表的实际内容与预期数据集进行比较的基本例子。现在我们要发现DBUnit对定制数据断言的支持。
4.1. Asserting with a SQL Query
4.1.用SQL查询进行断言
A straightforward way to check the actual state is with a SQL query.
检查实际状态的一个直接方法是使用SQL查询。
In this example, we’ll insert a new record into the CLIENTS table, then verify the contents of the newly created row. We defined the expected output in a separate XML file, and extracted the actual row value by an SQL query:
在这个例子中，我们将在CLIENTS表中插入一条新记录，然后验证新创建的行的内容。我们在一个单独的XML文件中定义了预期输出，并通过SQL查询提取了实际的行值。
@Test
public void givenDataSet_whenInsert_thenTableHasNewClient() throws Exception {
try (InputStream is = getClass().getClassLoader().getResourceAsStream("dbunit/expected-user.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = expectedDataSet.getTable("CLIENTS");
Connection conn = getDataSource().getConnection();
conn.createStatement()
.executeUpdate(
"INSERT INTO CLIENTS (first_name, last_name) VALUES ('John', 'Jansen')");
ITable actualData = getConnection()
.createQueryTable(
"result_name",
"SELECT * FROM CLIENTS WHERE last_name='Jansen'");
assertEqualsIgnoreCols(expectedTable, actualData, new String[] { "id" });
}
}The getConnection() method of the DBTestCase ancestor class returns a DBUnit-specific representation of the data source connection (an IDatabaseConnection instance). The createQueryTable() method of the IDatabaseConnection can be used to fetch actual data from the database, for comparison with the expected database state, using the Assertion.assertEquals() method. The SQL query passed onto createQueryTable() is the query we want to test. It returns a Table instance that we use to make our assert.
DBTestCase祖先类的getConnection()方法返回数据源连接的DBUnit特定表示（一个IDatabaseConnection实例）。IDatabaseConnection的createQueryTable()方法可用于从数据库中获取实际数据，以便使用Assertion.assertEquals()方法与预期数据库状态进行比较。传递给createQueryTable()的SQL查询是我们要测试的查询。它返回一个Table实例，我们用它来做断言。
4.2. Ignoring Columns
4.2.忽略列
Sometimes in database tests, we want to ignore some columns of the actual tables. These are usually auto-generated values that we can’t strictly control, like generated primary keys or current timestamps.
有时在数据库测试中，我们想忽略实际表的一些列。这些通常是我们无法严格控制的自动生成的值，比如生成的主键或当前的时间戳。
We could do this by omitting the columns from the SELECT clauses in the SQL queries, but DBUnit provides a more convenient utility for achieving this. With the static methods of the DefaultColumnFilter class we can create a new ITable instance from an existing one by excluding some of the columns, as shown here:
我们可以通过在SQL查询中的SELECT子句中省略这些列来做到这一点，但是DBUnit提供了一个更方便的工具来实现这一点。利用DefaultColumnFilter类的静态方法，我们可以从现有的ITable实例中排除一些列来创建一个新的ITable，如图所示。
@Test
public void givenDataSet_whenInsert_thenGetResultsAreStillEqualIfIgnoringColumnsWithDifferentProduced()
throws Exception {
Connection connection = tester.getConnection().getConnection();
String[] excludedColumns = { "id", "produced" };
try (InputStream is = getClass().getClassLoader()
.getResourceAsStream("dbunit/expected-ignoring-registered_at.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = excludedColumnsTable(expectedDataSet.getTable("ITEMS"), excludedColumns);
connection.createStatement()
.executeUpdate("INSERT INTO ITEMS (title, price, produced) VALUES('Necklace', 199.99, now())");
IDataSet databaseDataSet = tester.getConnection().createDataSet();
ITable actualTable = excludedColumnsTable(databaseDataSet.getTable("ITEMS"), excludedColumns);
assertEquals(expectedTable, actualTable);
}
}4.3. Investigating Multiple Failures
4.3.调查多个故障
If DBUnit finds an incorrect value, then it immediately throws an AssertionError.
如果DBUnit发现一个不正确的值，那么它立即抛出一个AssertionError。
In specific cases, we can use the DiffCollectingFailureHandler class, which we can pass to the Assertion.assertEquals() method as a third argument.
在特定情况下，我们可以使用DiffCollectingFailureHandler类，我们可以将其作为第三个参数传递给Assertion.assertEquals()方法。
This failure handler will collect all failures instead of stopping on the first one, meaning that the Assertion.assertEquals() method will always succeed if we use the DiffCollectingFailureHandler. Therefore, we’ll have to programmatically check if the handler found any errors:
这个故障处理程序将收集所有的故障，而不是在第一个故障上停止，这意味着如果我们使用Assertion.assertEquals()方法，将始终成功。因此，我们将不得不以编程方式检查处理程序是否发现了任何错误。
@Test
public void givenDataSet_whenInsertUnexpectedData_thenFailOnAllUnexpectedValues() throws Exception {
try (InputStream is = getClass().getClassLoader()
.getResourceAsStream("dbunit/expected-multiple-failures.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = expectedDataSet.getTable("ITEMS");
Connection conn = getDataSource().getConnection();
DiffCollectingFailureHandler collectingHandler = new DiffCollectingFailureHandler();
conn.createStatement()
.executeUpdate("INSERT INTO ITEMS (title, price) VALUES ('Battery', '1000000')");
ITable actualData = getConnection().createDataSet().getTable("ITEMS");
assertEquals(expectedTable, actualData, collectingHandler);
if (!collectingHandler.getDiffList().isEmpty()) {
String message = (String) collectingHandler.getDiffList()
.stream()
.map(d -> formatDifference((Difference) d))
.collect(joining("\n"));
logger.error(() -> message);
}
}
}
private static String formatDifference(Difference diff) {
return "expected value in " + diff.getExpectedTable()
.getTableMetaData()
.getTableName() + "." +
diff.getColumnName() + " row " +
diff.getRowIndex() + ":" +
diff.getExpectedValue() + ", but was: " +
diff.getActualValue();
}Furthermore, the handler provides the failures in the form of Difference instances, which lets us format the errors.
此外，处理程序以Difference实例的形式提供故障，这让我们可以格式化错误。
After running the test we get a formatted report:
运行测试后，我们得到一份格式化的报告。
java.lang.AssertionError: expected value in ITEMS.price row 5:199.99, but was: 1000000.0
expected value in ITEMS.produced row 5:2019-03-23, but was: null
expected value in ITEMS.title row 5:Necklace, but was: Battery
at com.baeldung.dbunit.DataSourceDBUnitTest.givenDataSet_whenInsertUnexpectedData_thenFailOnAllUnexpectedValues(DataSourceDBUnitTest.java:91)It’s important to notice that at this point we expected the new item to have a price of 199.99 but it was 1000000.0. Then we see that the production date to be 2019-03-23, but in the end, it was null. Finally, the expected item was a Necklace and instead we got a Battery.
需要注意的是，此时我们预计新商品的价格为199.99，但却是1000000.0。然后我们看到，生产日期为2019-03-23，但最后是空的。最后，预期的物品是一条项链，而我们得到的却是一个电池。
5. Conclusion
5.总结
In this article, we saw how DBUnit provides a declarative way of defining test data to test data access layers of Java applications.
在这篇文章中，我们看到了DBUnit是如何提供一种定义测试数据的声明性方式来测试Java应用程序的数据访问层。
As always, the full source code for the examples is available over on GitHub.
一如既往，这些示例的完整源代码可在GitHub上获得over。