Use DBLookup Mediator in WSO2 ESB

DBLookup mediator is used to execute a sql query in to the synapse, it provides you result of sql query in property tag and which these properties can be used anywhere in service as required.

Above logic can be implemented in DSS also and it is very easy to do that but it depends on the requirement. Here we will only discuss dblookup in synapse level.

DBLookup mediator can be implemented in synapse directly or there is console UI configuration as well as mentioned below.

In the dblookup mediator you need to pass database name, url or whatever required to make a connection with the database and pass the sql query which needs to be run.

 This is the UI configuration of the DB lookup where you need to pass parameter to make a connection with the database.

This UI is for to make connection from the data source. To make connection you need to pass required values.

The entire above configuration can be implemented directly in the synapse file.

Synapse Code:

<dblookup xmlns="http://ws.apache.org/ns/synapse">

<connection>

<pool>

<driver>oracle.jdbc.OracleDriver</driver>

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

<user>XXXX</user>

<password>XXXX</password>

<url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST=<HOST_NAME>)(PORT=<PORT_NO>)) (ADDRESS=(PROTOCOL=TCP)(HOST=<HOST_NAME>)(PORT=<PORT_NO>))(FAILOVER=on) (LOAD_BALANCE=on)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=<SERVICE_NAME>) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) )))</url></pool>

</connection>

<statement><sql>select * from EMP_DETAILS where emp_id = ?</sql> <parameter expression="synapse:get-property(empID)" type="VARCHAR"/>

<result name="RequestNumber" column="REQUEST_NUMBER_V"/></statement>

</dblookup>

In the above example employee id is the input parameter which you need to pass in parameter tag and you can get the detail whatever employee details has as required. Result of the query comes in result tag and there is a attribute in result tag which stored result data of query for a particular column which is attached with another attribute called column.