■ はじめに
http://blogs.yahoo.co.jp/dk521123/36156793.htmlの続き。 DbSetup だけだと、更新系(INSERT/UPDATE/DELETE)の確認はできそうにないので AssertJ / AssertJ-DB で確認する。 なお、以下のサイトが非常に参考になった。http://qiita.com/masatsugumatsus/items/7d12b037c89fb8962590
個人的な意見(結論)
* DbSetup と AssertJ / AssertJ-DB を組み合わせれば、DB周りの単体試験は結構できる * 役割は、以下の通り。DbSetup
* DBのデータクリア * テストデータをDBに入れるAssertJ / AssertJ-DB
* 処理後のDBデータ確認
■ 公式サイト
AssertJhttp://joel-costigliola.github.io/assertj/
AssertJ-DB
http://joel-costigliola.github.io/assertj/assertj-db.html
■ 設定
* いくつか方法がある。(以下の方法以外にもmavenとかもある)
その1: 直接、ダウンロード
* 以下のサイトからJAR(今回は「assertj-core-3.4.1.jar」「assertj-db-1.1.1.jar」)を 直接、ダウンロードし、インポートしておくAssertJ
[http://search.maven.org/#artifactdetails|org.assertj|assertj-core|3.4.1|bundle]
AssertJ-DB
[http://search.maven.org/#artifactdetails|org.assertj|assertj-db|1.1.1|bundle]
その2: Gradleで取得する
dependencies { // ... 略 ... // Use JUnit test framework testImplementation 'junit:junit:4.12' // DbSetup testCompile 'com.ninja-squad:DbSetup:2.1.0' // AssertJ / AssertJ-DB testCompile 'org.assertj:assertj-core:3.9.1' testCompile 'org.assertj:assertj-db:1.2.0' }http://joel-costigliola.github.io/assertj/assertj-core-quick-start.html
http://joel-costigliola.github.io/assertj/assertj-db.html
■ サンプル
環境
* Windows10 * Java1.8 * Eclipse Mars.2 Release (4.5.2) * MySQL * DbSetup-2.1.0 * assertj-core-3.4.1 / assertj-db-1.1.1 ※ DbSetup について、以下の関連記事を参照のこと。http://blogs.yahoo.co.jp/dk521123/36156793.html
サンプルデータ
MySQLCREATE TABLE person ( id char(8) NOT NULL PRIMARY KEY, name varchar(100), sex char(1) ); INSERT INTO person( id, name, sex) VALUES ('X0000000', 'admin', 'm'), ('X0000001', 'Mike', 'm'), ('X0000002', 'Tom', 'm'); select * from person;
Person.java
public class Person { public String id; public String name; public String sex; }
テスト対象クラス
TargetSample2.javaimport java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class TargetSample2 { /** * 動作確認用 * @param args * @throws Exception */ public static void main(String[] args) throws Exception { int result = TargetSample2.insert("C0001", "Peco", "m"); System.out.println("result : " + result); for (Person person : getAllPeople()) { System.out.println("***************************"); System.out.println("person.id : " + person.id); System.out.println("person.name : " + person.name); System.out.println("person.sex : " + person.sex); } } public static int insert(String id, String name, String sex) 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 person(id, name, sex) VALUES(?, ?, ?)");) { preparedStatement.setString(1, id); preparedStatement.setString(2, name); preparedStatement.setString(3, sex); result = preparedStatement.executeUpdate(); } catch (SQLException ex) { ex.printStackTrace(); } return result; } public static int updateById(String id, String name, String sex) 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("UPDATE person SET name = ?, sex = ? WHERE id =?");) { preparedStatement.setString(1, name); preparedStatement.setString(2, sex); preparedStatement.setString(3, id); result = preparedStatement.executeUpdate(); } catch (SQLException ex) { ex.printStackTrace(); } return result; } public static int deleteById(String id) 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("DELETE FROM person WHERE id = ?");) { preparedStatement.setString(1, id); result = preparedStatement.executeUpdate(); } catch (SQLException ex) { ex.printStackTrace(); } return result; } /** * 動作確認用 * @return * @throws Exception */ public static List<Person> getAllPeople() throws Exception { List<Person> returnValues = new ArrayList<Person>(); try ( Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sampledb", "root", "password"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM person");) { while (resultSet.next()) { Person person = new Person(); person.id = resultSet.getString("id"); person.name = resultSet.getString("name"); person.sex = resultSet.getString("sex"); returnValues.add(person); } } return returnValues; } }
テストコード
TargetSample2Test.javaimport static org.junit.Assert.*; import static com.ninja_squad.dbsetup.Operations.*; import static org.assertj.db.api.Assertions.assertThat; import org.junit.After; import org.junit.AfterClass; import org.junit.Before; import org.junit.BeforeClass; import org.junit.Test; import org.assertj.db.type.Changes; import org.assertj.db.type.Source; 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 TargetSample2Test { private static Destination destination; public static final Operation DELETE_ALL = deleteAllFrom("person"); @BeforeClass public static void setUpBeforeClass() throws Exception { destination = new DriverManagerDestination("jdbc:mysql://localhost:3306/sampledb", "root", "password"); } @AfterClass public static void tearDownAfterClass() throws Exception { } @Before public void setUp() throws Exception { Operation operation = sequenceOf( DELETE_ALL, insertInto("person") .columns("id", "name", "sex") .values("X0001", "Ken", "m") .values("X0002", "Smith", "m") .values("X0003", "Naomi", "f") .build()); DbSetup dbSetup = new DbSetup(destination, operation); dbSetup.launch(); } @After public void tearDown() throws Exception { } /** * INSERT Test. */ @Test public void insertTest() { Source source = new Source("jdbc:mysql://localhost:3306/sampledb", "root", "password"); Changes changes = new Changes(source); changes.setStartPointNow(); try { int result = TargetSample2.insert("X0004", "John", "m"); assertEquals(1, result); } catch (Exception e) { fail("Exception..."); e.printStackTrace(); } changes.setEndPointNow(); assertThat(changes) .hasNumberOfChangesGreaterThan(0) .change() .isOnTable("person") .rowAtEndPoint() .hasValues("X0004", "John", "m"); } /** * UPDATE Test. */ @Test public void updateByIdTest() { Source source = new Source("jdbc:mysql://localhost:3306/sampledb", "root", "password"); Changes changes = new Changes(source); changes.setStartPointNow(); try { int result = TargetSample2.updateById("X0002", "Chachy", "f"); assertEquals(1, result); } catch (Exception ex) { ex.printStackTrace(); fail("Exception..."); } changes.setEndPointNow(); assertThat(changes) .hasNumberOfChanges(1) .change() .isModification() .isOnTable("person") .rowAtStartPoint() .hasValues("X0002", "Smith", "m") .rowAtEndPoint() .hasValues("X0002", "Chachy", "f"); } /** * DELETE Test. */ @Test public void deleteByIdTest() { Source source = new Source("jdbc:mysql://localhost:3306/sampledb", "root", "password"); Changes changes = new Changes(source); changes.setStartPointNow(); try { int result = TargetSample2.deleteById("X0003"); assertEquals(1, result); } catch (Exception ex) { ex.printStackTrace(); fail("Exception..."); } changes.setEndPointNow(); assertThat(changes) .hasNumberOfChanges(1) .change() .isDeletion(); } }
参考文献
AssertJ
http://qiita.com/ikemo/items/165f01740995245f9009http://qiita.com/opengl-8080/items/b07307ab0d33422be9c5
http://aoking.hatenablog.jp/entry/2015/10/14/090551
http://d.hatena.ne.jp/Kazuhira/20140713/1405245955
AssertJ-DB
http://qiita.com/masatsugumatsus/items/7d12b037c89fb8962590関連記事
AssertJ / AssertJ-DB
AssertJ / AssertJ-DB ~ 基本編 [1] / DBデータの比較について ~https://blogs.yahoo.co.jp/dk521123/37499407.html
AssertJ / AssertJ-DB ~ 基本編 [2] / 時間に関する処理 ~
https://blogs.yahoo.co.jp/dk521123/36164701.html
AssertJ / AssertJ-DB ~ 基本編 [3] / 時間に関する比較 ~
https://blogs.yahoo.co.jp/dk521123/37145996.html
MySQL利用でAssertJ-DB を使った単体テストを行ったら日時でエラーになる
https://blogs.yahoo.co.jp/dk521123/36167284.html
DBに関わる単体試験
DbUnit ~ 入門編 ~https://blogs.yahoo.co.jp/dk521123/36146454.html
DbSetup ~ 入門編 ~
https://blogs.yahoo.co.jp/dk521123/36156793.html