Configuring Messaging JDBC Persistence Store on WildFly

In this quick tutorial we will learn how to configure WildFly 11 (or later) to use JMS JDBC Store against a RDBMS.

WildFly 11 JMS Broker, Apache ActiveMQ Artemis, ships with two persistence options:

• The File journal which is highly optimized for the messaging use case and gives great performance
• JDBC Store, which uses JDBC to connect to a database of your choice. The JDBC Store is still under development, but it is possible to use it’s journal features, (essentially everything except for paging and large messages).

For our example, we will use MySQL as JDBC Store so let’s start a Docker image of it to get started as quickly as possible:

$ sudo docker run -d --name mysql -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=demodb -e MYSQL_ROOT_PASSWORD=secret mysql

Let’s check the IP Address assigned to the MySQL container:

$ sudo docker inspect --format '{{ .NetworkSettings.IPAddress }}' mysql
172.17.0.2

Done with MySQL, let’s define a datasource in WildFly to use that connection:

module add --name=com.mysql --resources=/tmp/mysql-connector-java-5.1.15.jar --dependencies=javax.api,javax.transaction.api

/subsystem=datasources/jdbc-driver=mysql:add(driver-name=mysql,driver-module-name=com.mysql)

data-source add --jndi-name=java:/MySqlDS --name=MySQLPool --connection-url=jdbc:mysql://172.17.0.2:3306/demodb --driver-name=mysql --user-name=root --password=secret

reload

/subsystem=datasources/data-source=MySQLPool:test-connection-in-pool

Now the only change needed to the messaging server is setting the journal-datasource attribute to use MySQL datasource Pool:

/subsystem=messaging-activemq/server=default:write-attribute(name=journal-datasource, value=MySQLPool)

Now let’s try to deploy any of the JMS quickstarts such as: https://github.com/wildfly/quickstart/tree/11.x/helloworld-mdb

Once executed the quickstart Servlet http://localhost:8080/helloworld-mdb/HelloWorldMDBServletClient?queue let’s check if the messages are being written in the JDBC Storage:

docker exec -i -t mysql  /bin/bash

root@ca301fb894e2:/# mysql -u root -p                                                                                                                                          
Enter password:

As you can see, messages are being written in the JDBC Storage:

mysql> select * from MESSAGES;
+------+------------+--------------+------+----------------+--------------+--------+------------+--------+------------------+------+
| id   | recordType | compactCount | txId | userRecordType | variableSize | record | txDataSize | txData | txCheckNoRecords | seq  |
+------+------------+--------------+------+----------------+--------------+--------+------------+--------+------------------+------+
|   -1 |         18 |            0 |   77 |             -1 |            0 |        |          0 |        |                0 |   24 |
+------+------------+--------------+------+----------------+--------------+--------+------------+--------+------------------+------+
1 row in set (0.00 sec)