DataService Studio allows you to create an API in script mode. The script mode supports the basic SQL syntax and advanced SQL syntax. The advanced SQL syntax supports the following types of tags that comply with the MyBatis framework: 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, traverse for multiple values, dynamic query for tables, dynamic sorting, and aggregate query. This topic provides sample code for the advanced SQL syntax used in some common scenarios.

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

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>

Example 2: Query data from multiple tables by using conditions

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

Example 3: Determine whether to retain the WHERE clause based on whether the values of fields are null (query logic when request parameters are optional)

SELECT id, name, code
FROM table_name
<where>
    <if test='list!=null'>
    code in
        <foreach collection="list" open="(" close=")" separator="," item="code_num">
        ${code_num}
        </foreach>
    </if>
</where>