How to use built-in h2db with spring boot

1.First of all

Due to various reasons, I decided to make a spring boot web application. At that time, I decided to use the built-in h2db, so I summarized the problems and investigations at that time. By the way, the version of spring boot is 1.5.17 of 1 series.

2. Add dependency to pom.xml

Add spring-boot-starter-jdbc and h2 to the dependencies of pom.xml. When creating a project with Spring Initializr, it will be the same if you add the following two.

• H2
• JDBC

Dependencies to add

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

3. Add settings to application.properties (.yml)

Add the following settings to application.properties or application.yml. The settings are the following three.

• datasource settings
• Connection pool settings (if needed)
• H2 handy tool settings (if needed)

src/main/resources/application.propertie

# datasource
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:./h2db/sampledb
spring.datasource.username=username
spring.datasource.password=password

# connection pool use tomcat
spring.datasource.tomcat.maxActive=10
spring.datasource.tomcat.maxIdle=10
spring.datasource.tomcat.minIdle=10
spring.datasource.tomcat.initialSize=10
spring.datasource.tomcat.defaultAutoCommit=false

# h2 for debug tool
# spring.h2.console.enabled=true
# spring.h2.console.path=/h2-console
# spring.h2.console.settings.web-allow-others=true

The contents of each setting are described in Explanation of application.properties of official guideline. I will. The points are explained below.

Use connection pool only as a pool

spring.datasource.tomcat.*= # Tomcat datasource specific settings Therefore, I defined driverClass, url, username, password as well as the definition of datasource of Tomcat, and deleted spring.datasource.url etc. This seems to be useless. It seems correct to define it only as a pool.

Embedded DB, that is, the same JVM process but need a pool? I'm sure some people think that, but the author is also sweating. I set it because it is necessary for general web applications. The effect of not measuring the performance is unknown.

error

# datasource
# nothing see connection pool

# connection pool use tomcat
spring.datasource.tomcat.driver-class-name=org.h2.Driver
spring.datasource.tomcat.url=jdbc:h2:./h2db/sampledb
spring.datasource.tomcat.username=username
spring.datasource.tomcat.password=password
spring.datasource.tomcat.maxActive=10
spring.datasource.tomcat.maxIdle=10
spring.datasource.tomcat.minIdle=10
spring.datasource.tomcat.initialSize=10
spring.datasource.tomcat.defaultAutoCommit=false

Use h2's useful features for debugging

You can also use the web management function of h2 in the embedded database. The embedded database runs in the same JVM process as the application, and since it is a dedicated DB and cannot be connected to the network, it was difficult to debug. You can use the Web management function to refer to or change the data in the current DB.

# h2 for debug tool
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.h2.console.settings.web-allow-others=true

Since it is a debug function, it is better to enable it in the system properties instead of setting it in application.propeties. In this case, even if you forget to modify the configuration file, you can rest assured that the debug function will remain disabled.

java -jar your-app.jar -Dspring.h2.console.enabled=true -Dspring.h2.console.path=/h2-console -Dspring.h2.console.settings.web-allow-others=true

By the way, spring.h2.console.settings.web-allow-others is whether to allow access from a remote (other PC). The default false can only be accessed locally, that is, from the PC running the app.

4. DB and table preparation

h2db is an in-memory DB, but embedded DB allows data to be persistent. This means that it would be a problem if it was initialized every time it was started.

• h2db is automatically created if the db specified in the connection string does not exist
• Do not create (initialize) new if it exists
• Define initialization SQL with CREATE TABLE IF NOT EXISTS
• If the table exists, it will not be recreated
• jdbc: h2: ./h2db/sampledb means to store the DB file in the path (./h2db/sampledb) seen relative to the current directory at runtime.
• ./ is important for relative path specification

5. jdbcTemplate, datasource and transaction control are set automatically

If you use springframework as it is, various settings (Bean definition, AOP, etc.) required for DB access are required, but in the case of spring boot, they are set automatically.

• Datasource Bean is set automatically
• Bean of JdbcTemplate, NamedParameterJdbcTemplate is set automatically
• Immediately you can inject with @Autowired etc.
• Transaction control by @Transactional is enabled

6. Finally

This time, I have summarized the problems and researches on how to use the built-in h2db in the pring boot web application. When I actually tried it, I was surprised because there were fewer settings than I expected. In that sense, spring boot is easy.