Configuring Spring Boot with H2 Database

In this Spring boot tutorial, we will learn Spring boot auto-configuration of H2 database and how to customize various database options. We will also take a look at H2 web console.

1. Overview

H2 database supports disk-based and in-memory databases and is intended for unit testing or POC purposes. By default, the in-memory databases are volatile, but H2 provides the capability to persist and retain data between two sessions by storing the data in files.

Like other relational databases, it supports transactions, multiple connections, encryption, locking and various security features. It has very low memory requirements and database closing can be delayed or disabled to improve the performance.

Spring boot provides inbuilt auto-configuration support for H2 that can be further customized using simple properties configuration.

The H2 console browser view provides GUI for all DBMS operations we do in other databases.

2. Dependencies

To use H2 in a Sprong boot application, we need to include its dependency in the project. Also, to enable other JPA features, such as DataSource, connections and pooling, we need to add spring-boot-starter-data-jpa as well.

Note that we need H2 dependency only in runtime, it is not needed during compilation.

Use H2’s latest verion 2.0.x which have fixes for known vulnerability CVE-2021-23463.

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

implementation group: 'com.h2database', name: 'h2', version: '2.0.204'
implementation group: 'org.springframework.boot', name: 'spring-boot-starter-data-jpa'

3. H2 Database Configuration

3.1. Default Auto-configuration

If we do not provide any custom properties in application.properties file, by default, Spring boot JPA starter configures HikariDataSource connection pooling and H2 database with the following options:

Driver Class : org.h2.Driver
JDBC URL : Auto-generated
User Name : sa
Password : <blank>
H2 Console : Disabled

To get the auto-generated JDBC URL, enable the debug logging with logging.level.root=DEBUG in properties file and watch out in console logs for the generated URL.

In given example, the JDBC URL name is c596a281-f6a7-4552-b66b-ce2840d8de28.

2021-12-25 18:12:31.152 DEBUG - HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:testdb user=SA
...
2021-12-25 18:12:31.160 INFO  - H2 console available at '/h2'. Database available at 'jdbc:h2:mem:testdb'

3.2. Custom H2 Configuration

We can customize how Spring boot initializes the H2 DB using the respective properties configuration.

spring.h2.console.enabled : Enables the console view.
spring.h2.console.path : the h2 console URL.
spring.datasource.url : Custom database URL.
spring.datasource.driverClassName : Custom database driver.
spring.datasource.username : Custom DB username to connect to database.
spring.datasource.password : Custom DB password to connect to database.
spring.jpa.database-platform : Specifies which database we are going to use. Dialect allows Hibernate to generate SQL optimized for a particular relational database.

H2 supports multiple connection modes and connection settings. This is achieved using different database URLs. Settings in the URLs are not case-sensitive.

# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2

# Configure H2 Datasource
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

4. Loading Initial Data

Spring boot JPA, if found on the classpath, uses schema.sql and data.sql files script-based initialization for embedded databases such as the H2 database.

We only need to put these files in the resources folder with our DDL and DML scripts and the scripts will be executed when the Spring boot application starts.

DROP TABLE IF EXISTS TBL_USERS;

CREATE TABLE TBL_USERS (
  id INT AUTO_INCREMENT  PRIMARY KEY,
  first_name VARCHAR(250) NOT NULL,
  last_name VARCHAR(250) NOT NULL,
  email VARCHAR(250) DEFAULT NULL
);

INSERT INTO TBL_USERS (first_name, last_name, email) VALUES
  ('Alex', 'Kole', '[email protected]'),
  ('Brian', 'Smith', '[email protected]'),
  ('Johnny', 'Wu', '[email protected]');

Note that if the application uses Hibernate Entity classes then we only need to have data.sql file because schema will be generated for the entity classes, automatically.

If we have entity classes and schema.sql both, they may conflict during schema creation so it is better to have one strategy.

Set spring.jpa.hibernate.ddl-auto=none to disable hibernate schema generation, and set spring.sql.init.mode=never to disable script-based schema generation.

By default, Spring Boot enables the fail-fast feature of its script-based database initializer. This means that, if the scripts cause exceptions, the application fails to start. We can customize the behavior by setting spring.sql.init.continue-on-error property.

Please refer to official Spring documentation on initializing databases using scripts.

5. H2 Console

The H2 database provides a browser-based console that Spring Boot can auto-configure for you. The console is auto-configured when all the following conditions are met:

The application is a servlet-based web application.
com.h2database:h2 dependency is on the classpath.
Application has Spring Boot developer tools dependency included in the classpath.

If we are not using Spring Boot’s developer tools but would still like to make use of H2’s console, we can configure the spring.h2.console.enabled property with a value of true. Default URL is /h2-console/.

The H2 console is only intended for use during development, so we should take care to ensure that spring.h2.console.enabled is not set to true in production.

6. FAQs

6.1. java.lang.IllegalArgumentException: ‘script’ must not be null or empty

We can get this error when the SQL files in the classpath are empty.

Caused by: java.lang.IllegalArgumentException: 'script' must not be null or empty
    at org.springframework.util.Assert.hasText(Assert.java:289) ~[spring-core-5.3.13.jar:5.3.13]
    at org.springframework.jdbc.datasource.init. ScriptUtils.splitSqlScript(ScriptUtils.java:636) ~[spring-jdbc-5.3.13.jar:5.3.13]
    at org.springframework.jdbc.datasource.init. ScriptUtils.executeSqlScript(ScriptUtils.java:252) ~[spring-jdbc-5.3.13.jar:5.3.13]
    ... 33 common frames omitted

Make sure we have valid SQL statements in the schema and data files to fix this error.

6.2. H2 console not available in browser

By default, the H2 console is disabled if we do not have the spring boot devtools module included in the project. To fix this issue, we have two options:

Either, include the boot devtools module in the project
Or, enable the console using the property spring.h2.console.enabled=true

7. Conclusion

In this tutorial, we learned about how Spring boot auto-configures the H2 database and how we can customize those defaults using simple properties configuration. Additionally, we learned to seed the database with initial data for testing and demo purpose.

We also learned to enable the H2 console and the URL to access it in the browser.

You can check out the source code of these examples over Github.

Spring Boot with H2