JDBC Message Store

The JDBC message store can be used to store and retrieve messages more efficiently in comparison with other message stores.

The JDBC message store implementation is a variation of the already existing synapse message store implementation and is designed in a manner similar to the WSO2 ESB JMS message store. The JDBC message store uses a JDBC connector to connect to external relational databases.

The advantages of using a JDBC message store instead of any other message store are as follows:

Easy to connect – You only need to have a JDBC connector to connect to an external relational database.
Supports more operations on data – For example, JDBC message stores support random message selection and has operations very close to in-memory stores.
Quick transactions – JDBC message stores are capable of handling about 2300 transactions per second.
Ability to work with a high capacity for a long period of time – Since JDBC Stores use databases as the medium to store data, it can store terabytes of data and is capable of handling data for a longer period of time.

Configuring the JDBC message store

The syntax of the JDBC message store can be different depending on whether you connect to the database using a connection pool, or using a datasource. Click on the relevant tab to view the syntax based on how you want to connect to the database.

UI Configuration

The UI of the JDBC Message Store that is displayed can vary depending on whether you connect to the database using a connection pool, or using a datasource. Click on the relevant tab to view the UI that is displayed based on how you want to connect to the database.

Example

Initially you need to setup a database to suite for this.

DB Scripts to create table :

For MySQL :

CREATE TABLE jdbc_message_store(
indexId BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
msg_id VARCHAR( 200 ) NOT NULL ,
message BLOB NOT NULL ,
PRIMARY KEY ( indexId )
)

For H2 :

CREATE TABLE jdbc_message_store(
indexId BIGINT( 20 ) NOT NULL AUTO_INCREMENT ,
msg_id VARCHAR( 200 ) NOT NULL ,
message BLOB NOT NULL ,
PRIMARY KEY ( indexId )
)

You can create similar SQL script according to your database type.

You need to put the relevant database driver into repository/components/lib folder. This sample is based on a MySQL database name “sampleDB” and table “jdbc_message_store”.

<proxy xmlns="http://ws.apache.org/ns/synapse"
      name="MessageStoreProxy"
      transports="https http"
      startOnLoad="true"
      trace="disable">
  <description/>
  <target>
     <inSequence>
        <property name="FORCE_SC_ACCEPTED" value="true" scope="axis2"/>
        <property name="OUT_ONLY" value="true"/>
        <property name="target.endpoint" value="StockQuoteServiceEp"/>
        <store messageStore="SampleStore"/>
     </inSequence>
  </target>
  <publishWSDL uri="http://localhost:9000/services/SimpleStockQuoteService?wsdl"/>
</proxy>
 
<messageStore xmlns="http://ws.apache.org/ns/synapse"
             class="org.apache.synapse.message.store.impl.jdbc.JDBCMessageStore"
             name="SampleStore">
  <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">jdbc_message_store</parameter>
  <parameter name="store.jdbc.connection.url">jdbc:mysql://localhost:3306/sampleDB</parameter>
</messageStore>
 
<messageProcessor xmlns="http://ws.apache.org/ns/synapse"
                 class="org.apache.synapse.message.processor.impl.forwarder.ScheduledMessageForwardingProcessor"
                 name="ScheduledProcessor"
                 messageStore="SampleStore">
  <parameter name="max.delivery.attempts">5</parameter>
  <parameter name="interval">10</parameter>
  <parameter name="is.active">true</parameter>
</messageProcessor>

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

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

Refer documentation on working with axis2-stockquote client.