Chapter 6. Using JDBC to Connect to a Database Store

Abstract

Red Hat JBoss A-MQ supports the use of relational databases as a message store through JDBC.

6.1. Basics of Using the JDBC Persistence Adapter

Overview

For long term persistence you may want to use a relational database as your persistent message store. Red Hat JBoss A-MQ's default database when using the JDBC persistence adapter is Apache Derby. JBoss A-MQ also supports most major SQL databases. You can enable other databases by properly configuring the JDBC connection in the broker's configuration file.

Supported databases

JBoss A-MQ is known to work with the following databases:
  • MySQL
  • Oracle
  • PostgreSQL
  • Microsoft SQL Server
For full details of the compatible database versions, see Red Hat JBoss A-MQ Supported Configurations.

Specifying the type of JDBC store to use

JBoss A-MQ support two types of JDBC store:
The journaled JDBC store features better performance than the plain JDBC store.
Warning
The journaled JDBC store is incompatible with the JDBC master/slave failover pattern—see Fault Tolerant Messaging.

Prerequisites

Before you can use one of the JDBC persistence stores you need to ensure that the following are installed in the broker's container:
  • The org.apache.servicemix.bundles.commons-dbcp bundle. You can install this bundle into a standalone container using the following console command:
    osgi:install mvn:org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-dbcp/1.4_3
  • The JDBC driver for the database being used.
    Note
    Depending on the database being used, you may need to wrap the driver in an OSGi bundle by using the wrap: URI prefix when adding it to the container.

Configuring your JDBC driver

JBoss A-MQ autodetects the JDBC driver that is in use at start-up. For the supported databases, the JDBC adapter automatically adjusts the SQL statements and JDBC driver methods to work with the driver. If you wish to customize the names of the database tables or work with an unsupported database, you can modify both the SQL statements and the JDBC driver methods. See the section called “Customizing the SQL statements used by the adapter” for information about modifying the SQL statements. See the section called “Using generic JDBC providers” for information about changing the JDBC methods.

JDBC configuration for Apache Derby

Example 6.1, “Configuration for the Apache Derby Database” shows the configuration for using the default Apache Derby JDBC driver.

Example 6.1. Configuration for the Apache Derby Database

<beans ...>
  <broker xmlns="http://activemq.apache.org/schema/core"
          brokerName="localhost">
    ...
    <persistenceAdapter>
       <jdbcPersistenceAdapter
            dataDirectory="${activemq.base}/data"
            dataSource="#derby-ds"/>
    </persistenceAdapter>
    ...
  </broker>
 
  <!-- Embedded Derby DataSource Sample Setup -->
  <bean id="derby-ds" class="org.apache.derby.jdbc.EmbeddedDataSource">
    <property name="databaseName" value="derbydb"/>
    <property name="createDatabase" value="create"/>
  </bean>

</beans>

JDBC configuration for Oracle

Example 6.2, “Configuration for the Oracle JDBC Driver” shows the configuration for using the Oracle JDBC driver. The persistence adapter configuration refers to the Spring bean element that configures the JDBC driver.

Example 6.2. Configuration for the Oracle JDBC Driver

<beans ... >
  <broker xmlns="http://activemq.apache.org/schema/core"
          brokerName="localhost">
    ...
    <persistenceAdapter>
       <jdbcPersistenceAdapter
            dataDirectory="${activemq.base}/data"
            dataSource="#oracle-ds"/>
    </persistenceAdapter>
    ...
  </broker>
 
  <!-- Oracle DataSource Sample Setup -->
  <bean id="oracle-ds"
        class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:AMQDB"/>
    <property name="username" value="scott"/>
    <property name="password" value="tiger"/>
    <property name="maxActive" value="200"/>
    <property name="poolPreparedStatements" value="true"/>
  </bean>

</beans>
The JDBC drivers are configured using a Spring bean element. The id attribute specifies the name by which you will refer to the driver when configuring the JDBC persistence adapter. The class attribute specifies the class that implements the data source used to interface with the JDBC driver. The destroy-method attribute specifies the name of the method to call when the JDBC driver is shutdown.
In addition to the bean element, the JDBC driver configuration includes a number of property elements. Each property element specifies a property required by the JDBC driver. For information about the configurable properties refer to your JDBC driver's documentation.