WSO2 ESB Configure Datasource

     WSO2 ESB has two built in mediators called DBLookup and DBReport mediators that allows you to do database related operations.
In order to use these mediators you will require to load the database drivers, so copy those drivers into \<wso2esb-4.9.0>\repository\components\extensions directory.

Configuration for DBLookup is something similar to this,

<dblookup xmlns="http://ws.apache.org/ns/synapse">
 <connection>
  <pool>
   <driver>org.apache.derby.jdbc.ClientDriver</driver>
   <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
   <user>esb</user>
   <password>esb</password>
  </pool>
 </connection>
 <statement>
  <sql>select column1 from my_table where name =?</sql>
  <parameter expression="get-property('anyParam1')" type="VARCHAR" />
  <result name="anyName" column="column1"/>
 </statement>
</dblookup>

Configuration for DBReport is very similar to the above,

<dbreport xmlns="http://ws.apache.org/ns/synapse">
 <connection>
  <pool>
   <driver>org.apache.derby.jdbc.ClientDriver</driver>
   <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
   <user>esb</user>
   <password>esb</password>
  </pool>
 </connection>
 <statement>
  <sql>update my_table column1 = ? where name =?</sql>
  <parameter expression="get-property('anyParam1')" type="VARCHAR" />
  <parameter expression="get-property('anyParam2')" type="VARCHAR" />
 </statement>
</dblookup>

For better manageability, you would require to configure a datasource instead of configuring the connection pool using database properties in the proxy xml service.
Configuring datasource in WSO2 ESB is really simple. Start the wso2 server and login to management console at https://localhost:9443/carbon
Go to Configure tab on the left and then navigate to Datasources
Click on Add datasource link,

Fill out the details as required. Enter the Name for JNDI datasource as jdbc/demoDB.
Test the connection, save if successful.
Now you can reconfigure your DB mediators in proxy service xml to use the datasource as shown below,

<dblookup xmlns="http://ws.apache.org/ns/synapse">
 <connection>
  <pool>
   <dsName>jdbc/demoDB</dsName>
  </pool>
 </connection>
 <statement>
  <sql>select column1 from my_table where name =?</sql>
  <parameter expression="get-property('anyParam1')" type="VARCHAR" />
  <result name="anyName" column="column1"/>
 </statement>
</dblookup>

That's easy enough..!