How to use H2 embeded database in spring application

H2, as a embeded memory database, is mainly used for development and test phase. H2 also has a web console which is very convenient.

1. Use H2 in Spring boot application

Spring boot has almost everything done for you. You need to do 2 steps, first inluce H2 in you pom.xml.

  <!-- H2 -->
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
   <scope>runtime</scope>
  </dependency>

The version of H2 is managed by spring boot and  scope can be set to runtime because you code should not have any APIs come from H2 itself. Then specify datasource url to use H2 in the application.properties file or in lieu of yaml styled configuration

# define DataSrouce properties 
# use h2 can have a buid in web console http://localhost:8080/h2-console
spring.datasource.url=jdbc:h2:mem:mydb

In spring boot application, there is no need to set username and password for using H2 memory database, because H2's default username is 'sa' (or SA, case insensitive), default password is empty. These default username/password will be auto configured by spring boot.  Also Spring boot will config the H2 web console for you automatcally at http://localhost:8080/h2-console

Use H2 in spring-mvc application (non-spring boot)

In non-spring boot application, which is normall just powered by spring-webmvc and other spring artifacts, using H2 need 3 steps. First is also to include H2 in pom.xml

  <!-- H2 -->
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
   <version>1.4.190</version>
  </dependency>

Secondly,  use H2 in any ORM configuration. For example if your project uses JPA as persistence and uses Hibernate as JPA service provider. The JPA's configuration file /META-INF/persistence.xml may looks like below.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
 xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
 <persistence-unit name="spring-persistence-jpa-tx" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
  <exclude-unlisted-classes>false</exclude-unlisted-classes>
  <properties>
   <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
   <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:mydb" />
   <property name="javax.persistence.jdbc.user" value="sa" />
   <property name="javax.persistence.jdbc.password" value="" />
   
   <!-- Automatically drop then create table -->
   <property name="hibernate.hbm2ddl.auto" value="create" />
   <!-- print out sql  -->
   <property name="hibernate.show_sql" value="true"/>
  </properties>
 </persistence-unit>
</persistence>

Specify the jdbc driver, jdbc url and etc , just like other database.

Finally, and optionally, If you want the H2 web console, you need to add a bean into spring context. In xml-styled spring configuration file, it looks like below.

 <bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer"
  init-method="start" destroy-method="stop">
  <constructor-arg value="-web,-webAllowOthers,-webDaemon,-webPort,8082" />
 </bean>

In java-styled spring configuration, it looks like below.

@Configuration
@ComponentScan
public class JavaConfiguration {
 //... other beans
 
 @Bean(initMethod="start",destroyMethod="stop")
 public org.h2.tools.Server h2WebConsonleServer () throws SQLException {
   return org.h2.tools.Server.createWebServer("-web","-webAllowOthers","-webDaemon","-webPort", "8082");
 }
}

The -webDaemon means run H2 web console in a daemon thread which will not block the application process termination. -webPort can set the web console service port, default is 8082. The web console url is http://localhost:8082 (Different from the web console url in spring boot application)

Use H2 in spring boot application

In a spring boot application, first add H2 to the dependency.

  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.182</version>
    <scope>runtime</scope>
  </dependency>

Choose a proper H2 version to use and also set scope to runtime become you should not expliity use any class from H2 in you application.

Then configure application.properties or application.yml file for spring boot datasource. Following is example in application.yml file.

spring:
  datasource:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password:
    driver-class-name: org.h2.Driver
    platform: h2

  # enable H2 web console and set url for web console
  # http://localhost:8080/console
  h2:
    console:
      enabled: true
      path: /console

The default username for H2 is 'sa', default password is empty.

If you also use spring security in your application, which means spring security is in your pom.xml like below.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-security</artifactId>
</dependency>

Then you need to add a line in your spring security configuration to make H2 web console working.

@EnableWebSecurity
 
public class SecurityConfig extends WebSecurityConfigurerAdapter {
  Logger logger = LoggerFactory.getLogger(SecurityConfig.class);
 
 //......
 
 
  @Override
  protected void configure(HttpSecurity http) throws Exception {
    http.authorizeRequests().antMatchers("/").permitAll(); 
    http.authorizeRequests().antMatchers("/imgs/**").permitAll(); 
    http.authorizeRequests().antMatchers("/admin/**").hasRole("ADMIN");
    http.authorizeRequests().antMatchers("/**").hasRole("USER").and().formLogin();
  
    // add this line to use H2 web console
    http.headers().frameOptions().disable();
  }
}

Without this line to setup frame in spring security, you will see a empty page after you login the H2  web console.