JDBC Store Swiftlet
Overview
The JDBC Store Swiftlet (sys$store) provides persistent and non-persistent message storage for queues using a relational database via JDBC. It manages transactional message operations, durable subscriber metadata, and swap file handling for non-persistent storage, supporting high reliability and scalability for SwiftMQ routers.
Features
JDBC-Based Persistent Message Storage
The Swiftlet stores persistent queue messages in a relational database using JDBC. Each queue has its own persistent store, and messages are inserted, retrieved, updated, or deleted using configurable SQL statements. The Swiftlet supports different database backends (e.g., Oracle, TimesTen) by allowing the use of custom connection factories and SQL statement templates, including special handling for BLOBs in Oracle databases.
All SQL statements used for message and XA log operations are configurable, and a schema prefix can be set for table names. The Swiftlet manages connection pooling for efficient database access, with parameters for minimum/maximum connections, idle timeout, and keepalive interval.
Configurable SQL Statements
All SQL statements for message and XA log operations are defined under the statements entity. The schema-prefix property allows prepending a schema to all table names. Oracle-specific multi-step BLOB insert/update statements are supported for large message payloads.
Connection Pooling
Database connections are pooled for efficiency. The pool size, idle timeout, and keepalive interval are configurable. The connection pool ensures a minimum number of connections are always available, and idle connections are periodically validated and closed if necessary.
Configuration Example:
<swiftlet name="sys$store">
<configuration>
<entity name="jdbc" driver-classname="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/swiftmq" username="swiftuser" password="secret"/>
<entity name="connection-pool" min-connections="10" max-connections="50"/>
<entity name="statements" schema-prefix="myschema."/>
<entity name="swap" path="/var/swiftmq/swap" roll-over-size="20971520"/>
</configuration>
</swiftlet>
Non-Persistent Message Storage via Swap Files
For non-persistent queues, messages are stored in swap files on the filesystem. Each queue has its own set of swap files, which are created in a configurable directory. When a swap file reaches the configured roll-over size, a new file is created. The swap mechanism ensures that non-persistent messages are efficiently managed without database overhead, while still supporting large queues.
Swap File Management
The swap file directory and roll-over size are configurable. Swap files are automatically deleted on startup to ensure a clean state. Each swap file is used until it reaches the roll-over size, after which a new file is created.
Configuration Example:
<swiftlet name="sys$store">
<configuration>
<entity name="swap" path="/tmp/swiftmq_swap" roll-over-size="52428800"/>
</configuration>
</swiftlet>
Durable Subscriber Metadata Storage
The Swiftlet manages metadata for durable topic subscriptions, storing information such as clientId, durableName, topicName, selector, and noLocal flag in the database. This enables reliable recovery and management of durable subscriptions across router restarts. The SQL statements for managing durable subscriber entries are also configurable.
Durable Store Operations
APIs are provided to insert, retrieve, delete, and iterate over durable subscriber entries. All operations are performed using pooled JDBC connections and configurable SQL statements.
Configuration Example:
<swiftlet name="sys$store">
<configuration>
<entity name="statements" durables-select-all="select * from mydurables"/>
</configuration>
</swiftlet>
XA Transaction Support and Prepare Log
The Swiftlet supports XA (two-phase commit) transactions for both persistent and non-persistent stores. It maintains a prepare log in the database to track prepared transactions, ensuring transactional integrity even in the event of failures. The prepare log is managed using configurable SQL statements, and the Swiftlet provides APIs to retrieve and remove prepare log records as part of XA recovery.
Configurable XA Log SQL
All SQL statements for XA log operations (insert, select, delete) are configurable, including Oracle-specific BLOB handling for large transaction records.
Configuration Example:
<swiftlet name="sys$store">
<configuration>
<entity name="statements" xalog-insert="insert into myxalog (id,content) values (?,?)"/>
</configuration>
</swiftlet>
Internal Queue Naming
<clientId>$<durableName>— Queue for durable topic subscription storage; used to persist messages for a durable subscriber.
Configuration Guide
Configure for MySQL with Custom Schema Prefix
Use this scenario when deploying SwiftMQ with a MySQL backend and a specific schema for all tables. This ensures all SQL statements reference the correct schema.
- Set the JDBC driver class name and URL for MySQL.
- Set the username and password for the database.
- Set the schema prefix under the statements entity.
<swiftlet name="sys$store">
<configuration>
<entity name="jdbc" driver-classname="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/swiftmq" username="swiftuser" password="secret"/>
<entity name="statements" schema-prefix="myschema."/>
</configuration>
</swiftlet>
Increase Connection Pool Size for High Throughput
When expecting high message throughput, increase the minimum and maximum number of JDBC connections in the pool to avoid contention and improve performance.
- Adjust the min-connections and max-connections attributes in the connection-pool entity.
<swiftlet name="sys$store">
<configuration>
<entity name="connection-pool" min-connections="20" max-connections="100"/>
</configuration>
</swiftlet>
Change Swap File Location and Size
To optimize disk usage or use a faster storage device for non-persistent queues, change the swap file directory and increase the roll-over size.
- Set the path and roll-over-size attributes in the swap entity.
<swiftlet name="sys$store">
<configuration>
<entity name="swap" path="/mnt/fastdisk/swiftmq_swap" roll-over-size="52428800"/>
</configuration>
</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>