Alright, let’s dive into how to handle multiple parameter passing in nested queries using WSO2 Micro Integrator’s (MI) DataService component. I’ve worked through several similar use cases, so I’ll walk you through the implementation step-by-step with practical examples.

Nested queries in WSO2 Data Services work by having a main query that calls one or more sub-queries. When dealing with multiple parameters, you need to:

• Define parameters for both the main and sub-queries
• Map parameters correctly between the main query and sub-queries
• Ensure parameter types match across all layers (external input → main query → sub query → database)

Let’s use a common scenario: fetching a customer’s details along with their filtered orders (e.g., orders with a specific status and date range).

2.1 Configure the Datasource #

First, set up your database connection in the Data Service. Here’s a sample JDBC config:

<config id="MyDB">
  <property name="driverClassName">com.mysql.cj.jdbc.Driver</property>
  <property name="url">jdbc:mysql://localhost:3306/ecommerce</property>
  <property name="username">dbuser</property>
  <property name="password">dbpass</property>
</config>

2.2 Define the Sub-Query (with Multiple Parameters) #

We’ll create a sub-query that retrieves orders based on customer_id, order_status, and start_date. This query accepts three parameters:

<query id="getFilteredOrdersQuery" useConfig="MyDB">
  <sql>SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = ? AND order_status = ? AND order_date >= ?</sql>
  <param name="customer_id" sqlType="INTEGER"/>
  <param name="order_status" sqlType="STRING"/>
  <param name="start_date" sqlType="DATE"/>
  <result element="Orders">
    <element column="order_id" name="OrderID"/>
    <element column="order_date" name="OrderDate"/>
    <element column="total_amount" name="TotalAmount"/>
  </result>
</query>

2.3 Create the Main Query and Map Parameters to the Sub-Query #

The main query will fetch customer details, then call the sub-query, passing both the customer ID from the main query and additional parameters (status and start date). Notice how we use <with-param> to map each parameter:

<query id="getCustomerWithOrdersQuery" useConfig="MyDB">
  <sql>SELECT customer_id, name, email FROM customers WHERE customer_id = ?</sql>
  <param name="input_customer_id" sqlType="INTEGER"/>
  <param name="input_start_date" sqlType="DATE"/>
  <result element="Customer">
    <element column="customer_id" name="CustomerID"/>
    <element column="name" name="Name"/>
    <element column="email" name="Email"/>
    <!-- Call the sub-query and pass multiple parameters -->
    <call-query href="getFilteredOrdersQuery">
      <!-- Pass customer_id from main query result -->
      <with-param name="customer_id" query-param="customer_id"/>
      <!-- Pass a fixed value for order status -->
      <with-param name="order_status" value="SHIPPED"/>
      <!-- Pass start date from the main query's input parameter -->
      <with-param name="start_date" query-param="input_start_date"/>
    </call-query>
  </result>
</query>

Note: query-param references a column from the main query’s result, while value can be a fixed string/number or an external parameter passed to the main query.

2.4 Expose the Operation with External Parameters #

Finally, define an operation that accepts external parameters and invokes the main query:

<operation name="getCustomerWithFilteredOrders">
  <description>Fetch customer details with filtered orders</description>
  <call-query href="getCustomerWithOrdersQuery">
    <with-param name="input_customer_id" query-param="customer_id"/>
    <with-param name="input_start_date" query-param="start_date"/>
  </call-query>
  <messageFormatter contentType="application/xml" class="org.wso2.micro.integrator.dataservices.core.engine.XMLMessageFormatter"/>
  <messageBuilder contentType="application/xml" class="org.wso2.micro.integrator.dataservices.core.engine.XMLMessageBuilder"/>
</operation>

• Parameter Order Matters: In your SQL query, the order of ? placeholders must match the order of <param> tags in the query definition.
• Type Consistency: Ensure the sqlType of parameters matches the database column type (e.g., INTEGER for numeric IDs, DATE for date fields).
• Debug with Logs: Enable debug logs for Data Services in WSO2 MI (update log4j2.properties to set logger.dataservices.level = DEBUG) to see how parameters are being passed between queries.
• Avoid SQL Injection: WSO2 Data Services uses prepared statements, so parameter binding is safe, but always validate external inputs to prevent unexpected values.

内容的提问来源于stack exchange，提问作者aypar