约定格式
service.querys("表", condition(String ... 条件)),中condition方法的参数格式约定,如:service.querys("CRM_USER", condition("TYPE_ID:type"))
参数值⇢ 约定格式⇣ |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |
code=0 | code= | code=0&code=1&cd=2&user=5 |
code=0,1&cd=2&user=5 |
cd=2&cd=3 | code=0(密文) | cd=2(密文)&cd=3(密文) | code=1,2 | ||
1 | CODE:code | CODE = 0 | 忽略 |
CODE = 0 |
CODE = 0 | 忽略 | 忽略 | 忽略 | x |
2 | CODE:%code% | CODE LIKE '%0%' | 忽略 |
CODE LIKE '%0%' |
CODE LIKE '%0%' |
忽略 | 忽略 | 忽略 | x |
3 | CODE:%code | CODE LIKE '%0' | 忽略 |
CODE LIKE '%0' |
CODE LIKE '%0' | 忽略 | 忽略 | 忽略 | x |
4 | CODE:code% | CODE LIKE '0%' | 忽略 |
CODE LIKE '0%' |
CODE LIKE '0%' | 忽略 | 忽略 | 忽略 | x |
5 |
CODE:%code:cd% ![]() |
CODE LIKE '%0%' | 忽略 |
CODE LIKE '%0%' |
CODE LIKE '%0%' | CODE LIKE '%2%' | 忽略 | 忽略 | x |
6 |
CODE:%code:cd:${9}% ![]() |
CODE LIKE '%0%' | CODE LIKE '%9%' |
CODE LIKE '%0%' |
CODE LIKE '%0%' | CODE LIKE '%2%' | 忽略 | 忽略 | x |
7 | CODE:%code:cd | CODE LIKE '%0' | 忽略 |
CODE LIKE '%0' |
CODE LIKE '%0' | CODE LIKE '%2' | 忽略 | 忽略 | x |
8 | CODE:%code:cd:${9} | CODE LIKE '%0' | CODE LIKE '%9' |
CODE LIKE '%0' |
CODE LIKE '%0' | CODE LIKE '%2' | 忽略 | 忽略 | x |
9 |
CODE:[code] ![]() |
CODE = 0 | 忽略 |
CODE IN(0,1) |
CODE IN(0,1) | 忽略 | 忽略 | 忽略 | x |
10 |
CODE:[split(code)] ![]() |
CODE = 1 |
忽略 |
CODE IN(0,1) |
CODE IN(0,1) |
忽略 |
忽略 |
忽略 |
CODE IN(1,2) |
11 |
CODE:[org.ClassA.split(code)]![]() |
CODE = 1 |
忽略 |
CODE IN(0,1) |
CODE IN(0,1) |
忽略 |
忽略 |
忽略 |
CODE IN(1,2) |
12 | CODE:[code:cd] | CODE = 0 | 忽略 |
CODE IN(0,1) |
CODE IN(0,1) | CODE IN(2,3) | 忽略 | 忽略 | x |
13 | CODE:[cd+] |
忽略 |
忽略 |
CODE = 2 |
CODE = 2 | CODE IN(2,3) |
忽略 |
CODE IN(2,3) | x |
14 |
CODE:[code:cd:${[6,7,8]}] ![]() |
CODE = 0 | CODE IN(6,7,8) |
CODE IN(0,1) |
CODE IN(0,1) | CODE IN(2,3) | 忽略 | 忽略 | x |
15 |
CODE:[code:cd:${6,7,8}] |
CODE = 0 | CODE IN(6,7,8) |
CODE IN(0,1) |
CODE IN(0,1) | CODE IN(2,3) | 忽略 | 忽略 | x |
16 |
+CODE:code ![]() |
CODE = 0 | CODE IS NULL |
CODE = 0 |
CODE = 0 | CODE IS NULL | 忽略 | 忽略 | x |
17 |
++CODE:code ![]() |
CODE = 0 | 不执行 |
CODE = 0 |
CODE = 0 | 不执行 | 忽略 | 忽略 | x |
18 | CODE:>code | CODE > 0 | 忽略 |
CODE > 0 |
CODE > 0 | 忽略 | 忽略 | 忽略 | x |
19 | CODE:>code:cd | CODE > 0 | 忽略 |
CODE > 0 |
CODE > 0 | CODE > 2 | 忽略 | 忽略 | x |
20 | CODE:>code:${9} | CODE > 0 | CODE > 9 |
CODE > 0 |
CODE >0 | CODE > 9 | CODE > 9 | CODE > 9 | x |
21 | CODE:code:cd | CODE = 0 | 忽略 |
CODE = 2 |
CODE = 2 | CODE = 2 | 忽略 | 忽略 | x |
22 | CODE:code:cd:${9} | CODE = 0 | CODE = 9 |
CODE = 0 |
CODE = 0 | CODE = 2 | 忽略 | 忽略 | x |
23 |
CODE:code|cd ![]() |
CODE = 0 | 忽略 |
CODE =0 OR CODE = 2 |
CODE =0 OR CODE = 2 | 忽略 | 忽略 | 忽略 | x |
24 | CODE:code|{NULL} | CODE = 0 OR CODE IS NULL | 忽略 |
CODE = 0 OR CODE IS NULL |
CODE = 0 OR CODE IS NULL |
忽略 |
忽略 |
忽略 |
x |
25 |
CODE:code|CODE:cd ![]() |
CODE = 0 | 忽略 |
CODE = 0 OR CODE = 1 |
CODE = 0 OR CODE = 1 | CODE = 2 | 忽略 | 忽略 | x |
26 |
CODE:code|CD:cd ![]() |
CODE = 0 | 忽略 |
CODE = 0 OR CD = 2 |
CODE = 0 OR CD = 2 | CD = 2 | 忽略 | 忽略 | x |
27 |
CODE:code:cd|user |
CODE = 0 | 忽略 |
CODE = 0 OR CODE = 5 |
CODE = 0 OR CODE = 5 | CODE = 2 | 忽略 | 忽略 | x |
28 |
CODE:code:cd|${9} |
CODE = 0 |
忽略 |
CODE = 0 OR CODE = 9 |
CODE = 0 OR CODE = 9 | CODE = 2 OR CODE = 9 | CODE = 9 | CODE = 9 | x |
29 |
CODE:code+:${9} ![]() |
CODE = 9 | CODE = 9 |
CODE = 9 |
CODE = 9 | CODE = 9 | CODE = 0 | CODE = 9 | x |
30 |
CODE:code+:cd:${9}![]() |
CODE = 9 | CODE = 9 |
CODE = 2 |
CODE = 2 | CODE = 2 | CODE = 0 | CODE = 9 | x |
31 | CODE:code+:cd+ |
忽略 |
忽略 |
忽略 |
忽略 |
忽略 |
CODE = 0 | CODE = 2 | x |
32 | CODE:code|CODE:cd|CD:cd|CD:code | CODE = 0 OR CD = 0 | 忽略 |
CODE =0 OR CODE = 2 OR ID =0 OR ID = 2 |
CODE =0 OR CODE = 2 OR ID =0 OR ID = 2 | CODE =2 OR CD =2 | 忽略 | 忽略 | x |
33 | CODE:code:${9}|CD:cd:${9} | CODE = 0 OR CD = 9 | CODE = 9 OR CD = 9 |
CODE = 0 OR CD = 2 |
CODE = 0 OR CD = 2 | CODE = 9 OR CD = 2 | CODE = 9 OR CD = 9 | CODE = 9 OR CD = 9 | x |
34 |
[CODES]:code![]() |
FIND_IN_SET('0',CODES) |
忽略 |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
忽略 |
忽略 |
忽略 |
x |
35 |
[CODES]:[code]![]() |
FIND_IN_SET('0',CODES) |
忽略 |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
忽略 |
忽略 |
忽略 |
FIND_IN_SET('1',CODES) OR FIND_IN_SET('2',CODES) |
36 |
[CODES]:split(code)![]() |
FIND_IN_SET('0',CODES) |
忽略 |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
FIND_IN_SET('0',CODES) OR FIND_IN_SET('1',CODES) |
忽略 |
忽略 |
忽略 |
FIND_IN_SET('1',CODES) OR FIND_IN_SET('2',CODES) |
|
在提交多个值时FIND_IN_SET会涉及到AND,OR两种情况,默认按OR实现,如果需要实现AND请参考ConfigStore.and() | ||||||||
|
在低代码平台或运行时用户自定义查询条件场景中可以用到以下格式: 列名不是由Java中指定而是通过解析参数确定,也就是说:前后都是http 的参数key,如${column}:code |
以上SQL在实际运行中以占位符?生成,类似CODE > '0'的条件实际是CODE > ?,java中通过 preapreStatement赋值,最终执行结果与数据类型有关
忽略:表示合成SQL时不拼接当前查询条件
不执行:表示整个SQL不执行,querys返回长度为0的DataSet,query返回null
[]表示数组
[CODES]:cd表示数据库中CODES是数组形式如1,2,3 查询时需要FIND_IN_SET函数 FIND_IN_SET('1', CODES)
CODE:[cd]表示request参数中cd是数组形式查询需要IN函数 CODE IN(1,2,3)
"+"开头表示必须条件,如果没有值传则生成CODE IS NULL的条件(仅"="时有效,其他IN,>时,当前条件忽略)
“++”开头时,如果没有传值则整个SQL不执行,返回长度为零的DataSet
"x"表示不符合预期,就不能这么用,不是为这种场景设计的