DBUnit是一个方便的数据准备工具, 方便于我们做单元测试的时候准备数据, 它的数据准备是基于XML格式的, 如下:
|
<?xmlversion='1.0'encoding='UTF-8'?> <dataset> <YourTableName_1Field_1="1"Field_2="f2"Field_3="f3"/> <YourTableName_1Field_1="2"Field_2="f2_1"Field_3="f3_1"/> <YourTableName_2Field_1="1"Field_2="2"/> </dataset> |
DBUnit的一个XML数据文件中，可以同时放多个表的数据，并且可以方便的把上面XML中准备的数据插入倒数据库中. 只需要使用下面简单的代码就可以做到：
|
protected ReplacementDataSet createDataSet(InputStream is) throws Exception { return new ReplacementDataSet(new FlatXmlDataSetBuilder().build(is)); } ReplacementDataSet createDataSet = createDataSet(Thread.currentThread().getContextClassLoader().getResourceAsStream("data.xml")); DatabaseOperation.INSERT.execute(iconn, createDataSet); |
注：准备这处XML数据文件时，一定要把同一个表中字段数最多的记录放在前面，因为DBUnit在根据数据XML文件准备表的元数据字段的时候，是以当前表的第一记录为主的。如下面这个XML文件：
|
<?xml version='1.0' encoding='UTF-8'?> <dataset> <YourTableName_1 Field_1="1" Field_2="f2"/> <YourTableName_1 Field_1="2" Field_2="f2_1" Field_3="f3_1"/> <YourTableName_2 Field_1="1" Field_2="2"/> </dataset> |
Table YourTableName_1有三个字段，但是第三个字段Field_3是允许为空的，所以上面的XML是可以这样写的，并且DBUnit在执行插入的时候也不会报错，但是这里会出现一个问题，因为DBUnit在第一次分析到table YourTableName_1时，第一条记录只有两个字段，因而它在记录table YourTableName_1的字段的时候，就只会记录两个到这个表的元数据信息，因而在对在对后面记录进行数据处理的时候，只会取后面记录的两个字段，而第三个字段是不会被插入到数据库中。解决这个问题很简单，把YourTableName_1的第二条记录和第一条记录给换一下位置就好了。 同理，在数据result数据文件的时候，也要遵循这样的规则，否则会得不到想要的结果的。这是经验总结。
你可能会担心如果是要准备的数据比较多是不是会比较麻烦,如上百条的数据准备，这个可以不用担心，因为使用DBUnit可以方便的从数据库中导出数据到指定的文件中，然后供我们使用，使用以下这个方法就可以导出数据：
|
/** * Export data for the table names by the given IDatabaseConnection into the resultFile.<br> * The export data will be DBUnit format. * * @param conn * @param tableNameList * @param resultFile * @throws SQLException * @throws DatabaseUnitException * @throws FileNotFoundException * @throws IOException */ public void exportData(IDatabaseConnection iconn, List<String> tableNameList, String resultFile) throws SQLException, DatabaseUnitException, FileNotFoundException, IOException { QueryDataSet dataSet = null; if (iconn == null) { return; } if (tableNameList == null || tableNameList.size() == 0) { return; } try { dataSet = new QueryDataSet(iconn); for (String tableName : tableNameList) { dataSet.addTable(tableName); } } finally { if (dataSet != null) { FlatXmlDataSet.write(dataSet, new FileOutputStream(resultFile)); } } } |
DBUnit的另一个非常有用的功能，就是对执行结果进行比较，这样可以直接得到执行结果是否正确。 操作方式是准备一个执行期待结果的XML文件，再准备一条从数据库查询结果的SQL。这里有一个经验非常重要，那就是用于查询的执行结果的SQL文件，最好是加上某个关键字段的ORDER BY语句，否则可能会因为记录的顺序而比较失败，因为DBUnit是把查询出来的结果和准备的结果进行一一对应的比较。当然，既然SQL查询都加上了排序，那我们的结果XML文件，也应该是根据关键字段排好序的结果的，否则也会因为记录的顺序问题而比较失败。
你可能会担心如果是要准备的数据比较多是不是会比较麻烦,如上百条的数据准备，这个可以不用担心，因为使用DBUnit可以方便的从数据库中导出数据到指定的文件中，然后供我们使用，使用以下这个方法就可以导出数据：
|
/** * Export data for the table names by the given IDatabaseConnection into the resultFile.<br> * The export data will be DBUnit format. * * @param conn * @param tableNameList * @param resultFile * @throws SQLException * @throws DatabaseUnitException * @throws FileNotFoundException * @throws IOException */ public void exportData(IDatabaseConnection iconn, List<String> tableNameList, String resultFile) throws SQLException, DatabaseUnitException, FileNotFoundException, IOException { QueryDataSet dataSet = null; if (iconn == null) { return; } if (tableNameList == null || tableNameList.size() == 0) { return; } try { dataSet = new QueryDataSet(iconn); for (String tableName : tableNameList) { dataSet.addTable(tableName); } } finally { if (dataSet != null) { FlatXmlDataSet.write(dataSet, new FileOutputStream(resultFile)); } } } |
DBUnit的另一个非常有用的功能，就是对执行结果进行比较，这样可以直接得到执行结果是否正确。 操作方式是准备一个执行期待结果的XML文件，再准备一条从数据库查询结果的SQL。这里有一个经验非常重要，那就是用于查询的执行结果的SQL文件，最好是加上某个关键字段的ORDER BY语句，否则可能会因为记录的顺序而比较失败，因为DBUnit是把查询出来的结果和准备的结果进行一一对应的比较。当然，既然SQL查询都加上了排序，那我们的结果XML文件，也应该是根据关键字段排好序的结果的，否则也会因为记录的顺序问题而比较失败。
上面的是热身，该来点实际的东西了， 弄个真实的实例来看看，下面是一个用于DBUnit测试的抽象类：
|
import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Properties; import java.util.TreeMap; import junit.framework.Assert; import org.dbunit.Assertion; import org.dbunit.DatabaseUnitException; import org.dbunit.IDatabaseTester; import org.dbunit.JdbcDatabaseTester; import org.dbunit.database.DatabaseConnection; import org.dbunit.database.IDatabaseConnection; import org.dbunit.database.QueryDataSet; import org.dbunit.dataset.Column; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.ITable; import org.dbunit.dataset.ReplacementDataSet; import org.dbunit.dataset.filter.DefaultColumnFilter; import org.dbunit.dataset.xml.FlatXmlDataSet; import org.dbunit.dataset.xml.FlatXmlDataSetBuilder; import org.junit.runner.RunWith; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.TestExecutionListeners; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import org.springframework.test.context.support.DependencyInjectionTestExecutionListener; import org.springframework.transaction.annotation.Transactional; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "classpath:/spring.xml" }) @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class }) @Transactional public abstract class BasedTestCase { protected static Properties properties = new Properties(); static { try { /** * The DatabaseConfig.properties stores the database configuration information. It's like this: <br> * driverClass=oracle.jdbc.OracleDriver<br> * db_inst=jdbc:oracle:thin:@1.1.1.1:1521:schema<br> * db_user=username<br> * db_pwd=password<br> */ properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("DatabaseConfig.properties")); } catch (IOException e) { e.printStackTrace(); } } /** * This abstract is used for prepare data before do the real method call. * * @param iconn * @throws Exception */ protected abstract void prepareData(IDatabaseConnection iconn) throws Exception; /** * Execute one sql * * @param iconn * @param sql * @throws Exception */ protected void execSql(IDatabaseConnection iconn, String sql) throws Exception { Connection con = iconn.getConnection(); Statement stmt = con.createStatement(); try { stmt.execute(sql); } finally { if (stmt != null) { stmt.close(); } } } /** * Get IDatabaseConnection connection * * @return * @throws Exception */ protected IDatabaseConnection getIDatabaseConnection() throws Exception { String db_inst = "", db_user = "", db_pwd = "", driverClass = ""; //The default is commit the record db_user = properties.getProperty("db_user"); db_inst = properties.getProperty("db_inst"); db_pwd = properties.getProperty("db_pwd"); driverClass = properties.getProperty("driverClass"); IDatabaseConnection iconn = null; IDatabaseTester databaseTester; databaseTester = new JdbcDatabaseTester(driverClass, db_inst, db_user, db_pwd); iconn = databaseTester.getConnection(); return iconn; } /** * This is used to assert the data from table and the expected data set. If all of the them has the same records, then the assert is true. * * @param tableName * @param sql * @param expectedDataSet * @param iconn * @throws Exception */ protected void assertDataSet(String tableName, String sql, IDataSet expectedDataSet, IDatabaseConnection iconn) throws Exception { printDataAsXml(iconn, tableName, sql); QueryDataSet loadedDataSet = new QueryDataSet(iconn); loadedDataSet.addTable(tableName, sql); ITable table1 = loadedDataSet.getTable(tableName); ITable table2 = expectedDataSet.getTable(tableName); Assert.assertEquals(table2.getRowCount(), table1.getRowCount()); DefaultColumnFilter.includedColumnsTable(table1, table2.getTableMetaData().getColumns()); Assertion.assertEquals(table2, table1); } /** * Create the data set by input stream which read from the dbunit xml data file. * * @param is * @return * @throws Exception */ protected ReplacementDataSet createDataSet(InputStream is) throws Exception { return new ReplacementDataSet(new FlatXmlDataSetBuilder().build(is)); } /** * Convert the data in the ITable to List * * @param table * @return * @throws Exception */ private List<Map<?, ?>> getDataFromTable(ITable table) throws Exception { List<Map<?, ?>> ret = new ArrayList<Map<?, ?>>(); int count_table = table.getRowCount(); if (count_table > 0) { Column[] columns = table.getTableMetaData().getColumns(); for (int i = 0; i < count_table; i++) { Map<String, Object> map = new TreeMap<String, Object>(); for (Column column : columns) { map.put(column.getColumnName().toUpperCase(), table.getValue(i, column.getColumnName())); } ret.add(map); } } return ret; } /** * Get data by the SQL and table name, then convert the data in the ITable to List * * @param iconn * @param tableName * @param sql * @return * @throws Exception */ protected List<Map<?, ?>> getTableDataFromSql(IDatabaseConnection iconn, String tableName, String sql) throws Exception { ITable table = iconn.createQueryTable(tableName, sql); return getDataFromTable(table); } /** * Get data by the SQL and table name, then convert the data in the ITable to List. And the print the data as xml data format. * * @param iconn * @param tableName * @param sql * @throws Exception */ protected void printDataAsXml(IDatabaseConnection iconn, String tableName, String sql) throws Exception { List<Map<?, ?>> datas = getTableDataFromSql(iconn, tableName, sql); StringBuffer sb; for (Map<?, ?> data : datas) { sb = new StringBuffer(); sb.append("<" + tableName.toUpperCase() + " "); for (Object o : data.keySet()) { sb.append(o + "=\"" + data.get(o) + "\" "); } sb.append("/>"); System.out.println(sb.toString()); } } /** * Export data for the table names by the given Connection into the resultFile.<br> * The export data will be DBUnit format. * * @param conn * @param tableNameList * @param resultFile * @throws SQLException * @throws DatabaseUnitException * @throws FileNotFoundException * @throws IOException */ public void exportData(Connection conn, List<String> tableNameList, String resultFile) throws SQLException, DatabaseUnitException, FileNotFoundException, IOException { if (conn == null) { return; } IDatabaseConnection iconn = new DatabaseConnection(conn); exportData(iconn, tableNameList, resultFile); } /** * Export data for the table names by the given IDatabaseConnection into the resultFile.<br> * The export data will be DBUnit format. * * @param conn * @param tableNameList * @param resultFile * @throws SQLException * @throws DatabaseUnitException * @throws FileNotFoundException * @throws IOException */ public void exportData(IDatabaseConnection iconn, List<String> tableNameList, String resultFile) throws SQLException, DatabaseUnitException, FileNotFoundException, IOException { QueryDataSet dataSet = null; if (iconn == null) { return; } if (tableNameList == null || tableNameList.size() == 0) { return; } try { dataSet = new QueryDataSet(iconn); for (String tableName : tableNameList) { dataSet.addTable(tableName); } } finally { if (dataSet != null) { FlatXmlDataSet.write(dataSet, new FileOutputStream(resultFile)); } } } } |
这个抽象类里面有实用插入数据、导出数据及验证数据的实现，也包括了数据库连接的准备，该类里面包含了一个抽象方法prepareData，因为任何使用DBUnit做单元测试的，应该是少不了数据准备这么一个过程，否则就只能够使用数据库中的现成数据，这样的单元测试是不靠谱的，因为数据库中的数据随时可能发生变化，这里的抽象方法prepareData就相当于在提醒写单元测试的人，不要忘了准备单元测试要用的数据。
根据上面的思路，准备一个用于测试的Table：
|
create table YouTableName_1( filed_1 int, filed_2 varchar2(50), filed_3 varchar2(50) ) |
用于测试的数据：
|
<?xml version='1.0' encoding='UTF-8'?> <dataset> <YouTableName_1 Filed_1="1" Filed_2="f2" Filed_3="f3"/> <YouTableName_1 Filed_1="2" Filed_2="f2_1" Filed_3="f3_1"/> </dataset> |
用于验证测试结果的数据：
|
<?xml version='1.0' encoding='UTF-8'?> <dataset> <YouTableName_1 Filed_1="1" Filed_2="a" Filed_3="a1"/> <YouTableName_1 Filed_1="2" Filed_2="b" Filed_3="b1"/> </dataset> |
我们这个测试非常简单，就是把Filed_1为1的字段中Filed_2和Filed_3的字段的值分别设为"a"和"a1"，把Filed_1为2的字段中Filed_2和Filed_3的字段的值分别设为"b"和"b1"，做完测试后，数据库中是不会插入我们的单元测试的数据的。
下面这个类UpdateTest用于更新数据：
|
import java.sql.Connection; import java.sql.SQLException; public class UpdateTest { private static boolean commit = true; public UpdateTest() { } private void commit(Connection conn) throws SQLException { if (commit) { conn.commit(); } } public void updateFiled(Connection conn) throws SQLException { conn.createStatement().execute("update YouTableName_1 set filed_2='a',filed_3='a1' where filed_1=1"); conn.createStatement().execute("update YouTableName_1 set filed_2='b',filed_3='b1' where filed_1=2"); commit(conn); } } |
下面这个MyTest类，就是用于单元测试的类：
|
package com.ubs.cre.tools.datatool.ipl; import java.io.IOException; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.SQLException; import junit.framework.Assert; import org.dbunit.database.IDatabaseConnection; import org.dbunit.dataset.ReplacementDataSet; import org.dbunit.operation.DatabaseOperation; import org.junit.Test; import com.ubs.cre.BasedTestCase; public class MyTest extends BasedTestCase { @Test public void testSend() throws IOException, SQLException { Connection conn = null; Boolean result = Boolean.FALSE; IDatabaseConnection iconn = null; try { //Get DBUnit conneciton iconn = getIDatabaseConnection(); //Get database connection conn = iconn.getConnection(); //Set auto commit false conn.setAutoCommit(false); //prepare data prepareData(iconn); //use reflect to set the commit field to false Class<UpdateTest> clazz = UpdateTest.class; Field commitField = clazz.getDeclaredField("commit"); commitField.setAccessible(true); commitField.setBoolean(clazz, false); //call the method updateFiled Method method = clazz.getDeclaredMethod("updateFiled", java.sql.Connection.class); method.setAccessible(true); //Before call the method, the clazz must be get an new install, because the called method "updateFiled" is not static.<br> //If the called method is static, it will not need newInstance. method.invoke(clazz.newInstance(), conn); // get result data set by result xml file ReplacementDataSet dataload_result = createDataSet(Thread.currentThread().getContextClassLoader().getResourceAsStream("MyTest_Result.xml")); // compare the data which get from database and the expected result file assertDataSet("YouTableName_1", "select filed_1,filed_2,filed_3 from YouTableName_1 order by filed_1", dataload_result, iconn); } catch (Exception e) { e.printStackTrace(); Assert.assertTrue(result); } finally { if (conn != null) { conn.rollback(); conn.close(); } } } protected void prepareData(IDatabaseConnection iconn) throws Exception { //Remove the data from table YouTableName_1 execSql(iconn, "delete from YouTableName_1"); //INSERT TEST DATA ReplacementDataSet createDataSet = createDataSet(Thread.currentThread().getContextClassLoader().getResourceAsStream("MyTest.xml")); DatabaseOperation.INSERT.execute(iconn, createDataSet); } } |
好了，示例完了，非常的简单，也非常的清晰，不过美中不足就是和DBUnit的代码耦合度太高了，这过对于我们使用习惯了Spring的人来说，看起来是非常别扭的，后面我会写另外一个与Spring集成的、完全非侵入式的测试实现。
最新内容请见作者的GitHub页：http://qaseven.github.io/