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:
- JDBC store:The regular JDBC store is specified using the
jdbcPersistenceAdapterelement inside thepersistenceAdapterelement. For more details see Section 6.2, “Using the Plain JDBC Adapter”. - Journaled JDBC store:The journaled JDBC store is specified using the
journaledJDBCelement inside thepersistenceFactoryelement. For more details see Section 6.3, “Using JDBC with the High Performance Journal”.WarningThe 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-dbcpbundle. 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.NoteDepending 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.
