Database interactions in mediation using DBLookup and DBReport mediators with MySQL

MySQL is known to be the world's most popular open source database due to its fast performance. It is a known fact that a many individuals as well as a lot of world's largest organizations such as Yahoo, Google, Nokia and YouTube use MySQL as their database to save time and money. WS02 ESB has a wide variety of mediators that enables us to connect, manage and transform service interactions between Web services. The DBLookup and DBReport mediators allows you to read data from a database and also to write to a database. Generally we use Apache Derby as the database in our samples but we can use MySQL as well. Let us see how we can use these mediators to look up and report to a MySQL database.

Step 1 - Setting up the MySQL database
You will need to install MySQL server on your machine. Once you have installed MySQL, log into the database using the command

mysql -u <username> -p
E.g.:- mysql -u root -p

Create a database using the following command.
create database esb;

Select the newly created database as follows
CREATE table company(name varchar(10), id varchar(10), price double);

Now create a table using the following command on the newly created database
INSERT into company values ('IBM','c1',0.0);
INSERT into company values ('SUN','c2',0.0);
INSERT into company values ('MSFT','c3',0.0);

Step 2 - Installing WSO2 ESB

a) Downloading the binary distribution

You will need to install WSO2 ESB on your machine. You can download the latest ESB binary distribution from here.

b) Deploying the service

Extract the distribution and navigate to the ESB_HOME/samples/axis2Server folder and start the Axis2 Server. To deploy the SimpleStockQuoteService go to ESB_HOME/samples/axis2Server/src/SimpleStockQuoteService and type 'ant' and it will build the particular service. You can verify whether the service has been deployed successfully by accessing the URL http://localhost:9000/soap/

c) Starting the Simple Axis2 Server

To start the simple axis server navigate to ESB_HOME/samples/axis2Server and run ./axis2Server.sh. Then it would start the axis2 server on port 9000

d) Starting the WSO2 ESB server

To start the WSO2 ESB server go to ESB_HOME/bin and run ./wso2-esb.sh. Once the WSO2 ESB server is started you will be able to access the WSO2 ESB admin console using https://localhost:9444/esb

Step 3 - Create configuration

The following configuration sends a request to a service and receives a response and then the database is updated from those retrieved response values. Create the following configuration using the WS02 ESB admin console.

<syn:definitions xmlns:syn="http://ws.apache.org/ns/synapse">
  <syn:sequence name="sample_sequence2">
        <syn:in>
            <syn:send>
                <syn:endpoint>
                    <syn:address uri="http://localhost:9000/soap/SimpleStockQuoteService"/>
                </syn:endpoint>
            </syn:send>
        </syn:in>
        <syn:out>
            <syn:log level="custom">
                <syn:property name="text" value="** Reporting to the Database **"/>
            </syn:log>
            <syn:dbreport>
                <syn:connection>
                    <syn:pool>
                        <syn:driver>com.mysql.jdbc.Driver</syn:driver>
                        <syn:property name="autocommit" value="false"/>
                        <syn:password>admin</syn:password>
                        <syn:user>root</syn:user>
                        <syn:url>jdbc:mysql://127.0.0.1:3306/esb</syn:url>
                    </syn:pool>
                </syn:connection>
                <syn:statement>
                    <syn:sql><update company set price=? where name =?></syn:sql>
                    <syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:last/child::text()" type="DOUBLE"/>
                    <syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:symbol/child::text()" type="VARCHAR"/>
                </syn:statement>
            </syn:dbreport>
            <syn:log level="custom">
                <syn:property name="text" value="** Looking up from the Database **"/>
            </syn:log>
            <syn:dblookup>
                <syn:connection>
                    <syn:pool>
                        <syn:driver>com.mysql.jdbc.Driver</syn:driver>
                        <syn:password>admin</syn:password>
                        <syn:user>root</syn:user>
                        <syn:url>jdbc:mysql://127.0.0.1:3306/esb</syn:url>
                    </syn:pool>
                </syn:connection>
                <syn:statement>
                    <syn:sql><select * from company where name =?></syn:sql>
                    <syn:parameter xmlns:m0="http://services.samples/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" expression="//m0:return/m0:symbol/child::text()" type="VARCHAR"/>
                    <syn:result name="stock_price" column="price"/>
                </syn:statement>
            </syn:dblookup>
            <syn:log level="custom">
                <syn:property xmlns:ns1="http://org.apache.synapse/xsd" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" name="text" expression="fn:concat('Stock price - ',get-property('stock_price'))"/>
            </syn:log>
            <syn:send/>
        </syn:out>
    </syn:sequence>
</syn:definitions>

NOTE: When using MySQL as the database make sure that you set the following property in the configuration

<syn:property name="autocommit" value="false"/>

Step 4 - Invoking the client

Once the configuration is created you should be able to invoke the stockquote client using the following command.

ant stockquote -Daddurl=http://localhost:9000/soap/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

If the client executes successfully you should see the following on the WSO2 ESB console

DEBUG LogMediator Start : Log mediator
INFO LogMediator text = ** Reporting to the Database **
DEBUG LogMediator End : Log mediator
....
DEBUG DBReportMediator Successfully prepared statement : update company set price=? where name =? against DataSource : jdbc:mysql://127.0.0.1:3306/esb
DEBUG DBReportMediator Inserted 1 row/s using statement : update company set price=? where name =?
....
DEBUG LogMediator Start : Log mediator
INFO LogMediator text = ** Looking up from the Database **
DEBUG LogMediator End : Log mediator
....
DEBUG DBLookupMediator Successfully prepared statement : select * from company where name =? against DataSource : jdbc:mysql://127.0.0.1:3306/esb
DEBUG DBLookupMediator End : DBLookup mediator
DEBUG LogMediator Start : Log mediator
INFO LogMediator text = Stock price - 147.286209770313
DEBUG LogMediator End : Log mediator

Finally you can check if the company table has been updated with the new stock value by a simple query.