DB2 + DbUnit / http://d.hatena.ne.jp/sonota88/20110629/1309363507

make_table.sql

create table MEMBERS (ID integer primary key not null , NAME varchar(16) not null, LEVEL integer not null)
insert into members values (1, 'ライアン', 1)
insert into members values ((select max(id)+1 from members), 'ホイミン', 1)

Members.java

package dbunitsample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class Memebers {

	private Connection getMyConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        
        return DriverManager.getConnection(
                "jdbc:db2://localhost:50000/SAMPLE" // "SAMPLE" is a database name.
                , "db2inst1" // user name
                , "password" // password
        );
	}


	public void update() throws SQLException, ClassNotFoundException{
    	Connection con = null;

    	try {
    		con = getMyConnection();
        	String sql = "UPDATE MEMBERS SET LEVEL=2 WHERE ID=1";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.executeUpdate();
		} finally {
			if (con != null) {
				con.close();
			}
		}
	}
}

MembersTest.java

package dbunitsample;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.dbunit.Assertion;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.AmbiguousTableNameException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;


public class MembersTest {
	
	private String testDataDir = "test_data";

	
	private Connection getMyConnection() throws ClassNotFoundException, SQLException{
		Class.forName("com.ibm.db2.jcc.DB2Driver");
		
		return DriverManager.getConnection(
				"jdbc:db2://localhost:50000/SAMPLE"
				, "db2inst1"
				, "password"
		);
	}

	
	private IDataSet readDataSetFromXMLFile(String path) throws MalformedURLException, DataSetException{
		return new FlatXmlDataSetBuilder().build(new File(path));
	}

	
	private void escapeTable(String tableName, String path)
			throws DatabaseUnitException, AmbiguousTableNameException,
			IOException, DataSetException, FileNotFoundException,
			ClassNotFoundException, SQLException {
		Connection con = getMyConnection();
		File file = new File(path); // プロジェクトのルートからのパスで作成

		QueryDataSet queryDataSet = new QueryDataSet(new DatabaseConnection(con));
		queryDataSet.addTable(tableName);
		FlatXmlDataSet.write(queryDataSet, new FileOutputStream(file));
	}

	
	@Before
	public void setUp() throws Exception {
		// テーブルのデータを退避
		escapeTable("MEMBERS", testDataDir + "/escaped_members.xml" );
	}


	@After
	public void tearDown() throws Exception {
		IDataSet dataSet = readDataSetFromXMLFile( testDataDir + "/escaped_members.xml");
		Connection con = getMyConnection();
		
		// テーブルのデータをすべて削除して退避したデータを戻す
		DatabaseOperation.CLEAN_INSERT.execute(new DatabaseConnection(con), dataSet);
		
		con.close();
	}


	@Test
	public void testUpdate() throws DatabaseUnitException, SQLException, FileNotFoundException, ClassNotFoundException, MalformedURLException{
		Connection con = null;
		IDatabaseConnection dbcon = null;
		try{
			con = getMyConnection();
			dbcon = new DatabaseConnection(con);

			// テストデータをロード
			IDataSet testDataSet = readDataSetFromXMLFile(testDataDir
					+ "/test_update.xml");

			// テーブルのデータを全て消去＋テストデータ投入
			DatabaseOperation.CLEAN_INSERT.execute(dbcon, testDataSet);

			// 更新処理（テスト対象のメソッド）
			new Memebers().update();

			// 期待値データをロード
			IDataSet expectedDataSet = readDataSetFromXMLFile(testDataDir
					+ "/test_update_expected.xml");
			ITable expectedTable = expectedDataSet.getTable("MEMBERS");

			// DBのテーブルからデータを取得
			IDataSet actualDataSet = dbcon.createDataSet();
			ITable actualTable = actualDataSet.getTable("MEMBERS");

			// 期待値と実際の値を比較
			Assertion.assertEquals(expectedTable, actualTable);
			
		} finally {
			if (dbcon != null) {
				dbcon.close();
			}
			if (con != null) {
				con.close();
			}
		}
	}
}

test_update.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <MEMBERS ID="1" NAME="アリーナ" LEVEL="1"/>
  <MEMBERS ID="2" NAME="クリフト" LEVEL="1"/>
  <MEMBERS ID="3" NAME="ブライ" LEVEL="1"/>
</dataset>

test_update_expected.xml

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <MEMBERS ID="1" NAME="アリーナ" LEVEL="2"/>
  <MEMBERS ID="2" NAME="クリフト" LEVEL="1"/>
  <MEMBERS ID="3" NAME="ブライ" LEVEL="1"/>
</dataset>

escaped_members.xml - file missing here