LibraryLink ToToggle FramesPrintFeedback
Prev   Next

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. FUSE Message Broker's default database when using the JDBC persistence adapter is Apache Derby. FUSE Message Broker also supports most major SQL databases. You can enable other databases by properly configuring the JDBC connection in the broker's configuration file.

You can you use the JDBC persistence adapter either with or without journaling. Using the journal provides two main benefits. First, it improves the speed of the message store. Second, it provides support for JMS transactions.

Supported databases

FUSE Message Broker is known to work with the following databases:

In addition, FUSE Message Broker supports a number of generic JDBC providers.

Specifying the type of JDBC store to use

FUSE Message Broker support two types of JDBC store:

Configuring your JDBC driver

FUSE Message Broker 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 Customizing the SQL statements used by the adapter for information about modifying the SQL statements. See Using generic JDBC providers for information about changing the JDBC methods.

The default configuration shipped with FUSE Message Broker (InstallDir/conf/activemq.xml) includes configuration examples for a number of the supported databases. Example 3.1 shows the configuration for using the default database.

Example 3.1. Configuration for Using the Default Database

<beans ...>
  <broker xmlns="http://activemq.org/config/1.0" brokerName="localhost">
    ...
    <persistenceAdapter>
      <jdbcPersistenceAdapter dataDirectory="activemq-data"/>
    </persistenceAdapter<
    ...
  </broker>  
</beans>

Example 3.2 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 3.2. Configuration for the Oracle JDBC Driver

<beans ...>
  <broker xmlns="http://activemq.org/config/1.0" brokerName="localhost">
    ...
    <persistenceAdapter>
      <jounraledJDBC dataSource="#oracle-ds" />
    </peristenceAdapter>
    ...
  </broker>
  ...
  <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="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.

Customizing the SQL statements used by the adapter

You can configure the SQL statements used to create the message store. This is done by adding a statements element to your JDBC persistence adapters configuration. Example 3.3 shows a configuration snip-it that specifies that long strings are going to be stored as VARCHAR(128).

Example 3.3. Fine Tuning the Database Schema

<persistenceAdapter>
  <journaledJDBC ...>
    <statements>
      <statements stringIdDataType ="VARCHAR(128)"/>
    </statements>
  </journaledJDBC>
</persistenceAdapter>

The first statements element is a wrapper for one or more statements elements. Each internal statements element specifies a single configuration statement. Table 3.1 describes the configurable properties.

Table 3.1. Statements for Configuring the SQL Statements Used by the JDBC Persistence Adapter

AttributeDefaultDescription
tablePrefix  Specifies a prefix that is added to every table name. The prefix should be unique per broker if multiple brokers will be sharing the same database.
messageTableName ACTIVEMQ_MSGSSpecifies the name of the table in which persistent messages are stored.
durableSubAcksTableName ACTIVEMQ_ACKSSpecifies the name of the database table used to store acknowledgment messages from durable subscribers.
lockTableName ACTIVEMQ_LOCKSpecifies the name of the lock table used to determine the master in a master/slave scenario.
binaryDataType BLOBSpecifies the data type used to store the messages.
containerNameDataType VARCHAR(250)Specifies the data type used to store the destination name.
msgIdDataType VARCHAR(250)Specifies the data type used to store a message id.
sequenceDataType INTEGERSpecifies the datatype used to store the sequence id of a message.
longDataType BIGINTSpecifies the data type used to store a Java long.
stringIdDataType VARCHAR(250)Specifies the data type used to store long strings like client ids, selectors, and broker names.

The properties listed in Table 3.1 configure the default SQL statements used by the JDBC adapter and work with all of the supported databases. If you need to override the default statements to work with an unsupported database, there are a number of other properties that can be used. These include:

  • addMessageStatement

  • updateMessageStatement

  • removeMessageStatement

  • findMessageSequenceIdStatement

  • findMessageStatement

  • findAllMessagesStatement

  • findLastSequenceIdInMsgsStatement

  • findLastSequenceIdInAcksStatement

  • createDurableSubStatement

  • findDurableSubStatement

  • findAllDurableSubsStatement

  • updateLastAckOfDurableSubStatement

  • deleteSubscriptionStatement

  • findAllDurableSubMessagesStatement

  • findDurableSubMessagesStatement

  • findAllDestinationsStatement

  • removeAllMessagesStatement

  • removeAllSubscriptionsStatement

  • deleteOldMessagesStatement

  • lockCreateStatement

  • lockUpdateStatement

  • nextDurableSubscriberMessageStatement

  • durableSubscriberMessageCountStatement

  • lastAckedDurableSubscriberMessageStatement

  • destinationMessageCountStatement

  • findNextMessageStatement

  • createSchemaStatements

  • dropSchemaStatements

Using generic JDBC providers

To use a JDBC provider not natively supported by FUSE Message Broker you can typically configure the JDBC persistence adapter to work, by setting the persistence adapter's adapter attribute to one of the following values:

  • org.activemq.store.jdbc.adapter.BlobJDBCAdapter

  • org.activemq.store.jdbc.adapter.BytesJDBCAdapter

  • org.activemq.store.jdbc.adapter.DefaultJDBCAdapter

  • org.activemq.store.jdbc.adapter.ImageJDBCAdapter

The different settings change how the JDBC adapter stores and accesses BLOB fields in the database. To determine the proper setting consult the documentation for your JDBC driver and your database.

Example 3.4 shows a configuration snip-it configuring the journaled JDBC persistence adapter to use the blob JDBC adapter.

Example 3.4. Configuring a Generic JDBC Provider

<broker persistent="true" ...>
  ...
  <persistenceAdapter>
    <journaledJDBC adapter="org.activemq.store.jdbc.adapter.BlobJDBCAdapter" ... />
  </persistenceAdapter>
  ...
</broker>

Prev Up Next
 Home