Advanced SQL examples for creating an API-DataWorks(DataWorks)-阿里云帮助中心

In DataService Studio, you can use the code editor to create APIs. The editor lets you write query logic using basic or advanced SQL. Advanced SQL supports common MyBatis tags, such as if, choose, when, otherwise, trim, foreach, and where. These tags let you implement complex query logic, such as checking for null values, iterating over multiple values, dynamically querying tables, dynamic sorting, and aggregation.

Usage notes

This topic provides examples of writing query logic with advanced SQL when creating an API in the code editor. It also explains the mapping between the SQL code and the Request Parameters and Response Parameters on the API creation page. To prevent runtime errors, replace the tables, fields, and query conditions in the examples with your own.

Note

Providing example values for request and response parameters is optional. If you specify an example value for a request parameter, it is automatically loaded as the input when you test the API. This saves you from entering the same values for each test. Example values are for reference only.

For more information about how to create an API in the code editor, see Create an API by using the code editor.

This topic provides examples of the following advanced SQL (MyBatis syntax) use cases:

Example 1: Conditionally sort results by different table fields
Example 2: Conditionally query different tables
Example 3: Conditionally include a WHERE clause based on whether a parameter is null

Example 1: Conditionally sort results

In this dynamic SQL example, the value of the var parameter determines the sort order.

If var is 1, the query sorts the results using ORDER BY col01.
If var is 2, the query sorts the results using ORDER BY col02.
If var is 3, the query sorts the results using ORDER BY col01,col02.
If var is 4, the query sorts the results using ORDER BY col02,col01.

The following code shows an example.

Important

Replace the table name, fields, and other conditions in this example with your own.

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 following figure shows an example of the parameter configuration in the code editor. You can customize the example values.

Request parameters:

Parameter	Type	Position	Required	Example value	Default	Description
var	INT	QUERY	Yes	1	1	Sort order

Response parameters:

Parameter	Type	Example value
col01	STRING	shortname
col02	STRING	name

Example 2: Conditionally query different tables

This dynamic SQL example queries different tables based on the value of the var parameter. The query returns data from the col01 field.

If var is 1, the query retrieves data from the col01 field of the table_name01 table.
If var is 2, the query retrieves data from the col01 field of the table_name02 table.

The following code shows an example.

Important

Replace the table name, fields, and other conditions in this example with your own.

select col01
from
<choose>
 <when test='var == 1'>
 table_name01
 </when>
 <when test='var == 2'>
 table_name02
 </when>
</choose>

The following figure shows an example of the parameter configuration in the code editor. You can customize the example values.

Request parameters:

Parameter	Type	Position	Required	Example value	Default
var	INT	QUERY	Yes	1	1

Response parameters:

Parameter	Type	Example value
col01	STRING	123

Example 3: Conditionally include a WHERE clause

This example dynamically generates query conditions based on the list collection's area value, and queries data based on the conditions.

If list is not null, a query condition is generated with the value of the area field, the list collection is iterated to concatenate its elements in a specified way, and the data for the area_id, area, and amount fields is returned.

The following code shows an example.

Important

Replace the table name, fields, and other conditions in this example with your own.

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 following figure shows an example of the parameter configuration in the code editor. You can customize the example values.

Request parameters:

Parameter	Type	Position	Required	Example value	Default
list	STRING_LIST	QUERY	Yes	Beijing,Hangzhou	Beijing

Response parameters:

Parameter	Type	Example value
area_id	STRING	123120
area	STRING	Beijing
amount	STRING	50