onsdag 20 augusti 2014

Quick tip: How to change database dynamically in a Mule flow for a JDBC endpoint

Lets say you have lots of databases which you for some reason would like to use the same integration flow against. For example you might have a distributed company structure with lots of databases of the same structure for each subsidiary and you might want to run a query against all of them to consolidate the results to some kind of reporting system.
To avoid having a connector for each of the databases (if you lets say have 50 of subsidiaries with the same financial system) and to avoid having each query to be specific for each subsidiary system here comes today's quick tip on one way to dynamically change the database on the fly in your integration flow.

Note. I will not go into how to configure JDBC connectors , or any best practice around JDBC connectors , performance issues or discussions if this solution is good or bad architecture. I'm just creating a case to point out how one can change a data-source on the fly from within a Mule flow.
So here we go:

First you can specify a datasource...something like this:

 <spring:bean id="dataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown" name="dataSource">  
       <spring:property name="driverName" value="net.sourceforge.jtds.jdbc.Driver"/>  
       <spring:property name="user" value="${dbusername}"/>  
       <spring:property name="password" value="${dbpassword}"/>  
     </spring:bean>  
   </spring:beans>  

Then you can setup a connector using that datasource with all the querys you need like this.


  <jdbc:connector name="db_conn" dataSource-ref="dataSource" pollingFrequency="5000" doc:name="Database" validateConnections="false">  
     <jdbc:query key="myFantasticQuery1" value="SELECT #[header:INBOUND:company] as company, blah , blah, #[header:INBOUND:dynamicallysetvalue] as blah,FROM ... WHERE  #[header:INBOUND:anotherdynamicallysetvalue]);"/>           <jdbc:query key="myFantasticQuery2" value="SELECT blh blah blah.....and so on  

Now you can use an endpoint to call one of the querys..for example inside of an "ALL" component http://www.mulesoft.org/documentation/display/current/All+Flow+Control+Reference.

 <jdbc:outbound-endpoint connector-ref="db_conn" doc:name="DB" exchange-pattern="request-response" querykey="myFantasticQuery1"></jdbc:outbound-endpoint>  

But this would cause the query's for all subsidiaries to run the query on the same database.

To change the database on the fly dynamically based on header values right before the call to the jdbc:endpoint a Java component comes handy:


 public class ChangeDatabase implements Callable {  
           private String serverip;            
           private String serverport;  
           private String dbprefix;  
           @Override  
           public Object onCall(MuleEventContext eventContext) throws Exception {  
             boolean success = false;  
             MuleMessage message = eventContext.getMessage();  
             org.enhydra.jdbc.standard.StandardDataSource ds = (org.enhydra.jdbc.standard.StandardDataSource) eventContext.getMuleContext().getRegistry().lookupObject("dataSource");  
             ds.setUrl("jdbc:jtds:sqlserver://" + serverip + ":" + serverport + ";databaseName=" +dbprefix+ message.getInboundProperty("company"));  
             success = true;  
             return success;  
        }  
      }  

This solution actually looks up the datasource object defined earlier from the Mule reigistry and sets the jdbc url based on a dynamic header. In this case a "company" header set to identify the subsidiary.

To use it simply declare inn your spring:beans section (you could use a singleton instead if your changeDatabase is thread safe)


 <spring:beans>  
     <spring:bean id="changeDB" class="ChangeDatabase">  
        <spring:property name="serverip" value="${dbserverip}"/>   
        <spring:property name="serverport" value="${dbserverport}"/>   
        <spring:property name="dbprefix" value="${dbprefix}"/>   
     </spring:bean>  
   </spring:beans>  


and add a object reference to it before the jdbc:outbound endpoint and it will automatically change database before the query runs.

 <component>  
     <spring-object bean="changeDB"/>  
 </component>  

Thats it!

Happy hacking!

2 kommentarer:

  1. Hey there - I know this is an older article, but it's something we're facing now and we're having trouble getting Mule to answer this in a satisfactory answer. There are some minor changes (such as Scatter-Gather replacing All Flow Control), but we essentially need to query similar tables from multiple databases, transform the output and dump to an XML file. The last part is easy, but the dynamic DB connection is not working so well. Are you still using Mule and do you have any additional thoughts to what you have provided already? Thanks!

    SvaraRadera

  2. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work..

    Mulesoft online training

    SvaraRadera