Build a Query in MuleSoft With Optional Parameters
This tutorial article demonstrates a simple approach to building a query in MuleSoft using optional parameters for both filtering and sorting.
Join the DZone community and get the full member experience.
Join For FreeIn this tutorial we will see how to:
- Define a RAML API specification to search products in a catalog database
- Establish and configure a database connection to query a MySQL database
- Build a query to support multiple optional filters
- Add optional sorting capabilities to our query at DB level
1. Define the RAML of a Search API
Imagine that you want to create a MuleSoft API to search products inside a catalog. This API will need to accept multiple filter parameters and an optional sorting parameter. All these parameters can be optional and will be used to generate a dynamic query to search products in your catalog database.
1.1 RAML Definition
The RAML definition of this API could be like the following:
#%RAML 1.0
title: search-products-demo
types:
SearchProductRequest:
type: object
properties:
season?: string
size?: string
line?: string
category?: string
subcategory?: string
/search-products:
post:
displayName: Search products
body:
application/json:
type: SearchProductRequest
responses:
200:
body:
application/json:
type: array
items:
type: object
In this example, you can use season, line, category, subcategory, and size to filter products in your catalog. All these parameters are optional.
2. Flow Implementation
Create a new mule application project, add the API specification defined from the RAML, and allow the scaffolding process to complete.
2.1 Configure the Database Connection
Drag a new database select component from the palette and add a new database configuration object to configure the connection.
In this example, we will use a MySQL connection.
Add the required Maven dependency from Exchange.
Configure the connection properties and click OK.
2.2 Write the Query to Search Your Products
Now it’s time to add the first version of our query that will take all the input parameters from the API. In step three, we will then refactor this query to accept empty filters.
SELECT * FROM db_demo.product_catalog
WHERE category = :category
AND subcategory = :subcategory
AND line = :line
AND season = :season
AND size = :size;
Bind input parameters. To provide input parameters, we read values from the payload of the Ingress API call and assign them to the input parameters of our query
{
"category": payload.category,
"subcategory": payload.subcategory,
"line": payload.line,
"season": payload.season,
"size": payload.size
}
2.3 Add a Transformation to Return Back the Query Results in JSON Format
Add a transformer component with output application/JSON and this simple transformation logic:
%dw 2.0 output application/json --- { "result_count": sizeOf(payload), "products": payload }
2.4 Test the First Version of the API
At this point, the flow implementation should look like this:
We can use our favorite REST API client to test the first version of the API.
However, if we try to remove one or more filters from the request payload, we get no results.
3. Refactoring the Query to Work With Missing Filters
In this step, we are going to refactor our query in order to accept requests with missing filters.
3.1 Refactoring the Query
The key idea is to modify our filtering conditions by adding an OR
statement to check if the filter is populated or not.
Example: AND (subcategory = :subcategory)
becomes
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
In this way, if the filter is missing, the first condition of the OR
statement will always be met and the filter will have no effect on the query results.
Refactoring all the filters, our query becomes:
SELECT * FROM db_demo.product_catalog
WHERE (1 = :emptyCategoryFilter OR category = :category)
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
AND (1 = :emptyLineFilter OR line = :line)
AND (1 = :emptySeasonFilter OR season = :season)
AND (1 = :emptySizeFilter OR size = :size);
3.2 Populating the New Input Parameters to Check if the Filters Are Missing
To populate the new input parameters that we use to check if a filter is missing, we can use the ?
operator to check if the filter attribute is contained in the payload of the request.
{
"category": payload.category,
"emptyCategoryFilter": if (payload.category?) 0 else 1,
"subcategory": payload.subcategory,
"emptySubcategoryFilter": if (payload.subcategory?) 0 else 1,
"line": payload.line,
"emptyLineFilter": if (payload.line?) 0 else 1,
"season": payload.season,
"emptySeasonFilter": if (payload.season?) 0 else 1,
"size": payload.size,
"emptySizeFilter": if (payload.size?) 0 else 1
}
Now we can test the API with missing filters again and see that it returns results, even with missing filters.
4. Adding Sorting Capabilities
In a similar way, we can add optional sorting capabilities to our API.
We will use the ORDER BY
function at a database level (instead of ordering our results in DataWeave) because this approach can be useful to support large result sets use cases, limiting the number of results and providing pagination capabilities.
To do that we need to first modify our RAML API spec to add two new parameters for the sorting attributes and the sorting direction.
4.1 Adding Sorting Parameters to Our RAML API Spec
sorting_attribute
will be a string.
sorting_direction
will be an enum with ASC and DESC as possible values.
Both parameters will be optional.
#%RAML 1.0
title: search-products-demo
types:
SearchProductRequest:
type: object
properties:
season?: string
size?: string
line?: string
category?: string
subcategory?: string
sorting_attribute?: string
sorting_direction?:
type: string
enum: [ASC, DESC]
/search-products:
post:
displayName: Search products
body:
application/json:
type: SearchProductRequest
responses:
200:
body:
application/json:
type: array
items:
type: object
4.2 Modifying the Flow Implementation to Support Sorting Parameters
After updating the API version in our project, we will need to slightly modify our implementation.
Store the sorting attribute in a variable sortAttr
using the default to provide a value even if the sorting_attribute
is missing from the original query.
In the same way, store the sorting direction attribute in a variable providing a default “ASC” direction.
Store the query in a variable and add an ORDER BY
clause using the newly create variables for sortAttr
and sortDir
.
Modify the database select operation to read the query from a variable
Attention! Be careful with this approach of building queries by concatenating variables because, if misused, it can lead to SQL injection problems. Use parameter binding whenever possible!
In our example, we used variable concatenation only to build the ORDER BY
clause, where it was not possible to use parameter binding like we did for all the other filter params.
4.3 Test the API With Sorting Parameters
Test the API with the following:
"sorting_attribute": "line",
"sorting_direction": "DESC"
It returns “Woman” products first, followed by “Man” products.
5. Wrap-Up: The Final Project
In this tutorial we showed how to:
- Define a RAML API specification to search products in a catalog database
- Establish and configure a database connection to query a MySQL database
- Build a query to support multiple optional filters
- Add optional sorting capabilities to our query at DB level
The key approach that we adopted to support optional filtering was to refactor our query in order to add an OR
condition to check if the filter is present or if it missing
For example, AND (subcategory = :subcategory)
becomes
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
.
To add optional sorting capabilities, we had to use a slightly different approach because we wanted to use an ORDER BY
clause in our query but it was not possible to build it using parameter binding.
Here the strategy was to implement the ORDER BY
clause by concatenating the fixed part of the query (where we can use parameter binding) with the variables to define sorting attributes and sorting directions.
It was also highlighted that this strategy has to be adopted carefully, only when parameter binding cannot be used, in order to prevent SQL injection issues.
The Final Project
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:apikit="http://www.mulesoft.org/schema/mule/mule-apikit" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:json-logger="http://www.mulesoft.org/schema/mule/json-logger" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd http://www.mulesoft.org/schema/mule/json-logger http://www.mulesoft.org/schema/mule/json-logger/current/mule-json-logger.xsd http://www.mulesoft.org/schema/mule/mule-apikit http://www.mulesoft.org/schema/mule/mule-apikit/current/mule-apikit.xsd http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">
<apikit:config name="search-products-demo-config" api="resource::bc191955-0c23-499c-8429-5392d1c3c041:search-products-demo:1.0.1:raml:zip:search-products-demo.raml" outboundHeadersMapName="outboundHeaders" httpStatusVarName="httpStatus" />
<db:config name="Database_Config" doc:name="Database Config" doc:id="16b67dda-b66e-4327-9c25-071f022f8b17">
<db:my-sql-connection host="${secure::db_host}" port="${secure::db_port}" user="${secure::db_user}" password="${secure::db_password}" database="${secure::db_database}" />
</db:config>
<flow name="search-products-demo-main">
<http:listener config-ref="search-products-demo-httpListenerConfig" path="/v1/*">
<http:response statusCode="#[vars.httpStatus default 200]">
<http:headers><![CDATA[#[vars.outboundHeaders default {}]]]></http:headers>
</http:response>
<http:error-response statusCode="#[vars.httpStatus default 500]">
<http:body><![CDATA[#[payload]]]></http:body>
<http:headers><![CDATA[#[vars.outboundHeaders default {}]]]></http:headers>
</http:error-response>
</http:listener>
<set-variable value="#[payload]" doc:name="Set Original Payload" doc:id="08131364-c610-47a1-9a92-de70c6c8b702" variableName="originalPayload" />
<apikit:router config-ref="search-products-demo-config" />
</flow>
<flow name="post:\search-products:application\json:search-products-demo-config">
<logger level="INFO" message="post:\search-products:application\json:search-products-demo-config ++ #[payload]" />
<set-variable value='#[payload.sorting_attribute default "id"]' doc:name="Set Variable sortAttr" doc:id="21f415e6-50ea-4ac6-b23d-721b6d4aa3ec" variableName="sortAttr"/>
<set-variable value='#[payload.sorting_direction default "ASC"]' doc:name="Set Variable sortDir" doc:id="db5896be-cc5c-4125-bfad-21d38875e113" variableName="sortDir"/>
<set-variable value='#["SELECT * FROM db_demo.product_catalog
WHERE (1 = :emptyCategoryFilter OR category = :category)
AND (1 = :emptySubcategoryFilter OR subcategory = :subcategory)
AND (1 = :emptyLineFilter OR line = :line)
AND (1 = :emptySeasonFilter OR season = :season)
AND (1 = :emptySizeFilter OR size = :size)
ORDER BY " ++ vars.sortAttr ++ " " ++ vars.sortDir ++ ";"]' doc:name="Set Variable query" doc:id="55a2306d-38d2-45b2-a607-e7f82cee3c4a" variableName="query"/>
<db:select doc:name="Select" doc:id="aa376842-cb2d-4d2c-9bbb-a6c0efba81bc" config-ref="Database_Config">
<db:sql>#[vars.query]</db:sql>
<db:input-parameters><![CDATA[#[{
"category": payload.category,
"emptyCategoryFilter": if (payload.category?) 0 else 1,
"subcategory": payload.subcategory,
"emptySubcategoryFilter": if (payload.subcategory?) 0 else 1,
"line": payload.line,
"emptyLineFilter": if (payload.line?) 0 else 1,
"season": payload.season,
"emptySeasonFilter": if (payload.season?) 0 else 1,
"size": payload.size,
"emptySizeFilter": if (payload.size?) 0 else 1,
"sortingAttr": (payload.sorting_attribute),
"sortingDir": (payload.sorting_direction)
}]]]></db:input-parameters>
</db:select>
<ee:transform doc:name="Transform Message" doc:id="7b5c1e1b-33f4-4035-82af-ce9eb474a3b9">
<ee:message>
<ee:set-payload><![CDATA[%dw 2.0
output application/json
---
{
"result_count": sizeOf(payload),
"products": payload
}]]></ee:set-payload>
</ee:message>
</ee:transform>
</flow>
</mule>
Opinions expressed by DZone contributors are their own.
Comments