WSO2 ESB Simple vs Complex Data Operations

WSO2 supports simple JDBC operations by providing couple of mediators, called DBLookup and DBReport Mediators.
According to the WSO2 docs,
The DBLookup Mediator can execute an arbitrary SQL select statement and then set a resulting values as a local message property in the message context. The DB connection used may be looked up from an external data source or specified inline.
DBReport is similar to DBLookup but should be used to insert and update data.

The limitation of DBLookup is that it will return only one row and can not return multiple rows. WSO2 recommends using WSO2 Data Services product for any use cases that falls outside of this scope.
DBReport mediator allows us to define one or more DML statements within it like as shown below,

<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 some_table1 set column1=? where column2 =?</sql>
  <parameter expression="" type=""/>
  <parameter expression="" type=""/>
 </statement>
 <statement>
  <sql>update some_table2 set column1=?, column2=? where column3 =?</sql>
  <parameter expression="" type=""/>
  <parameter expression="" type=""/>
  <parameter expression="" type=""/>
 </statement>
</dbreport>

These 2 mediator would suffice most of the simple and common use cases.

But there are use cases which pushes you to the other side when you are dealing with complex applications integration. Lets say a use case where you want to check if something exists, if so do some inserts updates on multiple tables and then return something else with in a single transaction.
WSO2 built in mediators would not help much with uses cases like this.

WSO2's OSGI framework offer serious limitations on using frameworks like Spring within WSO2. At this point you are kind of forced to write plain JDBC code on your own using class mediator.
Who wants to write this plain old boring JDBC boiler plate code line getConnection(), connection.createStatement(), connection.close() etc. Please refer to other article on how to configure the datasource.

You would obviously think of bringing in some open source tool that does all this so I decided to bring in apache commons DBUtils lib. Apparently there are some issues loading DButils in WSO2 ESB version 4.9.0 running on JDK8. It simply crashes the server, I mean the WSO2 server doesn't start up.

So I left with no choice other than building my own framework to achieve data operations using simple JDBC.
Here is the class that helps you get connection from datasource,

class DataSupport {

 public static DataSource sqlDataSource;
 private static String ENV_DS_KEY = "java.naming.factory.initial";
 private static String ENV_DS_VALUE = "org.wso2.carbon.tomcat.jndi.CarbonJavaURLContextFactory";
 private static String DEMO_DS = "jdbc/demoDB";

 /**
  * Defaults to DEMO_DS
  * 
  * @return
  */
 public static DataSource getDataSource() {
  return getDataSource(DEMO_DS);
 }

 /**
  * overloaded method for getDataSource()
  * 
  * @param dataSourceName
  * @return
  */
 public static DataSource getDataSource(String dataSourceName) {

  if (sqlDataSource != null)
   return sqlDataSource;
  if(dataSourceName == null)
   dataSourceName=DEMO_DS;
  try {
   Hashtable<String, String> environment = new Hashtable<String, String>();
   environment.put(ENV_DS_KEY, ENV_DS_VALUE);
   Context initContext = new InitialContext(environment);
   sqlDataSource = (DataSource) initContext.lookup(dataSourceName);
   if (sqlDataSource != null)
    System.out.println("Datasource Bind successfully :"
      + dataSourceName);
   else
    System.out.println("Cannot find DataSource Binding for: "
      + dataSourceName);

  } catch (NamingException e) {
   e.printStackTrace();
  }

  return sqlDataSource;
 }
 
 /**
  * Gets the connection If the DataSource is null then uses default
  * DataSource
  * 
  * @param dataSourceName
  * @return
  */
 public static Connection getConnection(DataSource dataSource)
   throws SQLException {

  Connection connection = null;
  if (dataSource == null)
   getDataSource();

  try {
   connection = sqlDataSource.getConnection();
  } catch (SQLException e) {
   System.out.println("Failed to obtain Connection for DataSource: ");
   System.out.println(e.getMessage());
  }

  return connection;
 }
}

You can add commit(), rollback() and close() methods to the above class.
After this you can write any class that simply obtains datasource, connection and does remaining boiler plate code as shown below,

public class DemoDBMediator extends AbstractMediator {

 private static String UPDATE1_SOME_TABLE = "Your update Query 1";
 private static String UPDATE2_SOME_TABLE = "Your update query 2";
 
 public boolean mediate(MessageContext mc) {

 try {
  DataSource dataSource = DataSupport.getDataSource();
  connection = DataSupport
    .getConnection(dataSource);
  preparedStatement = connection.prepareStatement(UPDATE1_SOME_TABLE);
  preparedStatement.setString(1, variable1);
  preparedStatement.setString(2, variable2);
  preparedStatement.executeUpdate();
  DataSupport.close(preparedStatement);
  
  preparedStatement = connection.prepareStatement(UPDATE1_SOME_TABLE);
  preparedStatement.executeUpdate();
  DataSupport.close(preparedStatement);

  DataSupport.commitAndcloseConnection(connection);
 } catch (SQLException e) {
  try {
   DataSupport.rollbackAndcloseConnection(connection);
  } catch (SQLException ex) {
   System.out
     .println("Error while closing connection"
       + ex.getMessage());
  }

                return false;

 } finally {
  try {
   DataSupport.close(preparedStatement);
   DataSupport.close(connection);
  } catch (SQLException e) {
   System.out
     .println("Error while closing connection"
       + e.getMessage());
  }

 }

      return true;
}

You can configure this class mediator in your proxy xml just like any other mediator.
Of course you are responsible for managing the transactions at connection level in this form of database operations.

Another approach if you are interested is, you could create your own DB Mediator by extending AbstractDBMediator, just like DBLookup or DBReport and build the WSO2 synapse OSGI bundle. You can get the wso2-synapse source code from GitHub.

Happy Coding!