Author: Ayesha Saoji
Objective:
To query databases using HTTP Request and query Params using filter expressions similar to ODATA.
Just as we use OData to query data uniformly in URL, we can replicate the same using HTTP filter operators and query Params.
For Ex: Query DataBase using filter operators in HTTP Request.
Sample HTTP Request Format
http://localhost:8081/Customers?$format=json&$select=CustomerID,CompanyName,Address,City,Country,Phone,Fax&$filter=Country eq ‘France’ &$orderby=Phone&$top=10&$skip=2
Operators Used
- $format
- $select
- $filter
- $orderby
- $top
- $skip
Flow Design Components
- Listener to listen to endpoint /Customers at port 8081.
- Transform Message Component to Write DataWeave Transformation on an HTTP Request to form a SQL select query to retrieve expected results.
- Select DataBase Component.
- Transform Message Component to Display retrieved data from the database.
Flow Design Diagram
Observe the content of the Query String
Observe the payload
Payload contains the resultset from the database
PostMan request result
{
“Customers”: [
{
“Country”: “France”,
“Address”: “184, chausse de Tournai”,
“City”: “Lille”,
“Phone”: “20.16.10.16”,
“CompanyName”: “Folies gourmandes”,
“CustomerID”: “FOLIG”,
“Fax”: “20.16.10.17”
},
{
“Country”: “France”,
“Address”: “59 rue de l’Abbaye”,
“City”: “Reims”,
“Phone”: “26.47.15.10”,
“CompanyName”: “Vins et alcools Chevalier”,
“CustomerID”: “VINET”,
“Fax”: “26.47.15.11”
},
{
“Country”: “France”,
“Address”: “67, avenue de l’Europe”,
“City”: “Versailles”,
“Phone”: “30.59.84.10”,
“CompanyName”: “La corne d’abondance”,
“CustomerID”: “LACOR”,
“Fax”: “30.59.85.11”
},
{
“Country”: “France”,
“Address”: “54, rue Royale”,
“City”: “Nantes”,
“Phone”: “40.32.21.21”,
“CompanyName”: “France restauration”,
“CustomerID”: “FRANR”,
“Fax”: “40.32.21.20”
},
{
“Country”: “France”,
“Address”: “67, rue des Cinquante Otages”,
“City”: “Nantes”,
“Phone”: “40.67.88.88”,
“CompanyName”: “Du monde entier”,
“CustomerID”: “DUMON”,
“Fax”: “40.67.89.89”
},
{
“Country”: “France”,
“Address”: “1 rue Alsace-Lorraine”,
“City”: “Toulouse”,
“Phone”: “61.77.61.10”,
“CompanyName”: “La maison d’Asie”,
“CustomerID”: “LAMAI”,
“Fax”: “61.77.61.11”
},
{
“Country”: “France”,
“Address”: “2, rue du Commerce”,
“City”: “Lyon”,
“Phone”: “78.32.54.86”,
“CompanyName”: “Victuailles en stock”,
“CustomerID”: “VICTE”,
“Fax”: “78.32.54.87”
},
{
“Country”: “France”,
“Address”: “24, place Klber”,
“City”: “Strasbourg”,
“Phone”: “88.60.15.31”,
“CompanyName”: “Blondesddsl pre et fils”,
“CustomerID”: “BLONP”,
“Fax”: “88.60.15.32”
},
{
“Country”: “France”,
“Address”: “12, rue des Bouchers”,
“City”: “Marseille”,
“Phone”: “91.24.45.40”,
“CompanyName”: “Bon app’”,
“CustomerID”: “BONAP”,
“Fax”: “91.24.45.41”
}
]
}
Cross verification — DataBase query result same as PostMan result
DataWeave code for SQL query creation
%dw 2.0
import * from dw::core::Arrays
output application/java
var ArrParams = attributes.queryString splitBy “&”
//This is how ArrayParams looks like.
[$format=json,$select=CustomerID,CompanyName,Address,City,Country,Phone,Fax, $filter=Country eq ‘France’ , $orderby=Phone, $top=10, $skip=2]
var SQLFilter = (filter) ->
filter replace “eq null” with “is null”
replace “ne null” with “is not null”
replace “ eq “ with “ like “
replace “ ne “ with “ != “
replace “ gt “ with “ > “
replace “ lt “ with “ < “
replace “ ge “ with “ >= “
replace “ le “ with “ <= “
replace “ and “ with “ AND “
replace “ or “ with “ OR “
var toSQLOrderBy = (orderby ) -> if(orderby != “”) (“ ORDER BY “ ++ (orderby replace “=” with “ “)) else “”
var toSQLSkipAndTop = (top, skip) -> if(top != “” and skip != “”) “ LIMIT $top OFFSET $skip” else if (top == “” and skip != “”) “ LIMIT 2147483647 OFFSET $skip” else if (top != “” and skip == “”) “ LIMIT $top” else “”
var toSQLWhere = (filter) -> if(filter != “”) “ WHERE “ ++ SQLFilter(filter) else “”
— -
using(SelectIndex = ArrParams indexWhere (item) -> item contains “select”)
using(ArrSelect = ArrParams[SelectIndex] splitBy “=”)
using(select = ArrSelect[1])
using(FilterIndex = ArrParams indexWhere (item) -> item contains “filter”)
using(ArrFilter = ArrParams[FilterIndex] splitBy “=”)
using(filter = ArrFilter[1])
using(OrderByIndex = ArrParams indexWhere (item) -> item contains “orderby”)
using(ArrOrderBy = ArrParams[OrderByIndex] splitBy “=”)
using(orderby = ArrOrderBy[1])
using(TopIndex = ArrParams indexWhere (item) -> item contains “top”)
using(ArrTop = ArrParams[TopIndex] splitBy “=”)
using(top = ArrTop[1])
using(SkipIndex = ArrParams indexWhere (item) -> item contains “skip”)
using(ArrSkip = ArrParams[SkipIndex] splitBy “=”)
using(skip = ArrSkip[1])
“SELECT “ ++ select ++ “ FROM customers” ++ (toSQLWhere(filter)) ++ (toSQLOrderBy(orderby)) ++ (toSQLSkipAndTop(top,skip))
Link to use contains function in Mule 4
Contains
https://docs.mulesoft.com/mule-runtime/4.3/dw-core-functions-contains
Dataweave Function: https://docs.mulesoft.com/mule-runtime/4.3/dataweave-functions
SplitBy
https://docs.mulesoft.com/mule-runtime/4.3/dw-core-functions-splitby
Conclusion
This is how we get data from a database using HTTP requests and filter operators. We have explored the contains operator, dataweave functions and splitby using operator, etc.
