...
| Info |
|---|
Note: Dynamic query support can lead to SQL injection attacks. Therefore, we recommend to put this feature with validated values for the validate the values set to |
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:
| Code Block |
|---|
<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> |