H2 database tutorial
- Published: 27 February 2012
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:
The Data will be saved into the 'test' folder lacated in the user home directory
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:
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
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-1.3.173.jar -tcpPort 9101 -webAllowOthers -tcpAllowOthers -webPort 8888
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:
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.
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 database or execute SQL. (By the way this web application has also a nice built-in autocompletion for your DB objects)
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:
Next, move to the Data Source explorer view, where you will be able to explore the selected Database and eventually open an SQL ScrapBook:
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