Excelから単体テストデータを投入する(SpringBoot + SQLServer + DBUnit)

## 初めに

単体テスト時のテストデータの生成・管理って、結構めんどくさいですよね。
そこで今回は、「テストデータをExcelで予め用意しておいて、テストメソッド実行時にDB登録する」ことでテストの効率化を図る仕組みを作ってみます。

既存の記事(例えば[こんなの](https://tanaka653.hatenadiary.jp/entry/2021/03/14/111033))だと、テストメソッド内で明示的にテストデータ生成を行うものが多かったのですが、これだとテストメソッドが肥大化してしまいます。

個人的にはテストメソッドの肥大化は好きじゃないので、本記事ではこれを防ぐためにspring-test-dbunitライブラリの**LookUpクラス**を使用することとします。

## 前提

### 使用技術

|技術|バージョン|用途|
|--|--|--|
|Java|amazon-correto 16|プログラミング言語|　
|SpringBoot|2.6.1|Webフレームワーク|　
|SQL Server|2017-latest|データベース(dockerで用意)|
|Junit-jupiter|5.5.2|Java単体テストフレームワーク|
|DBUnit|2.5.3|JunitにおけるDB試験を楽にしてくれるFW|
|Spring-test-dbunit|1.1.0|SpringにおけるDBUnitお助けライブラリ|

### 利用される主なクラス

|パッケージ|クラス|自作orライブラリ|備考/用途|
|--|--|--|--|
|任意|テストクラス|自作|その名の通りテストクラス|
|任意|XlsDataSetLoader|自作|Excelファイルを読み込んでXlsDataSetを返す。AbstractDataSetLoaderクラスを継承|　
|org.dbunit.data.set.excel|XlsDataSet|DBUnit|Excelから取得できたテーブルのデータを保持|
|org.dbunit.ext.mssql|InsertIdentityOperation|DBUnit|自動採番されるカラムに明示的に値をInsertできるようにする|
|com.github.springtestdbunit.annotation|DatabaseOperation|spring-test-dbunit|テーブルに対する操作設定（INSERT/UPDATE/REFRESH/DELETE/DELETE_ALL/TRUNCATE_TABLE/CLEAN_INSERT)|
|com.github.springtestdbunit.operation|MicrosoftSqlDatabaseOperationLookup|spring-test-dbunit|SQLServer用のDatabaseOperationまとめ|

### テスト対象

ユーザ情報を全件取得し、リストとして返すAPI（GETメソッド）を試験する。

### DB設計

ユーザ情報テーブル

|カラム名|id|name|password|created_at|updated_at|
|--|--|--|--|--|--|
|**型**|int,IDENTITY|nvarchar(50)|nvarchar(50)|datetime2|datetime2|
|**データ例**|1|John|PASSWORD|2022-01-01 00:00:00.000|2022-01-01 00:00:00.000|

## Todo

### 1.DataSetLoaderクラスを作る
Resource（=Excelファイル)をもとにXlsDataSetを返すもの

```XlsDataSetLoader.java
public class XlsDataSetLoader extends AbstractDataSetLoader {

    @Override
    protected IDataSet createDataSet(Resource resource) throws Exception {
        // TODO 自動生成されたメソッド・スタブ
        try(InputStream inputStream = resource.getInputStream()) {
            return new XlsDataSet(inputStream);
        }
    }
}
```

### 2.エクセルを用意
パスはsrc/test/data/ユーザ一覧取得/user.xlsx

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552268/72847071-edb3-d890-a4c7-613eca12cb6e.png)


### 3.テストクラス生成

**テストクラス**
・SpringBootTestアノテーションを付与
・DbUnitConfigrationアノテーションで、1で作ったXlsDataSetLoaderとMicrosoftSqlDatabaseOperationLookupを設定

**テストメソッド**
・Testアノテーション付与
・DatabaseSetupアノテーションで、2で作ったExcelファイルのパスとtypeを設定

```ReadUserTest.java
@SpringBootTest
@DbUnitConfiguration(
        dataSetLoader = XlsDataSetLoader.class,
        databaseOperationLookup = MicrosoftSqlDatabaseOperationLookup.class
)
@TestExecutionListeners({
        DbUnitTestExecutionListener.class
})
class ReadUserTest {

//中略(データソース設定など)

  @Test
  @DatabaseSetup(
          value = {
                  "/data/ユーザ一覧取得/user.xlsx"
          },
          type = CLEAN_INSERT
  )
  void データ1件() throws Exception {

    ResultActions results =
            mockMvc
                    .perform(
                            get("/user"))
                    .andExpect(status().isOk())
                    .andDo(log());

    //各種ASSERT（中略）
}
```

### 4.テストを実行
テスト途中でブレークポイントを貼ってテストデータが入っていることを確認

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/552268/d944960e-fd74-2ae3-b744-d358f3e85fe9.png)

ASSERT内容はよしなに...

## ハマったこと

### 1.自動採番カラムに明示的にデータを登録できない問題
InsertIdentityOperationクラスおよび、それを内包するLookupクラスの存在を知らなかった時の話です。
Excelから自動採番カラムに明示的にデータを登録しようとすると、以下エラーが出てしまいました。

```
Cannot insert explicit value for identity column in table 'user' when IDENTITY_INSERT is set to OFF.
```

解決策としては、そのままですがInsertIdentityOperationクラスを使うことです。（本記事では、	MicrosoftSqlDatabaseOperationLookupクラス内で使用されています）
InsertIdentityOperationクラスの実装を見ると分かりますが、主キーとなるカラムがテーブルに設定されているときは`SET IDENTITY_INSERT {テーブル名} ON`を最初に実行するよう設定されています。ここらへん(207行目あたり）です↓

```IdentityInsertOperation.java
if (hasIdentityColumn) {
  StringBuffer sqlBuffer = new StringBuffer(128);
  sqlBuffer.append("SET IDENTITY_INSERT ");
  sqlBuffer.append(getQualifiedName(connection.getSchema(),
      metaData.getTableName(), connection));
  sqlBuffer.append(" ON");
  statement.execute(sqlBuffer.toString())
}
```
※自作ソースではないです。IdentityInsertOperation.java内から引用

**参考：[Class InsertIdentityOperation](http://dev.cs.ovgu.de/java/dbjunit/apidocs/org/dbunit/ext/mssql/InsertIdentityOperation.html)**

### 2.意図しない行をDBUnitがテストデータと認識してしまう問題

Excelでは全てのカラムにデータを登録している（NULLではない）のにも関わらず、 非NULLカラムにNULLを挿入しようとしてSQLExcepitonが出る場合がありました。なんでやねん。

```
Cannot insert the value NULL into column 'id', table 'dbo.user'; column does not allow nulls. INSERT fails.
```

ライブラリ内のコードを追いつつよく調べてみると、Excel側の書き方(?)の問題で「1行の実データ＋999行の空文字データを登録する設定」になっていました。
**A1セルで「command + End」（Windowsならctrl + end）を押して書類の末尾までスクロールしてみる**と、1000行目まで飛んでしまう場合、この書き方になっています。

解決策としては、別シートを新しく立ち上げて、データを記載し直してみたところ直りました。「command + End」では、テストデータの末尾まで飛ぶことを確認しています。

**参考記事:[dbunitエラー | 基礎からのJava](https://matasaburou.hatenadiary.org/entry/20150715/1436963613)**
「空白セルに注意」段落がとても参考になりました。


## もっと掘ってみたいこと
・テスト前のデータ登録だけではなく、テスト後にデータ消す時の挙動も見たい
・Excel以外（CSVとか）でも似たようなことはできるっぽいので、動かしてみたい
・そもそもExcelで予め決め打ちのテストデータを準備しておくことの是非
　-システム日時と比較して期限切れが発生する日時データとかは、テスト起動時刻に対して相対的に決めてデータ登録したい