Read H2 database for unit tests

Overview

We are creating an app to personally manage the performance dates of indie live events and the visitor list with Spring Boot. When implementing a unit test, I prepared an H2 database in memory for the test, but it didn't read at all, so I was addicted to it.

environment

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

What i did

Added "runtimeOnly'com.h2database: h2'" in the dependencies of gradle.


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'
}

Create test.properties under src/test/resources for unit test implementation.

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=

Also specify the table and data to be created at test startup under 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, 'American Village', 'Capacity 10');
INSERT INTO live_list VALUES(20210105, 'Tokyo Egypt', 'Capacity 50');

INSERT INTO customers_list VALUES(NULL, 20201210, 'Gonzo Tanaka', 1, 'Came recently');
INSERT INTO customers_list VALUES(NULL, 20210105, 'Shungoro Mitarai', 3, 'Always come');

Prepared so far and implemented a simple test in the Mapper class. I checked if the data prepared in the DB can be called.

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 All performance date lists can be obtained() throws Exception {
		List<LiveList> acutual = mapperTest.liveFindAll();
		assertThat(acutual.size()).isEqualTo(2);
	}

	@Test
public void Being able to get all the visitors list() throws Exception {
		List<CustomerList> acutual = mapperTest.customerFindAll();
		assertThat(acutual.size()).isEqualTo(2);
	}
}

As a result of execution, the following error occurred.

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, 'Gonzo Tanaka', 1, 'Came recently') INSERT INTO customers_list VALUES(0, 20210105, 'Shungoro Mitarai', 3, 'Come a lot'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException:SQL statement has syntax error"INSERT INTO CUSTOMERS_LIST VALUES(0, 20201210, 'Gonzo Tanaka', 1, 'Came recently') INSERT[*] INTO CUSTOMERS_LIST VALUES(0, 20210105, 'Shungoro Mitarai', 3, 'Come a lot')"
Syntax error in SQL statement "INSERT INTO CUSTOMERS_LIST VALUES(0, 20201210, 'Gonzo Tanaka', 1, 'Came recently') INSERT[*] INTO CUSTOMERS_LIST VALUES(0, 20210105, 'Shungoro Mitarai', 3, 'Come a lot')"; SQL statement:
INSERT INTO customers_list VALUES(0, 20201210, 'Gonzo Tanaka', 1, 'Came recently') INSERT INTO customers_list VALUES(0, 20210105, 'Shungoro Mitarai', 3, 'Come a lot') [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

Cause

In conclusion, the data and table information to create in test.properties was not specified. Reference https://qiita.com/niwasawa/items/af6c5485c1c71d55d7dd

In test.properties

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

By adding, the test works normally.

When I read the error statement, it was displayed as a SQL syntax error, so I was desperately investigating the mistake in the SQL statement, so it took a long time. In short, it seems that the data you are trying to INSERT in data.sql was thrown out as an error as to where to insert it. As a lesson for the future, I will not really accept the translation of a grammatical error, but will take a closer look around the path specification.

Recommended Posts

Read H2 database for unit tests
Set console timeout (session timeout) for H2 Database
How to create a database for H2 Database anywhere
[Rails] How to implement unit tests for models
[Ralis] About unit tests
Use Spring Test + Mockito + JUnit 4 for Spring Boot + Spring Retry unit tests
Java unit tests with Mockito