Monday, October 16, 2017

Create a dynamic/filtered query service in WSO2 DSS.



Sometimes we have requirement in which we have to call a database query at run time. For example, we have an employee table and need to get details from this table with some condition like “where emp_last_name=? and address=?”. In above scenario if we don’t know the how many condition cab be there at run time, DSS filtered query can be used.


DSS file:


<data name="DynamicDS" transports="http https local">
   <config enableOData="false" id="DynamicQueryEmp">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/emp</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="DynaminQuery" useConfig="DynamicQueryEmp">
      <sql>select id, name, address from emp :filterQuery</sql>
      <result defaultNamespace="http://shriwithjava.blogspot.co.nz/" element="employees" rowName="employee">
         <element column="id" name="id" xsdType="xs:string"/>
         <element column="name" name="name" xsdType="xs:string"/>
         <element column="address" name="address" xsdType="xs:string"/>
      </result>
      <param name="filterQuery" sqlType="QUERY_STRING"/>
   </query>
   <operation name="getEmployees">
      <call-query href="DynaminQuery">
         <with-param name="filterQuery" query-param="filterQuery"/>
      </call-query>
   </operation>
</data> 




In this DSS file we can use any database condition and pass it to DSS as parameter