1. Introduction
The H2 database engine is a popular open-source database built on Java. In this brief tutorial, we’ll learn how to automatically create a schema for the H2 in-memory database.
2. What Is H2?
The H2 database engine is a Java-based database that is both SQL and JDBC compliant. It has a number of features that set it apart from other relational databases:
- Persistence: it can operate as a purely in-memory database or using a file system.
- Mode: runs as a stand-alone server or embedded inside another application.
Both of these characteristics make H2 a great choice for development and testing purposes. However, because of its transient nature, it can also present some challenges.
When connecting to an H2 in-memory database, the schema may not exist. This is because in-memory databases are transient in nature and only survive as long as the application they are running inside is also running. Once that application is terminated, the entire contents of the in-memory database are lost.
Next, we’ll see a few different ways to initialize an H2 in-memory database when connecting to it.
3. Automatically Creating Schemas in H2
There are several approaches for automatically creating schemas for H2 in-memory databases. As with most things, there are pros and cons to each, and the choice of which one to use depends on multiple factors.
3.1. Plain Java
The example below shows how to initialize an in-memory H2 database with plain Java code and JDBC. This is a good option for applications that aren’t using Spring or other frameworks that provide database connectivity:
Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung",
"admin",
"password");
In the above example, we use the connection URL to specify the schema to create. We can also pass additional commands to further initialize the database:
Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;CREATE TABLE users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);",
"admin",
"password");
Because all of these commands can make the URL difficult to read, H2 also supports initializing an in-memory database by referencing an SQL file. First, we create the file with the initialization statements:
CREATE SCHEMA IF NOT EXISTS baeldung;
SET SCHEMA baeldung;
CREATE TABLE users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);
Then we use a slightly modified connection URL to reference the file:
Connection conn = DriverManager.getConnection(
"jdbc:h2:mem:baeldung;INIT=RUNSCRIPT FROM 'h2init.sql';",
"admin",
"password");
3.2. Spring Boot
When working with Spring Boot applications, we can also utilize familiar Spring data properties to initialize H2 in-memory databases.
First, we can provide all of the initialization statements in the URL itself, just like above. We start by defining the Spring properties for the H2 data source:
spring.datasource.url=jdbc:h2:mem:baeldung;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;
Then we can use the default Datasource bean as we do in any normal application:
public void initDatabaseUsingSpring(@Autowired DataSource ds) {
try (Connection conn = ds.getConnection()) {
conn.createStatement().execute("create table users (name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL);");
conn.createStatement().execute("insert into users (name, email) values ('Mike', '[email protected]')");
}
catch (Exception e) {
e.printStackTrace();
}
}
Just like with plain Java code, we can also reference a SQL file in the connection URL that contains all of the initialization statements. All we have to do is update our properties:
spring.datasource.url=jdbc:h2:mem:baeldung;INIT=RUNSCRIPT FROM 'src/main/resources/h2init.sql';
Notably, H2 doesn’t load resources through Spring Boot. Therefore, we have to reference the file path in its full context of where the application is running.
And now we can use the Datasource like before, but without having to initialize the schema first:
private void initDatabaseUsingSpring(@Autowired DataSource ds) {
try (Connection conn = ds.getConnection()) {
conn.createStatement().execute("insert into users (name, email) values ('Mike', '[email protected]')");
}
catch (Exception e) {
e.printStackTrace();
}
}
Finally, when using Spring Boot, we can also leverage the SQL init mode without relying on H2 features. We simply rename our initialization file to data.sql and change our properties slightly:
spring.datasource.url=jdbc:h2:mem:baeldung
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=password
spring.sql.init.mode=embedded
We should note that our properties make no mention of a schema or initialization file.
3.3. Spring XML
Additionally, if we are using plain Spring XML to configure a Datasource, we can include the initialization statements there as well.
Let’s see how to create the schema:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="org.h2.Driver"/>
<property name="url" value="jdbc:h2:mem:testdb;INIT=CREATE SCHEMA IF NOT EXISTS baeldung\\;SET SCHEMA baeldung;"/>
<property name="username" value="admin"/>
<property name="password" value="password"/>
</bean>
As we saw before, having multiple initialization statements in the URL can make it difficult to read the properties. Therefore, it’s a good idea to put them into a single SQL file and reference that in the URL:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="org.h2.Driver"/>
<property name="url" value="jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'src/main/resources/h2init.sql';"/>
<property name="username" value="admin"/>
<property name="password" value="password"/>
</bean>
4. Conclusion
The H2 in-memory database is one of the more popular in-memory and embedded database options for Java developers. Because it’s fast and has a small footprint, it is great for use cases like software tests and automated pipelines.
In this article, we saw several ways to ensure that our H2 in-memory database is automatically initialized and ready to use for querying when our application starts. Whether we use plain JDBC or Spring framework, it only takes a few lines of configuration to ensure our in-memory database is fully initialized and ready to use on startup.
As always, the code examples above can be found over on GitHub.
