使用DBUnit做單元測試

<?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>

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'?>

<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"/>

　　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) {

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 {

* 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 sql

protected void execsql(idatabaseconnection iconn, string sql) throws exception {

connection con = iconn.getconnection();

statement stmt = con.createstatement();

stmt.execute(sql);

if (stmt != null) {

stmt.close();

* get idatabaseconnection connection

* @return

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 expecteddataset

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

* convert the data in the itable to list

* @param table

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

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.

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>

public void exportdata(connection conn, list<string> tablenamelist, string resultfile) throws sqlexception, databaseunitexception, filenotfoundexception, ioexception {

if (conn == null) {

idatabaseconnection iconn = new databaseconnection(conn);

exportdata(iconn, tablenamelist, resultfile);

這個抽象類裡面有實用插入資料、導出資料及驗證資料的實作，也包括了資料庫連接配接的準備，該類裡面包含了一個抽象方法preparedata，因為任何使用dbunit做單元測試的，應該是少不了資料準備這麼一個過程，否則就隻能夠使用資料庫中的現成資料，這樣的單元測試是不靠譜的，因為資料庫中的資料随時可能發生變化，這裡的抽象方法preparedata就相當于在提醒寫單元測試的人，不要忘了準備單元測試要用的資料。

　　根據上面的思路，準備一個用于測試的table：

create table youtablename_1(

filed_1 int,

filed_2 varchar2(50),

filed_3 varchar2(50)

)

　　用于測試的資料：

<youtablename_1 filed_1="1" filed_2="f2" filed_3="f3"/>

<youtablename_1 filed_1="2" filed_2="f2_1" filed_3="f3_1"/>

　　用于驗證測試結果的資料：

<youtablename_1 filed_1="1" filed_2="a" filed_3="a1"/>

<youtablename_1 filed_1="2" filed_2="b" filed_3="b1"/>

　　我們這個測試非常簡單，就是把filed_1為1的字段中filed_2和filed_3的字段的值分别設為"a"和"a1"，把filed_1為2的字段中filed_2和filed_3的字段的值分别設為"b"和"b1"，做完測試後，資料庫中是不會插入我們的單元測試的資料的。

　　下面這個類updatetest用于更新資料：

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.lang.reflect.field;

import java.lang.reflect.method;

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;

//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) {

assert.asserttrue(result);

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"));

　　好了，示例完了，非常的簡單，也非常的清晰，不過美中不足就是和dbunit的代碼耦合度太高了，這過對于我們使用習慣了spring的人來說，看起來是非常别扭的，後面我會寫另外一個與spring內建的、完全非侵入式的測試實作。

最新内容請見作者的github頁：http://qaseven.github.io/