使用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/