ããã¯ããªã«ãããããŠæžãããã®ïŒ
Javaã®ãã¹ããŸããã®æ å ±ãèŠãŠããŠãããŒã¿ããŒã¹ã®äºæ ã£ãŠä»ã¯ã©ããªãã ããïŒãšæããŸããŠã
èŠãŠããŠãDbUnitããå€ãã£ãŠããªãããã ã£ãã®ãšãããããDbUnitã䜿ã£ãããšããªãã£ãã®ã§ã¡ãã£ãšè©ŠããŠã¿ãããšã«ããŸããã
DbUnitãšåšèŸºäºæ
DbUnitã¯JUnitã®ExtensionãšãããŠããŠãããŒã¿ããŒã¹ããªãã³ãªãããžã§ã¯ããã¿ãŒã²ããã«ããŠããŸãã
DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs.
ããŒã¿ã®ãšã¯ã¹ããŒãã»ã€ã³ããŒãã®æ©èœãæã¡ãããŒã¿ããŒã¹ãæã€å€ã§ã®ã¢ãµãŒã·ã§ã³ãè¡ããŸãã
DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DbUnit can also help you to verify that your database data match an expected set of values.
Spring Frameworkã®ãã¹ãã«é¢ããããã¥ã¡ã³ãå ã§ããããŒã¿ããŒã¹ã«é¢ãããã¹ãã®ãªãœãŒã¹ã§åç §ãããŠããã®ã¯ãDbUnitã®ã¿ã§ãã
DbUnit: JUnit extension (also usable with Ant and Maven) that is targeted at database-driven projects and, among other things, puts your database into a known state between test runs.
ãªã®ã§ããŸãã¯DbUnitãèŠãŠã¿ãã®ã劥åœãªã®ããªããšã¯æãã®ã§ããã
DbUnitã®ãµã€ããèŠãŠã¿ããšãæçµãªãªãŒã¹ã2012幎ã®2.4.9ã«ãªã£ãŠããŠSourceForgeã«ãããããžã§ã¯ããæŽæ°ãããŠããŸããã
dbUnit download | SourceForge.net
ããããèŠããšMaven Centralããã¯æ°ããããŒãžã§ã³ãããŠã³ããŒãã§ãããšæžãããŠããã®ã§ãèŠãŠã¿ããšçŸæç¹ã®ææ°ã®ããŒãžã§ã³ã¯
2.7.3ã§ãã
https://search.maven.org/artifact/org.dbunit/dbunit/2.7.3/jar
çŸåšããœãŒã¹ã³ãŒãã¯ãã¡ãã§ããŒãžã§ã³ç®¡çãããŠããããã§ãã
ãªã®ã§ãã¡ã³ããã³ã¹ã¯ç¶ç¶ãããŠãããã§ãã
ãã®ä»ãDbUnitãåãå·»ãOSSãšããŠãSpring Teståãã®Spring Test DBUnitãšãããã®ããããŸããããã¡ãã¯æŽæ°ãæ¢ãŸã£ãŠ
ä¹
ãããâŠã
Spring Test DBUnit – Introduction
Database Riderãšãããã®ãããã®ã§ãä»ã¯ãã¡ããèŠãã®ãããã®ãããããŸããã
DbUnitã«ã€ããŠ
DbUnitã«åœ±é¿ãäžãããããŒã¿ããŒã¹ã®ãã¹ãã«é¢ãã話ã¯ãã¡ãã
ããããGetting Startedã
ã¹ããããšããŠã¯ã以äžã®ããã§ãã
- ããŒã¿ãã¡ã€ã«ã®äœæ
- ããŒã¿ããŒã¹ã®æ¥ç¶æ å ±çã®ã»ããã¢ãããè¡ãããã¹ããå®æœ
DbUnitã¯DBTestCase
ã¯ã©ã¹ãæäŸããŠããããã§ãããJUnit 5æ代ã®ãã®ã§ã¯ãªãã®ã§ãã¡ãã¯äœ¿ããªãã§ããããã
å®éãDbUnitã®äŸåé¢ä¿ã«ã¯JUnit 5ã¯å«ãŸããŠããŸããã
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/pom.xml
ã¢ãµãŒã·ã§ã³ã®ãµããŒããšããŠã¯ã以äžããããã§ãããã§ãã
- ã¯ãšãªãŒãå
ã«ããã¹ãããã·ã§ããïŒ
ITable
ïŒã®ååŸ - ã¹ãããã·ã§ããã®æ¯èŒã®éã«ãã«ã©ã ã®ãã£ã«ã¿ãªã³ã°
- ã¹ãããã·ã§ããã®ãœãŒã
ãŸããããŒã¿ãã¡ã€ã«ããã®ããŒã¿ããŒããå¯èœãªããã§ãã
DbUnitãæ§æããäž»ãªã³ã³ããŒãã³ãã¯ããã¡ãã«æžãããŠããŸãã
IDatabaseConnection
ã€ã³ã¿ãŒãã§ãŒã¹ ⊠ããŒã¿ããŒã¹ãžã®æ¥ç¶ãè¡šãIDataSet
ã€ã³ã¿ãŒãã§ãŒã¹ ⊠ããŒãã«ã®ã³ã¬ã¯ã·ã§ã³ãè¡šãDatabaseOperation
ã¯ã©ã¹ ⊠åãã¹ãã®ååŸã§ããŒã¿ããŒã¹ã«å¯Ÿããæäœãæœè±¡åãã
IDataSet
ã€ã³ã¿ãŒãã§ãŒã¹ã®å®è£
ãšããŠãXMLãã¯ãšãªãŒãExcelãããã°ã©ã ã§çµã¿ç«ãŠããã®çããããããããã§ããã
ããã¥ã¡ã³ãã«èšèŒãããŠãããã®ã¯å€ãããªã®ã§ããœãŒã¹ã³ãŒããèŠãæ¹ããããã§ããããã
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/src/main/java/org/dbunit/dataset/
CSVãã¡ã€ã«ã§ãæ±ãããã§ãã
DatabaseOperation
ã¯ã©ã¹ã¯ãåãã¹ãã®ååŸã§ããŒã¿ããŒã¹ã«å¯Ÿããæäœãå®è¡ããŸããã以äžã®ãããªããšãè¡ããŸãã
UPDATE
INSERT
DELETE
DELETE_ALL
TRUNCATE
REFRESH
CLEAN_INSERT
NONE
çŽæçã«ããããªãããªã®ã¯REFRESH
ãšCLEAN_INSERT
ã§ããããã
REFRESH
ã¯å¯Ÿè±¡ãšãªãããŒã¿ã»ãããããŒã¿ããŒã¹ã«åæ ãããã®ã§ãããŒã¿ã»ããã«å«ãŸããç¯å²ã®ããŒã¿ã®ã¿ã察象ãšãã
ãã以å€ã®ããŒã¿ã«ã¯åœ±é¿ãäžããŸãããå
éšçã«ã¯ãupdate
ããŠæŽæ°ãããªããã°insert
ããããšããŸãã
CLEAN
ã¯DELETE_ALL
ã®åŸã«INSERT
ãå®è¡ãããã®ã§ãã
ãã®ä»ãè€æ°ã®æäœãã²ãšã€ã«ãŸãšããCompositeOperation
ã¯ã©ã¹ããã©ã³ã¶ã¯ã·ã§ã³å
ã§æäœãå®è¡ããTransactionOperation
ã¯ã©ã¹ãªã©ã
ãããŸãã
ãšãŸããããã¥ã¡ã³ããèªãã®ã¯ãããããã«ããŠãå®éã«äœ¿ã£ãŠãã£ãŠã¿ãŸãããã
ãé¡
ä»åããé¡ã¯ä»¥äžã«ããŸãã
- DbUnitã䜿ã£ããã¹ããæžã
- ãã¡ã€ã«ã§çšæããããŒã¿ã»ããã«ã¯ãXMLã䜿çšãã
- ããŒã¿ããŒã¹ã¢ã¯ã»ã¹ã«ã¯Doma 2ã䜿çšãã
- ãã¹ãã£ã³ã°ãã¬ãŒã ã¯ãŒã¯ã«ã¯ãJUnit 5ã䜿çšãã
- ããŒã¿ããŒã¹ã«ã¯MySQL 8.0ã䜿çšãã
ããŒã¿ã»ããã§äœ¿ããã¡ã€ã«ã¯CSVã«ãããããªãšæã£ãŠããã®ã§ãããCSVã§ã®ããŒã¿ã»ããã¯ãªããªãç¬ç¹ã¿ãããªã®ã§ãä»åã¯XMLã«
ããŸããã
ç°å¢
ä»åã®ç°å¢ã¯ããã¡ãã
$ java --version openjdk 17.0.2 2022-01-18 OpenJDK Runtime Environment (build 17.0.2+8-Ubuntu-120.04) OpenJDK 64-Bit Server VM (build 17.0.2+8-Ubuntu-120.04, mixed mode, sharing) $ mvn --version Apache Maven 3.8.5 (3599d3414f046de2324203b78ddcf9b5e4388aa0) Maven home: $HOME/.sdkman/candidates/maven/current Java version: 17.0.2, vendor: Private Build, runtime: /usr/lib/jvm/java-17-openjdk-amd64 Default locale: ja_JP, platform encoding: UTF-8 OS name: "linux", version: "5.4.0-107-generic", arch: "amd64", family: "unix"
MySQLã¯172.17.0.2ã§åäœããŠãããã®ãšããŸããããŒãžã§ã³ã¯ããã¡ãã
$ mysql --version mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
æºå
ãŸãã¯ããããžã§ã¯ããäœæããŠãããŸãã
ä»åã®MavenäŸåé¢ä¿çã¯ãã¡ãã
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> <scope>runtime</scope> </dependency> <dependency> <groupId>org.seasar.doma</groupId> <artifactId>doma-core</artifactId> <version>2.51.0</version> </dependency> <dependency> <groupId>org.seasar.doma</groupId> <artifactId>doma-processor</artifactId> <version>2.51.0</version> <optional>true</optional> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter</artifactId> <version>5.8.2</version> <scope>test</scope> </dependency> <dependency> <groupId>org.dbunit</groupId> <artifactId>dbunit</artifactId> <version>2.7.3</version> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <artifactId>maven-surefire-plugin</artifactId> <version>2.22.2</version> </plugin> </plugins> </build>
DbUnitã¯2.7.3ã§ããã
ç¶ããŠãããŒã¿ããŒã¹ã¢ã¯ã»ã¹ãè¡ãããã°ã©ã ãDoma 2ã䜿ã£ãŠäœæããŸãã
ãšã³ãã£ãã£ã¯ã©ã¹ã
src/main/java/org/littlewings/dbunit/Book.java
package org.littlewings.dbunit; import java.time.LocalDate; import org.seasar.doma.Entity; import org.seasar.doma.Id; @Entity public class Book { @Id String isbn; String title; Integer price; LocalDate publishDate; String category; public static Book create(String isbn, String title, Integer price, LocalDate publishDate, String category) { Book book = new Book(); book.setIsbn(isbn); book.setTitle(title); book.setPrice(price); book.setCategory(category); book.setPublishDate(publishDate); return book; } // getterïŒsetterã¯çç¥ }
Daoã
src/main/java/org/littlewings/dbunit/BookDao.java
package org.littlewings.dbunit; import org.seasar.doma.Dao; import org.seasar.doma.Insert; import org.seasar.doma.Script; import org.seasar.doma.Select; import org.seasar.doma.Sql; @Dao public interface BookDao { @Sql(""" drop table if exists book; create table book( isbn varchar(14), title varchar(100), price int, publish_date date, category varchar(20), primary key(isbn) ) """) @Script void createTableIfExistsRecreate(); @Sql("truncate table book") @Script void truncate(); @Insert int insert(Book book); @Sql("select /*%expand*/* from book where isbn = /* isbn */'dummy'") @Select Book selectByIsbn(String isbn); }
Configã
src/main/java/org/littlewings/dbunit/DomaConfig.java
package org.littlewings.dbunit; import javax.sql.DataSource; import org.seasar.doma.jdbc.Config; import org.seasar.doma.jdbc.Naming; import org.seasar.doma.jdbc.dialect.Dialect; import org.seasar.doma.jdbc.dialect.MysqlDialect; import org.seasar.doma.jdbc.tx.LocalTransactionDataSource; import org.seasar.doma.jdbc.tx.LocalTransactionManager; import org.seasar.doma.jdbc.tx.TransactionManager; public class DomaConfig implements Config { private static final DomaConfig CONFIG = new DomaConfig(); Dialect dialect; LocalTransactionDataSource dataSource; TransactionManager transactionManager; public static DomaConfig singleton() { return CONFIG; } private DomaConfig() { dialect = new MysqlDialect(); dataSource = new LocalTransactionDataSource( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password" ); transactionManager = new LocalTransactionManager(dataSource.getLocalTransaction(getJdbcLogger())); } @Override public DataSource getDataSource() { return dataSource; } @Override public Dialect getDialect() { return dialect; } @Override public TransactionManager getTransactionManager() { return transactionManager; } @Override public Naming getNaming() { return Naming.SNAKE_LOWER_CASE; } }
ãããŸã§ã§ãæºåã¯å®äºã§ããããšã¯ãããã䜿ã£ãŠãã¹ãã³ãŒããæžããŠãããŸãã
DbUnitã䜿ã£ãŠã¿ã
ã§ã¯ãDbUnitã䜿ã£ãŠãããŸãããããŸãã¯ããã¹ãã³ãŒãã®é圢ãçšæã
src/test/java/org/littlewings/dbunit/DbUnitGettingStartedTest.java
package org.littlewings.dbunit; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.time.LocalDate; import java.util.List; import org.dbunit.Assertion; import org.dbunit.DatabaseUnitException; import org.dbunit.database.DatabaseConnection; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.ITable; import org.dbunit.dataset.SortedTable; import org.dbunit.dataset.xml.FlatXmlDataSetBuilder; import org.dbunit.ext.mysql.MySqlConnection; import org.junit.jupiter.api.BeforeAll; import org.junit.jupiter.api.BeforeEach; import org.junit.jupiter.api.Test; public class DbUnitGettingStartedTest { @BeforeAll public static void setUpAll() { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); DomaConfig .singleton() .getTransactionManager() .required(() -> bookDao.createTableIfExistsRecreate()); } @BeforeEach public void setUp() { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); DomaConfig .singleton() .getTransactionManager() .required(() -> bookDao.truncate()); } // ããã«ãã¹ããæžãïŒ }
ãã¹ãåã«1床ã ãããŒãã«ãdrop ïŒ createããŠãåãã¹ãããšã«ããŒãã«ãtruncateããŸãã
æåã¯DbUnitã䜿ã£ãŠããŒã¿ããŒã¹ã«æ¥ç¶ããŠã¿ãŸãããããããªæãã§ãJDBCã®Connection
ããDatabaseConnection
ãååŸããŸãã
@Test public void createDbUnitConnection() throws DatabaseUnitException, SQLException { DatabaseConnection databaseConnection = null; try (Connection conn = DriverManager .getConnection( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password");) { databaseConnection = new MySqlConnection(conn, null); } finally { if (databaseConnection != null) { databaseConnection.close(); } } }
ä»åã¯MySqlConnection
ã¯ã©ã¹ã䜿ã£ãŠããŸãããæ±çšçãªDatabaseConnection
ã¯ã©ã¹ãnew
ããŠãè¯ãã§ãããä»ã®ããŒã¿ããŒã¹ã®å Žåã¯
ããããã«å¿ããã¯ã©ã¹ã䜿ã£ãŠãããã§ãããã
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/src/main/java/org/dbunit/ext/
èŠããšãããæ±çšçãªDatabaseConnection
ã¯ã©ã¹ãšã®å·®ã¯ããããã®ããŒã¿ããŒã¹ãèæ
®ããããŒã¿åãã¡ã¿ããŒã¿ãæ±ãã¯ã©ã¹ãé©çšããŠ
ããããšããã¿ããã§ããã
次ã«ãã¢ãµãŒã·ã§ã³ãè¡ã£ãŠã¿ãŸãã
@Test public void firstAssertion() throws DatabaseUnitException, SQLException { DatabaseConnection databaseConnection = null; try (Connection conn = DriverManager .getConnection( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password");) { databaseConnection = new MySqlConnection(conn, null); DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); }); IDataSet actualDataSet = databaseConnection.createDataSet(); ITable actualTable = actualDataSet.getTable("book"); IDataSet expectedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitGettingStartedTest.firstAssertion.xml")); ITable expectedTable = expectedDataSet.getTable("book"); // assert dataset Assertion.assertEquals(expectedDataSet, actualDataSet); // assert table Assertion.assertEquals(expectedTable, actualTable); } finally { if (databaseConnection != null) { databaseConnection.close(); } } }
ããŒãã«ã«ããŒã¿ãç»é²ã
DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); });
ãã®åŸãDatabaseConnection
ã¯ã©ã¹ã®ã€ã³ã¹ã¿ã³ã¹ããããŒã¿ã»ããããã³ããŒãã«ã®ããŒã¿ãååŸããŸãã
IDataSet actualDataSet = databaseConnection.createDataSet();
ITable actualTable = actualDataSet.getTable("book");
ãããŠã確èªãè¡ãããŒã¿ã»ãããXMLãã¡ã€ã«ããèªã¿èŸŒã¿ãŸãããã¡ããããŒã¿ã»ãããšãããŒã¿ã»ããããããŒãã«ã«å¯Ÿå¿ãã
ããŒã¿ãšããŠæ±ããŸãã
IDataSet expectedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitGettingStartedTest.firstAssertion.xml")); ITable expectedTable = expectedDataSet.getTable("book");
çšæããXMLãã¡ã€ã«ã¯ãã¡ãã
src/test/resources/DbUnitGettingStartedTest.firstAssertion.xml
<?xml version="1.0" encoding="UTF-8"?> <dataset> <book isbn="978-4295008477" title="æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]" price="2860" publish_date="2020-03-13" category="java"/> <book isbn="978-4621303252" title="Effective Java 第3ç" price="4400" publish_date="2018-10-30" category="java"/> <book isbn="978-4774189093" title="Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§" price="3278" publish_date="2017-04-18" category="java"/> <book isbn="978-4798147406" title="詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã" price="3960" publish_date="2016-08-26" category="mysql"/> <book isbn="978-4798161488" title="MySQL培åºå ¥é 第4ç MySQL 8.0察å¿" price="4180" publish_date="2020-07-06" category="mysql"/> </dataset>
èŠçŽ åã«ããŒãã«åãå±æ§ã«ã«ã©ã åãšãã£ãæãã§ããã
ãã®ä»ã®ãã¡ã€ã«åœ¢åŒã®å Žåã¯ããªããžããªã®ãã¹ãã«é¢ããéšåãèŠããšããã§ãããã
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/src/test/resources/
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/src/test/java/org/dbunit/dataset/
ã¢ãµãŒã·ã§ã³ãããŒã¿ã»ããåäœã§ãè¡ããŸãããããŒãã«åäœã§ãè¡ããŸãã
// assert dataset Assertion.assertEquals(expectedDataSet, actualDataSet); // assert table Assertion.assertEquals(expectedTable, actualTable);
ãã¡ãã§ãçŸåšããŒã¿ã»ããããã³ããŒãã«ïŒä»åã¯ããŒã¿ã»ãããšããŒãã«ãåãç¯å²ã§ããïŒã«æ ŒçŽãããŠããããŒã¿ãšãXMLãã¡ã€ã«ã§
çšæããããŒã¿ãçããããšã確èªã§ããŸããã
ã¢ãµãŒã·ã§ã³ãè¡ã察象ã®ããŒã¿ãçµã蟌ãããšãã§ããŸããããšãã°ãSQLã䜿ãå Žåã¯ãã¡ãã
@Test public void createDataSetFromQuery() throws DatabaseUnitException, SQLException { DatabaseConnection databaseConnection = null; try (Connection conn = DriverManager .getConnection( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password");) { databaseConnection = new MySqlConnection(conn, null); DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); }); ITable actualTable = databaseConnection .createQueryTable("query_result", "select * from book where category = 'mysql' order by isbn asc"); IDataSet expectedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitGettingStartedTest.createDataSetFromQuery.xml")); ITable expectedTable = expectedDataSet.getTable("book"); Assertion.assertEquals(expectedTable, actualTable); } finally { if (databaseConnection != null) { databaseConnection.close(); } } }
ãã®éšåã§ãããå
ã»ã©ã®å Žåã¯ããŒã¿ã»ãããšããŠéžæããç¯å²ããã³ãããŒãã«ã«é¢ããŠã¯ãã®ããŒãã«ã®å
šããŒã¿ã察象ã«ãªããŸããã
ä»åã¯ã«ããŽãªãŒãmysql
ã®ãã®ã«çµã£ãŠããŸãã
ITable actualTable = databaseConnection .createQueryTable("query_result", "select * from book where category = 'mysql' order by isbn asc");
ãã®å Žåã¯ããŒãã«ã®ããŒã¿ãšããŠæ±ãããšã«ãªãã®ã§ãã¢ãµãŒã·ã§ã³ãããŒãã«åäœã«ãªããŸãã
IDataSet expectedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitGettingStartedTest.createDataSetFromQuery.xml")); ITable expectedTable = expectedDataSet.getTable("book"); Assertion.assertEquals(expectedTable, actualTable);
çšæããXMLãã¡ã€ã«ã¯ãã¡ããã«ããŽãªãŒãmysql
ãšãªã£ãŠãããã®ã®ã¿ã«ãªããŸããã
src/test/resources/DbUnitGettingStartedTest.createDataSetFromQuery.xml
<?xml version="1.0" encoding="UTF-8"?> <dataset> <book isbn="978-4798147406" title="詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã" price="3960" publish_date="2016-08-26" category="mysql"/> <book isbn="978-4798161488" title="MySQL培åºå ¥é 第4ç MySQL 8.0察å¿" price="4180" publish_date="2020-07-06" category="mysql"/> </dataset>
ããŒã¿ã»ããå
ã®ããŒã¿ã¯ãäž»ããŒã«ãããœãŒããããŸãããã®ãœãŒãããŒãå€æŽããã«ã¯ãSortedTable
ã¯ã©ã¹ã䜿ããŸãã
@Test public void sortTable() throws DatabaseUnitException, SQLException { DatabaseConnection databaseConnection = null; try (Connection conn = DriverManager .getConnection( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password");) { databaseConnection = new MySqlConnection(conn, null); DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); }); IDataSet actualDataSet = databaseConnection.createDataSet(); SortedTable actualTable = new SortedTable(actualDataSet.getTable("book"), new String[]{"price"}); actualTable.setUseComparable(true); IDataSet expectedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitGettingStartedTest.sortTable.xml")); ITable expectedTable = expectedDataSet.getTable("book"); //SortedTable expectedTable = new SortedTable(expectedDataSet.getTable("book"), new String[]{"price"}); //expectedTable.setUseComparable(true); Assertion.assertEquals(expectedTable, actualTable); } finally { if (databaseConnection != null) { databaseConnection.close(); } } }
ãããªæãã§ãããŒãã«ãããã«SortedTable
ã¯ã©ã¹ã§ã©ããããŸããSortedTable
ã¯ã©ã¹ã®ã€ã³ã¹ã¿ã³ã¹äœæåŸã«ã
SortedTable#setUseComparable
ã¡ãœãããåŒã³åºãå¿
èŠãããããã§ãã
IDataSet actualDataSet = databaseConnection.createDataSet(); SortedTable actualTable = new SortedTable(actualDataSet.getTable("book"), new String[]{"price"}); actualTable.setUseComparable(true);
ä»åã¯ãäŸ¡æ Œã§ãœãŒãããŸããããœãŒãé ã®æå®ã¯ã§ããªãããã§ãããšãããããœãŒããè¡ã£ãŠããã®ã¯ããŒã¿ããŒã¹äžã§ã¯ãªã
Javaäžã§ã§ããã
çšæããXMLãã¡ã€ã«ã¯ããã¡ãããããããäŸ¡æ Œã§ãœãŒãããŠãããŸããã
src/test/resources/DbUnitGettingStartedTest.sortTable.xml
<?xml version="1.0" encoding="UTF-8"?> <dataset> <book isbn="978-4295008477" title="æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]" price="2860" publish_date="2020-03-13" category="java"/> <book isbn="978-4774189093" title="Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§" price="3278" publish_date="2017-04-18" category="java"/> <book isbn="978-4798147406" title="詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã" price="3960" publish_date="2016-08-26" category="mysql"/> <book isbn="978-4798161488" title="MySQL培åºå ¥é 第4ç MySQL 8.0察å¿" price="4180" publish_date="2020-07-06" category="mysql"/> <book isbn="978-4621303252" title="Effective Java 第3ç" price="4400" publish_date="2018-10-30" category="java"/> </dataset>
ãã£ãŠãã¡ãã¯ãœãŒãããªããŠãããã®ã§ããããœãŒãããå Žåã¯ã³ã¡ã³ãã¢ãŠãããŠããç®æã®ãããªæãã«ãªããŸãã
ITable expectedTable = expectedDataSet.getTable("book"); //SortedTable expectedTable = new SortedTable(expectedDataSet.getTable("book"), new String[]{"price"}); //expectedTable.setUseComparable(true);
ã¢ãµãŒã·ã§ã³æã«ãããŒã¿ã®ãœãŒãçµæãåããªããšãã¹ãã倱æããã®ã§æ³šæããŸãããã
DatabaseOperation
ã¯ã©ã¹ã䜿ã£ãŠã¿ã
æåŸã«ãDatabaseOperation
ã¯ã©ã¹ã䜿ã£ãŠã¿ãŸãããããã®ã¯ã©ã¹ã䜿ããšããã¹ãååŸã§è¡ããããããªããŒã¿ã»ããã®æäœãç°¡åã«
è¡ããŸãã
ãã ãä»åã¯ãã¹ãååŸã§å®è¡ããã®ã§ã¯ãªãããã¹ãã³ãŒãå
ã§ã¢ãµãŒã·ã§ã³ãšçµã¿åãããŠDatabaseOperation
ã¯ã©ã¹ã®æ©èœèªäœã
確èªããŠãããããªãšæããŸãã
æåã«ãã¹ãã³ãŒããäžžããšèŒããŠããŸããŸãããã
src/test/java/org/littlewings/dbunit/DbUnitOperationTest.java
package org.littlewings.dbunit; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.time.LocalDate; import java.util.List; import org.dbunit.Assertion; import org.dbunit.DatabaseUnitException; import org.dbunit.database.DatabaseConnection; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.ITable; import org.dbunit.dataset.xml.FlatXmlDataSetBuilder; import org.dbunit.ext.mysql.MySqlConnection; import org.dbunit.operation.DatabaseOperation; import org.junit.jupiter.api.Assertions; import org.junit.jupiter.api.BeforeAll; import org.junit.jupiter.api.Test; public class DbUnitOperationTest { @BeforeAll public static void setUpAll() { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); DomaConfig .singleton() .getTransactionManager() .required(() -> bookDao.createTableIfExistsRecreate()); } @Test public void test() throws SQLException, DatabaseUnitException { DatabaseConnection databaseConnection = null; try (Connection conn = DriverManager .getConnection( "jdbc:mysql://172.17.0.2:3306/practice?characterEncoding=utf-8&characterSetResults=utf-8&connectionCollation=utf8mb4_0900_bin", "kazuhira", "password");) { databaseConnection = new MySqlConnection(conn, null); // INSERT Operation IDataSet insertedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitOperationTest.test.xml")); DatabaseOperation.INSERT.execute(databaseConnection, insertedDataSet); // JUnit 5 Assersions Assertions.assertEquals(2, databaseConnection.getRowCount("book")); DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); }); Assertions.assertEquals(7, databaseConnection.getRowCount("book")); // REFRESHïŒupdate or insertïŒ Operation IDataSet refreshedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitOperationTest.test.xml")); DatabaseOperation.REFRESH.execute(databaseConnection, refreshedDataSet); // JUnit 5 Assersions Assertions.assertEquals(7, databaseConnection.getRowCount("book")); ITable categories = databaseConnection.createQueryTable("result", "select category from book group by category order by category"); Assertions.assertEquals("java", categories.getValue(0, "category")); Assertions.assertEquals("linux", categories.getValue(1, "category")); Assertions.assertEquals("mysql", categories.getValue(2, "category")); // CLEAN_INSERTïŒdelete all ïŒ insertïŒ Operation DatabaseOperation.CLEAN_INSERT.execute(databaseConnection, insertedDataSet); IDataSet actualDataSet = databaseConnection.createDataSet(); // JUnit 5 Assersions Assertions.assertEquals(2, databaseConnection.getRowCount("book")); // DbUnit Assertion Assertion.assertEquals(insertedDataSet, actualDataSet); } finally { if (databaseConnection != null) { databaseConnection.close(); } } } }
å ã»ã©ã®ãã¹ãã³ãŒããšåæ§ã1åç®ã«ããŒãã«ãdrop ïŒ createããããšã«ããŸãã
@BeforeAll public static void setUpAll() { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); DomaConfig .singleton() .getTransactionManager() .required(() -> bookDao.createTableIfExistsRecreate()); }
XMLãã¡ã€ã«ã§ã®ããŒã¿ã»ãããçšæã
src/test/resources/DbUnitOperationTest.test.xml
<?xml version="1.0" encoding="UTF-8"?> <dataset> <book isbn="978-4797397642" title="æ¬æ°ã§åŠã¶ Linuxå®è·µå ¥é ãµãŒãéçšã®ããã®æ¥åã¬ãã«ç®¡çè¡" price="3278" publish_date="2019-05-30" category="linux"/> <book isbn="978-4798155760" title="UbuntuãµãŒããŒåŸ¹åºå ¥é" price="4180" publish_date="2018-06-13" category="linux"/> </dataset>
DatabaseOperation
ã¯ã©ã¹ã䜿ã£ãããŒã¿ã»ããã®æäœã«çµã£ãŠèŠãŠãããŸãã
ãŸãã¯ãDatabaseOperation.INSERT
ã§çšæããXMLãã¡ã€ã«ã§ã®ããŒã¿ã»ããã®å
容ãinsertããŸãã
// INSERT Operation IDataSet insertedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitOperationTest.test.xml")); DatabaseOperation.INSERT.execute(databaseConnection, insertedDataSet); // JUnit 5 Assersions Assertions.assertEquals(2, databaseConnection.getRowCount("book"));
DatabaseConnection#getRowCount
ã䜿ããšã察象ã®ããŒãã«ã®ã¬ã³ãŒãæ°ãååŸã§ããŸãã
ããã§ã¯ãDatabaseOperation.INSERT#execute
ã¡ãœããå®è¡åŸã«ãçšæããXMLãã¡ã€ã«ã«å«ãŸããããŒã¿ä»¶æ°ãã€ãŸã2ã¬ã³ãŒãã
ååšããŠããããšã確èªã§ããŸããã
次ã«ãDoma 2ã䜿ã£ãŠããŒã¿ãç»é²ã
DomaConfig .singleton() .getTransactionManager() .required(() -> { BookDao bookDao = new BookDaoImpl(DomaConfig.singleton()); List<Book> books = List.of( Book.create("978-4295008477", "æ°äžä»£Javaããã°ã©ãã³ã°ã¬ã€ã[Java SE 10/11/12/13ãšèšèªæ¡åŒµãããžã§ã¯ã]", 2860, LocalDate.of(2020, 3, 13), "java"), Book.create("978-4621303252", "Effective Java 第3ç", 4400, LocalDate.of(2018, 10, 30), "java"), Book.create("978-4774189093", "Javaæ¬æ Œå ¥é ãã¢ãã³ã¹ã¿ã€ã«ã«ããåºç€ãããªããžã§ã¯ãæåã»å®çšã©ã€ãã©ãªãŸã§", 3278, LocalDate.of(2017, 4, 18), "java"), Book.create("978-4798161488", "MySQL培åºå ¥é 第4ç MySQL 8.0察å¿", 4180, LocalDate.of(2020, 7, 6), "mysql"), Book.create("978-4798147406", "詳解MySQL 5.7 æ¢ãŸãã¬é²åã«ä¹ãé ããªãããã®ãã¯ãã«ã«ã¬ã€ã", 3960, LocalDate.of(2016, 8, 26), "mysql") ); books.forEach(bookDao::insert); }); Assertions.assertEquals(7, databaseConnection.getRowCount("book"));
ããŒãã«å ã«ã¯7件ã®ããŒã¿ãããããšã確èªã§ããŸãã
DatabaseOperation.REFRESH
ã䜿ããšã察象ã®ããŒã¿ã»ããã䜿ã£ãŠupdateãããŒã¿ããªããã°insertãè¡ããŸãã
// REFRESHïŒupdate or insertïŒ Operation IDataSet refreshedDataSet = new FlatXmlDataSetBuilder() .build(getClass().getClassLoader().getResourceAsStream("DbUnitOperationTest.test.xml")); DatabaseOperation.REFRESH.execute(databaseConnection, refreshedDataSet); // JUnit 5 Assersions Assertions.assertEquals(7, databaseConnection.getRowCount("book")); ITable categories = databaseConnection.createQueryTable("result", "select category from book group by category order by category"); Assertions.assertEquals("java", categories.getValue(0, "category")); Assertions.assertEquals("linux", categories.getValue(1, "category")); Assertions.assertEquals("mysql", categories.getValue(2, "category"));
ãã£ãŠãããŒã¿ã®æ°ã¯å€ãã£ãŠããŸããã
æåŸã«DatabaseOperation.CLEAN_INSERT
ããã¡ãã¯å¯Ÿè±¡ã®ããŒã¿ã»ããã®ç¯å²ãå
šä»¶åé€ããåŸãæå®ã®ããŒã¿ã»ããã®ããŒã¿ã
ç»é²ããŸãã
// CLEAN_INSERTïŒdelete all ïŒ insertïŒ Operation DatabaseOperation.CLEAN_INSERT.execute(databaseConnection, insertedDataSet); IDataSet actualDataSet = databaseConnection.createDataSet(); // JUnit 5 Assersions Assertions.assertEquals(2, databaseConnection.getRowCount("book")); // DbUnit Assertion Assertion.assertEquals(insertedDataSet, actualDataSet);
ãã£ãŠãä»åDoma 2ã䜿ã£ãŠç»é²ããããŒã¿ã¯åé€ãããXMLãã¡ã€ã«ã§çšæãã2件ã ããæ®ã£ãŠããç¶æ ã«ãªããŸãã
ãããã®æäœã¯ããã®ããã«ç¬ç«ããã¯ã©ã¹ããã®çµã¿åããã§å®çŸãããŠããŸãã
/** @see DummyOperation */ public static final DatabaseOperation NONE = new DummyOperation(); /** @see UpdateOperation */ public static final DatabaseOperation UPDATE = new UpdateOperation(); /** @see InsertOperation */ public static final DatabaseOperation INSERT = new InsertOperation(); /** @see RefreshOperation */ public static final DatabaseOperation REFRESH = new RefreshOperation(); /** @see DeleteOperation */ public static final DatabaseOperation DELETE = new DeleteOperation(); /** @see DeleteAllOperation */ public static final DatabaseOperation DELETE_ALL = new DeleteAllOperation(); /** @see TruncateTableOperation */ public static final DatabaseOperation TRUNCATE_TABLE = new TruncateTableOperation(); /** * @see DeleteAllOperation * @see InsertOperation * @see CompositeOperation */ public static final DatabaseOperation CLEAN_INSERT = new CompositeOperation( DELETE_ALL, INSERT);
åDatabaseOperation
ã¯ã©ã¹ã®å®è£
ãæ°ã«ãªãã®ã§ããã°ãããã¥ã¡ã³ãã®èª¬æããã³ãœãŒã¹ã³ãŒããèŠããšè¯ãã§ãããã
Core Components / DatabaseOperation
https://sourceforge.net/p/dbunit/code.git/ci/dbunit-2.7.3/tree/src/main/java/org/dbunit/operation/
ãã®ä»
ä»åã¯æ±ããŸããã§ãããIDatabaseTester
ã€ã³ã¿ãŒãã§ãŒã¹ãšãããããŸã§èŠãŠãããããªæäœãããçšåºŠãŸãšããããã¯ã©ã¹ã
ååšããŸããå®è£
ãšããŠã¯JdbcDatabaseTester
ã¯ã©ã¹ãDataSourceDatabaseTester
ã¯ã©ã¹ãªã©ã䜿ãã®ããªãšæããŸãã
æ¬æ¥ã¯DBTestCase
ã¯ã©ã¹ãç¶æ¿ã§ããªãå Žåã«äœ¿ããããªã®ã§ãããDBTestCase
ã¯ã©ã¹ã¯å€ãJUnitåãã®ãã®ãªã®ã§ãçŸåšã¯
䜿ãããšã¯ãªãã§ãããã
IDatabaseTester
ã€ã³ã¿ãŒãã§ãŒã¹ã®å®è£
ã¯ã©ã¹ã䜿ãããèªåã§äœæãããããŠãã®ãããã®åŠçã¯ããŸãæ±ããããã«èããŠ
ãããã§ããããã
ãŸãšã
DbUnitãè©ŠããŠã¿ãŸããã
æåã¯Webãµã€ããèŠãŠãæŽæ°ãããŠããªãã®ããªïŒããšæã£ãã®ã§ãããçŸåšãJUnit 5ã«ã¯å¯Ÿå¿ããŠããªããšã¯ããã¡ã³ããã³ã¹ã¯
ç¶ããŠããããã§ããã䜿ãæ¹ãæŒãããŠãããŠãããããªãšæããŸããã
ä»åã¯ããããªãšããã§ã