加载用于单元测试的H2数据库
概括
我正在开发一个用于管理独立音乐演出活动日期和访客清单的应用程序，使用Spring Boot。
在实施单元测试时，我为了测试而准备了一个H2内存数据库，但它根本不读取任何数据，让我非常困惑，所以我做了一些备忘录。
环境
操作系统 Windows10
集成开发环境 Eclipse 版本：2020-06 (4.16.0)
Java 11
Spring Boot 2.4.0
我做过的事情 (Wǒ zuò guò de
在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。
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 目录下指定要在测试启动时创建的表和数据。
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)
);
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类中实现了简单的测试。
确认调用数据库中准备好的数据是否可用。
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
The reason for something. (某事的原因)
以上可以得出结论，在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操作的数据无法确定应该插入到何处，所以出现了错误。作为以后的教训，我将不再仅仅相信语法错误，并且会认真检查路径的指定。
