I will compare and contrast the two ways of getting information. I’m going to use an API to retrieve the information from the database and the response is going to be false or true depending on whether the client is actually a premium client.
Use Case
This case revolves around an organization that needs to check whether a user is a premium client. This example is simplified by the removing all information that is not needed for this purpose. In this case, the database needs to be checked for the user’s unique number rather than a whole record being retrieved.
The user swipes his loyalty card presenting us with a 16-character loyalty card number. The 16 characters are a unique number that will be present in the database if the client is a premium client.
Database setup
The database set up is done in my SQL and looks as follows:
CREATE database clients;
use clients;
CREATE TABLE `premiumclientdb` (
`clientnr` char(16),
`dateinserted` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`clientnr`)
);
insert into premiumclientdb (clientnr) values ("RBNL912828282828");
insert into premiumclientdb (clientnr) values ("RBNL282709383827");
select * from premiumclients;In order to test it we added the ‘select all from premium clients’ that will give us the two records that we’ve inserted.
MariaDB [clients]> select * from premiumclientdb;
+------------------+---------------------+
| clientnr | dateinserted |
+------------------+---------------------+
| RBNL282709383827 | 2020-03-09 09:03:14 |
| RBNL912828282828 | 2020-03-09 09:03:14 |
+------------------+---------------------+The logic for this use case is simple: we get the client number as a parameter in the API and we make a selection from the database where we match the client number with the database. After that we are going to use a filter mediator to filter out this binary situation, the client is either a premium client and therefore in the database or he or she is not, and the return value will be empty or nil.
Solution one: using DB lookup mediator
In this example, I have put a number of log mediators to show you that the value is passed into the API. You can see the DB lookup mediator, which is the filter mediator in the two conditional branches. In each of these branches I will use a payload mediator to build the response that is given back to the client, either false or true (as a value in the payload). As you can see, everything is done in the InSequence. As there isn’t that much backend service to call, this makes sense. The respond mediator returns the value to the client. It’s important to realize that the current approach is to handle the entire mediation from the inSequence without using the outSequence.
Let’s take a look at the source code. I will not explain every line, but focus on the two most important things, the DBLookup mediator and the Filter mediator.
<?xml version="1.0" encoding="UTF-8"?>
<api context="/clientcheck" name="IsPremiumClient" xmlns="http://ws.apache.org/ns/synapse">
<resource methods="GET" uri-template="/{clientnr}">
<inSequence>
<log level="custom">
<property expression="get-property('uri.var.clientnr')" name="Value"/>
</log>
<dblookup>
<connection>
<pool>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/clients</url>
<user>root</user>
<password>root</password>
</pool>
</connection>
<statement>
<sql>select clientnr from premiumclientdb where clientnr=?;</sql>
<parameter expression="get-property('uri.var.clientnr')" type="CHAR"/>
<result column="clientnr" name="foundnr"/>
</statement>
</dblookup>
<log level="custom">
<property expression="get-property('foundnr')" name="Value"/>
</log>
<filter regex="(.*?)" source="get-property('foundnr')">
<then>
<payloadFactory media-type="json">
<format>"Premium client" : "True"</format>
<args/>
</payloadFactory>
<log level="custom">
<property name="Result" value="Yes"/>
</log>
</then>
<else>
<payloadFactory media-type="json">
<format>"Premium client" : "False"</format>
<args/>
</payloadFactory>
<log level="custom">
<property name="Result" value="No"/>
</log>
</else>
</filter>
<respond/>
</inSequence>
<outSequence/>
<faultSequence/>
</resource>
</api>DBLookup
Looking at the DBLookup we can see that it actually requires defining a connection to the database, consisting of the definition of the driver URL, user and password. These plain text values are of course a security risk as anyone can inspect the XML files and see the password. In a real world setting, you w se Secure Vault to encrypt and store the values.
After that we need to define the Select SQL statement with the parameter that we’re going to pass and the field in which we’re going to receive the result in. The input parameter for the query is getting its value from the parameter of the API that we get when the API is invoked. In the result we’ll get the clientnr database-column value which will be filled with the client number or an empty value.
Filter
The filter mediator below the DBLookup will filter on the fact if there is a value or not. Because we’re looking to find exactly one user, we know that the user is a premium client when a value is found. So, any value that consists of some characters is OK, we don’t even have to check whether the return number matches the original parameter.
Once we’ve determined the provided clientnr is or is not a premium client, we’ll use a simple payload factory mediator to define the response and the respond mediator will send it back to the caller.
Drawbacks
Are there any drawbacks to this approach? Yes, there are! First off all, we’re defining everything inline, so if you want to reuse this DB lookup mediator in other artifacts, you’ll need to copy it rather than reference it from a sequence. Copying means that you’ll have multiple copies to maintain which may not be optimal. DBLookup can also only return one value, not for instance a list of records.
There is also a username and password directly mentioned in the API source code. This is not secure and therefore unwanted. You can change that by using the Secure Vault instead of using the plain text password.
Now let’s look at the same setup, but instead of using the DBlookup mediator, we will use the inbuilt data services service of the Enterprise Integrator to create a SOAP service. We could also define a resource in DSS, enabling REST invocations, but for now we will keep it as SOAP, to show you the extra steps needed.
In the end, this is what is created;
<data name="Premium" transports="http https local">
<config enableOData="false" id="clients">
<property name="driverClassName">com.mysql.jdbc.Driver</property>
<property name="url">jdbc:mysql://localhost:3306/clients</property>
<property name="username">root</property>
<property name="password">root</property>
</config>
<query id="find" useConfig="clients">
<sql>select clientnr from premiumclientdb where clientnr=?</sql>
<result element="Entries" rowName="Entry">
<element column="clientnr" name="clientnr" xsdType="string"/>
</result>
<param name="param0" sqlType="STRING"/>
</query>
<operation name="OpsClient">
<call-query href="find">
<with-param name="param0" query-param="param0"/>
</call-query>
</operation>
</data>Now this should needs to be integrated into the API. In this case, since we use a Data Service that is available as a SOAP message, we need to set the payload, messageType and Action (Header) before we can call the (separately defined) endpoint. This is done in the first couple of lines.
<payloadFactory media-type="xml">
<format>
<OpsClient>
<param0>$1</param0>
</OpsClient>
</format>
<args>
<arg evaluator="xml" expression="get-property('uri.var.clientnr')" />
</args>
</payloadFactory>
<property name="messageType" scope="axis2" value="text/xml"/>
<header name="Action" value="OpsClient"/>
<call description="Premium Data service">
<endpoint key="PremiumEndpoint"/>
</call>The endpoint is defined as:
<endpoint name="PremiumEndpoint" xmlns="http://ws.apache.org/ns/synapse">
<address format="soap11" statistics="enable" trace="enable" uri="http://localhost:8280/services/Premium">
</address>
</endpoint>
But also, on the side of the filter there are differences. We do not get the value in a variable but in a SOAP body, tagged with <Entries>, <Entry> and <clientnr>. The p prefix is showing the name of the namespace that is used. We now use a Boolean to check is there is a value in the response.
<filter xmlns_p="http://ws.wso2.org/dataservice" xpath="boolean(//p:Entries/p:Entry/p:clientnr)">
<then>
<payloadFactory media-type="json">
<format>"Premium client" : "True"</format>
<args/>
</payloadFactory>
<log level="custom">
<property name="Result" value="Yes"/>
</log>
</then>
<else>
<payloadFactory media-type="json">
<format>"Premium client" : "False"</format>
<args/>
</payloadFactory>
<log level="custom">
<property name="Result" value="No"/>
</log>
</else>
</filter>
You can even solve this without a Filter mediator by determining the True of False based on the Boolean value on the value in the SOAP Body.
<payloadFactory media-type="json">
<format>"Premium client" : "$1"</format>
<args>
> <arg
expression=” boolean(//p:Entries/p:Entry/p:clientnr)” xmlns_p="http://ws.wso2.org/dataservice" />
</args>
</payloadFactory>
<log level="custom">
<property name="Result"
expression=" boolean(//p:Entries/p:Entry/p:clientnr)” xmlns_p=”http://ws.wso2.org/dataservice” />
></log>
<respond />Drawbacks
The drawback of this approach is that it requires more work (defining data service, extra endpoint definition and setting of properties.However, the DSS service can be reused by other services and the data service can return a result set or list of records.
Similarity
When you look at the way it is done, both DBLookup and DSS use the same mechanism (defining connection to database and defining the SQL query). In this example, it’s only the way the mediation continues that defines the specific differences filtering the value to determine a premium client.
Yenlo is the leading, global, multi-technology integration specialist in the field of API-management, Integration technology and Identity Management. Known for our strong focus on best-of-breed hybrid and cloud-based iPaaS technologies.
Yenlo is the product leader and multi-award winner in WSO2, Boomi, MuleSoft and Microsoft Azure technologies and offers best-of-breed solutions from multiple leading integration vendors.
