亚洲精品在线视频观看,亚洲今日精彩视频,亚洲成A∨人片在线观看不卡

使用DBUnit做單元測試

　DBUnit是一個方便的數據準備工具, 方便于我們做單元測試的時候準備數據, 它的數據準備是基于XML格式的, 如下:

<?xmlversion='1.0'encoding='UTF-8'?>

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

<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文件，也應該是根據關鍵字段排好序的結果的，否則也會因為記錄的順序問題而比較失敗。

/**

* 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測試的抽象類：

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

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

<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集成的、完全非侵入式的測試實現，等著吧。

posted on 2013-11-25 10:35 順其自然EVO 閱讀(678) 評論(0) 編輯收藏所屬分類: 數據庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: MySQL數字輔助表 PLSQL Developer鮮為人知的快捷鍵淘寶分布式配置管理服務Diamond iOS開發之FMDB AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移 SQL Server 2008 (R2) 單機版安裝的先決條件 AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉移聊聊Oracle外鍵約束的幾個操作選項 SQL優化中索引列使用函數之靈異事件利用binlog進行數據庫的還原

qileilove

使用DBUnit做單元測試

導航

統計

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜