Sample code for the advanced SQL syntax used for creating an API in the code editor - DataWorks

DataService Studio allows you to create an API by using the code editor. In the code editor, you can use basic SQL syntax or advanced SQL syntax to implement the query logic. The advanced SQL syntax supports common tags of MyBatis, such as if, choose, when, otherwise, trim, foreach, and where. You can use the advanced SQL syntax to implement complex query logic, such as null value check, multi-value traversing, dynamic query of tables, dynamic sorting, and aggregate query.

Usage notes

This topic provides sample code on how to use advanced SQL statements to implement query logic if you create an API by using the code editor. This topic also describes the mappings between SQL statements and the request parameters and response parameters of an API. You must replace the table names, field names, and query conditions in the sample code based on your business requirements.

Note

When you create an API, the example values of the request parameters and response parameters are not required. If you use the same values for request parameters each time you test an API, the example values are automatically loaded as the input of the request parameters. This way, you do not need to repeatedly specify values for request parameters. Example values are only for reference.

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

This topic provides the following examples in which the advanced SQL syntax is used:

Example 1: Sort returned results based on table fields by using conditions
Example 2: Query data from different tables by using conditions
Example 3: Determine whether to retain the WHERE clause based on whether the values of fields are null

Example 1: Sort returned results based on table fields by using conditions

In this example, a dynamic SQL statement is used. You can configure the var variable to determine the method that is used to sort query results.

If you assign 1 to the var variable, the query results are sorted by using the order by col01 clause.
If you assign 2 to the var variable, the query results are sorted by using the order by col02 clause.
If you assign 3 to the var variable, the query results are sorted by using the order by col01,col02 clause.
If you assign 4 to the var variable, the query results are sorted by using the order by col02,col01 clause.

The following code shows an example.

Important

Replace the table name, fields, and other conditions based on your business requirements.

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 configurations used to create an API by using the code editor. You can replace the example values based on your business requirements.

Request parameters
Parameter Name
Type
Position
Required
Example Value
Default Value
Description
var
INT
QUERY
Yes
1
1
The sorting method.
Response parameters
Parameter Name
Type
Example Value
col01
STRING
shortname
col02
STRING
name

Example 2: Query data from different tables by using conditions

In this example, a dynamic SQL statement is used. You can assign different values to the var variable to query the data of the col01 field in different tables.

If you assign 1 to the var variable, data of the col01 field in the table_name01 table is returned.
If you assign 2 to the var variable, data of the col01 field in the table_name02 table is returned.

The following code shows an example.

Important

Replace the table name, fields, and other conditions based on your business requirements.

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 configurations used to create an API by using the code editor. You can replace the example values based on your business requirements.

Request parameters
Parameter Name
Type
Position
Required
Example Value
Default Value
var
INT
QUERY
Yes
1
1
Response parameters
Parameter Name
Type
Example Value
col01
STRING
123

Example 3: Determine whether to retain the WHERE clause based on whether the values of fields are null

In this example, a query condition is dynamically generated based on the value of the area parameter in the list collection. Then, data is queried based on the condition.

If the list collection is not null, a query condition that contains the value of the area field is generated. Then, the list collection is traversed based on the query condition, and the elements in the collection are concatenated in a specified way. As a result, the data of the area_id, area, and amount fields are returned.

The following code shows an example.

Important

Replace the table name, fields, and other conditions based on your business requirements.

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 configurations used to create an API by using the code editor. You can replace the example values based on your business requirements.

Request parameters
Parameter Name
Type
Position
Required
Example Value
Default Value
list
STRING_LIST
QUERY
Yes
Beijing,Hangzhou
Beijing
Response parameters
Parameter Name
Type
Example Value
area_id
STRING
123120
area
STRING
Beijing
amount
STRING
50

Parameter Name	Type	Position	Required	Example Value	Default Value	Description
var	INT	QUERY	Yes	1	1	The sorting method.