DataService Studio lets you generate APIs in the code editor using basic SQL or advanced SQL. Advanced SQL is based on MyBatis dynamic SQL syntax and supports the following tags: if, choose, when, otherwise, trim, foreach, and where. Use these tags to implement complex query logic, including null value checks, multi-value traversals, dynamic table queries, dynamic sorting, and aggregations.
Usage notes
This topic provides sample code for the following advanced SQL (MyBatis syntax) scenarios, along with the corresponding Request Parameters and Return Parameters configuration on the API generation page.
Replace all table names, field names, and query conditions with your own before running the code. Using the sample values as-is will cause runtime failures.
Example values for request and return parameters are optional. If you test the API with the same values each time, enter them as example values — the system loads them automatically during testing. Example values are for reference only.
For more information about generating an API in the code editor, see Generate an API in the code editor.
This topic covers the following scenarios:
Conditional sorting by multiple fields
Use the <choose>/<when> tags to implement switch-case logic: the value of an input parameter determines which ORDER BY clause applies. Without this tag combination, you would need a separate query for each sort order.
In this example, the var parameter controls the sort order:
var value |
Sort order |
|---|---|
| 1 | ORDER BY col01 |
| 2 | ORDER BY col02 |
| 3 | ORDER BY col01, col02 |
| 4 | ORDER BY col02, col01 |
SELECT col01, col02
FROM table_name
<choose>
<when test='var == 1'>
ORDER BY col01
</when>
<when test='var == 2'>
ORDER BY col02
</when>
<when test='var == 3'>
ORDER BY col01, col02
</when>
<when test='var == 4'>
ORDER BY col02, col01
</when>
</choose>The parameter name in the Request Parameters table maps directly to the variable name used in the test= condition in the SQL code. For example, the var parameter in the Parameter name column corresponds to var in test='var == 1'.
The following tables show the parameter settings for this example on the API generation page. You can replace the example values as needed.
Request parameters
| Parameter name | Parameter type | Parameter location | Required | Example value | Default value | Description |
|---|---|---|---|---|---|---|
| var | INT | QUERY | Yes | 1 | 1 | Sorting method |
Return parameters
| Parameter name | Parameter type | Example value |
|---|---|---|
| col01 | STRING | shortname |
| col02 | STRING | name |
Dynamic table selection based on a condition
Use the <choose>/<when> tags to select which table to query at runtime. The value of the input parameter determines the target table. Without this tag combination, you would need a separate query statement for each table.
In this example, the var parameter selects the table:
var value |
Table queried |
|---|---|
| 1 | table_name01 |
| 2 | table_name02 |
SELECT col01
FROM
<choose>
<when test='var == 1'>
table_name01
</when>
<when test='var == 2'>
table_name02
</when>
</choose>The following tables show the parameter settings for this example on the API generation page. You can replace the example values as needed.
Request parameters
| Parameter name | Parameter type | Parameter location | Required | Example value | Default value |
|---|---|---|---|---|---|
| var | INT | QUERY | Yes | 1 | 1 |
Return parameters
| Parameter name | Parameter type | Example value |
|---|---|---|
| col01 | STRING | 123 |
Filtered query using a collection
Use the <where>, <if>, and <foreach> tags together to dynamically generate an IN clause from a list of values.
Without the <where> tag, if the list collection is null, the SQL produces a bare WHERE keyword with no condition — which causes a query failure. The <where> tag suppresses the WHERE keyword automatically when no conditions apply.
In this example, if list is not null, the query generates an area IN (...) condition by iterating over the collection and returns data from the area_id, area, and amount fields.
SELECT area_id, area, amount
FROM table_name
<where>
<if test='list != null'>
area IN
<foreach collection="list" open="(" close=")" separator="," item="area">
#{area}
</foreach>
</if>
</where>The <foreach> attributes work as follows:
| Attribute | Description |
|---|---|
collection |
The request parameter to iterate over. Must match the parameter name in the Request Parameters table. |
item |
The variable name for each element in the iteration. Referenced as #{area} in the SQL fragment. |
open |
The string to prepend before the first element. ( opens the IN list. |
close |
The string to append after the last element. ) closes the IN list. |
separator |
The delimiter between elements. , separates each value. |
The following tables show the parameter settings for this example on the API generation page. You can replace the example values as needed.
Request parameters
| Parameter name | Parameter type | Parameter location | Required | Example value | Default value |
|---|---|---|---|---|---|
| list | STRING_LIST | QUERY | Yes | Beijing, Hangzhou | Beijing |
Return parameters
| Parameter name | Parameter type | Example value |
|---|---|---|
| area_id | STRING | 123120 |
| area | STRING | Beijing |
| amount | STRING | 50 |