JDBC Store Swiftlet
Overview
The JDBC Store Swiftlet provides persistent and non-persistent message storage for SwiftMQ queues using a JDBC-compliant relational database. It manages queue message storage, durable subscriber state, and transactional (XA) log records, supporting both standard and advanced database backends. The Swiftlet is highly configurable, allowing tuning of connection pooling, SQL statements, and swap file management for non-persistent data.
Features
JDBC-Based Persistent Message Storage
The Swiftlet stores persistent queue messages in a relational database using JDBC. For each queue, a dedicated persistent store is created, which manages message insertion, retrieval, and deletion. The implementation uses prepared SQL statements for all operations, with the ability to substitute a schema prefix for table names. The persistent store tracks message keys and ensures that message IDs are unique and incrementing per queue. On startup, the store queries the database for the maximum message ID to continue key assignment without collisions.
Schema Prefix Substitution
All SQL statements can include the ${schema-prefix} variable, which is replaced at runtime with the configured schema prefix. This allows for easy integration with databases where tables are namespaced by schema.
Custom SQL Statements
The Swiftlet allows overriding all SQL statements used for message and durable subscriber management. This is useful for adapting to different database dialects or optimizing queries for specific backends.
Configuration Example:
<swiftlet name="sys$store">
<jdbc driver-classname="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/swiftmq" username="swiftuser" password="secret"/>
</swiftlet>
JDBC Connection Pooling
The Swiftlet manages a pool of JDBC connections to the backend database, minimizing connection overhead and improving performance. The pool size, idle timeout, and keep-alive interval are configurable. The connection pool ensures a minimum number of connections are always available and can grow up to a configurable maximum. Idle connections are periodically checked for liveness and closed if they exceed the idle timeout.
Custom Connection Factory
The pool can use different connection factory classes, enabling support for database-specific connection implementations (e.g., Oracle, TimesTen). The factory is specified by class name and must implement the required interface.
Configuration Example:
<swiftlet name="sys$store">
<connection-pool min-connections="10" max-connections="50" idle-timeout="300000" keepalive-interval="60000"/>
</swiftlet>
Non-Persistent Message Swap File Management
For non-persistent queues, the Swiftlet uses local swap files to temporarily store messages. Each queue's non-persistent store manages a set of swap files, rolling over to new files when the current one reaches a configurable size limit. Swap files are stored in a configurable directory, and are cleaned up on startup. The swap mechanism ensures that non-persistent messages do not exhaust memory resources.
Swap File Path and Size
The directory for swap files and the maximum size of each swap file are configurable. When a swap file reaches its size limit, a new file is created for subsequent messages.
Configuration Example:
<swiftlet name="sys$store">
<swap path="/var/swiftmq/swap" roll-over-size="20971520"/>
</swiftlet>
Durable Subscriber State Management
The Swiftlet maintains durable subscriber information (client ID, durable name, topic, selector, no-local flag) in the database. It supports creating, retrieving, iterating, and deleting durable subscriber entries. This ensures that durable subscriptions survive broker restarts and are managed transactionally with the rest of the store.
Customizable SQL for Durables
All SQL statements for managing durable subscriber state can be customized, allowing adaptation to different database schemas or optimizations.
Configuration Example:
<swiftlet name="sys$store">
<statements schema-prefix="prod_"/>
</swiftlet>
XA Transaction Log Support
The Swiftlet supports distributed (XA) transactions by maintaining a prepare log in the database. Each prepare log record contains the transaction ID, queue name, and affected message keys. The log is used to recover in-doubt transactions after failures. The implementation supports both standard and Oracle-specific blob handling for large log records.
Customizable XA Log SQL
SQL statements for XA log operations (insert, select, delete) can be customized, including Oracle-specific multi-step blob handling.
Configuration Example:
<swiftlet name="sys$store">
<statements xalog-insert-oracle-1="insert into prod_xalog (id,content) values (?,empty_blob())"/>
</swiftlet>
Configuration Guide
Configuring for Oracle Database with Schema Prefix
Use this scenario when deploying SwiftMQ with an Oracle backend and a dedicated schema for message tables. This ensures all SQL statements use the correct schema prefix, and Oracle-specific blob handling is enabled.
- Set the JDBC driver class and URL for Oracle.
- Specify the schema prefix matching your Oracle schema.
- Ensure the connection factory is set to the Oracle-specific implementation if needed.
- Optionally, adjust SQL statements for Oracle blob handling.
<swiftlet name="sys$store">
<jdbc driver-classname="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:orcl" username="swiftuser" password="secret"/>
<connection-pool connection-factory="com.swiftmq.impl.store.jdbc.pool.PooledOracleConnectionFactory"/>
<statements schema-prefix="myschema_"/>
</swiftlet>
Tuning Connection Pool for High Throughput
Increase the minimum and maximum number of JDBC connections to handle high message throughput and reduce contention for database resources.
- Increase min-connections and max-connections in the connection pool configuration.
- Reduce idle-timeout and keepalive-interval to ensure connections are frequently checked and stale connections are closed.
- Monitor database and SwiftMQ performance to ensure optimal settings.
<swiftlet name="sys$store">
<connection-pool min-connections="20" max-connections="100" idle-timeout="120000" keepalive-interval="30000"/>
</swiftlet>
Customizing Swap File Location and Size
Change the directory and maximum size for swap files to optimize disk usage for non-persistent queues, especially in environments with dedicated storage volumes.
- Set the swap path to the desired directory.
- Increase or decrease the roll-over-size based on available disk space and expected message volume.
- Restart the router to apply changes.
<swiftlet name="sys$store">
<swap path="/mnt/fastdisk/swiftmq_swap" roll-over-size="52428800"/>
</swiftlet>
Configuration Reference
The top-level entity in routerconfig.xml is <swiftlet name="sys$store">.
<jdbc> Entity
JDBC Settings
This is a fixed child entity of <swiftlet name="sys$store">.
| Parameter | Type | Default | Mandatory | Reboot Required | Description |
|---|---|---|---|---|---|
driver-classname |
String | — | Yes | Yes | Name of the JDBC Drive Class |
url |
String | — | Yes | Yes | JDBC URL |
username |
String | — | No | Yes | JDBC Username |
password |
String | — | No | Yes | JDBC Password |
<swiftlet name="sys$store">
<jdbc driver-classname="..." url="..." username="..." password="..."/>
</swiftlet>
<connection-pool> Entity
JDBC Connection Pool
This is a fixed child entity of <swiftlet name="sys$store">.
| Parameter | Type | Default | Mandatory | Reboot Required | Description |
|---|---|---|---|---|---|
connection-factory |
String | com.swiftmq.impl.store.jdbc.pool.PooledConnectionFactory |
No | Yes | Pooled Connection Factory |
min-connections |
Integer | 5 |
No | No | Minimum Connections (min: 0) |
max-connections |
Integer | -1 |
No | No | Maximum Connections |
idle-timeout |
Long | 6000000 |
No | No | Idle Timeout |
keepalive-interval |
Long | 600000 |
No | No | Keep Alive Interval |
<swiftlet name="sys$store">
<connection-pool connection-factory="..." min-connections="..." max-connections="..." idle-timeout="..." keepalive-interval="..."/>
</swiftlet>
<statements> Entity
SQL Statements
This is a fixed child entity of <swiftlet name="sys$store">.
| Parameter | Type | Default | Mandatory | Reboot Required | Description |
|---|---|---|---|---|---|
schema-prefix |
String | — | No | Yes | Schema Prefix for Tables |
keepalive |
String | select 1 from ${schema-prefix}xalog |
No | Yes | Keep Alive Statement |
messages-select-all |
String | select id,priority,deliverycount,expirationtime from ${schema-prefix}messages where queuename = ? order by id |
No | Yes | Messages/Select All |
messages-select-single |
String | select priority,deliverycount,expirationtime,content from ${schema-prefix}messages where queuename = ? and id = ? |
No | Yes | Messages/Select Single |
messages-select-max-id |
String | select max(id) from ${schema-prefix}messages where queuename = ? |
No | Yes | Messages/Select max(id) |
messages-delete-all |
String | delete from ${schema-prefix}messages where queuename = ? |
No | Yes | Messages/Delete All |
messages-delete-single |
String | delete from ${schema-prefix}messages where queuename = ? and id = ? |
No | Yes | Messages/Delete Single |
messages-delete-sequence |
String | delete from ${schema-prefix}messages where queuename = ? and id between ? and ? |
No | Yes | Messages/Delete Sequence |
messages-update-single |
String | update ${schema-prefix}messages set deliverycount = deliverycount + 1 where queuename = ? and id = ? |
No | Yes | Messages/Update Single |
messages-update-sequence |
String | update ${schema-prefix}messages set deliverycount = deliverycount + 1 where queuename = ? and id between ? and ? |
No | Yes | Messages/Update Sequence |
messages-insert |
String | insert into ${schema-prefix}messages (queuename,id,priority,deliverycount,expirationtime,content) values (?,?,?,?,?,?) |
No | Yes | Messages/Insert |
messages-insert-oracle-1 |
String | insert into ${schema-prefix}messages (queuename,id,priority,deliverycount,expirationtime,content) values (?,?,?,?,?,empty_blob()) |
No | Yes | Messages/Insert/Oracle/1 |
messages-insert-oracle-2 |
String | select content from ${schema-prefix}messages where queuename = ? and id = ? |
No | Yes | Messages/Insert/Oracle/2 |
messages-insert-oracle-3 |
String | update ${schema-prefix}messages set content = ? where queuename = ? and id = ? |
No | Yes | Messages/Insert/Oracle/3 |
durables-select-all |
String | select clientid,durablename,topicname,selector,nolocal from ${schema-prefix}durables |
No | Yes | Durables/Select All |
durables-select-single |
String | select topicname,selector,nolocal from ${schema-prefix}durables where clientid = ? and durablename = ? |
No | Yes | Durables/Select Single |
durables-insert |
String | insert into ${schema-prefix}durables (clientid,durablename,topicname,selector,nolocal) values (?,?,?,?,?) |
No | Yes | Durables/Insert |
durables-delete |
String | delete from ${schema-prefix}durables where clientid = ? and durablename = ? |
No | Yes | Durables/Delete |
xalog-select-all |
String | select id,content from ${schema-prefix}xalog |
No | Yes | XA Log/Select All |
xalog-select-single |
String | select content from ${schema-prefix}xalog where id = ? |
No | Yes | XA Log/Select Single |
xalog-select-max-id |
String | select max(id) from ${schema-prefix}xalog |
No | Yes | XA Log/Select max(id) |
xalog-insert |
String | insert into ${schema-prefix}xalog (id,content) values (?,?) |
No | Yes | XA Log/Insert |
xalog-insert-oracle-1 |
String | insert into ${schema-prefix}xalog (id,content) values (?,empty_blob()) |
No | Yes | XA Log/Insert/Oracle/1 |
xalog-insert-oracle-2 |
String | select content from ${schema-prefix}xalog where id = ? |
No | Yes | XA Log/Insert/Oracle/2 |
xalog-insert-oracle-3 |
String | update ${schema-prefix}xalog set content = ? where id = ? |
No | Yes | XA Log/Insert/Oracle/3 |
xalog-delete |
String | delete from ${schema-prefix}xalog where id = ? |
No | Yes | XA Log/Delete |
<swiftlet name="sys$store">
<statements schema-prefix="..." keepalive="..." messages-select-all="..." messages-select-single="..." messages-select-max-id="..." messages-delete-all="..." messages-delete-single="..." messages-delete-sequence="..." messages-update-single="..." messages-update-sequence="..." messages-insert="..." messages-insert-oracle-1="..." messages-insert-oracle-2="..." messages-insert-oracle-3="..." durables-select-all="..." durables-select-single="..." durables-insert="..." durables-delete="..." xalog-select-all="..." xalog-select-single="..." xalog-select-max-id="..." xalog-insert="..." xalog-insert-oracle-1="..." xalog-insert-oracle-2="..." xalog-insert-oracle-3="..." xalog-delete="..."/>
</swiftlet>
<swap> Entity
Swap Settings
This is a fixed child entity of <swiftlet name="sys$store">.
| Parameter | Type | Default | Mandatory | Reboot Required | Description |
|---|---|---|---|---|---|
path |
String | ./ |
No | Yes | Path of Swap Files |
roll-over-size |
Long | 10485760 |
No | Yes | Roll Over Size (min: 1048576) |
<swiftlet name="sys$store">
<swap path="..." roll-over-size="..."/>
</swiftlet>