qileilove

blog已经转移至github,大家请访问 http://qaseven.github.io/

使用DBUnit做单元测试

　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集成的、完全非侵入式的测试实现，等着吧。

posted on 2013-11-25 10:35 顺其自然EVO 阅读(662) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。
网站导航:
 
<2013年11月>
日一二三四五六
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(55)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜