H2 database tutorial

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

H2 is an opensource lightweight Java database which is ships with JBoss AS and WildFly distribution. Whilst it's not recommended for production usage, it can be pretty useful for development/test with a more advanced configuration. This tutorial shows how.

H2 database can be embedded in Java applications or run in the client-server mode. Depending on how the database is executed, you will use a different JDBC connection URL for accessing it:

Embedded into your application:

jdbc:h2:~/test

The Data will be saved into the 'test' folder lacated in the user home directory

Client-Server mode:

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

The Data will be saved in the user home directory

Additionally the H2 database can be configured to run as in-memory database, which means that data will not be persisted on the disk:

In-Memory database

jdbc:h2:mem:test

JBoss AS and WildFly H2 implementation

The application server ships with an H2 database which is configured as in-memory database:  

<datasource jndi-name="java:jboss/datasources/ExampleDS"
    pool-name="ExampleDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</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>  

That is enough for very basic tests of JPA applications, 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 database you need client-server mode database, which actually exposes TCP/IP socket for other processes. So let's turn our datasource into an H2 server based DB:

<datasource jndi-name="java:jboss/datasources/ExampleDS"
    pool-name="ExampleDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:h2:tcp://localhost/~/test</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>  

Starting H2 Database using Client/Server mode

As H2 Database runs in a separate process, before starting the application server, we will need to start the Database. This can be done in several ways, for example through the runnable H2 JAR file:

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

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

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

Finally, another option requires adding as listener in your web.xml the DbStarter class, which will start and stop the Server when the application is started or stopped:

<listener>
    <listener-class>org.h2.server.web.DbStarter</listener-class>
</listener>

Note: You will need as well to include a dependency to the module com.h2database.h2 if you want to use the latter approach.

Starting the application Server

Now startup the application server and deploy our basic JPA applications which persists a sets of key/values in the DB. (Full code available at the end of this article).

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>
     

Now you can run you Web application and start adding some properties:

h2 database tutorial

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

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 client-server mode you can monitor externally the Database, just like any other commercial DB.

Using the Buildt-in H2 Web console

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

h2 tutorial

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 database tutorial

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

h2 database tutorial

Using Eclipse to monitor H2 Database

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 database 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

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.

Download sample application which uses H2 database

Related articles available on mastertheboss.com

How to deploy a DataSource in jboss at application level ?

  Do you need to deploy your DataSource along with your Enterpri

JBoss Datasource HA

This tutorial has been updated for the new release of JBoss Appli

JBoss run out of Connections ?

Have you got No ManagedConnections available error message ? well

How to connect to a DataSource from a remote client?

  If you want to connect to your JBoss Connection Pool from a re

JBoss Datasource configuration

A Datasource is a Java Naming and Directory Interface (JNDI) obje

How do I get the list of Datasources available ?

JBOSS AS 4/5 Users You can use either twiddle: $ twiddle.sh que