## 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:

- Apache Derby

- Axion

- DB2

- HSQL

- Informix

- MaxDB

- MySQL

- Oracle

- Postgresql

- SQLServer

- Sybase

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

### 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

Attribute | Default | Description
--- | --- | ---
`tablePrefix` |  | Specifies a prefix that is added to every table name.
| Tip
The prefix should be unique per broker if multiple brokers will be sharing the same database.
`messageTableName` | `ACTIVEMQ_MSGS` | Specifies the name of the table in which persistent messages are stored.
`durableSubAcksTableName` | `ACTIVEMQ_ACKS` | Specifies the name of the database table used to store acknowledgment messages from durable subscribers.
`lockTableName` | `ACTIVEMQ_LOCK` | Specifies the name of the lock table used to determine the master in a master/slave scenario.
`binaryDataType` | `BLOB` | Specifies 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` | `INTEGER` | Specifies the datatype used to store the sequence id of a message.
`longDataType` | `BIGINT` | Specifies 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>