dbunit: DbUnit is a JUnit extension (also usable with Ant) targeted for database-driven projects
official site, 好久沒更新了, 最新版本2.1還是2004年5月的
1. use ant task
<taskdef classpathref="project.classpath" classname="org.dbunit.ant.DbUnitTask" name="dbunit" />
導出數據
<target name="export">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}" supportbatchstatement="true">
<export format="xml" dest="data/export-data.xml">
<query name="FOO" sql="SELECT COL1, COL2 FROM FOO WHERE COL1=4"/>
<table name="BAR"/>
</export>
</dbunit>
</target>
tip: 不指定query、table， 導出所有的table
導入數據
<target name="clean_insert">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}">
<operation format="xml" src="data/init-data.xml" type="CLEAN_INSERT" />
</dbunit>
</target>
tip:
type: UPDATE, INSERT, DELETE, DELETE_ALL, REFRESH,
MSSQL_INSERT, MSSQL_REFRESH, MSSQL_CLEAN_INSERT.
比較數據
<target name="compare">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}">
<compare format="xml" src="data/init-data.xml" />
</dbunit>
</target>
2. use code
導出數據
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection, schema); // oracle指定schema
IDataSet dataSet = conn.createDataSet();
XmlDataSet.write(dataSet, new FileOutputStream("export-data.xml")); // xml file
FlatXmlDataSet.write(dataSet,new FileOutputStream("export-data.xml")); // flat xml file
XlsDataSet.write(dataSet,new FileOutputStream("export-data.xls")); // xls file
FlatDtdDataSet.write(dataSet,new FileOutputStream("export-data.dtd")); // dtd file
CsvDataSetWriter.write(dataSet, new File("export-data-csv")); // csv file
使用DatabaseSequenceFilter, 解決違反外鍵約束的問題
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection);
ITableFilter filter = new DatabaseSequenceFilter(conn);
// ITableFilter filter = new DatabaseSequenceFilter(conn, tableNames);
IDataSet dataset = new FilteredDataSet(filter, conn.createDataSet());
XmlDataSet.write(dataset, new FileOutputStream("export-data.xml"));
導入數據
DatabaseOperation.REFRESH.execute(conn, dataSet);
DatabaseOperation.INSERT.execute(conn, dataSet);
刪除數據
DatabaseOperation.DELETE.execute(conn, dataSet);
比較數據
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection);
Compare compare = new Compare();
compare.setFormat("xml");
compare.setSrc(new File("export-data.xml"));
compare.execute(conn);
tip: 使用assert進行比較, 作用不大
code: Assertion.assertEquals(expectedTable, actualTable);
3. QueryDataSet: use sql
QueryDataSet queryDataSet = new QueryDataSet(conn);
queryDataSet.addTable("orders", ordersQuerySQL);