6.4. Customizing the JDBC Persistence Adapter

Overview

Red Hat JBoss A-MQ provides options to customize the interaction between the JDBC persistence adapter and the underlying database. In some cases you might be able to use these customization options to integrate the JDBC persistence adapter with an unsupported database.

Customizing the SQL statements used by the adapter

You can customize the SQL statements that the JDBC persistence adapter uses to access the database. This is done by adding a statements element to the JDBC persistence adapter configuration. Example 6.5, “Fine Tuning the Database Schema” shows a configuration snippet that specifies that long strings are going to be stored as VARCHAR(128).

Example 6.5. Fine Tuning the Database Schema

<persistenceAdapter>
  <jdbcPersistenceAdapter ... >
    <statements>
      <statements stringIdDataType ="VARCHAR(128)"/>
    </statements>
  </jdbcPersistenceAdapter>
</persistenceAdapter>
The first statements element is a wrapper for one or more nested statements elements. Each nested statements element specifies a single configuration statement. Table 6.3, “Statements for Configuring the SQL Statements Used by the JDBC Persistence Adapter” describes the configurable properties.

Table 6.3. 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 6.3, “Statements for Configuring the SQL Statements Used by the JDBC Persistence Adapter” configure the default SQL statements used by the JDBC adapter and work with all of the supported databases.

Customizing SQL statements for unsupported 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 set. 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 Red Hat JBoss A-MQ, you can configure the JDBC persistence adapter, by setting the persistence adapter's adapter attribute to reference the bean ID of the relevant adapter. The following adapter types are supported:
  • org.activemq.store.jdbc.adapter.BlobJDBCAdapter
  • org.activemq.store.jdbc.adapter.BytesJDBCAdapter
  • org.activemq.store.jdbc.adapter.DefaultJDBCAdapter
  • org.activemq.store.jdbc.adapter.ImageJDBCAdapter
Various settings are provided to customize how the JDBC adapter stores and accesses BLOB fields in the database. To determine the proper settings, consult the documentation for your JDBC driver and your database.
Example 6.6, “Configuring a Generic JDBC Provider” shows a configuration snippet configuring the journaled JDBC persistence adapter to use the blob JDBC adapter.

Example 6.6. Configuring a Generic JDBC Provider

<broker persistent="true" ... >
  ...
  <persistenceAdapter>
    <jdbcPersistenceAdapter adapter="#blobAdapter" ... />
  </persistenceAdapter>


  <bean id="blobAdapter"
        class="org.activemq.store.jdbc.adapter.BlobJDBCAdapter"/>
  ...
</broker>