使用DBUnit做单元测试

﹏ヽ暗。殇╰゛Y 2022-09-18 11:45 240阅读 0赞

DBUnit是一个方便的数据准备工具, 方便于我们做单元测试的时候准备数据, 它的数据准备是基于XML格式的, 如下:

<?xml version='1.0' encoding='UTF-8'?>
 <dataset>
 <YourTableName_1 Field_1="1" Field_2="f2" Field_3="f3"/>
 <YourTableName_1 Field_1="2" Field_2="f2_1" Field_3="f3_1"/>
 <YourTableName_2 Field_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. 
 	 * 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: 
 			 * driverClass=oracle.jdbc.OracleDriver 
 			 * db_inst=jdbc:oracle:thin:@1.1.1.1:1521:schema 
 			 * db_user=username 
 			 * db_pwd=password 
 			 */
 			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. 
 	 * 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. 
 	 * 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. 
 			//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集成的、完全非侵入式的测试实现，等着吧。

（基于Spring的已经写好了，看这篇文章了：[http://blog.csdn.net/fenglibing/article/details/16856363][http_blog.csdn.net_fenglibing_article_details_16856363]）

[http_blog.csdn.net_fenglibing_article_details_16856363]: http://blog.csdn.net/fenglibing/article/details/16856363