...
You can add the SQL query using the mapping name:
| Info |
|---|
To avoid any errors, the value we pass to the query param must be URL encoded. You may refer to https://www.w3schools.com/tags/ref_urlencode.ASP and do the encoding. |
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> |
Sample 10: Defining Named parameters
Named Parameters enable reusability of parameters and reduce the complexity of database configurations. Named parameters are specified in SQL queries of data services.
A named parameter must have the same name as the input parameter along with a colon ':' in front. For example,
Sample 11: Grouping data into complex elements
Complex Elements help represent data in a structured manner. For example, let's take a table containing customer information. There can be several columns that keep data related to the address of employees such as number, street, city, postal code. You can group them into one element called address using complex elements and present them in a more structured manner.
Following example illustrates how to use complex elements:
After defining the query, proceed to adding an Output Mapping. In output mappings, select the Mapping Type as complex element. Specify an Element Name and click the Add Nested Element button. For example,
The added element appears under Complex Elements in the Output Mapping.
According to the figure below, addressline1, addressline2 and city are nested elements that come under address element.
