During my WSO2 training I often tell my students that there is another way to do something. In a series of blogs, I will look into the alternatives that WSO2 Enterprise Integrator offers.
These alternatives achieve the same functional goal. The difference is mainly a technical one and sometimes comes down to just something of preference or implementation-style. It might be that one has an advantage over the other. There are situations where one way is typically far more superior than the other, and there are also situations where, however the possibility is, it is still not advisable executing it.
In this blog I will compare a Data Service to the DBReport and DBLookup mediator as a way to insert data in a table.
Differences between Data Service and DBReport and DBLookup mediator
For the comparison of these functions let’s start with a simple setup where we have to create content in a database. We have two mediators, namely the DBReport and the DBLookup mediator, that we’ll make a connection to a database with and the alternative is using a data services service, meaning a special service that you define using either the management UI of the Enterprise Integrator or Integration Studio.
A data services service can be generated based on an existing datasource, or can be defined manually from scratch. The Wizard in the Management UI can be used to auto generate a number of operations that allow you to access the datasource’s data. The service is generated with multiple operations for the tables, so there will be an endpoint, with a number of operations generated, depending on the fact whether the table has a primary key. So, let’s say that you have a database with 10 tables. The data service generation will actually create one or multiple so-called Data Services files that will allow you to insert, read, update and delete data in those tables.
So, as one of my old friends used to say, “he who does my work, is my friend”. In this case the generation wizard is my friend as it speeds up the process, and it will give you something that you can then modify and or manipulate.
So, if you have to do an insert, you might as well generate it, and then finetune the stuff.
If you just need to insert one record in a database or update or retrieve it. Then of course you have the DB report, and the DB lookup mediator. DB lookup mediator is as the name suggests. A simple way to get the data from a database. The look up mediator is something that actually retrieves a value from the database, it can be a sum or something where if you want to do an update, you need to have the DB report mediator to update, insert or do something else.
Technology stack
I am creating a data service that inserts records in a MySQL database table. Do not forget to add the MySQL JDBC connector to the [EI-HOME]/lib directory.
I will not generate the data services, if I would have done that, I would have gotten something like this:
I will use the Create Functionality to set up the data service using the Management Console. First let us select the Create option.
Enter a name and click Next.
Next, we need to add a DataSource, click on ‘Add New Datasource’.
Fill in the details as shown and Test Connection.
I’ve used some default credentials to connect to my test-database. Please change them accordingly for your situation. Click on Save and after that on Next.
Click on Add New Query.
Fill in the details as shown in the SQL field and click the ‘Generate Input Mappings’ below it.
Navigate to Save on the bottom of the screen. Press Next to go to the final step.
Click Add New Operation.
Again, enter the details as shown in the screenshot.
Click on Save and click on Finish in the next screen.
See that the data service has been added by opening the Services list (see the left menu in the management console) and click on ‘Try this service’.
After filling in some testdata and pressing Send we can check our database to see that the record has been inserted.
Although it is a simple setup and can be done quickly it does require several steps to create. Let us look at the alternative where we’ll use the mediators.
Using a DBReport mediator
When I want to do the same with the DBReport mediator, I switch to the WSO2 Integration Studio IDE and I create a new proxy or API. There you can simply drag the mediator to the inSequence of the proxy or API and configure it.
With a Dbreport mediator the source code looks like this:
<dbreport>
<connection>
<pool>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/active</url>
<user>root</user>
<password>root</password>
</pool>
</connection>
<statement>
<sql><![CDATA[insert into records (DATE) values (?)]]></sql>
<parameter expression="get-property('DATE')" type="CHAR"/>
</statement>
</dbreport>In this sample I’ve defined the database details directly in the source code but I could’ve also defined a datasource in the Enterprise Integrator’s configuration and refer to it in the DBReport mediator. For now, the inline-details are even simpler.
As you can see, it is less work than the Data Service. But the question arises… which is better?
Mediator or Data Service, which is better?
Is there a clear winner? Let us first look from the narrow perspective of this example. A Data Service can be reused relatively easily as it is a service with an endpoint that can be addressed from anywhere. It can also be made into an API with one simple extra step. However, it is also another artifact that we deploy next to the proxy or other artifacts that we develop. In this case we defined one SQL statement, but we can define many more in a Data Service, as we saw in the image with the generated code. Also, we can do more things like validating values that will be stored within the Data Service or transform the resulting structure just-in-time before returning it to the client.
The alternative is one of two mediators, the DBReport mediator which writes to a table and the DBLookup mediator that can set a property from a field from one row in a result set. It cannot return multiple rows, for that you’ll need a Data Service.
On the other hand, calling the Data Service means that we are departing from the inSequence to call the service and returning which also takes more time than simply executing a mediator. You’ll need to implement additional fault handling logic to handle different errors where with the mediators we can solely rely on the faultSequence to handle faults.
One aspect that from this narrow perspective does not come into play is that the Data Service will also allow other data sources to be updated like a CSV file, XLS file or even a custom datasource.
A Data Service is clearly more versatile but needs more work in invoking it. But is there a clear winner? I would say that if you simply need to insert data in a table within your integration, then the DBReport mediator works fine. The same holds for the DBLookup mediator. Doing a simple lookup for one field value from a database can be done simply using the DBLookup mediator. For more complex setups, or when you need to expose your data to more consumers, then just that one integration however the Data Service is the way to go. The Data Service offers superior flexibility with a broader applicability.
