【JUnit】 AssertJ / AssertJ-DB ～ 基本編 [2] / 時間に関する処理 ～

Test tool

■ はじめに

http://blogs.yahoo.co.jp/dk521123/36157721.html
で取り扱った AssertJ-DB だが、全然情報がないので
分かった事をこまめにメモる。

■ 日付/時間/日時について

https://joel-costigliola.github.io/assertj/assertj-db-concepts.html#datevalue-timevalue-datetimevalue
 * DateValue     : 日付 (yyyy-mm-dd)
 * TimeValue     : 時間 (hh:mm, hh:mm:ss, hh:mm:ss.nnnnnnnnn)
 * DateTimeValue : 日時 (yyyy-mm-dd, yyyy-mm-ddThh:mm, yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.nnnnnnnnn)

# カッコ書きは文字列化する際のフォーマット
DateValue
http://joel-costigliola.github.io/assertj/db/1.2.x/api/org/assertj/db/type/DateValue.html
TimeValue
http://joel-costigliola.github.io/assertj/db/1.2.x/api/org/assertj/db/type/TimeValue.html
DateTimeValue
http://joel-costigliola.github.io/assertj/db/1.2.x/api/org/assertj/db/type/DateTimeValue.html

■ 日付/時間/日時 に関する比較

 * 以下の関連記事を参照
https://blogs.yahoo.co.jp/dk521123/37145996.html

■ 注意

 * MySQL 5.6からDATETIMEは小数点以下が四捨五入(round)される
   対して、DateTimeValueはミリ秒まで設定するので不一致となり、エラーと判断されてしまう
詳細は以下の関連記事を参照のこと
http://blogs.yahoo.co.jp/dk521123/36167284.html

■ サンプル

サンプルデータ：MySQL

CREATE TABLE IF NOT EXISTS `item` (
  `id` char(8) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `releasedate` datetime DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp,
  `updatedate` datetime DEFAULT current_timestamp on update current_timestamp
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Item.java

import java.sql.Date;
import java.util.Calendar;

public class Item {
  public String id;
  public String name;
  public Long price;
  public Calendar releaseDate;
  public Date createDate;
  public Date updateDate;
}

TargetSample3.java

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Calendar;

public class TargetSample3 {
  public static int insert(String id, String name, Integer price, Calendar releaseDate) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");

    int result = 0;
    try (
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sampledb", "root", "password");
        PreparedStatement preparedStatement = connection
            .prepareStatement("INSERT INTO item(id, name, price, releasedate) VALUES(?, ?, ?, ?)");) {
      preparedStatement.setString(1, id);
      preparedStatement.setString(2, name);
      preparedStatement.setInt(3, price);
      preparedStatement.setDate(4, new Date(releaseDate.getTimeInMillis()));
      result = preparedStatement.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return result;
  }
}

TargetSample3Test.java

import static org.junit.Assert.*;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import static com.ninja_squad.dbsetup.Operations.*;
import static org.assertj.db.api.Assertions.assertThat;

import org.assertj.db.type.DateTimeValue;
import org.assertj.db.type.DateValue;
import org.assertj.db.type.Source;
import org.assertj.db.type.Table;
import org.assertj.db.type.TimeValue;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import com.ninja_squad.dbsetup.DbSetup;
import com.ninja_squad.dbsetup.destination.Destination;
import com.ninja_squad.dbsetup.destination.DriverManagerDestination;
import com.ninja_squad.dbsetup.operation.Operation;

/**
 *
 */
public class TargetSample3Test {
  public static final Operation DELETE_ALL = deleteAllFrom("item");

  /**
   * @throws java.lang.Exception
   */
  @BeforeClass
  public static void setUpBeforeClass() throws Exception {
  }

  /**
   * @throws java.lang.Exception
   */
  @AfterClass
  public static void tearDownAfterClass() throws Exception {
  }

  /**
   * @throws java.lang.Exception
   */
  @Before
  public void setUp() throws Exception {
    Operation operation = sequenceOf(DELETE_ALL);

    Destination destination = new DriverManagerDestination("jdbc:mysql://localhost:3306/sampledb", "root", "password");
    DbSetup dbSetup = new DbSetup(destination, operation);
    dbSetup.launch();
  }

  /**
   * @throws java.lang.Exception
   */
  @After
  public void tearDown() throws Exception {
  }

  /**
   * INSERT Test.
   */
  @Test
  public void insertTest() {
    try {
      Calendar targetDate = Calendar.getInstance();
      System.out.println("Debug : " + new SimpleDateFormat("yyyy-MM-dd hh:mm:ss SSS").format(targetDate.getTime()));
      int result = TargetSample3.insert("X0001", "Apple", 129, targetDate);
      assertEquals(1, result);

      Source source = new Source("jdbc:mysql://localhost:3306/sampledb", "root", "password");
      Table table = new Table(source, "item");

      assertThat(table).hasNumberOfRows(1);
      assertThat(table).row(0).value("id").isEqualTo("X0001");
      assertThat(table).row(0).value("name").isEqualTo("Apple");
      assertThat(table).row(0).value("price").isEqualTo(129);
      assertThat(table).row(0).value("releasedate").isEqualTo(toDateTimeValue(targetDate));
      assertThat(table).row(0).value("createdate").isAfterOrEqualTo(toDateTimeValue(targetDate));
      assertThat(table).row(0).value("updatedate").isAfterOrEqualTo(toDateTimeValue(targetDate));
    } catch (Exception ex) {
      ex.printStackTrace();
      fail("Exception...");
    }
  }

  private static DateTimeValue toDateTimeValue(Calendar targetDateTime) {
    DateValue dateValue = new DateValue(targetDateTime);
    int hour = targetDateTime.get(Calendar.HOUR_OF_DAY);
    int minute = targetDateTime.get(Calendar.MINUTE);
    int second = targetDateTime.get(Calendar.SECOND);
    int milliSecond = targetDateTime.get(Calendar.MILLISECOND);

    // For MySQL
    second = second + ((milliSecond + 500) / 1000);

    TimeValue timeValue = new TimeValue(hour, minute, second);
    return new DateTimeValue(dateValue, timeValue);
  }
}

以下の関連記事に続く
https://blogs.yahoo.co.jp/dk521123/37145996.html

参考文献

http://joel-costigliola.github.io/assertj/assertj-db-concepts.html
http://rpouiller.developpez.com/tutoriels/java/assertj-db-introduction/

関連記事

AssertJ / AssertJ-DB ～ 入門編 ～

http://blogs.yahoo.co.jp/dk521123/36157721.html

AssertJ / AssertJ-DB ～ 基本編 [3] / 時間に関する比較 ～

https://blogs.yahoo.co.jp/dk521123/37145996.html

MySQL利用でAssertJ-DB を使った単体テストを行ったら日時でエラーになる

http://blogs.yahoo.co.jp/dk521123/36167284.html