LoginSignup
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@keitaUenishi(keita uenishi)

単体テスト用のH2データベース読み込み

Posted at

概要

Spring Bootでインディーズライブイベントの公演日と、来客リストを個人管理するアプリを作成中。
単体テストを実装する際、テスト用にH2データベースをインメモリで用意したものの全く読み込んでくれずめちゃくちゃハマったのでメモ。

環境

OS Windows10
IDE Eclipse Version: 2020-06 (4.16.0)
java 11
Spring Boot 2.4.0

やったこと

gradleのdependencies内に「runtimeOnly 'com.h2database:h2'」を追加。


dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-validation'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.4'
	implementation 'org.thymeleaf.extras:thymeleaf-extras-springsecurity5'
	implementation 'org.springframework.boot:spring-boot-starter-security'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'mysql:mysql-connector-java'
	runtimeOnly 'com.h2database:h2'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testImplementation 'org.springframework.security:spring-security-test'
	testImplementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter-test:2.1.4'
}

単体テスト実装用に src/test/resources 配下にtest.propertiesを作成。

test.properties
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=TRUE;MODE=MySQL
spring.datasource.username=sa
spring.datasource.password=

こちらも src/test/resources 配下にテスト起動時に作成するテーブルとデータを指定。

schema.sql
CREATE TABLE IF NOT EXISTS live_list(
		dateId INT(10) PRIMARY KEY NOT NULL,
		place VARCHAR(100) NOT NULL ,
		remarks VARCHAR(200)
		);

CREATE TABLE IF NOT EXISTS customers_list(
		id INT(10) AUTO_INCREMENT PRIMARY KEY NOT NULL,
		dateId INT(10) NOT NULL,
		name VARCHAR(50) NOT NULL ,
		number INT(5) NOT NULL ,
		remarks VARCHAR(200)
		);
data.sql
INSERT INTO live_list VALUES(20201210, 'アメリカ村', 'キャパ10');
INSERT INTO live_list VALUES(20210105, '東京エジプト', 'キャパ50');

INSERT INTO customers_list VALUES(NULL, 20201210, '田中権蔵', 1, '最近来た');
INSERT INTO customers_list VALUES(NULL, 20210105, '御手洗俊五郎', 3, 'いつもくる');

ここまで用意し、Mapperクラスに簡単なテストを実装。
DBに用意したデータが呼び出せるか確認した。

LiveListMapperTest.java
package product.MapperTest;

import static org.assertj.core.api.Assertions.*;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.boot.test.autoconfigure.MybatisTest;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.TestPropertySource;
import org.springframework.test.context.junit4.SpringRunner;

import product.domain.CustomerList;
import product.domain.LiveList;
import product.mapper.LiveListMapper;

@RunWith(SpringRunner.class)
@MybatisTest
@TestPropertySource(locations = "classpath:test.properties")
public class LiveListMapperTest {

	@Autowired
	private LiveListMapper mapperTest;

	@Test
	public void 公演日リストが全件取得できること() throws Exception {
		List<LiveList> acutual = mapperTest.liveFindAll();
		assertThat(acutual.size()).isEqualTo(2);
	}

	@Test
	public void 来場客リストが全件取得できること() throws Exception {
		List<CustomerList> acutual = mapperTest.customerFindAll();
		assertThat(acutual.size()).isEqualTo(2);
	}
}

実行した結果、下記のエラーが出た。

java.lang.IllegalStateException: Failed to load ApplicationContext
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:132) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:123) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:118) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.prepareTestInstance(DependencyInjectionTestExecutionListener.java:83) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.boot.test.autoconfigure.SpringBootDependencyInjectionTestExecutionListener.prepareTestInstance(SpringBootDependencyInjectionTestExecutionListener.java:43) ~[spring-boot-test-autoconfigure-2.4.0.jar:2.4.0]
	at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:244) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:227) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289) ~[spring-test-5.3.1.jar:5.3.1]
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:246) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) ~[spring-test-5.3.1.jar:5.3.1]
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) ~[spring-test-5.3.1.jar:5.3.1]
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413) ~[org.junit_4.13.0.v20200204-1500.jar:4.13]
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) ~[spring-test-5.3.1.jar:5.3.1]
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89) ~[.cp/:na]
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41) ~[.cp/:na]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:542) ~[.cp/:na]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:770) ~[.cp/:na]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:464) ~[.cp/:na]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210) ~[.cp/:na]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #3 of URL [file:/C:/Users/keita/Documents/pleiades-2020-06-java-win-64bit-jre_20200702/pleiades/workspace/TicketManegerTool/bin/default/data.sql]: INSERT INTO customers_list VALUES(0, 20201210, '田中権蔵', 1, '最近来た') INSERT INTO customers_list VALUES(0, 20210105, '御手洗俊五郎', 3, 'たくさんくる'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: SQLステートメントに文法エラーがあります "INSERT INTO CUSTOMERS_LIST VALUES(0, 20201210, '田中権蔵', 1, '最近来た') INSERT[*] INTO CUSTOMERS_LIST VALUES(0, 20210105, '御手洗俊五郎', 3, 'たくさんくる')"
Syntax error in SQL statement "INSERT INTO CUSTOMERS_LIST VALUES(0, 20201210, '田中権蔵', 1, '最近来た') INSERT[*] INTO CUSTOMERS_LIST VALUES(0, 20210105, '御手洗俊五郎', 3, 'たくさんくる')"; SQL statement:
INSERT INTO customers_list VALUES(0, 20201210, '田中権蔵', 1, '最近来た') INSERT INTO customers_list VALUES(0, 20210105, '御手洗俊五郎', 3, 'たくさんくる') [42000-200]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1788) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:609) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:531) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944) ~[spring-beans-5.3.1.jar:5.3.1]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:925) ~[spring-context-5.3.1.jar:5.3.1]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:588) ~[spring-context-5.3.1.jar:5.3.1]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) ~[spring-boot-2.4.0.jar:2.4.0]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~[spring-boot-2.4.0.jar:2.4.0]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) ~[spring-boot-2.4.0.jar:2.4.0]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) ~[spring-boot-2.4.0.jar:2.4.0]
	at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:122) ~[spring-boot-test-2.4.0.jar:2.4.0]
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99) ~[spring-test-5.3.1.jar:5.3.1]
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124) ~[spring-test-5.3.1.jar:5.3.1]
	... 27 common frames omitted

原因

結論としては、test.properties内で作成するデータとテーブル情報が指定されていなかった。
参考 https://qiita.com/niwasawa/items/af6c5485c1c71d55d7dd

test.properties内に

spring.datasource.schema=classpath:schema.sql
spring.datasource.data=classpath:data.sql

を追加することで、正常にテストが動作するようになった。

エラー文を読んでいるとSQLの文法エラーと表示されるため、必死にSQL文の間違いを調べていたのでめちゃくちゃ時間がかかってしまいました。
要するにdata.sql内でINSERTしようとしているデータが、いったいどこにINSERTすればいいのかということでエラーが吐き出されていたよう。今後の教訓として、文法エラーという訳だけを真に受けず、パスの指定周りをしっかり見ていくことにする。

4
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?