6.5. Tutorial: JDBC Persistence

Overview

This tutorial provides complete instructions for installing a JDBC persistence layer into the JBoss A-MQ broker, using the MySQL database to store the broker's data. This example uses a plain JDBC persistence adapter and uses the default database schema.
This tutorial assumes you are using a standalone JBoss A-MQ container, which is the condition of the container immediately after the product is installed. It does not cover the case of a Fabric container.

Prerequisites

Before following the instructions for this tutorial, make sure that your system satisfies the following prerequisites:
  • You have already installed a MySQL database server (following the instructions in the MySQL Installation Guide, including the post installation set-up and testing).
  • The MySQL database server is already running.
  • You have root access to the MySQL database server (that is, you have access to the root user account in MySQL, which you can use to administer the database).
  • You have access to the Internet (so that you can install the MySQL JDBC driver bundle and the Apache Commons data source bundle, both of which must be downloaded from the Maven Central repository).

Steps to configure JDBC persistence with MySQL

To configure a standalone JBoss A-MQ broker to use JDBC persistence with MySQL, perform the following steps:
  1. Log into the MySQL database using the mysql client shell. Enter the following command to log on as the root user: 
    mysql -u root -p
    You will be prompted to enter the root user password (alternatively, if the root user has no associated password, you can omit the -p option).
  2. Add the new user account, amq, to MySQL with password, amqPass, by entering the following command at the mysql shell prompt: 
    mysql> CREATE USER 'amq'@'localhost' IDENTIFIED BY 'amqPass';
    If you would rather create the amq user without any password, you can omit the IDENTIFIED BY clause, as follows: 
    mysql> CREATE USER 'amq'@'localhost';
    Note
    This example assumes you are invoking the mysql shell from the same host where the MySQL database server is running. If you are logging on to the MySQL database remotely, however, you should replace localhost in the preceding command (and subsequent commands) by the name of the host where you are invoking the mysql shell.
  3. Grant privileges to the amq user, enabling it to access the activemq database instance (which has yet to be created). Enter the following GRANT command at the mysql shell prompt: 
    mysql> GRANT ALL PRIVILEGES ON activemq.* TO 'amq'@'localhost' WITH GRANT OPTION;
  4. Create the activemq database instance, by entering the following command: 
    mysql> CREATE DATABASE activemq;
    There is no need to create any database tables at this point. The broker's JDBC persistence will automatically create the necessary tables when it starts up for the first time.
  5. Start the JBoss A-MQ standalone container, with its default (unchanged) configuration: 
    cd InstallDir/bin
./amq
  6. Install the MySQL JDBC driver into the container, as follows: 
    JBossA-MQ:karaf@root> osgi:install mvn:mysql/mysql-connector-java/5.1.27
  7. Install the Apache Commons data source bundle, as follows: 
    JBossA-MQ:karaf@root> osgi:install mvn:org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-dbcp/1.4_3
  8. Stop the JBoss A-MQ container (for example, by entering the shutdown command at the console). Now configure the broker to use JDBC persistence by editing the InstallDir/etc/activemq.xml file. Modify the broker/persistenceAdapter element and add a new bean element (for the MySQL data source) as follows: 
    <beans ...>
    ...
    <bean id="mysql-ds"
          class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/activemq?relaxAutoCommit=true"/>
        <property name="username" value="amq"/>
        <property name="password" value="amqPass"/> <property name="poolPreparedStatements" value="true"/> </bean>

    <broker ...>
        ...
        <persistenceAdapter>
 <jdbcPersistenceAdapter dataSource="#mysql-ds"/>
        </persistenceAdapter>
        ...
    </broker>

</beans>
    Where the bean with the ID, mysql-ds, creates a data source instance for connecting to the MySQL database through the JDBC protocol. Note particularly the following property settings for this bean:
    url
    Is used to specify a JDBC URL in the following format: 
    jdbc:mysql://Hostame/DBName[?Property=Value]
    Where Hostname is the host where the MySQL database server is running; DBName is the name of the database instance used to store the broker data (which is activemq, in this example); and you can optionally set property values, Property=Value, after the ? character.
    password
    If you specified a password for the amq user when you created it, specify the password here. Otherwise, if no password was defined, specify a blank string, "".
  9. Restart the JBoss A-MQ container, as follows: 
    ./amq
    As the broker initializes, it automatically creates new tables in the activemq database instance to hold the broker data (this is the default behavior).
  10. To verify that the requisite tables have been created in the activemq database instance, enter the following commands at the mysql client shell: 
    mysql> USE activemq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+--------------------+
| Tables_in_activemq |
+--------------------+
| ACTIVEMQ_ACKS      |
| ACTIVEMQ_LOCK      |
| ACTIVEMQ_MSGS      |
+--------------------+
3 rows in set (0.00 sec)

mysql> describe ACTIVEMQ_LOCK;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| ID          | bigint(20)   | NO   | PRI | NULL    |       |
| TIME        | bigint(20)   | YES  |     | NULL    |       |
| BROKER_NAME | varchar(250) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe ACTIVEMQ_MSGS
    -> ;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | bigint(20)   | NO   | PRI | NULL    |       |
| CONTAINER  | varchar(250) | YES  | MUL | NULL    |       |
| MSGID_PROD | varchar(250) | YES  | MUL | NULL    |       |
| MSGID_SEQ  | bigint(20)   | YES  |     | NULL    |       |
| EXPIRATION | bigint(20)   | YES  | MUL | NULL    |       |
| MSG        | longblob     | YES  |     | NULL    |       |
| PRIORITY   | bigint(20)   | YES  | MUL | NULL    |       |
| XID        | varchar(250) | YES  | MUL | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)