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 message store of the ESB profile. 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.
Quick transactions – JDBC message stores are capable of handling a large number of 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 a large volume 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.

Sample scenario

In this sample:

The client sends requests to a proxy service.
The proxy service stores the messages in a JDBC message store.
The back-end service is invoked by a message forwarding processor, which picks the messages stored in the JDBC message store.

Prerequisites:

Setup the database.

If you are setting up a MySQL database, the DB script to create the required table is as follows:

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

If you are setting up a H2 database, the DB script to create the required table is as follows:
```
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 )
)
```
Note

You can create a similar script based on the database you want to set up.

Add the relevant database driver into the <EI_HOME>/lib folder.

Configure the sample

Create a proxy service that stores messages to the JDBC message store.
Create a JDBC message store.

Create a message forwarding processor to consumes the messages stored in the message store.

Sample configuration that uses a MySQL database named sampleDB and the database 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>

Configure the back-end service

Deploy the SimpleStockQuoteService client by navigating to <EI_HOME>/samples/axis2Server/src/SimpleStockQuoteService, and running the ant command in the command prompt or shell script. This will build the sample and deploy the service for you. For more information on sample back-end services, see Deploying sample back-end services.
The ESB profile of WSO2 EI comes with a default Axis2 server, which you can use as the back-end service for this sample. To start the Axis2 server, navigate to <EI_HOME>/samples/axis2server, and run axis2Server.sh on Linux or axis2Server.bat on Windows.
Go to http://localhost:9000/services/SimpleStockQuoteService?wsdl and verify that the service is running.

Execute the sample

To invoke the proxy service, navigate to <EI_HOME>/repository/samples/axis2client, and execute the following command:

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