Dynamic SQL 语法

Dynamic SQL 用于提供强大的 SQL 生成能力。

常用转义字符

1 Dynamic SQL Engine 概述

如果您有在程序中直接拼接 SQL 调用数据库的经验，您就能体会到根据不同条件拼接 SQL 语句的难处。例如拼接时要确保不能忘记添加必要的空格，还需要去掉列表最后一个列名的逗号，去掉第一个 where 条件前的 AND 和 OR。
而利用动态 SQL 这一特性可以彻底摆脱这种难处。
动态 SQL 借鉴了 MyBatis-3 的元素设计，并在 Golang 上进行实现，使其运行的很快，且在 Mybatis-3 的方案上添加了 Function 元素，用于支持更强大的功能，比如递归解析一些嵌套结构。
动态 SQL 用于提供强大的 SQL 生成功能。可根据 SQL 模板和参数，生成最终的 SQL 语句。SQL 模板提供常见的参数替换功能，同时还提供强大的逻辑控制能力。
动态 SQL 的格式是 XML，类似于：

<select>
    select a from b where c = #{c_value}....
</select>

如果您的动态 SQL 比较简单，XML 内部只有一个元素，可以使用简写方式，去掉 XML 元素, 如下:

select a from b where c = #{c_value}....

<  => &lt;  如 a <= 10 改成 a &lt;= 10
>  => &gt;

1.1 常见例子

假设 Dynamic SQL 的参数为下面的 JSON 数据：

{
    "table_name": "table_test",
    "int_value": 10,
    "string_value": "10",
    "array_value": [10, 5],
    "dims": ["field_a", "field_b"],
    "other": {
        "array_value": ["a", "b"],
        "string_value":null
    },
    "keyword": "新中国",
    "prefix": "pp",
    "suffix": "ss"，
    "pageSize": 100,
    "pageCount": 3
}

1. 实例一 基础用法: 使用整数，字符串和数组

   select * from ${table_name} where a = #{int_value} and b = #{string_value} and c in #{array_value} and d in #{other.array_value}
   

   这个实例会生成下列的 SQL:

   select * from table_test where a = 10 and b = '10' and c in (10,5) and d in ('a','b')
   

   #{} 运算符会根据数据类型自动生成 SQL 片段，而 ${} 运算符 直接将对应的数据插入到 SQL 中。

   注意

   应当尽量使用 #{} 符号，这可以有效避免 SQL 注入问题。
   #{} 会根据提供的参数类型来决定如何生成最终的 SQL 语句。例如 date=#{date}表达式：

   • 如果 Env 中 date 参数为数值类型，比如20190714，则最终生成的 SQL 语句为date=20190714；
   • 如果 date 为字符串类型，则最终生成的 SQL 语句为date='20190714'；
   • 如果 dynamic-sql 语句为 date in #{date}, 且 date 为数组，比如["20190714"," 20190715"]，则最终生成的 SQL 语句为 date in ('20190714', '20190715')。

2. 实例二 基础用法: 使用表达式计算，例如利用计算表达式实现分页功能。

   select a, b from ${table_name} limit #{pageSize*(pageCount-1)}, #{pageSize}
   

   这个实例实现了一个分页功能，pageSize表示页大小，pageCount表示第几页。
   这会生成下列的 SQL:

   select a, b from table_test limit 200,100
   

3. 实例三 进阶: 使用内置函数

   select ${Join(dims, ', ')} from table_test where a like #{Format('%v', keyword)} and d = #{Format('%v_%v', prefix, suffix)} group by ${Join(dims, ', ')}
   

   这个实例会生成下列的 SQL:

   select field_a, field_b from table_test where a like '新中国' and d = 'pp_ss' group by field_a, field_b
   

   实例中使用到了 Join(array, delimiter) 函数和 Format(format, args) 函数。
   其中：

   • Join 函数将数组使用 delimiter 拼接起来。
   • Format 函数将参数进行格式化。注意，

     注意

     Format 函数的 format 参数使用的是 GOLANG 的格式化方式，如果字符串中需要用到符号 % ,需要使用 %% 进行转义， 如：

     select * from table_test where keyword like #{Format('%%%v%%', keyword)} 
     ==> 生成的 SQL 是 select * from table_test where keyword like '%新中国%'
     

4. 实例四 进阶: 使用 XML 格式的 Dynamic SQL：

   <select>
        select field_a, field_b from table_test 
           <where> 
               <if test = 'other.string_value != nil'>
                   and a like '%${other.string_value}%'
               </if>
               <if test = 'int_value != nil and string_value != nil'>
                   and b = #{int_value} and c = #{string_value}
               </if>
           </where>
       group by field_a, field_b   
   </select>
   

   上面的实例会生成下列的 SQL：

   select field_a, field_b from table_test  WHERE b = 10 and c = '10' group by field_a, field_b
   

   • if 标签会判断 test 里的语句是否成立，如果成立则会将对应的 SQL 拼接到最终的 SQL 中。
   • where 标签会在其子标签返回的 SQL 子句前添加 WHERE 子句，并会去掉多余的 and/or 连接词。 并且如果 where 标签的所有子标签都没有返回 SQL 子句，那 where 标签也不会拼接 WHERE SQL 子句。
     可以使用支持 XML 高亮插件的编辑器编辑 XML，或者使用在线的 XML formatter 检查语法。

2 Dynamic SQL 元素

Dynamic SQL 支持以下类型的元素：

• if
• choose (when, otherwise)
• trim (where)
• foreach
• bind
• function

下文将为您分别介绍相关元素说明。

2.1 if

动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分。比如：

<select>
  SELECT * FROM BLOG
  WHERE state = 'ACTIVE'
  <if test="title != nil">
    AND title like #{title}
  </if>
</select>

上方这条语句提供了一种可选的查找文本功能：

• 如果语句中没有传入“title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；
• 反之若传入了 “title”，那么就会对 “title” 一列进行模糊查找并返回 BLOG 结果。

  说明

  “title” 参数值允许包含一些掩码或通配符。

如果您希望通过 “title” 和 “author” 两个参数进行可选搜索，应该如何操作呢？
首先，您需要改变语句的名称让它更具实际意义；然后只要加入另一个条件即可：

<select>
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="title != nil">
    AND title like #{title}
  </if>
  <if test="author != nil and author.name != nil">
    AND author_name like #{author.name}
  </if>
</select>

表达式中也可以使用字符串、数字等常量值：

<select>
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="dim_type == 'app'">
    AND app = #{dim_value}
  </if>
</select>

使用 len() 表示判断数组长度，例如：

<select>
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="name_list != nil and len(name_list) > 0">
    AND name_list in #{name_list}
  </if>
</select>

2.2 choose, when, otherwise

若不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，Dynamic SQL 提供了 choose 元素，类似编程语言中的 switch 语句。
还是通过上方的例子，但这次改为提供了 “title” 就按 “title” 查找，提供了 “author” 就按 “author” 查找；若两者都没有提供，就返回所有符合条件的 BLOG（实际情况可能是由管理员按一定策略选出 BLOG 列表，而不是返回大量无意义的随机结果）。

<select>
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != nil">
      AND title like #{title}
    </when>
    <when test="author != nil and author.name != nil">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

2.3 trim, where

前面几个例子已经合宜地解决了一个动态 SQL 问题。现在回到 “if” 示例中，这次我们将 “ACTIVE = 1” 也设置成动态的条件：

<select>
  SELECT * FROM BLOG
  WHERE
  <if test="state != nil">
    state = #{state}
  </if>
  <if test="title != nil">
    AND title like #{title}
  </if>
  <if test="author != nil and author.name != nil">
    AND author_name like #{author.name}
  </if>
</select>

• 若语句中的条件没有一个能匹配上，那么最终这条 SQL 会变成以下这样：

  SELECT * FROM BLOG
  WHERE
  

  这会导致查询失败。

• 若仅仅第二个条件匹配，则这条 SQL 最终会是这样:

  SELECT * FROM BLOG
  WHERE
  AND title like 'someTitle';
  

  这个查询也会失败。这个问题不能简单地用条件句式来解决，如果您也曾经被迫这样写过，那么您很可能从此以后都不会再写出这种语句了。

Dynamic SQL 有一个简单的处理，这在 90% 的情况下都会有用。而在不能使用的地方时，您可以通过自定义处理方式来使其正常工作。一处简单的修改就能达到目的：

<select>
  SELECT * FROM BLOG
  <where>
    <if test="state != nil">
         state = #{state}
    </if>
    <if test="title != nil">
        AND title like #{title}
    </if>
    <if test="author != nil and author.name != nil">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

where 元素只会在至少有一个子元素的条件返回 SQL 子句时，才去插入“WHERE”子句。而且，若语句的开头为 “AND” 或“OR”，where 元素也会将它们去除。
如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

其中：
prefixOverrides 属性会忽略通过 | 分隔的文本序列（需注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。
您可以写一些更加复杂的 trim 元素来应对其他情况，比如:

<trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
  ...
</trim>

上面的 trim 元素会尝试去掉结果开头的和结尾多余的 ,，并在开头和结尾加上括号。

2.4 foreach

动态 SQL 的另外一个常用操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select>
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

foreach 元素的功能非常强大，它允许您指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许您指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。
这个样例可以简化写成下面的形式：

<select>
  SELECT *
  FROM POST P
  WHERE ID in #{list}
</select>

2.5 bind

bind 元素可以根据指定的表达式创建一个变量并将其绑定到上下文。比如：

<select>
  <bind name="pattern" value="'%' + book.title + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

2.6 function

function 元素允许您定义一个函数片段，并且该函数可以递归，您可以这样定义一个函数：

<select>
    <function name="f1" parameters="dimension,value">
        ${dimension} = #{value}
    </function>
    SELECT * FROM BLOG
    WHERE
    <f1 dimension="title" value="book.title" />
    <f1 dimension="author" value="book.author" />
</select>

上面的 function 元素中定义了一个函数叫 “f1”, 并且其拥有两个参数 dimension 和 value，参数名之间用 , 隔开。
接下来， 我们使用 “f1” 元素来调用函数，并通过元素的属性 dimension 和 value 给函数传递参数。
function 元素可以和 foreach 配合使用来实现十分强大的功能。并且 function 允许递归调用，比如您可以这样来递归调用：

<select>
    <function name="f1" parameters="dimension,value">
        <if test="dimension != nil">
            ${dimension} = #{value}
        </if>
        <if test="dimension == nil">
            <f1 dimension="value.first" value="value.second" />
        </if
    </function>
    SELECT * FROM BLOG
    WHERE
    <f1 dimension="title" value="book.title" />
    <f1 dimension="nil" value="book.filter" />
</select>

上面的 function 元素中，通过递归调用完成进一步的解析，递归调用使得解析一些嵌套逻辑变得十分简单，比如实现多层的 AND OR 的逻辑嵌套。
您也可以通过定义只返回固定 SQL 片段的常函数，类似 C语言中的宏定义。

3 表达式计算

3.1 Supported Literals

The package supports:

• strings - single and double quotes (e.g. "hello", 'hello')
• numbers - e.g. 103, 2.5
• arrays - e.g. [1, 2, 3]
• maps - e.g. {foo: "bar"}
• booleans - true and false
• nil - nil

3.1.1 Accessing Public Properties

Public properties on structs can be accessed by using the . syntax.
If you pass an array into an expression, use the [] syntax to access array keys.

foo.Array[0].Value

3.1.2 Calling Methods

The . syntax can also be used to call methods on an struct.

price.String()

3.2 Supported Operators

The package comes with a lot of operators:

3.2.1 Arithmetic Operators

• + (addition)
• - (subtraction)
• * (multiplication)
• / (division)
• % (modulus)
• ** (pow)

Example:

life + universe + everything

3.2.2 Digit separators

Integer literals may contain digit separators to allow digit grouping into more legible forms.
Example:

10_000_000_000

3.2.3 Comparison Operators

• == (equal)
• != (not equal)
• < (less than)
• > (greater than)
• <= (less than or equal to)
• >= (greater than or equal to)

3.2.4 Logical Operators

• not or !
• and or &&
• or or ||

Example:

life < universe || life < everything

3.2.5 String Operators

• + (concatenation)
• matches (regex match)
• contains (string contains)
• startsWith (has prefix)
• endsWith (has suffix)

To test if a string does not match a regex, use the logical not operator in combination with the matches operator:

not ("foo" matches "^b.+")

You must use parenthesis because the unary operator not has precedence over the binary operator matches.
Example:

'Arthur' + ' ' + 'Dent'

Result will be set to Arthur Dent.

3.2.6 Membership Operators

• in (contain)
• not in (does not contain)

Example:

user.Group in ["human_resources", "marketing"]

"foo" in {foo: 1, bar: 2}

3.2.7 Numeric Operators

• .. (range)

Example:

user.Age in 18..45

The range is inclusive:

1..3 == [1, 2, 3]

3.2.8 Ternary Operators

• foo ? 'yes' : 'no'

Example:

user.Age > 30 ? "mature" : "immature"

3.3 Builtin functions

• len (length of array or string)
• all (will return true if all element satisfies the predicate)
• none (will return true if all element does NOT satisfies the predicate)
• any (will return true if any element satisfies the predicate)
• one (will return true if exactly ONE element satisfies the predicate)
• filter (filter array by the predicate)
• map (map all items with the closure)

Example:

// Ensure all tweets are less than 140 chars.
all(Tweets, {.Size < 140})

3.3.1 Closures

• {...} (closure)

Closures allowed only with builtin functions. To access current item use # symbol.

map(0..9, {# + 1})

If the item of array is struct, it's possible to access fields of struct with omitted # symbol (#.Value becomes .Value).

filter(Tweets, {.Size > 140})

3.3.2 Slices

• array[:] (slice)

Slices can work with arrays or strings.
Example:

// array is [1,2,3,4,5]
array[1:5] == [2,3,4] 
array[3:] == [4,5]
array[:4] == [1,2,3]
array[:] == array

4 总结

Dynamic SQL 会对 if、choose 里的 test 语句，foreach 里的 collection 语句，bind 里的 value 语句，以及 function 的参数进行表达式求值。
除了会对上述元素的个别属性的表达式进行求值以外，还会对 ${} 和 #{} 里包含的表达式求值，两者的不同之处在于后者会转成类似于 prepare statement 里的 SQL 参数，类似于 select * from BLOG where title = ? 里的 ?，而前者会直接拼接到 SQL 中，所以应当尽量使用 #{}，这可以有效防止 SQL 注入问题。