H2 Database tutorial

H2 DB is an open source lightweight database written in Java. It is included in JBoss EAP and WildFly application server to speed up developing and testing Java applications. Some of its main features are:

  • It is an Opensource engine written in Java
  • It has an extremely fast DB engine.
  • Supports standard SQL and JDBC API.
  • It can be used embedded in your Java Process or in Server mode.S
  • 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 H2 Database can be used 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, the H2 server needs to be started within the same or another virtual machine, or on another computer.

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 located in 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-1.4.200.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.

At the same time, the H2 DB console will show up in the browser. Please notice that the IP address used to reach the H2 DB console might not work on your local machine. 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-1.4.193.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();

Note: When using H2 Database API, you need to include a dependency to the H2 Database:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version>
</dependency>

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, DB_CLOSE_DELAY=-1 is used.

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.

Sample JPA Application using H2 DB

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 autocompletion for your DB objects)

Using Eclipse to monitor H2 DB

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

How to inject a Datasource in your Enterprise applications

To inject a Datasource in your Enterprise applications you can use the javax.annotation.Resource annotation to declare a reference to that resource.

The @Resource can decorate a class, a field, or a method. The container will inject the resource referred to by @Resource into the component either at runtime or after its initialization.

In the following example, we can see how to inject a DataSource object into a Class field:

@WebServlet("/demo")
public class DemoServlet extends HttpServlet {

   @Resource(lookup="java:/PostGreDS")
   private DataSource ds;


    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();
        out.write("<h1>Datasource example</h1>");

        try (Connection con = ds.getConnection();
             PreparedStatement ps = createPreparedStatement(con);
             ResultSet rs = ps.executeQuery()) {

            while(rs.next()) {
                out.write("Time from Database: " +rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


    }
    private PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        String sql = "SELECT NOW();";
        PreparedStatement ps = con.prepareStatement(sql);
        return ps;
    }
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

By deploying the above Servlet, you will be able to check the current time on a PostgreSQL Datasource which needs to be available on the application server:

To learn how to configure PostgreSQL Database on WildFly we recommend checking this tutorial: Configuring a datasource with PostgreSQL and JBoss/WildFly

How do you connect a Datasource to a Cluster ?

When connecting WildFly / JBoss EAP to a Database cluster you need to provide an appropriate JDBC Connection String.

We’ll examine three popular Database: Oracle, MySQL and PostgreSQL.

Oracle Real Application Clusters (RAC)

Oracle Real Application Clusters (RAC) is a software component you can add to a high-availability solution that enables users on multiple machines to access a single database with increased performance. RAC comprises two or more Oracle database instances running on two or more clustered machines and accessing a shared storage device via cluster technology. To support this architecture, the machines that host the database instances are linked by a high-speed interconnect to form the cluster.

Oracle RAC allows multiple computers to run the Oracle RDBMS software simultaneously while accessing a single database  thus providing a clustered database. In order to benefit from Oracle RAC features like fault tolerance and load balancing all you have to do is configuring the connection url with the list of Oracle hosts which belongs to the cluster.

In this example we are configuring our datasource to connect to a RAC made up of host1 and host2:

<connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on)       
(address=(protocol=tcp)(host=host1)(port=1521))(address=(protocol=tcp)(host=host2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=(type=select)(method=basic)))) 
</connection-url>

MySQL and Maria DB Cluster

MySQL / MariaDB Cluster is a real-time open source transactional database designed for fast, always-on access to data under high throughput conditions.

In order to achieve load-balancing and failover across MySQL cluster you need to modify your jdbc Connection string to specify the failover and load balancing mode:

jdbc:(mysql|mariadb):[replication:|sequential:|loadbalance:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

Each parameter corresponds to a specific use case:

Mode Description
sequential This mode supports connection failover in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on slaves. The connector will try to connect to hosts in the order in which they were declared in the connection URL, so the first available host is used for all queries.
For example, let’s say that the connection URL is the following: jdbc:mariadb:sequential:host1,host2,host3/testdb
When the connector tries to connect, it will always try host1 first. If that host is not available, then it will try host2. etc. When a host fails, the connector will try to reconnect to hosts in the same order.
loadbalance This mode permits load-balancing connection in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on slaves. The connector performs load-balancing for all queries by randomly picking a host from the connection URL for each connection, so queries will be load-balanced as a result of the connections getting randomly distributed across all hosts.
replication This mode supports connection failover in a master-slave environment, such as a MariaDB Replication cluster. The mode supports environments with one or more masters. This mode does support load-balancing reads on slaves if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave from the connection URL to execute read queries for a connection.
aurora This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection.

Example:

"jdbc:mysql:replication://master1,slave1/test"

PostgreSQL Failover

To support simple connection fail-over it with PostgreSQL it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas. The driver will try to once connect to each of them in order until the connection succeeds. If none succeed, a normal connection exception is thrown.

The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database

The simple connection fail-over is useful when running against a high availability postgres installation that has identical data on each node.

For example an application can create two connection pools. One data source is for writes, another for reads. The write pool limits connections only to a primary node:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=primary

And read pool balances connections between secondary nodes, but allows connections also to a primary if no secondaries are available:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSecondary&loadBalanceHosts=true

If a secondary fails, all secondaries in the list will be tried first. In the case that there are no available secondaries the primary will be tried. If all of the servers are marked as “can’t connect” in the cache then an attempt will be made to connect to all of the hosts in the URL in order

Solving “No managed connections available within configured blocking timeout” error

This article discusses how to solve the error “No managed connections available within configured blocking timeout” which happens when you are not able to collect a Connection object from a WildFly Datasource.

Here is an example of this error, which can be found in the application server logs:

Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasource/PostgreDS
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)
    at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
    ... 51 more
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:495)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:374)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

    The datasource connection pool has not been tuned (e.g. max-pool-size and blocking-timeout-millis) correctly for the maximum load on the application.
    The application is leaking connections because it is not closing them and thereby returning them to the pool.
    Threads with connections to the database are hanging and holding on to the connections or slow performance (e.g. due to CPU or memory usage issues) may delay (otherwise rapid) return of connections by worker threads and lead to pool exhaustion.

There can be several reasons why you are not able to collect a Datasource object:

1) Your application is is not properly closing connections so there is a Connection leak which needs to be identified in your applications.

Actions:

2) Your datasource might not be tuned for the required load.

Actions:

  • Configure an appropriate value for max-pool-size and blocking-timeout-millis. Again, check the article WildFly Connection Pool configuration to find how to monitor the Datasource to find out the optimal values for max-pool-size and blocking-timeout-millis.

3) Your Database connection or SQL Statements are the bottleneck. Verify which statements are taking most of the time using your vendor’s SQL statements.

For example, MySQL provides a number of built-in tools to check the long running transaction. First of all, SHOW PROCESSLIST or SHOW FULL PROCESSLIST commands can expose the running queries in real-time.

WildFly Database connection pool configuration made easy

WildFly uses a pool of Database Connection objects to  your applications. Think of a connection pool like a bucket pre-filled with a minimum number of ready-to-use connections for your application. There is also an upper bound limit that defines the maximum number of connections that the pool can hold. The default values for the minimum and maximum size of the pool are 0 and 20 respectively, with the prefill attribute set to false by default.

This means that when a Datasource is started up, its connection pool is created with 0 active and valid connections, and that it can hold up to 20 connections.

Before we dig into the Connection pool configuration, check this tutorial if you want to learn how to create and use a Datasource: How to configure a Datasource with JBoss / WildFly

Configuring the Datasource attributes

Once created, the data source uses some default settings that might be good for an initial shot; in order to achieve optimal performance you should adjust these values based on your needs. Here is the list of attributes that you can configure with a short description:

  • min­-pool­-size: The minimum number of connections in the pool (default 0)
  • initial­-pool­-size: The initial number of connections to acquire from the database (New since WildFly 9)
  • max­-pool­-size: The maximum number of connections in the pool (default 20)
  • pool­-use­-strict­min: Whether idle connections below the min­pool­size should  be closed
  • pool­-prefill: Attempt to pre­fill the connection pool to the minimum number of connections. This will check your connections as soon as the Datasource is installed.
  • flush­-strategy: How the pool should be flushed in case of an error. The default one (FailingConnectionOnly) forces destroying only connections with error.
  • idle­-timeout­-minutes: Specifies the maximum time, in minutes, a connection may be idle before being closed. The actual maximum time depends also on the IdleRemover scan time, which is half of the smallest idle­timeout­minutes value of any pool.
  • track­-statements: Whether to check for unclosed statements when a connection is returned to the pool, result sets are closed, a statement is closed or return to the prepared statement cache. Valid values are: “false” ­ do not track statements, “true” ­ track statements and result sets and warn when they are not closed, “nowarn” ­track statements but do not warn about them being unclosed

Datasource optimal configuration

So how these attributes can affect your applications ? Let’s see it from the boot process.
When the application server starts, if you have configured an initial­-pool­-size, the datasource will be eventually filled up with that number of connections. Otherwise, you start with an empty pool. From now on, every time a Connection is requested to the datasource a check is made to see if any idle Connection is available. If not, the application server will attempt to acquire a new database Connection.

So, unless the max­-pool­-size has been reached, a new Connection will be created. When a Connection completes its job it becomes idle. A Connection can stay idle up to a maximum number of minutes as specified by the idle­-timeout­-minutes. After that, the Connection is returned to the pool.
The pool­-use-­strict­-min allows for a variation to this rule. If set to true, idle connections are not returned to the pool if you have hit the min-­pool­-size. They will just stay idle, ready to be used by your applications. Here is the min-­pool­-size and max-­pool­-size settings are applied to the MySQL datasource:

/subsystem=datasources/data-source=MySqlDS:write-attribute(name=min-pool-size,value=10)
/subsystem=datasources/data-source=MySqlDS:write-attribute(name=max-pool-size,value=50)

Monitoring Runtime attributes of the Datasource

In order to tune your applications, it is essential to monitor the runtime attributes of the Database Pool. This will help you to discover if the Connection pool has enough connections to meet your requirements and if any leak in Connection is detected. As a rule of thumb, you have to enable statistics on your Datasource first:

/subsystem=datasources/data-source=PostgrePool:write-attribute(name=statistics-enabled,value=true)

Then, you can query the statistics=pool Node to investigate the current number of active Database connections:

/subsystem=datasources/data-source=PostgrePool/statistics=pool:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => 3,
        "AvailableCount" => 17,
        "AverageBlockingTime" => 0L,
        "AverageCreationTime" => 32L,
        "AverageGetTime" => 33L,
        "AveragePoolTime" => 0L,
        "AverageUsageTime" => 0L,
        "BlockingFailureCount" => 0,
        "CreatedCount" => 3,
        "DestroyedCount" => 0,
        "IdleCount" => 0,
        "InUseCount" => 3,
. . . .

In this example, we have 3 active Database connections (“ActiveCount“) but we also have 3 “InUseCount” Connections. This in turn can mean two things:

  • There are 3 Connection objects which are executing SQL statements right now.
  • There are active Connections which have completed their execution but they haven’t been closed properly.

Detecting Connection leaks

One way to detect Database connection leaks is using a combination of settings in the datasource and the jca subsystem. First, make sure that the Cached Connection Manager (use-ccm) is activated for the Datasource:

/subsystem=datasources/data-source=PostgrePool:read-attribute(name=use-ccm)
{
    "outcome" => "success",
    "result" => true
}

The above should be “true” by default.  Then set to “debug” the Cached Connection Manager in the jca subsystem:

 /subsystem=jca/cached-connection-manager=cached-connection-manager:write-attribute(name=debug,value=true)

That will cause connections to be closed automatically for you by the Cached Connection Manager and the following  information will be reported in your logs:

2020-08-21 13:04:54,946 INFO  [org.jboss.jca.core.api.connectionmanager.ccm.CachedConnectionManager] (default task-1) IJ000100: Closing a connection for you. Please close them yourself: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK82ba43757: java.lang.Throwable: STACKTRACE
	at org.jboss.ironjacamar.impl1.4.22.Final//org.jboss.jca.core.connectionmanager.ccm.CachedConnectionManagerImpl.registerConnection(CachedConnectionManagerImpl.java:308)
	at org.jboss.ironjacamar.impl1.4.22.Final//org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:819)
	at org.jboss.ironjacamar.jdbcadapters1.4.22.Final//org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)
	at org.jboss.as.connector20.0.0.Final//org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:64)

Finally, consider that you can have Datasource runtime statistics printed in your server log file on each Connection usage by enabling this logger:

/subsystem=logging/logger=org.jboss.jca/:add(level=TRACE)

That will print the full list of static and runtime attributes for your Datasource in the server logs:

Pool:
  Name: PostgrePool
  Class: org.jboss.jca.core.connectionmanager.pool.strategy.OnePool
  Object: 316a6237
  FIFO: false
PoolConfiguration:
  MinSize: 0
  InitialSize: 0
  MaxSize: 20
  BlockingTimeout: 30000
  IdleTimeoutMinutes: 30
  ValidateOnMatch: false
  BackgroundValidation: false
  BackgroundValidationMillis: 0
  StrictMin: false
  UseFastFail: false
  Decrementer: org.jboss.jca.core.connectionmanager.pool.capacity.TimedOutDecrementer
Available (0):
InUse (1):
  668c5c34 (NORMAL) (CheckedOut: 1598007894938) (Validated: 1598007794843) (Usage: 10)
Statistics:
  ActiveCount: 1
  AvailableCount: 19
  AverageBlockingTime: 0
  AverageCreationTime: 17
  AverageGetTime: 9
  AveragePoolTime: 100086
  AverageUsageTime: 9
  BlockingFailureCount: 0
  CreatedCount: 1
  DestroyedCount: 0
  IdleCount: 0
  InUseCount: 1
. . . . .

Configuring a Datasource with JBoss AS 4 and 5

In order to create a DataSource (so that you can use JDBC connectivity) you need to create a file ending with -ds.xml under the “deploy” directory of your server.

The default Datasource file

The default data source configured with JBoss 4.0 is the HypersonicDB data source.
Here’s the hsqldb-ds.xml that is shipped with jboss :

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
    <local-tx-datasource>

        <jndi-name>DefaultDS</jndi-name>

        <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB
        </connection-url>

        <driver-class>org.hsqldb.jdbcDriver</driver-class>

        <user-name>sa</user-name>
        <password></password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>

        <idle-timeout-minutes>0</idle-timeout-minutes>

        <track-statements />

        <security-domain>HsqlDbRealm</security-domain>

        <prepared-statement-cache-size>32</prepared-statement-cache-size>

        <metadata>
            <type-mapping>Hypersonic SQL</type-mapping>
        </metadata>

        <depends>jboss:service=Hypersonic,database=localDB</depends>

    </local-tx-datasource>

    <mbean code="org.jboss.jdbc.HypersonicDatabase" name="jboss:service=Hypersonic,database=localDB">
        <attribute name="Database">localDB</attribute>
        <attribute name="InProcessMode">true</attribute>
    </mbean>

</datasources>

As you can see from this file, JDBC connectivity uses Connection pools to dispatch Connections. The initial size and the max size of the Connection pool can be configured with <min-pool-size> and <max-pool-size>.

With <idle-timeout-minutes> you can indicate the maximum time a connection may be idle before being closed and returned to the pool. If not specified it’s 15 minutes.

<track-statements/> is a debugging feature: it checks that all statements are closed when the connection is returned to the pool: remember to disable it in production environment.

 <security-domain> tells to use the security domain defined in conf/login-config.xml : in our case:

 <application-policy name="HsqlDbRealm">
    <authentication>
        <login-module
            code="org.jboss.resource.security.ConfiguredIdentityLoginModule"
            flag="required">
            <module-option name="principal">sa</module-option>
            <module-option name="userName">sa</module-option>
            <module-option name="password"></module-option>
            <module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS
            </module-option>
        </login-module>
    </authentication>
</application-policy>

<prepared-statement-cache-size> is the number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.

Enterprise datasources

I) Local Datasource

This is a sample Oracle local datasource configuration: a local DataSource is one that does not support two phase commit using a java.sql.Driver.

<datasources>
    <local-tx-datasource>
        <jndi-name>OracleDS</jndi-name>
        <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>

        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
        <user-name>x</user-name>
        <password>y</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>100</max-pool-size>
        <query-timeout>60</query-timeout>
        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
        </exception-sorter-class-name>

        <metadata>
            <type-mapping>Oracle9i</type-mapping>
        </metadata>
    </local-tx-datasource>

</datasources>

Notice the <query-timeout> tag which configures the maximum of seconds before a query times out ( avaliable since Jboss 4.0.3). The <exception-sorter-class-name> is used to Check the Oracle error codes and messages for fatal errors.

In order to be able to use a Datasource, you need to install a JDBC Driver. For JBoss AS 7/WildFly users check this tutorial: How to configure a Datasource with JBoss / WildFly . Of you are using JBoss AS 4/5 it’s enough to drop the JDBC Driver in the lib folder of the application server.

II) XA Datasource

This is a sample XA Datasource: XA DataSources support two phase commit using a  javax.sql.XADataSource

<datasources>
    <xa-datasource>
        <jndi-name>XAOracleDS</jndi-name>
        <track-connection-by-tx></track-connection-by-tx>
        <isSameRM-override-value>false</isSameRM-override-value>
        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource
        </xa-datasource-class>
        <xa-datasource-property name="URL">jdbc:oracle:oci8:@tc
        </xa-datasource-property>
        <xa-datasource-property name="User">scott
        </xa-datasource-property>
        <xa-datasource-property name="Password">tiger
        </xa-datasource-property>

        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
        </exception-sorter-class-name>

        <no-tx-separate-pools></no-tx-separate-pools>


        <metadata>
            <type-mapping>Oracle9i</type-mapping>
        </metadata>
    </xa-datasource>

    <mbean
        code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter"
        name="jboss.jca:service=OracleXAExceptionFormatter">
        <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager
        </depends>
    </mbean>

</datasources>

Notice the <isSameRM-override-value>  set to false to fix problems with Oracle. The element <track-connection-by-tx/>  can be omitted on JBoss 5 where it’s enabled by default.
At last, the  <no-tx-separate-pools> means that Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa.


Configuring your datasource to connect to Oracle RAC

Oracle RAC allows multiple computers to run the Oracle RDBMS software simultaneously while accessing a single database  thus providing a clustered database.
In order to benefit from Oracle RAC features like fault tolerance and load balancing all you have to do is configuring the connection url with the list of Oracle hosts which belongs to the cluster.
In this example we are configuring our datasource to connect to a RAC made up of host1 and host2:

 <connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=host1)(port=1521))(address=(protocol=tcp)(host=host2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=(type=select)(method=basic))))
 </connection-url>

Where to put Datasources if JBoss is clustered ?
If you are running a Cluster of JBoss AS 5 servers, position the file -ds.xml in the JBOSS_HOME\server\all\farm\ directory, it will be replicated on all members of the cluster.
Monitoring your Connection Pool (JBoss AS 5)

Datasource relevant MBeans

Each datasource translates into several MBeans that you can interact with in the JMX Console. All the datasource-related objects are in the jboss.jca domain. You can find them by searching through the JMX Console page, or by using jboss.jca:* as the query filter.
Supposing you want to monitor your OracleDS datasource from the previous example: you could use a more specific filter, such as jboss.jca:name=OracleDS,*, to see only the OracleDS entries. In either case, four MBeans will be related to the OracleDS datasource:

  • name=OracleDS ,service=DataSourceBinding

  • name=OracleDS ,service=LocalTxCM

  • name=OracleDS ,service=ManagedConnectionFactory

  • name=OracleDS ,service=ManagedConnectionPool

While each plays a critical role in providing the datasource functionality in JBoss, you are most likely to need to interact with the connection pool. Click the connection pool MBean to expose the management attributes and operations.

In our datasource file we’ve been using specified a minimum connection pool size of 2 and a maximum pool size of 10. You’ll see those values reflected in the MinSize and MaxSize attributes. You can change the values in the running server by adjusting the values and clicking Apply Changes.

Setting the values here affects the connection pool only in memory. To change the configuration permanently, update the datasource file. Try setting the pool sizes there. When you save the file, JBoss will redeploy the datasource and the new pool sizes will be displayed when you reload the page.

You might occasionally want to adjust the pool size to account for usage; you are more likely to be curious how much of the connection pool is being used. The ConnectionCount attribute shows how many connections are currently open to the database.

However, open connections are not necessarily in use by application code. The InUseConnectionCount attribute shows how many of the open connections are in use. Viewing the statistic from the other direction, AvailableConnectionCount shows how much room is left in the pool.

Finally, the MBean has several statistics that track connection pool usage over the pool’s lifetime. ConnectionCreatedCount and Connection-DestroyedCount keep running totals of the number of connections created and destroyed by the pool. If IdleTimeout is greater than 0, connections will eventually timeout, be destroyed, and be replaced by fresh connections. This will cause the created and destroyed counts to rise constantly. The MaxConnectionsInUseCount attribute keeps track of the highest number of connections in use at a time.

If you notice anything awkward in the connection pool, or you just want to reset the statistics, you can flush the connection pool using the flush operation on the MBean. This will cause a new connection pool to be created, abandoning the previous connection pool.

A Graph is worth 100 words

or so ! if you read our recent Recipes (and we really appreciate that you are a faithful visitor !) then you know that JBoss Web Console is able to show you graphically the value of many MBeans attribute (those who have a numeric value).
So look for the MBean “name=OracleDS ,service=ManagedConnectionPool”, there you can choose to add a graph to any of the attributes: in this picture you can see a sample graph of the JMX Attribute Connection Count

How to connect to a DataSource from a remote client?

WildFly and JBoss EAP 6/7 no longer support remote JNDI lookup of datasource objects. If you attempt to lookup a Datasource object from a remote client, the following error will be thrown:

ERROR: org.jboss.remoting3.MessageCancelledException
Exception in thread "main" org.jboss.naming.remote.protocol.NamingIOException: Failed to lookup [Root exception is java.io.NotSerializableException: org.jboss.jca.core.connectionmanager.pool.strategy.OnePool]

As an alternative, it is recommend using an EJB as facade in accessing the data source, and defining a proper contract for how the data should be accessed and managed.

JBoss 5 users

If you are running JBoss As 5 this option is still available, although discouraged. To allow it, you need to tell JBoss NOT to bind the Datasource under the “java:/”
namespace.
As a matter of fact this restricts the lookup to the same VM as the JBoss server.

Simply use’ tag <use-java-context>false</use-java-context> in your -ds.xml file

This is a sample Datasource file for mysql configured to accept remote client access

 <datasources>
      <local-tx-datasource>
        <jndi-name>MySqlDS</jndi-name>
        <use-java-context>false</use-java-context>
        <connection-url>jdbc:mysql://mysql-hostname:3306/jbossdb
        </connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>x</user-name>
        <password>y</password>

          <metadata>
             <type-mapping>mySQL</type-mapping>
          </metadata>
      </local-tx-datasource>

    </datasources>

How to deploy a DataSource in jboss at application level ?

Data source definitions can be also packed in your application so that you don’t have to modify at all the server configuration. When deploying data source on WildFly as part of your application you have to add them in a specific folder, which varies depending on the application package format.

For example, for a Web application, check the kitchensink example from WildFly quickstarts:

src
├── main
│   ├── java
│   │   └── org
│   │       └── jboss
│   │           └── as
│   │               └── quickstarts
│   │                   └── kitchensink
│   │                       ├── controller
│   │                       │   └── MemberController.java
│   │                       ├── data
│   │                       │   ├── MemberListProducer.java
│   │                       │   └── MemberRepository.java
│   │                       ├── model
│   │                       │   └── Member.java
│   │                       ├── rest
│   │                       │   ├── JaxRsActivator.java
│   │                       │   └── MemberResourceRESTService.java
│   │                       ├── service
│   │                       │   └── MemberRegistration.java
│   │                       └── util
│   │                           └── Resources.java
│   ├── resources
│   │   ├── import.sql
│   │   └── META-INF
│   │       └── persistence.xml
│   └── webapp
│       ├── index.html
│       ├── index.xhtml
│       ├── resources

│       └── WEB-INF
│           ├── beans.xml
│           ├── faces-config.xml
│           ├── kitchensink-quickstart-ds.xml
│           └── templates
│               └── default.xhtml
└── test
    ├── java
    │   └── org
    │       └── jboss
    │           └── as
    │               └── quickstarts
    │                   └── kitchensink
    │                       └── test
    │                           └── MemberRegistrationIT.java
    └── resources
        ├── arquillian.xml
        ├── META-INF
        │   └── test-persistence.xml
        └── test-ds.xml

And here is the kitchensink-quickstart-ds.xml datasource:

<datasources xmlns="http://www.jboss.org/ironjacamar/schema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.jboss.org/ironjacamar/schema http://docs.jboss.org/ironjacamar/schema/datasources_1_0.xsd">
    <!-- The datasource is bound into JNDI at this location. We reference
        this in META-INF/persistence.xml -->
    <datasource jndi-name="java:jboss/datasources/KitchensinkQuickstartDS"
        pool-name="kitchensink-quickstart" enabled="true"
        use-java-context="true">
        <connection-url>jdbc:h2:mem:kitchensink-quickstart;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1</connection-url>
        <driver>h2</driver>
        <security>
            <user-name>sa</user-name>
            <password>sa</password>
        </security>
    </datasource>
</datasources>
As you can see, the format of the datasource element is the same as the one defined in the server configuration.
Please note that a Datasource deployed at application level cannot be managed using the CLI or the Web console. Therefore, it is not recommended to use deployable datasource for applications running in production.

How to add Connection properties to your JBoss DataSource ?

In this article we will learn how to configure Connection Properties in a JBoss / WildFly Datasource.

Let’s step back first. To configure Connection Pooling (non-XA) to a Database you have two options:

  • Using a class which implements java.sql.Driver
  • Using a class which implements javax.sql.DataSource

When using a Class which implements java.sql.Driver, you can simply add a connection-property element in your datasource as in this example:

<datasource jndi-name="java:/comp/env/jdbc/mysqlantoioviDB" pool-name="MysqlPools" enabled="true" use-java-context="true">
                <connection-url>jdbc:mysql://localhost:3306/demoDB</connection-url>
             
                <connection-property name="vendorProperty">value</connection-property>

                <driver>mysql</driver>
                <security>
                    <user-name>sa</user-name>
                    <password>sa</password>
                </security>
                <pool>
                    <max-pool-size>30</max-pool-size>
                </pool>
</datasource>

When using a class which implements javax.sql.DataSource, add a Property named “ConnectionProperties” with a comma separated list of properties. Example:

<datasource jndi-name="java:/comp/env/jdbc/mysqlantoioviDB" pool-name="MysqlPools" enabled="true" use-java-context="true">
                <connection-url>jdbc:mysql://localhost:3306/demodb</connection-url>
             
                <connection-property name="ConnectionProperties">property1=value,property2=valueTwo</connection-property>


                <driver>mysql</driver>
                <security>
                    <user-name>sa</user-name>
                    <password>sa</password>
                </security>
                <pool>
                    <max-pool-size>30</max-pool-size>
                </pool>
</datasource>

On the other hand, for XA Datasources, you can set Properties as follows:

<xa-datasource-property name="ConnectionProperties">property1=value,property2=vaue2</xa-datasource-property>

 

JBoss run out of Connections ? here is how to fix it

Have you got No ManagedConnections available error message ?

... javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/MyDataSource
... javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])

In this tutorial we will learn which are the possible causes to this problem.

# 1  Your connection pool is too small

Increase the max number of connections in your datasource configuration (default is 20):

<max-pool-size>100</max-pool-size>

To find the optimal pool size, it is recommended to monitor the Maximum amount of connection requested, which is available through the MaxUsedCount attribute:

/subsystem=datasources/data-source=ExampleDS/statistics=pool:read-attribute(name=MaxUsedCount)

It is recommended that you set the max-pool-size around 15-20% higher than the MaxUsedCount under load.

# 2  Need longer timeouts.  If the application server has reached the max-pool-size, it will wait up to the blocking-timeout-wait-millis . When your SQL statements are taking too long, it can be worth increasing the blocking timeout from default 30000 in your datasource configuration:

<blocking-timeout-millis>50000</blocking-timeout-millis>

# 3 Are you closing your connections in the finally method ? (If not your job is really in danger )

finally {
    if (resultSet != null)
      resultSet.close();
    if (statement != null)
      statement.close();

    connection.close();
}

It can be helpful to trace Database connections so that you find the piece of code causing pool exaustion. Check the following article to learn how to trace your Datasource connections: How to trace JDBC statements with JBoss and WildFly

# 4 Check the CPU usage of your application server

If your application server is hanging, due to CPU or memory issues, the threads used to manage Connections to the database may delay the return of Connections. This, in turn, can lead to Connection Pool exhaustion.

Check this tutorial to learn how to monitor the CPU usage of JBoss / WildFly: How to monitor JBoss CPU usage like a pro