H2 Database Tutorial and expert Tips

H2 DB is an open source lightweight Database written in Java. It ships 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 Java Database
  • 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 (March 2024) is the following one: 2.2.224

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version></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-.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-2.2.224.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 Embedded 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:

h2 database how to tutorial

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.

Proper Database shutdown

According to the documentation, H2 database connections are closed automatically when the virtual machine exits normally. Therefore, you don’t need to perform any extra action as a shutdown hook, which is a mechanism for closing resources, is already there.

In some cases, you can choose to disable automatic closing of the database by adding the following parameter to the database URL:

"jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE"

Finally, you can issue the SHUTDOWN command, which will make H2 free all resources related to the connection immediately:

SHUTDOWN

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>3.1.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.

H2 db tutorial

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

H2 db tutorial

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)

H2 db tutorial

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:

H2 db tutorial

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

h2 database tutorial

There are several plugins also for other Devlopment IDE that will let you visualize the H2 Database while coding. For example, if you are using IntelliJ Idea you might find useful this article: How to visualize H2 Database with IntelliJ Idea

Troubleshooting H2 Issues:

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).

Syntax error in SQL statement “Create…..”; expected “identifier”

This error can happen if you are using reserved keywords such as USER is your SQL. As a matter of fact, USER is a reserved word in Standard SQL Standard and also is a keyword in H2. To solve it, force quotation of the identifiers. For example:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true

Also, you can add to the JDBC Connection String the list of keywords you want to exclude such as:

;NON_KEYWORDS=USER

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

Found the article helpful? if so please follow us on Socials