DbUnitとH2 Databaseでデータベースのユニットテスト　はじめの一歩（環境構築、初期値データのセット、XML/Excel/CSV テーブルの比較）

# はじめに
データベースを使ったJavaアプリケーションのユニットテストが難しい理由として以下の３つがあげられると思います。
1. データベースのセットアップの問題
2. テストコードやテストデータのメンテナンス性の問題
3. テストに時間がかかる問題

これらの問題に対応するため、DbUnit（[リンク](http://dbunit.sourceforge.net/)）とH2 Database（[リンク](http://www.h2database.com/html/main.html)）を使ってユニットテストをしてみます。
DbUnitは、依存しているライブラリが多いため、Gradleを使って環境構築していきます。

## DBUnit
+ DBUnitは、JUnitのデータベース用の拡張モジュールです。  
+ xmlファイルに書いたデータベースの状態をセットアップします。
+ テスト完了時のデータベースの状態を、データベースの状態を記載したxmlファイルと比較します。
+ ファイルフォーマットは、Excelもサポートしています。

## H2 Database
+ ピュアJavaのSQLデータベースです。
+ データベースは、インメモリ形式とファイル形式で作成することが可能です。
+ 組み込みモード、サーバーモード、ミックスモードで動作します。
    - **組み込みモード**  
        ![connection-mode-embedded-2.png](https://qiita-image-store.s3.amazonaws.com/0/107056/09704719-d25e-2b84-2427-860295c2fb57.png)
    - **サーバーモード**  
        ![connection-mode-remote-2.png](https://qiita-image-store.s3.amazonaws.com/0/107056/4a9daf16-6644-e34e-ffd6-64f02114cb44.png)
    - **ミックスモード**  
        ![connection-mode-mixed-2.png](https://qiita-image-store.s3.amazonaws.com/0/107056/bddd5b86-05c4-719f-c634-359f56197b35.png)
+ ブラウザやコンソールからデータベースを操作することができます。
+ jarファイル１つ（1.5MB）で動作します。
+ JDBCとODBCの両方をサポートしています。

# Gradle
+ コンパイル、テストを実行するビルドツールです。
+ EclipseやAndroid Studioに付属しています。
+ Maven同様にライブラリーの依存関係を自動的に解決してくれます。
+ 規約に従った構成にしておけば、コンパイル、テスト、ドキュメント生成などがデフォルトのまま可能です。
+ ビルドファイルにGroovyスクリプトを使えるため、ビルドファイルを簡潔に書けます。

# テスト実行環境のセットアップ
## Eclipse、JDK
今回は、以下の環境で動作確認しました。

+ Eclipse 4.6 Neon Pleiades All in One
+ Java SE Development Kit 8u102
+ Windows 10

EclipseとJDKのインストールがまだの方はこちらから。

+ Pleiades - Eclipse：[リンク](http://mergedoc.osdn.jp/)
+ Oracle Java SE Development Kit：[リンク](http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html)

コマンドラインで、Javaがインストールされているかどうか、確認します。

> $ java -version
> java version "1.8.0_102"
> Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
> Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

## Gradleを使ってEclipseプロジェクト作成
1. **Eclipse - Gradle プロジェクトを作成**
    Eclipseを起動 → ファイル(F) → 新規(N) → Gradle プロジェクト → 
    
    ![Eclipse_s1.png](https://qiita-image-store.s3.amazonaws.com/0/107056/fb6dd625-a952-426e-75d1-cfd24f46326b.png)
    
    → 新規 Gradle プロジェクト → プロジェクト名、ロケーションを指定 → 次へ(N)> → 
    
    ![Eclipse_s2.png](https://qiita-image-store.s3.amazonaws.com/0/107056/6778839e-9b9f-5b8e-42ee-20210ddd98b0.png)

    → 次へ(N)> →
    
    ![Eclipse_s3.png](https://qiita-image-store.s3.amazonaws.com/0/107056/6aea6bee-b007-4790-c5b4-e9a8520f3207.png)

    → Gradle ディストリビューション：◎ Gradle ラッパー(推奨) → 次へ(N)> →
    
    ![Eclipse_s4.png](https://qiita-image-store.s3.amazonaws.com/0/107056/a10edebd-992e-41a0-71e3-a8f031095867.png)
    
    → 完了(F)
    　
    
2. **Gradleラッパーの設定**
    Eclipseにデフォルトで同梱されているGradleのバージョンは少し古いため更新してあげます。
    
    ![Eclipse_s5.png](https://qiita-image-store.s3.amazonaws.com/0/107056/e97c19e2-3820-ae94-2373-a7b5d679184f.png)
    
    Sampleプロジェクト → gradle/wrapper/gradle-wrapper.properties

    （変更前）
    
    > distributionUrl=https\://services.gradle.org/distributions/gradle-2.13-bin.zip
    
    （変更後）
    
    > distributionUrl=https\://services.gradle.org/distributions/gradle-3.1-bin.zip
    
    Gradleラッパーを動作させたときに、指定したバージョンが自動的に適用されます。
    　
3. **build.gradleの設定**
    プロジェクト内にデフォルトで生成されたbuild.gradleに対し、次の設定を追加しました。
    　  
    + eclipseプラグイン
    + h2への依存関係
    + slf4j-nopへの依存関係
    + dbunitへの依存関係
    + Javaコンパイラーに対してエンコードUTF-8を指定
    
    コメント抜きでbuild.gradleは、このようになります。
    
    ```build.gradle
    apply plugin: 'java'
    apply plugin: 'eclipse'

    repositories {
        jcenter()
    }

    dependencies {
        compile 'org.slf4j:slf4j-api:1.7.21'
    	compile group: 'com.h2database', name: 'h2', version: '1.4.192'

        compile group: 'org.slf4j', name: 'slf4j-nop', version: '1.7.21'
        compile group: 'org.dbunit', name: 'dbunit', version: '2.5.3'
        testCompile 'junit:junit:4.12'
    }

    tasks.withType(JavaCompile) {
        options.encoding = 'UTF-8'
    }
    ```
    
    ここでEclipseを 再起動（終了 → 起動） します。
    build.gradleに追加したeclipseプラグインの情報をEclipseが認識します。
    　
    ![Eclipse_s9.png](https://qiita-image-store.s3.amazonaws.com/0/107056/ec637d11-830f-559b-8606-a6dd6e8302b9.png)
    　  
4. **プロジェクトに必要なjarファイルを取得**
    + gradleのeclipseタスクでプロジェクトに必要なjarファイルを構築します。  
        　Eclipse → Gradleタスク タブ → プロジェクトを選択 → ide → eclipse をクリック  
        　eclipseタスクが実行されます。
        　　![Eclipse_s9_2.png](https://qiita-image-store.s3.amazonaws.com/0/107056/1bd5ff40-2f18-1cc5-2a15-8f022a35f30a.png)  
        　
    + プロジェクトのビルド・パスの状態を確認します。
        Eclipse → パッケージ・エクスプローラー → プロジェクトを右クリック → ビルド・パス(B) → ビルド・パスの構成(C)... → 
        ![Eclipse_s7.png](https://qiita-image-store.s3.amazonaws.com/0/107056/25ddbc04-6a05-0adc-c489-25296e9bf8e6.png)
        
        → 順序およびエクスポート(O) → 
        
        ![Eclipse_s8.png](https://qiita-image-store.s3.amazonaws.com/0/107056/8f0267bf-0183-5ef7-28a3-f51cee39c984.png)
        
        プロジェクトに必要なファイルが自動的にダウンロードされ、.gradleディレクトリ内に格納されました。
        ※ 今回動作確認した DBUnit ver.2.5.3 は、 POI ver.3.14 と動作するようになっていました。

# プログラム
## 概要説明
### データベースとの接続
　データベースのユニットテスト時は、H2 Databaseをインメモリモードで動作させます。インメモリモードはデータベース接続のURLで指定します。

|Topic|URL Examples|
|:---|:---|
|In-memory (private)|jdbc:h2:mem:|
|In-memory (named)|jdbc:h2:mem:\<databaseName\>|

　H2 Databaseのインメモリモードのデフォルトの挙動は、データベースとの接続が切れた時点で、データベースが失われます。テスト中、接続が切れてもデータベースが失われないようにするため、データベースURLに`;DB_CLOSE_DELAY=-1`を指定してあげます。これによりテスト実行のためにJavaVMが動作している間は、データベースが存在しつづけます。

```
private static final String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
```

ソースコード（Person.java, PersonRepository.java, PersonRepositoryTest.java）およびデータベースのデータ（setup_dataset.xml, schema.sql）は次のように配置します。
　　　　　![eclipse_01.png](https://qiita-image-store.s3.amazonaws.com/0/107056/1150e387-bf93-8017-e707-f58b5e1fa0d3.png)

### スキーマの構築
@BeforeClassアノテーションのところで、schema.sqlを実行してテーブルを作成します。
org.h2.tools.RunScript#execute()でsqlを実行することができます。

```execute_sql.java
RunScript.execute(JDBC_URL, USER, PASSWORD, "data/schema.sql", UTF8, false);
```

### データのセットアップ
まず、org.dbunit.dataset.IDataSetにXMLファイルをセットします。
XMLファイルの読み込みは、org.dbunit.dataset.xml.FlatXmlDataSetBuilder.FlatXmlDataSetBuilder().build()で行います。

```read_xml.java
return new FlatXmlDataSetBuilder().build(new File("data/setup_dataset.xml"));
```

続いて、org.dbunit.IDatabaseTesterでデータベースのセットアップをします。setSetUpOperation()に、`DatabaseOperation.CLEAN_INSERT`を指定しました。`DatabaseOperation.CLEAN_INSERT`は、データセットに現れるテーブルの、すべてのデータを削除してからデータセットのデータをセットします。setDataSet()で対象データを指定し、onSetup()でセットアップを実行します。

```set_dataset.java
		IDatabaseTester databaseTester = new JdbcDatabaseTester(JDBC_DRIVER, JDBC_URL, USER, PASSWORD);
		databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
		databaseTester.setDataSet(dataSet);
		databaseTester.onSetup();
```

## ER図
![ER.png](https://qiita-image-store.s3.amazonaws.com/0/107056/35c516e2-50b6-d15c-6a33-d056ad9ca066.png)

personsテーブルのgender_idにgendersテーブルのidを外部キー制約として設定します。

## ソースコード
ソースコード、データベースのデータです。

**プロダクションコード**

```Person.java
package databasetest;

public class Person {

	private final String firstName;
	private final String lastName;
	private final int age;
	private final String genderName;

	public Person(String firstName, String lastName, int age, String genderName) {
		this.firstName = firstName;
		this.lastName = lastName;
		this.age = age;
		this.genderName = genderName;
	}

	public String getFirstName() {
		return firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public int getAge() {
		return age;
	}

	public String getGenderName() {
		return genderName;
	}
}
```

```PersonDao.java
package databasetest;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

public class PersonDao {

	private final DataSource dataSource;

	public PersonDao(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public Person findPersonByFirstName(String name) throws SQLException {
		Person person = null;
		PreparedStatement statement = dataSource.getConnection()
				.prepareStatement("SELECT first_name, last_name, age, gender_name FROM persons, genders WHERE gender_id = genders.id AND first_name = ?");
		statement.setString(1, name);
		ResultSet resultSet = null;
		try {
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				person = convertSingleRow(resultSet);
			}
		} finally {
			closeQuietly(resultSet);
			closeQuietly(statement);
		}
		return person;
	}

	private Person convertSingleRow(ResultSet resultSet) throws SQLException {
		String firstName = resultSet.getString("first_name");
		String lastName = resultSet.getString("last_name");
		int age = resultSet.getInt("age");
		String genderName = resultSet.getString("gender_name");
		return new Person(firstName, lastName, age, genderName);
	}

	private void closeQuietly(ResultSet resultSet) {
		try {
			resultSet.close();
		} catch (SQLException exception) {
		}
	}

	private void closeQuietly(Statement statement) {
		try {
			statement.close();
		} catch (SQLException exception) {
		}
	}

}
```

**テストコード**

```PersonDaoTest.java
package databasetest;

import static org.h2.engine.Constants.*;
import static org.hamcrest.CoreMatchers.*;
import static org.hamcrest.MatcherAssert.*;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;

import javax.sql.DataSource;

import org.dbunit.Assertion;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.excel.XlsDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.h2.jdbcx.JdbcDataSource;
import org.h2.tools.RunScript;
import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;

public class PersonDaoTest {

	private static final String JDBC_DRIVER = org.h2.Driver.class.getName();
	private static final String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
	private static final String USER = "sa";
	private static final String PASSWORD = "";

	@BeforeClass
	public static void createSchema() throws Exception {
		RunScript.execute(JDBC_URL, USER, PASSWORD, "data/schema.sql", UTF8, false);
	}

	private IDataSet readDataSet(String dataPath) throws Exception {
		// for XML
		return new FlatXmlDataSetBuilder().build(new File(dataPath));
		// for Excel
		// return new XlsDataSet(new File(dataPath));
	}

	private void cleanlyInsert(IDataSet dataSet) throws Exception {
		IDatabaseTester databaseTester = new JdbcDatabaseTester(JDBC_DRIVER, JDBC_URL, USER, PASSWORD);
		databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
		databaseTester.setDataSet(dataSet);
		databaseTester.onSetup();
	}

	@Test
	public void 存在する人を検索するテスト() throws Exception {
		// Arrange
		IDataSet dataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(dataSet);
		PersonDao dao = new PersonDao(dataSource());

		// Action
		Person person = dao.findPersonByFirstName("Theresa");

		// Assert
		assertThat(person.getFirstName(), is("Theresa"));
		assertThat(person.getLastName(), is("May"));
		assertThat(person.getAge(), is(43));
		assertThat(person.getGenderName(), is("Female"));
	}

	@Test
	public void 存在しない人を検索するテスト() throws Exception {
		// Arrange
		IDataSet dataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(dataSet);
		PersonDao dao = new PersonDao(dataSource());

		// Action
		Person person = dao.findPersonByFirstName("I don't exist");

		// Assert
		assertThat(person, is(nullValue()));
	}

	@Test
	public void テーブルのデータを比較するテスト() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(setupDataSet);

		// Action
		Connection conn = dataSource().getConnection();
		DatabaseConnection dbConn = new DatabaseConnection(conn);
		IDataSet databaseDataSet = dbConn.createDataSet();
		ITable actualTable = databaseDataSet.getTable("persons");

		// Assert
		IDataSet expectedDataSet = readDataSet("data/expected_dataset_table.xml");
		ITable expectedTable = expectedDataSet.getTable("persons");

		Assertion.assertEquals(expectedTable, actualTable);
		//assertThat(actualTable, is(expectedTable));
	}

	@Test
	public void ビューと比較するテスト() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(setupDataSet);

		// Action


		// Assert
		IDataSet expectedDataSet = readDataSet("data/expected_dataset_view.xml");
		ITable expectedTable = expectedDataSet.getTable("persons_view");
		Connection conn = dataSource().getConnection();
		DatabaseConnection dbConn = new DatabaseConnection(conn);
		String tableName = "persons";
		String sqlQuery = "SELECT first_name, last_name, age FROM persons ORDER BY age";
		String[] ignoreCols = new String[0];
		Assertion.assertEqualsByQuery(expectedTable, dbConn, tableName, sqlQuery, ignoreCols);
	}

	private DataSource dataSource() {
		JdbcDataSource dataSource = new JdbcDataSource();
		dataSource.setURL(JDBC_URL);
		dataSource.setUser(USER);
		dataSource.setPassword(PASSWORD);
		return dataSource;
	}

}
```

**データベース スキーマ**

```schema.sql
CREATE TABLE IF NOT EXISTS genders (
	id INT IDENTITY PRIMARY KEY,
	gender_name VARCHAR,
);

CREATE TABLE IF NOT EXISTS persons (
	id INT IDENTITY PRIMARY KEY,
	first_name VARCHAR,
	last_name VARCHAR,
	age  INT,
	gender_id INT,
	FOREIGN KEY (gender_id) REFERENCES genders(id),
);
```

**テストデータ**

```setup_dataset.xml
<dataset>
  <genders id="1" gender_name="Male"/>
  <genders id="2" gender_name="Female"/>
  <genders id="3" gender_name="Trans Male"/>
  <genders id="4" gender_name="Trans Female"/>
  <genders id="5" gender_name="Cross Dressing"/>
  <persons first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <persons first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <persons first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <persons first_name="Matsuko" last_name="Deluxe" age="45" gender_id="1"/>
</dataset>
```

```expected_dataset_table.xml
<dataset>
  <persons id="1" first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <persons id="2" first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <persons id="3" first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <persons id="4" first_name="Matsuko" last_name="Deluxe" age="45" gender_id="1"/>
</dataset>
```

```expected_dataset_view.xml
<dataset>
  <persons_view first_name="Theresa" last_name="May" age="43"/>
  <persons_view first_name="Matsuko" last_name="Deluxe" age="45"/>
  <persons_view first_name="Vladimir" last_name="Putin" age="52"/>
  <persons_view first_name="Barack" last_name="Obama" age="58"/>
</dataset>
```

セットアップ用のデータは、初期値が設定されているカラム、PRIMARY KEYが設定されているカラム、AUTOINCREMENTが設定されているカラムは省略することができます。
例えば、personsテーブルのidはPRIMARY KEY制約が設定されていて、自動で数値が格納されるため、設定ファイルから省略することができます。
PRIMARY KEYとAUTOINCREMENTで自動に設定される値は、テストの際は注意が必要です。
それぞれ、次のような動作になります。

+ PRIMARY KEYのみ指定
    対象のカラムに現在格納されている最大の値に１が加えられる
+ PRIMARY KEYとAUTOINCREMENTを指定
    対象のカラムに今までに格納されたことのある最大の値に１が加えられる

AUTOINCREMENTを指定したカラムは、テストを実行するごとに値が変わってしまうため、期待値に含まれないようにテストを設計します。

# テスト実行
### テスト成功
参照系のテストを４件実施します。

+ 存在する人を検索するテスト
+ 存在しない人を検索するテスト
+ テーブルのデータを比較するテスト
+ ビューと比較するテスト

![Run_JUnit.png](https://qiita-image-store.s3.amazonaws.com/0/107056/25e98816-b7f5-ca4b-c2bf-88be93e3ebb7.png)

４件のテストが成功しました。

### テスト失敗
テストが失敗するときは、どのようなメッセージがでるのかも見ておきます。
テストの期待値をMatsuko Deluxeさんのgender_idを5に変更してテストを実行してみます。

```expected_dataset_table.xml
<dataset>
  <persons id="1" first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <persons id="2" first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <persons id="3" first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <persons id="4" first_name="Matsuko" last_name="Deluxe" age="45" gender_id="5"/>
</dataset>
```

**テスト失敗時のメッセージ**

```
junit.framework.ComparisonFailure: value (table=persons, row=3, col=gender_id) expected:<[5]> but was:<[1]>
	at org.dbunit.assertion.JUnitFailureFactory.createFailure(JUnitFailureFactory.java:39)
	at org.dbunit.assertion.DefaultFailureHandler.createFailure(DefaultFailureHandler.java:97)
	at org.dbunit.assertion.DefaultFailureHandler.handle(DefaultFailureHandler.java:223)
	at org.dbunit.assertion.DbUnitAssert.compareData(DbUnitAssert.java:524)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:409)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:312)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:274)
	at org.dbunit.Assertion.assertEquals(Assertion.java:122)
	at databasetest.PersonRepositoryTest.updateGenderByFirstNameTest(PersonRepositoryTest.java:98)
...
```

table, row, colに対して、期待値と実際の値を出力してくれています。

# ビューとの比較
自動で値が入るカラムがある場合の比較について考えてみます。
personsテーブルのidは自動で値がセットされるため、期待値データが準備できません。
idを省略した期待値データを使ってテストしてみます。

```expected_dataset_view.xml
<dataset>
  <persons_view first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <persons_view first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <persons_view first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <persons_view first_name="Matsuko" last_name="Deluxe" age="45" gender_id="1"/>
</dataset>
```

**カラム数の違いで発生するjunit.framework.ComparisonFailure**

```
junit.framework.ComparisonFailure: column count (table=persons, expectedColCount=4, actualColCount=5) expected:<[[age, first_name, gender_id, last_name]]> but was:<[[AGE, FIRST_NAME, GENDER_ID, ID, LAST_NAME]]>
	at org.dbunit.assertion.JUnitFailureFactory.createFailure(JUnitFailureFactory.java:39)
	at org.dbunit.assertion.DefaultFailureHandler.createFailure(DefaultFailureHandler.java:97)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:396)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:312)
	at org.dbunit.assertion.DbUnitAssert.assertEquals(DbUnitAssert.java:274)
	at org.dbunit.Assertion.assertEquals(Assertion.java:122)
	at databasetest.PersonRepositoryTest.updateGenderByFirstNameTest(PersonRepositoryTest.java:96)
...
```

テーブル同士を直接比較するテストケースの期待値として用意するテーブルのデータはフルセットで準備する必要があるため、personsテーブルのidを省略するとカラム数の違いが原因でテストが失敗します。

期待値データのほうもidを省略したい場合は、`assertEqualsByQuery()`メソッドで「ビューと比較するテスト」をします。

```assert_view.java
	@Test
	public void ビューと比較するテスト() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(setupDataSet);

		// Action


		// Assert
		IDataSet expectedDataSet = readDataSet("data/expected_dataset_view.xml");
		ITable expectedTable = expectedDataSet.getTable("persons");
		Connection conn = dataSource().getConnection();
		DatabaseConnection dbConn = new DatabaseConnection(conn);
		String tableName = "persons";
		String sqlQuery = "SELECT first_name, last_name, age FROM persons ORDER BY age";
		String[] ignoreCols = new String[0];
		Assertion.assertEqualsByQuery(expectedTable, dbConn, tableName, sqlQuery, ignoreCols);
	}
```

**assertEqualsByQuery**
SQLクエリで作成したテーブルと比較するアサーションです。

```assertEqualsByQuery.java
public void assertEqualsByQuery(ITable expectedTable,
                       IDatabaseConnection connection,
                       String tableName,
                       String sqlQuery,
                       String[] ignoreCols)
                         throws DatabaseUnitException,
                                SQLException
```

+ パラメータ
    - expectedTable : 期待値のテーブルのデータ
    - connection : SQLを発行するためのDBコネクション
    - tableName : データベースのクエリ対象のテーブル（※ 今回は適当な文字列でもテストが成功します。）
    - sqlQuery : 比較対象のテーブルを作成するSQLクエリ
    - ignoreCols : 比較対象にしないカラム

# 外部キー制約
テストデータのdatasetは、上から順に実行されます。
外部キー制約を設定した場合、その制約を満たす順にテスト定義しておく必要があります。

今回のサンプルでは、personテーブルのgender_idに外部キー制約を設定しています。
datasetの定義順を逆にしてテストを実行してみます。

**テストデータ**

```setup_dataset.xml
<dataset>
  <person first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <person first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <person first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <person first_name="Matsuko" last_name="Deluxe" age="45" gender_id="1"/>
  <gender id="1" gender_name="Male"/>
  <gender id="2" gender_name="Female"/>
  <gender id="3" gender_name="Trans Male"/>
  <gender id="4" gender_name="Trans Female"/>
  <gender id="5" gender_name="Cross Dressing"/>
</dataset>
```

**実行結果**
参照整合性制約違反が発生します。

```
Caused by: org.h2.jdbc.JdbcSQLException: 参照整合性制約違反: "CONSTRAINT_8C: PUBLIC.PERSON FOREIGN KEY(GENDER_ID) REFERENCES PUBLIC.GENDER(ID) (1)"
Referential integrity constraint violation: "CONSTRAINT_8C: PUBLIC.PERSON FOREIGN KEY(GENDER_ID) REFERENCES PUBLIC.GENDER(ID) (1)"; SQL statement:
insert into PERSON (FIRST_NAME, LAST_NAME, AGE, GENDER_ID) values (?, ?, ?, ?) [23506-192]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:372)
	at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:314)
	at org.h2.table.Table.fireConstraints(Table.java:967)
	at org.h2.table.Table.fireAfterRow(Table.java:985)
	at org.h2.command.dml.Insert.insertRows(Insert.java:161)
	at org.h2.command.dml.Insert.update(Insert.java:114)
	at org.h2.command.CommandContainer.update(CommandContainer.java:98)
	at org.h2.command.Command.executeUpdate(Command.java:258)
	at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
	at org.dbunit.database.statement.SimplePreparedStatement.addBatch(SimplePreparedStatement.java:80)
	at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addBatch(AutomaticPreparedBatchStatement.java:70)
	at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)
	... 29 more
```

テスト用のスキーマ作成も順番を考慮する必要があります。
テーブルの作成順序を逆にしてみます。

**スキーマ定義**

```schema.sql
CREATE TABLE IF NOT EXISTS person (
	id INT IDENTITY PRIMARY KEY,
	first_name VARCHAR,
	last_name VARCHAR,
	age  INT,
	gender_id INT,
	FOREIGN KEY (gender_id) REFERENCES gender(id),
);

CREATE TABLE IF NOT EXISTS gender (
	id INT IDENTITY PRIMARY KEY,
	gender_name VARCHAR,
);
```

**実行結果**
外部キー制約を設定しようとしたときに"GENDER"テーブルが見つからず`SQLException`が発生します。

```
org.h2.jdbc.JdbcSQLException: テーブル "GENDER" が見つかりません
Table "GENDER" not found; SQL statement:
CREATE TABLE IF NOT EXISTS person (
	id INT IDENTITY PRIMARY KEY,
	first_name VARCHAR,
	last_name VARCHAR,
	age  INT,
	gender_id INT,
	FOREIGN KEY (gender_id) REFERENCES gender(id),
) [42102-192]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.schema.Schema.getTableOrView(Schema.java:436)
	at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:201)
	at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:77)
	at org.h2.command.ddl.CreateTable.update(CreateTable.java:176)
	at org.h2.command.CommandContainer.update(CommandContainer.java:98)
	at org.h2.command.Command.executeUpdate(Command.java:258)
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184)
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
	at org.h2.tools.RunScript.process(RunScript.java:260)
	at org.h2.tools.RunScript.process(RunScript.java:190)
	at org.h2.tools.RunScript.process(RunScript.java:328)
	at org.h2.tools.RunScript.execute(RunScript.java:303)
	at databasetest.PersonRepositoryTest.createSchema(PersonRepositoryTest.java:31)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at 
...
```

# org.dbunit.Assertion
DBUnitでテーブル比較をするときに`org.hamcrest.MatcherAssert.assertThat`を使うと、テーブルのデータが一致していても、`java.lang.AssertionError`が発生します。DBUnitの`org.dbunit.Assertion.assertEquals`を使って比較してあげる必要があります。

**assertThatを使うと発生するAssertionError**

```
java.lang.AssertionError: 
Expected: is <org.dbunit.dataset.DefaultTable[_metaData=tableName=persons, columns=[(id, UNKNOWN, nullableUnknown), (first_name, UNKNOWN, nullableUnknown), (last_name, UNKNOWN, nullableUnknown), (age, UNKNOWN, nullableUnknown), (gender_id, UNKNOWN, nullableUnknown)], keys=[], _rowList.size()=4]>
     but: was <org.dbunit.database.CachedResultSetTable[_metaData=table=PERSONS, cols=[(ID, INTEGER, noNulls), (FIRST_NAME, VARCHAR, nullable), (LAST_NAME, VARCHAR, nullable), (AGE, INTEGER, nullable), (GENDER_ID, INTEGER, nullable)], pk=[(ID, INTEGER, noNulls)], _rowList.size()=4]>
	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:8)
	at databasetest.PersonRepositoryTest.updateGenderByFirstNameTest(PersonRepositoryTest.java:98)
...
```

DBUnitには、IDataSet、ITableに対して、単純な比較、SQLクエリを使った比較、カラムを絞った比較などの Assertion（[リンク](http://dbunit.sourceforge.net/apidocs/org/dbunit/assertion/DbUnitAssert.html)）用意されています。IDataSet、ITableに対する検証は、用途に適したDBUnitのAssertionメソッドを使います。

|■ DBUnitのAssertメソッド一覧|
|:------------------------|
|assertEquals(IDataSet expectedDataSet, IDataSet actualDataSet)|
|assertEquals(IDataSet expectedDataSet, IDataSet actualDataSet, FailureHandler failureHandler)|
|assertEquals(ITable expectedTable, ITable actualTable)|
|assertEquals(ITable expectedTable, ITable actualTable, Column[] additionalColumnInfo)|
|assertEquals(ITable expectedTable, ITable actualTable, FailureHandler failureHandler)|
|assertEqualsByQuery(IDataSet expectedDataset, IDatabaseConnection connection, String sqlQuery, String tableName, String[] ignoreCols)|
|assertEqualsByQuery(ITable expectedTable, IDatabaseConnection connection, String tableName, String sqlQuery, String[] ignoreCols)|
|assertEqualsIgnoreCols(IDataSet expectedDataset, IDataSet actualDataset, String tableName, String[] ignoreCols)|
|assertEqualsIgnoreCols(ITable expectedTable, ITable actualTable, String[] ignoreCols)|

# Excelデータのインポート＆エクスポート
DBUnitは、Excelシートのデータをインポート、エクスポートすることができます。

テーブルデータをシート単位に記載します。
シートの１行目はカラム名です。

**PERSONSテーブル**
![PERSONS.png](https://qiita-image-store.s3.amazonaws.com/0/107056/19c1cc48-7349-17bb-5d84-2b363902309e.png)

**GENDERSテーブル**
![GENDERS_Sheet.png](https://qiita-image-store.s3.amazonaws.com/0/107056/f6098569-39c9-7f1c-8f7c-34576c1247eb.png)

**インポートしたxmlデータ**

```flatxml_import.xml
<dataset>
  <genders id="1" gender_name="Male"/>
  <genders id="2" gender_name="Female"/>
  <genders id="3" gender_name="Trans Male"/>
  <genders id="4" gender_name="Trans Female"/>
  <genders id="5" gender_name="Cross Dressing"/>
  <persons id="1" first_name="Barack" last_name="Obama" age="58" gender_id="1"/>
  <persons id="2" first_name="Theresa" last_name="May" age="43" gender_id="2"/>
  <persons id="3" first_name="Vladimir" last_name="Putin" age="52" gender_id="1"/>
  <persons id="4" first_name="Matsuko" last_name="Deluxe" age="45" gender_id="1"/>
</dataset>
```

**Excelデータのインポート**

```excel_import.java
	@Test
	public void Excelからデータをインポートする() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/flatxml_import.xml");
		cleanlyInsert(setupDataSet);

		// Action
		IDataSet xlsDataSet = new XlsDataSet(new File("data/xls_export.xls"));

		// Assert
		Assertion.assertEquals(xlsDataSet, setupDataSet);
	}
```

**Excelにデータをエクスポート**

```excel_export.java
	@Test
	public void Excelにデータをエクスポートする() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(setupDataSet);

		// Action
		Connection conn = dataSource().getConnection();
		DatabaseConnection dbConn = new DatabaseConnection(conn);
		IDataSet actualDataSet = dbConn.createDataSet();

		XlsDataSet.write(actualDataSet, new FileOutputStream("data/xls_export.xls"));

		// Assert
	}
```

# CSVデータのインポート＆エクスポート
DBUnitは、CSV形式のテキストデータをインポート、エクスポートすることができます。
CSVデータを読み込むCsvDataSetクラス、CSVデータを出力するCsVDataSetWriterクラスには、テーブル単位に作成したCSVファイルとその読み込み順序を記載したtable-ordering.txtのあるディレクトリを指定します。CSVファイルは、１行目がカラム名、２行目以降がレコードになります。

```table-ordering.txt
GENDERS
PERSONS
```

```GENDERS.csv
ID, GENDER_NAME
"1","Male"
"2","Female"
"3","Trans Male"
"4","Trans Female"
"5","Cross Dressing"
```

```PERSONS.csv
ID, FIRST_NAME, LAST_NAME, AGE, GENDER_ID
"1","Barack","Obama","58","1"
"2","Theresa","May","43","2"
"3","Vladimir","Putin","52","1"
"4","Matsuko","Deluxe","45","1"
```

```write_csv.java
	@Test
	public void CSVにデータをエクスポートする() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/setup_dataset.xml");
		cleanlyInsert(setupDataSet);

		// Action
		Connection conn = dataSource().getConnection();
		DatabaseConnection dbConn = new DatabaseConnection(conn);
		IDataSet actualDataSet = dbConn.createDataSet();

		CsvDataSetWriter.write(actualDataSet, new File("data"));

		// Assert
	}
```

```read_csv.java
	@Test
	public void CSVからデータをインポートする() throws Exception {
		// Arrange
		IDataSet setupDataSet = readDataSet("data/flatxml_import.xml");
		cleanlyInsert(setupDataSet);

		// Action
		IDataSet csvDataSet = new CsvDataSet(new File("data"));

		// Assert
		Assertion.assertEquals(csvDataSet, setupDataSet);
	}
```

# おわりに
みなさんは、データベースにアクセスするコードを自動テストしていますでしょうか？
また、その自動テストは、高速かつ安定的に実施できていますでしょうか？

データベースと結合して行うユニットテストでは、テスト前にデータベースを特定の状態に自動的にセットアップする必要があります。

この手順をおろそかにすると、テスト結果が実行のたびにと異なってしまったり、試験の準備に時間が膨大にかかってしまったりするためこの部分はDbUnitをつかってしっかりとやっていきたい部分になります。

また、データベースのスキーマの変更、例えば、名前の変更や列の追加や削除が発生した場合、テストコードやテストデータを手動でメンテナンスする必要があるため、テストの維持工数がそれなりに積みあがってしまいます。DbUnitは、データをExcelで作成することも可能なので、そういった機能も使って効率的にテストしていければと思います。