JDBC Message Store for WSO2 ESB


Message stores in WSO2 ESB is important in implementing various scenarios like store & forwarding, and reliable delivery. They can be used to persist messages during mediation or even after. Currently synapse uses in-memory message stores, which is incapable of persists after execution. It is also consuming memory too. JMS message stores can save messages, but it need more additional resources and speed becomes slower. So as a good alternative for those is a JDBC message store. This has been added to WSO2 ESB 4.9.0 onwards.

JDBC message store

In this implementation ESB uses the same implementation of message store already in ESB but in a different form. It is designed closely similar to the design of JMS message store implementation in WSO2 ESB. It uses a JDBC connector to connect to external relational databases (eg: mysql, h2 are tested).

JDBC Message Store for store and Forward Pattern


From the very beginning of the designing phase JDBC Message Store focused on eliminating the difficulties that faced when using JMS queues as message stores. So following list of aims are achieved from JDBC store,

Easy to connect – It’s rather easy to connect with databases compared to JMS queues
More operations on data – In JMS queues methods like random selecting messages are not supposed to support. But with JDBC it become a reality, and has operations very close to in-memory stores.
Fast transactions – In test I have seen that JDBC stores are capable of handling about 2300 transactions per second which is 10 times faster than the existing system.
Work with high capacity and long-time – Since JDBC Stores uses databases as the medium to store data, and can depend on up to Terabytes of data. It is also generally accepted that Databases are capable of handling data for long-time compared to JMS queues.
After having tests in different backgrounds with different configurations, I have seen that the outcomes of JDBC message store has achieved more than expected at the initial stages.

In this store implementation, the message is converted to serializable Java object, so it to be able to store as a blob.
Construction of the persisting message has two basic parts JDBC Axis2 Message and JDBC Synapse Message. Combination of those two will produce the Storable Message , which is sent to database.

Constructing a Storable Message

Other than message constructing classes following are explanation on what rest of the classes are doing.

JDBCMessageStore– Provides the fundamental interface to external parties by encapsulating the underlying implementations. This class exposes store, poll, get, clear, peek and other generic methods of messages stores to outside parties.
JDBCMessageStoreConstants – This class defines the related constant values for JDBC message store. This class make it easy for maintain JDBC store implementation by gathering all the constants in to a single place.
JDBCConfiguration – This class was defined to provide necessary utility functionalities to JDBC operations. Basically it deals with creating connections and terminating connections, querying database tables etc.
JDBCMessageConverter – This class is to help with converting SOAP messages in to serializable Java objects and the reverse process after querying the required. This works as an adaptor between database and ESB.
JDBCProducer – This is to produce messages into store which is used by the store mediator.
JDBCConsumer – This is to consume messages from a message-store, and to be used by message processors.
Those classes along with the classes mentioned previously, creates a successful JDBC Message Store.

Configuration of JDBC Message Store

To use JDBC Message store customer has to add the required JDBC support. There after following configuration will allow any message processor to use JDBC message store as same as other message store. Configuration can be specified as an inline or points to a datastore (which gives you additional control over database).

<store messageStore="MyStore"/>

<messageStore class="org.apache.synapse.message.store.jdbc.JDBCMessageStore" name="MyStore">

<parameter name="store.jdbc.driver">com.mysql.jdbc.Driver</parameter>
<parameter name="store.jdbc.connection.url">jdbc:mysql://localhost:3306/mystore</parameter>
<parameter name="store.jdbc.username">root</parameter>
<parameter name="store.jdbc.password"></parameter>
<parameter name="store.jdbc.table">store_table</parameter>


<parameter name="store.jdbc.dsName">reportDB</parameter>
<parameter name="store.jdbc.table">store_table</parameter>



In-lined Data Source

store.jdbc.driver – Database driver class name
store.jdbc.connection.url– Database URL
store.jdbc.username – User name for access Database
store.jdbc.password – Password for access Database
store.jdbc.table – Table name of the database

External Data Source

store.jdbc.dsName – The name of the Datasource to be looked up
store.jdbc.table – Table name of the database
store.jdbc.icClass – Initial context factory class. The corresponding java environment property is java.naming.factory.initial
store.jdbc.connection.url– The naming service provider url . The corresponding java environment property is java.naming.provider.url
store.jdbc.username – This is corresponding to the java environment property java.naming.security.principal
store.jdbc.password – This is corresponding to the java environment property This is corresponding to the java environment property java.naming.security.principal

Database script

For creation database table you can use following scripts.

CREATE TABLE jdbc_store_table(
msg_id VARCHAR( 200 ) NOT NULL ,
message BLOB NOT NULL ,
PRIMARY KEY ( indexId )

H2 :
CREATE TABLE jdbc_store_table(
msg_id VARCHAR( 200 ) NOT NULL ,
message BLOB NOT NULL ,
PRIMARY KEY ( indexId )

You can create similar SQL script according to your database.


First you need to put the relevant database driver into repository/components/lib folder. [2]
Following sample configuration is based on a mqsql database name “mystore” and table “store_table”.

<proxy xmlns="http://ws.apache.org/ns/synapse"
       transports="https http"
         <property name="FORCE_SC_ACCEPTED" value="true" scope="axis2"/>
         <property name="OUT_ONLY" value="true"/>
         <property name="target.endpoint" value="StockQuoteServiceEp"/>
         <store messageStore="MyStore"/>
   <publishWSDL uri="http://localhost:9000/services/SimpleStockQuoteService?wsdl"/>

<messageStore xmlns="http://ws.apache.org/ns/synapse"
   <parameter name="store.jdbc.password"/>
   <parameter name="store.jdbc.username">root</parameter>
   <parameter name="store.jdbc.driver">com.mysql.jdbc.Driver</parameter>
   <parameter name="store.jdbc.table">store_table</parameter>
   <parameter name="store.jdbc.connection.url">jdbc:mysql://localhost:3306/mystore</parameter>

<messageProcessor xmlns="http://ws.apache.org/ns/synapse"
   <parameter name="max.delivery.attempts">5</parameter>
   <parameter name="interval">10</parameter>
   <parameter name="is.active">true</parameter>

Message processor also can be added via management console through following section:

JDBC Message Processor


Use axis2 client as follows to send messages to the proxy:

ant stockquote -Daddurl=http://localhost:8280/services/MessageStoreProxy

Rough comparison between JMS and JDBC message store performance

For the testing the selected database details are as follows:

Type : MySQL on XAMPP server for database (JMS is just for comparison)
Storage Engine : MyISAM
Machine Details : Ubuntu 14.04 on Intel® Core™ i7-4800MQ CPU @ 2.70GHz × 8 with 16 GB RAM
Messages sent via : JMeter client

# of Threads Messages/Thread Total Messages Throughput / sec
JMS Store Producer JDBC Store Producer
1 100 100 63.9 552.5
1 500 500 62.8 566.3
1 1000 1000 63.5 577.4
1 2000 2000 62.3 629.3
1 3000 3000 62.8 649.9
10 10 100 73.7 108.8
10 50 500 70.4 511.8
10 100 1000 70.7 928.5
10 200 2000 71.3 1537.3
10 300 3000 72.3 1827
50 10 500 71.6 494.1
50 100 5000 72.5 3494.1
100 250 25000 73.4 5529.8
500 100 50000 7055.2

(Note: At small number of messages, JDBC figures have not reached stable throughput)


[1] Pull request containing the implementation – https://github.com/wso2/wso2-synapse/pull/91

[2] MySQL connector – https://dev.mysql.com/downloads/connector/j/

[3] WSO2 ESB 4.9.0-ALPHA – https://github.com/wso2/product-esb/releases/tag/esb-parent-4.9.0-ALPHA



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s