H2 Database Tutorial and expert Tips

H2 DB is an open source lightweight Database written in Java. It is bundled in JBoss EAP and WildFly application server to speed up developing and testing Java applications. Let’s have a look to the main configuration options and how to create an example application using the H2 DB.

What is H2 database?

Firstly, let’s see which are the main features of this Database:

  • It is an Opensource engine written in Java
  • It has an extremely fast DB engine.
  • Supports standard SQL and JDBC API.
  • You can use it in embedded mode, within in your Java Process, or in Server mode.
  • Supports clustering and multi-version concurrency.

H2 Database can be started as standalone Java process or it can be run embedded in an existing Java Process. You would typically use it in embedded mode on application servers like WildFly / JBoss EAP.

The latest version of H2 Database (August 2022) is the following one: 2.1.214

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>test</scope>
</dependency>

You can run the H2 Database in three different modes:

1. Server mode:

jdbc:h2:tcp://localhost/~/test

When using H2 db in server mode (also known as client/server mode) all data is transferred over TCP/IP. Before application can use H2 Database in server mode, you need to start the H2 DB within the same or another machine.

2. Embedded mode:

jdbc:h2:~/test

H2 db in embedded mode will be faster but the downside of it is that no other process can access the Database. In the above connection string, the Data will be saved into the ‘test’ folder, under the user’s home directory.

3. Mixed mode:

The mixed mode combines some features of the embedded and the server mode. The first application connecting to the H2 db does that in embedded mode, but at the same time it starts a server so that other applications can concurrently access the same data, even from different processes.

jdbc:h2:/data/test;AUTO_SERVER=TRUE

When using automatic mixed mode, you can share the JDBC URL for all applications using the DB. By default the server uses any free TCP port. The port can be set manually using AUTO_SERVER_PORT=9090.

Running H2 DB in Server mode

To run H2 Database in Server Mode you need the JAR file containing the DB Classes. You can download it from http://www.h2database.com/html/download.html

You can then Start the DB in Server mode by executing the H2 DB Runnable JAR file:

$ java -jar h2-2.1.214.jar -webAllowOthers -tcpAllowOthers

You will see in the Console the following log:

Web Console server running at http://10.5.126.52:8082 (others can connect)
TCP server running at tcp://10.5.126.52:9092 (others can connect)
PG server running at pg://10.5.126.52:5435 (only local connections)

You can now connect to the H2 database from an external process by pointing to the TCP Address indicated in the log.

Please note, if you are exposing the Web Console to remote servers there's a severe security issue. The issue impacts H2 Releases between 1.1.100 and 2.0.204. Read more here: What you need to know about CVE-2021-42392

At the same time, the H2 DB console will show up in the browser. If using a local machine, simply connect to localhost:8082 to see the Web console.

To see how to monitor H2 Database using the Web Console, check the section “Monitoring H2 Database“.

What if you need to run multiple H2 databases on your machine ? then simply use a different tcpPort and webPort of the database. For example:

$ java -jar h2-.jar -tcpPort 9101 -webAllowOthers -tcpAllowOthers -webPort 8888

Starting H2 DB in Server mode programmatically

As an alternative, you can start also H2 DB in Server Mode programmatically, with one line of code:

import org.h2.tools.Server;
...
// start the H2 DB TCP Server
Server server = Server.createTcpServer("-tcpPort", "9092", "-tcpAllowOthers").start();
...
// stop the H2 DB TCP Server
server.stop();

Running H2 Database in Emebedded mode

The H2 Database is generally run in embedded mode on the top of application server such as WildFly or JBoss EAP. Here is the default Datasource configuration of WildFly which uses H2 database as in-memory database:

<datasources>
	<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
	    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
	    <driver>h2</driver>
	    <security>
		<user-name>sa</user-name>
		<password>sa</password>
	    </security>
	</datasource>
	<drivers>
	    <driver name="h2" module="com.h2database.h2">
		<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
	    </driver>
	</drivers>
</datasources>

Please notice the DB_CLOSE_DELAY=-1 parameter in the database URL. By default, when the last connection is closed, the embedded h2 DB will shutdown. To keep H2 DB alive as long as the virtual machine is alive, we set DB_CLOSE_DELAY to -1.

That is enough for very basic application tests, however it does not persists data on a physical storage, so the data won’t survive a server restart. Additionally, in-memory database are local to the JVM thus accessing the same database using this URL only works within the same virtual machine and class loader environment.

Thus, if you want to get the best from H2 DB and also if you want to monitor the H2 DB you need server mode database, which actually exposes TCP/IP socket for other processes. So let’s modify the Datasource URL String as follows:

<datasources>
	<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
	    <connection-url>jdbc:h2:tcp://localhost/~/test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
	    <driver>h2</driver>
	    <security>
		<user-name>sa</user-name>
		<password>sa</password>
	    </security>
	</datasource>
	<drivers>
	    <driver name="h2" module="com.h2database.h2">
		<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
	    </driver>
	</drivers>
</datasources>

Before starting the application server, start the H2 Database in server mode as indicated in the previous section.

H2 Database example application (JPA)

As an example, we will show a basic JPA applications which persists a sets of key/values in the H2 DB. Here is the persistence.xml file which is adapted for H2 database:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="persistenceUnit"
        transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>com.sample.model.Property</class>

        <jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
            <property name="hibernate.max_fetch_depth" value="3" />
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
        </properties>
    </persistence-unit>
</persistence>
     

This is part of a simple Web application which adds up some Properties using a Web interface:

You can find the source code of this example application at: https://github.com/fmarchioni/mastertheboss/tree/master/h2/h2-demo

Executing SQL Scripts when the JDBC Connection is active

Many times, you require to execute some SQL Initialization DDL, before the application start. For example, if you are using a Database Realm which secures your application, it is required to have the Database tables created. The trick is to include an “INIT” parameter in the JDBC Connection String which contains a “RUNSCRIPT FROM” command as in this example:

jdbc:h2:tcp://localhost/mem:elytron_jdbc_test;DB_CLOSE_DELAY=-1;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:import.sql

You can place the import.sql script wherever it is accessible in the classpath. For example into the resources folder of a Maven project.

Now let’s see how you can monitor the H2 Database, once that our Data is persistent.

Starting and Stopping H2 DB from Maven

It is worth mentioning that you can use the exec-maven-plugin to trigger H2 Database Start and Stop as part of your Integration Tests. Simply define two classes:

  • A StartServer class which starts up the H2 DB as discussed before
  • A StopServer class which stops the H2 DB as discussed before
<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>exec-maven-plugin</artifactId>
  <version>1.6.0</version>
  <executions>
    <execution>
      <!-- start H2 DB before integration tests -->
      <id>start</id>
      <phase>pre-integration-test</phase>
      <goals>
        <goal>java</goal>
      </goals>
      <configuration>
        <mainClass>com.mastertheboss.StartServer</mainClass>
      </configuration>
     </execution>
     <execution>
      <!-- stop H2 DB after integration tests -->
      <id>stop</id>
      <phase>post-integration-test</phase>
      <goals>
        <goal>java</goal>
      </goals>
      <configuration>
        <mainClass>com.mastertheboss.StopServer</mainClass>
      </configuration>
     </execution>
   </executions>
 </plugin>

Monitoring H2 Database

There are several options for monitoring the H2 database. Obviously if you are running H2 in the in-memory mode you should launch a SQL tool running in the same JVM where WildFly is running. On the other hand, if you are running server mode you can monitor externally the Database, just like any other commercial DB.

Using the built-in H2 DB Web console

Double click the h2*.jar which is located in the modules\com\h2database\h2\main of your JBoss/Wildfly distribution.

Also, running the java -jar [H2DatabaseJar] will do.

The H2 web console will prompt. The H2 Console application lets you access a database using a browser at the address http://localhost:8082

There you can monitor your H2 DB or execute SQL. (By the way this web application has also a nice built-in auto completion for your DB objects)

Eclipse H2 database viewer

If you are using Eclipse or JBoss Developer Studio for developing your applications then you can create as well a Datasource view to the H2 Database. Start by creating a new Generic JDBC Connection, including reference to the JDBC Driver file and JDBC Properties:

Next, move to the Data Source explorer view, where you will be able to explore the selected Database and eventually open an SQL ScrapBook:

Using IntelliJ Idea to monitor H2 Database

Much the same way, you can configure H2 DB Connectivity using IntelliJ idea. In order to do that, you need to install the Database Navigator plugin from the Settings | Plugin Panel:

Then restart your IDE. Open the DB Browser View and configure the H2 Database in it:

That’s all! Now you can check your H2 Database from the DB Browser View:

Troubleshooting:

Sorry, remote connections (‘webAllowOthers’) are disabled on this server.

If you get this error code it means that you are trying to access the Web console by a remote address (or also by the same host but using the IP address). You can enable remote connections in the h2 console property file which are stored in a configuration file called .h2.server.properties in you user home directory. Alternatively, just use http://localhost:8082 to access the Web console.

Preventing DB Reset

H2, by default, drops your in memory database if there are no active connections. To prevent this add DB_CLOSE_DELAY=-1 to the url (use a semicolon as a separator) eg: jdbc:h2:mem:play;MODE=MYSQL;DB_CLOSE_DELAY=-1

Uppercase for DB Tables

H2 DB, by default, creates tables with upper case names. Sometimes you don’t want this to happen. To prevent this add DATABASE_TO_UPPER=FALSE to the url (use a semicolon as a separator).

If you need a H2 DB cheatsheet, then check the following one: H2 Database cheatsheet