数据查询 DSL

查询 DSL 是一种用于构建查询语句的结构化的表达方式，DSL 基于 Map/List 结构，在保持简便性的同时，也提供了一种可以更好地控制和描述查询的方式。

Map/List 结构的优点

• 构建方便，可以将前端的参数直接映射为查询条件，也可以用 Groovy 等脚本很方便的构建：

  def query = [
    select: ['id', 'name'],
    from: 'user',
    where: [ department: 1001]
  ]

• 结构化数据，可以更方便的编程控制
• 语法分析简单，存在大量的工具库降低开发与维护成本

DML —— 增删改

1. 插入

单数据插入

def insertStatement = [
        insert: 'tableA',
        values: [
                field1: 'value1',
                field2: 'value2'
        ]
]

批量插入

def batchInsertStatement = [
        insert: 'tableA',
        values: [
                [
                        field1: 'value11',
                        field2: 'value21'
                ],
                [
                        field1: 'value12',
                        field2: 'value22'
                ],
        ]
]

2. 更新

def updateStatement = [
        update: 'tableA',
        set   : [
                field1: 'value1',
                field2: 'value2'
        ],
        where : [ /* 条件定义 */]
]

WHERE 子句请参考 DQL 中的4. WHERE 子句

3. 删除

def deleteStatement = [
        delete: "tableA",
        where : [ /* 条件定义 */]
]

WHERE 子句请参考 DQL 中的4. WHERE 子句

DQL —— 查询

def selectStatement = [
        // 未实现
        with     : [ /* 关联定义 */],
        // 是否去重
        distinct : false,
        select   : [
                'id',
                [nameAlias: 'name'],
                /* 字段表 */
        ],
        omit     : [ /* 剔除字段列表 (模型上可用) */],
        from     : [ // 也可以直接设定表名字符串
                     'a': 'tableA'
                     /* 别名或子查询 */
        ],
        // INNER JOIN
        join     : [
                /* 连接表 */
                [
                        b : 'tableB',
                        on: ['b.id': 'a.id']
                ]
        ],
        leftJoin : [ /* 连接表 */],
        rightJoin: [ /* 连接表 */],
        fullJoin : [ /* 连接表 */],
        crossJoin: [ /* 连接表 */],
        populate : [ /* 填充关联字段（模型查询可用） */],
        where    : [ /* 条件定义 */],
        group    : [ /* 分组字段 */],
        having   : [ /* 聚合条件 */],
        order    : [ /* 排序定义 */],
        // 分页
        skip     : 0,
        limit    : 10,
        union    : [ /* UNION 语句 */]
]

1. SELECT 子句

// 简单SELECT
def select = ['id', 'name', 'gender']

// 字段别名 [别名: '字段名']
def select2 = ['id', [username: 'name'], [gender: 'gender']]

// CASE WHEN
def select3 = ['id', 'name', [gender: ['case', [[gender: 1], '男'], [[gender: 2], '女']]]]

// 聚合函数
def select4 = [[cnt: ['count', [1]]], [sum: ['sum', ['price']]]]

// 子查询结果作为返回字段
def select5 = ['id', 'name', 'gender',
               [
                       orderCount: [
                               select: ['count', 1],
                               from  : 'order',
                               where : ['=', ['order.user'], ['user.id']]
                       ]
               ]
]
// 字段列表中的第一个字段名不能为关键字，如and、or、not等，及标准SQL函数名等
// 可以通过加表别名前缀或者把第一个字段设为 'field' 的方式解决
// 在多表关联查询中，没有指定表名的字段默认都会指向主表

2. OMIT 子句

注意

模型专用

只能是字段列表，且不支持表达式（因为没有意义）

3. FROM 子句

基于数据模型查询的时候可以省略，默认即为当前模型

// 简单FROM
def from1 = 'tableA'

// 指定表别名
def from2 = [a: 'tableA']

// 子查询
def from3 = [a: [ /* 查询语句 */]]

4. WHERE 子句

def where = [id: 1]

为什么不直接用字符串形式的 SQL 表达式？

1. 字符串形式的表达式很难杜绝拼接 SQL 的使用方式，会存在 SQL 注入风险。
2. 字符串形式的表达式要兼容各种数据库的方言的时候，需要做完整的语法解析，工作量大。
3. 对字符串形式的表达式做运行时检查要远高于结构化的数据结构。
4. 字符串形式的表达式难以二次修改，比如在原有的查询条件中加入权限控制条件，或者剔除某些受限制的查询条件。

WHERE 子句由结构化的条件表达式构成，表达式又分为 Map 表达式 和 S 表达式。

Map表达式

Map 表达式的优势

Map 表达式的主要价值在于，在 WebAPI 接口中，最常用的是查询接口，查询接口的参数传递方式都是键值对，而键值对可以很自然的转化为 Map 结构，而 Map 结构的修改、合并等操作很方便，也就便于组合查询逻辑，比如合并权限控制查询参数、提供默认参数或者覆写敏感参数。

Map 表达式的局限性

Map 表达式的表达能力有限，比如这样的 SQL 条件，用 Map 表达式就很难表达了：

select * from user where createdAt = updatedAt;

尝试用 Map 表达式表达上面的 SQL 条件：

// ❌ 有歧义，无法区分'updatedAt'代表的是字段还是常量
def whereA = [createdAt: 'updatedAt']

// 😔 可接受，但嵌套层次深，不易编写、不易读
def whereM = [createdAt: [equal: [field: 'updatedAt']]]

再复杂一点的例子：

select * from news where YEAR(createdAt) = YEAR(current_timestamp());

// ❌ 过于复杂，不可接受，并且函数参数依然存在歧义
def whereA = [['year': [[field: 'createdAt']]]: [equal: [year: [[current_timestamp: []]]]]]

// 😔 如果需要拼接参数，则会留下注入漏洞，还是尽量不要这样做
def whereB = [RAW_EXP: 'YEAR(createdAt) = YEAR(CURRENT_TIMESTAMP())']

// 😔 可接受，但嵌套层次深，不易编写、不易读
def whereM = [equal: [[year: [[field: ['createdAt']]]], [year: [current_timestamp: []]]]]

BTW: 这个例子中 whereM 的语法形式是 M 表达式（Meta-expression），第一个例子中的 whereM 混合了 Map 表达式 和 M 表达式 两种写法

为了不引入更多复杂性，暂 不支持 M 表达式

S表达式

S 表达式更加灵活

如果采用 S 表达式（S-expression）的形式，上面的表达式可以更简洁一些：

// 😄 相比M表达式，S表达式的括号嵌套层次更浅
def whereS = ['equal', ['$year', ['$field', 'createdAt']], ['$year', ['$current_timestamp']]]

通过上面的例子我们可以得出 S 表达式 的基本形式：['<符号>', ...参数列表]。

BTW: S 表达式就是 Lisp 语言的的基本形式，Clojure 中著名的 ORM 库 HoneySQL¹ 就是基于 S 表达式的。

S 表达式的缺点

S 表达式是半结构化数据，相比于结构化的 Map 表达式，定位特定查询字段比较麻烦，不方便做查询字段覆写之类的操作。

两种表达式的适用场景

Map 表达式 擅长自动处理大量字段的查询需求，快速生成 WebAPI^{2 3 4 5}满足绝大多数的 OLTP（在线交易）场景需求。

S 表达式 大多数 OLAP（在线分析）的场景，查询比较复杂但是参数较少且参数位置固定，这时候用 S 表达式 比较合适。

---

比较表达式

比较运算符

比较操作符	兼容写法	说明
equal	=、==、eq	等于
notEqual	!=、<>、ne、	不等于
greaterThan	>、gt	大于
greaterThanOrEqual	>=、ge	大于等于
lessThan	<、lt	小于
lessThanOrEqual	<=、le	小于等于
between	between	在指定的值之间
in	in	在指定的值中
notIn	notIn	不在指定的值中
like	like、contains、~	匹配指定的值
notLike	notContains、!~	不匹配指定的值
leftLike	startsWith、~*	匹配指定的值
rightLike	endsWith、*~	匹配指定的值
regexp	regex	匹配指定的正则表达式

其中，多单词的操作符，均支持下划线分割的写法

比较条件的基本表达方式：

// Map表达式
def mapCompare = [fieldName: ['<操作符>': '常量值']]

// S表达式
def sCompare1 = ['<操作符>', ['fieldName'], '常量值']

// S表达式的优点是能够表表达复杂的逻辑
def sExprB = ['=', ['left', ['fieldNameA'], 5], ['left', ['fieldNameB'], 5]]
// 等价SQL表达式：WHERE LEFT(fieldNameA, 5) = LEFT(fieldNameB, 5)

比较操作符不区分大小写。

绝大多数的操作符都有兼容写法，如表示 等于条件 的 equal 兼容符号有"="、"=="等。

还有一些操作符存在便捷写法，如 等于条件：

// 简便写法
def equalExprA = [fieldName: '比较值']

// 等同于
def equalExprB = [fieldName: [equal: "比较值"]]

比较条件的简便写法

• 等于条件：

def equalExpr = [fieldNameA: "值"]

• in 条件：

def inExpr = [fieldNameA: ["值1", "值2"]] // 也就是等于表达式

• notIn 条件：

def notInExpr = [fieldName: ['!=': ["值1", "值2"]]] // 也就是不等于表达式

逻辑表达式

逻辑运算符

逻辑运算符	兼容写法	说明
and	&&	并且
or	||	或者
not	!	取反
xor		异或
xnor		同或

逻辑表达式的表达式方式：

// Map表达式
def logicMapExpr = [
        "<AND|OR|NOT|XOR|XNOR>": [
                [fieldA: ['=': '<搜索值>']],
                [fieldB: ['=': '<搜索值>']]
        ]
]

// S表达式
def logicSExpr = [
        "<AND|OR|NOT|XOR|XNOR>",
        ['=', ['fieldA'], '<搜索值>'],
        ['=', ['fieldB'], '<搜索值>']
]

逻辑运算符也有简便写法，如下：

AND/OR 运算符

// 简便写法A（即内部的条件表达式可以简写）：
def andExprA = [
        and: [
                fieldA: '<搜索值>',
                fieldB: '<搜索值>'
        ]
]

def orExprA = [
        or: [
                fieldA: '<搜索值>',
                fieldB: '<搜索值'
        ]
]

// AND操作符在不需要参与其他组合条件的情况下，可以直接使用更简便的写法B：
def andExprB = [
        fieldA: '<搜索值>',
        fieldB: '<搜索值>'
]

NOT 运算符支持多条件的写法，隐含了先将条件组合成一个 AND 条件，然后再使用 NOT 操作符：

def notExprA = [
        not: [
                fieldA: "searchValue",
                fieldB: "searchValue"
        ]
]

复杂条件（S 表达式）

// 1. （S表达式）字段间比较，比如：createdAt != updatedAt
def sExpr = ['!=', ['createdAt'], ['updatedAt']]

// 2. （S表达式）函数
def sExpr2 = ['=', ['year', ['createdAt']], ['year', ['now']]]

// 3. （S表达式）IN条件的多字段子查询
def sExpr3 = ['in', ['a', 'b'], [select: ['a', 'b'], from: 'tableC', where: [id: ['>': 1]]]]
//                     ^
//              同 SELECT 语法

// 4. CASE WHEN（S表达式）
def sExpr4 = ['case',
              ['when', ['=', ['gender'], 1], '男'],
              ['when', ['=', ['gender'], 2], '男'],
              ['when', [], '未知']
]
// CASE WHEN 简写
def sExpr5 = ['case',
              [[gender: 1], '男'],
              [[gender: 2], '女'],
              [[], '未知']
]


// S表达式、Map表达式混写
def sExprMapExpr1 = ['and', [department: 1001], ['>', ['age'], 30]]
def mapExprSExpr1 = ['and': [[department: 1001], ['>', ['age'], 30]]]
// 等价Map表达式：
def mapExpr1 = [department: 1001, age: ['>': 30]]
// 等价SQL表达式：
// department = 1001 AND age > 30

5. ORDER 子句

def orderA1 = 'fieldA'
def orderA2 = 'fieldA ASC'
def orderB1 = ['fieldA', 'fieldB']
def orderB2 = ['fieldA ASC', 'fieldB DESC']
def orderC1 = [[fieldA: 'ASC'], [fieldB: 'ASC']]
def orderD1 = [fieldA: 'ASC', fieldB: 'ASC']

6. SKIP/LIMIT 子句

skip/limit 子句用于分页，skip 表示跳过的记录数，limit 表示每页的记录数。

7. POPULATE 子句

注意

模型专用

def populateA = ['user']
def populateB = [user: [select: [], omit: [], where: []]]

8. JOIN 子句

// 简单JOIN
def join1 = [with: 'tableB', on: ['tableA.fk': 'tableB.id']]

// 指定别名
def join2 = [with: [b: 'tableB'], on: ['a.fk': 'b.id']]

// 复杂条件
def join3 = [with: [b: 'tableB'], on: [ /* S表达式 */]]

9. GROUP 子句

// 简单GROUP
def group1 = ['fieldA', 'fieldB']

// 复杂GROUP
def group2 = [[/* S表达式 */]]

10. HAVING 子句

// 基于select中的聚合字段
def havingA = [arragedFiledA: ['>': 1000]]

// 使用聚合函数
def havingB = ['>', ['count', ['fieldA']], 1000]

11. UNION/UNIONAll 子句

def union = [ /* SELECT 查询 */]

Footnotes

1. Honey SQL ↩

2. Sails.js - find where ↩

3. Wix - API Query Language ↩

4. Resource Query Language for REST ↩

5. REST Query Language with RSQL ↩