Create datasource for H2 in JBoss

This post is older than a year. Consider some information might not be accurate anymore.

This post demonstrates how to create a datasource for the H2 database in JBoss. This recipe can easily applied to any other database like Oracle DB, MySQL and PostgreSQL.

First, check if the h2 database driver is available:

[standalone@localhost:9999 /] /subsystem=datasources:installed-drivers-list
{
    "outcome" => "success",
    "result" => [{
        "driver-name" => "h2",
        "deployment-name" => undefined,
        "driver-module-name" => "com.h2database.h2",
        "module-slot" => "main",
        "driver-datasource-class-name" => "",
        "driver-xa-datasource-class-name" => "org.h2.jdbcx.JdbcDataSource",
        "driver-class-name" => "org.h2.Driver",
        "driver-major-version" => 1,
        "driver-minor-version" => 3,
        "jdbc-compliant" => true
    }]
}

We add a datasource by this template

data-source add \
   --name=DATASOURCE_NAME \
   --jndi-name=JNDI_NAME \
   --driver-name=DRIVER_NAME \
   --connection-url=CONNECTION_URL

Use H2 as file and add custom settings within the connection-url. After the add, don’t forget to enable the new datasource.

#      _       _
#   __| | __ _| |_ __ _ ___  ___  _   _ _ __ ___ ___
#  / _` |/ _` | __/ _` / __|/ _ \| | | | '__/ __/ _ \
# | (_| | (_| | || (_| \__ \ (_) | |_| | | | (_|  __/
#  \__,_|\__,_|\__\__,_|___/\___/ \__,_|_|  \___\___|
#
data-source add \
    --name=demo \
    --driver-name=h2 \
    --connection-url="jdbc:h2:demo;INIT=RUNSCRIPT FROM '~/tables.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE" \
    --jndi-name=java:jboss/datasources/demo \
    --user-name=admin \
    --password=geheim \
    --check-valid-connection-sql="SELECT 1"
data-source enable --name=demo

This will result in console.log

16:48:49,991 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-7) JBAS010400: Bound data source [java:jboss/datasources/demo]

This will generate this section in the standalone.xml.

<datasource jndi-name="java:jboss/datasources/demo" pool-name="demo" enabled="true">
	<connection-url>jdbc:h2:demo;INIT=RUNSCRIPT FROM '~/tables.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE</connection-url>
	<driver>h2</driver>
	<security>
		<user-name>admin</user-name>
<password>geheim</password>
	</security>
	<validation>
		<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
	</validation>
</datasource>

check-valid-connection-sql is important! You provide the SQL statement used to validate the connection.

The following is an example of how you might specify a SQL statement to validate a connection for Oracle:

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

For MySQL or PostgreSQL, you might specify the following SQL statement. This also works for the H2 database.

<check-valid-connection-sql>select 1</check-valid-connection-sql>