Dynamic SQL query support allows you to create SQL queries on the fly rather than changing the data service configuration. For this to work, you must specify required SQL query statements (e.g., with WHERE clause) as a QUERY_STRING data type. In the runtime, these statements are directed to the final SQL query.
Note: Dynamic query support can lead to SQL injection attacks. Therefore, we recommend to put this feature with validated parameters for the QUERY_STRING data type.
The QUERY_STRING data type is available as an SQL type when creating Input Mappings for queries. For example,
You can add the SQL query using the mapping name:
A sample configuration for the data service is shown below:
<data name="DynamicQuerySample" serviceNamespace="http://ws.wso2.org/dataservice/samples/rdbms_sample">
<config id="default">
<property name="driverClassName">org.h2.Driver</property>
<property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
<property name="username">wso2ds</property>
<property name="password">wso2ds</property>
<property name="minIdle">1</property>
<property name="maxActive">10</property>
<property name="autoCommit">false</property>
</config>
<query id="employeesSQL" useConfig="default">
<sql>select * from Employees :filterQuery</sql>
<result element="employees" rowName="employee">
<element column="lastName" name="last-name" xsdType="string"/>
<element column="firstName" name="first-name" xsdType="string"/>
<element column="email" name="email" xsdType="string"/>
<element column="salary" name="salary" xsdType="double"/>
</result>
<param name="filterQuery" sqlType="QUERY_STRING"/>
</query>
<query id="customerInCountrySQL" useConfig="default">
<sql>select * from Customers where country = :country :filter</sql>
<result element="customer-addresses" rowName="customer-address">
<element column="customerNumber" name="customer-number" xsdType="integer"/>
<element column="contactLastName" name="contact-last-name" xsdType="string"/>
<element column="contactFirstName" name="contact-first-name" xsdType="string"/>
<element column="addressLine1" name="address-line1" xsdType="string"/>
<element column="addressLine2" name="address-line2" xsdType="string"/>
<element column="city" name="city" xsdType="string"/>
<element column="state" name="state" xsdType="string"/>
<element column="postalCode" name="postal-code" xsdType="string"/>
<element column="country" name="country" xsdType="string"/>
</result>
<param name="country" sqlType="STRING"/>
<param name="filter" sqlType="QUERY_STRING"/>
</query>
<query id="insertUpdateQuery" useConfig="default">
<sql>:query</sql>
<param name="query" sqlType="QUERY_STRING"/>
</query>
<operation name="getEmployees">
<call-query href="employeesSQL">
<with-param name="filterQuery" query-param="filterQuery"/>
</call-query>
</operation>
<operation name="getCustomersInCountry">
<call-query href="customerInCountrySQL">
<with-param name="country" query-param="country"/>
<with-param name="filter" query-param="filter"/>
</call-query>
</operation>
<operation name="insertUpdateOp">
<call-query href="insertUpdateQuery">
<with-param name="query" query-param="query"/>
</call-query>
</operation>
</data>