println("----------1、#{} 注入参数----------")var id =1;var result = db.select("""
select * from test_data where id = #{id}
""");printf("result = %s", result)println("")/*
要执行的SQL:select * from test_data where id = ?
要执行的SQL参数:[1]
*/println("----------2、${} 拼接参数----------")var id =2;var result2 = db.select("""
select * from test_data where id = ${id}
""");printf("result2 = %s", result2)println("")/*
要执行的SQL:select * from test_data where id = 2
要执行的SQL参数:[]
*/println("----------3、动态SQL参数----------")var id =1;var result3 = db.select("select * from test_data ?{id,where id = #{id}}");// 当id有值时,生成SQL:select * from test_data where id = ?`,相当于mybatis中的<if test="id != null and id != ''">// 当id无值时,生成SQL:select * from test_dataprintf("result3 = %s", result3)println("")/*
要执行的SQL:select * from test_data where id = ?
要执行的SQL参数:[1]
*/var id ="2";var result4 = db.select("select * from test_data ?{id!=null&&id.length() > 3,where id = #{id}}");// 当id!=null&&id.length() > 3判断为true时,生成SQL:`select * from sys_user where id = ?// 当判断为false时,生成SQL:select * from sys_userprintf("result4 = %s", result4)println("")/*
要执行的SQL:select * from test_data
要执行的SQL参数:[]
*/println("----------4、切换数据源----------")var id =1;// 从数据源key定义为slave的库中查询var result5 = db.slave.select("""
select * from user where id = ${id}
""")printf("result5 = %s", result5)println("")/*
要执行的SQL:select * from user where id = 1
要执行的SQL参数:[]
*/println("----------5、SQL缓存----------")var id =3;// 将查询结果缓存到名为user_cache的缓存中,有效期1小时var result6 = db.cache("user_cache",3600*1000).select("""
select * from user where id = ${id}
""")printf("result6 = %s", result6)println("")// 当执行以下语句时,将清空user_cache缓存// db.cache("user_cache").update(""" ...... """)// db.cache("user_cache").insert(""" ...... """)/*
要执行的SQL:select * from user where id = 3
要执行的SQL参数:[]
*/println("----------6、自动事务----------")// var result7 = db.transaction(() => {// var v1 = db.update('...');// var v2 = db.update('....');// return v2;// });// printf("result7 = %s", result7);// println("");println("----------7、手动事务----------")// var tx = db.transaction(); //开启事务// try {// var value = db.update('...');// tx.commit(); // 提交事务// return value;// } catch (e) {// tx.rollback(); // 回滚事务// }println("----------8、Mybatis语法支持----------")var id =2;var sql ="""
select * from test_data
where 1 = 1
<if test="id != null">
and id = #{id}
</if>
"""var result8 = db.select(sql)printf("result8 = %s", result8)println("")/*
要执行的SQL:select * from test_data
where 1 = 1 and id = ?
要执行的SQL参数:[2]
*/var sql ="""
select * from test_data
where 1 = 1
<if test="id == null">
and id = 0
</if>
<elseif test="id < 1000">
and id = #{id}
</elseif>
"""var result9 = db.select(sql)printf("result9 = %s", result9)println("")/*
要执行的SQL:select * from test_data
where 1 = 1 and id = ?
要执行的SQL参数:[2]
*/var sql ="""
select * from test_data
where
<if test="id == null">
id = 0
</if>
<else>
id = #{id}
</else>
"""var result10 = db.select(sql)printf("result10 = %s", result10)println("")/*
要执行的SQL:select * from test_data
where id = ?
要执行的SQL参数:[2]
*/var sql ="""
select * from test_data
<where>
<if test="id != null and id > 10">
and id = #{id}
</if>
</where>
"""var result11 = db.select(sql)printf("result11 = %s", result11)println("")/*
要执行的SQL:select * from test_data
要执行的SQL参数:[]
*/return"增删改查";
6. 单表crud接口
println("----------1、select----------")var id =1;var result = db.table('test_data').column("id").select();printf("result = %s", result)println("")/*
要执行的SQL:select id from test_data
要执行的SQL参数:[]
*/println("----------2、page----------")var result2 = db.table('test_data').page();printf("result2 = %s", result2)println("")/*
要执行的SQL:select count(1) from (select * from test_data) count_
要执行的SQL参数:[]
*/return"单表crud接口";
7. 分页
println("----------1、自动分页----------")var page =1var size =2// 自动从请求参数中获取页码(默认为page)、页大小(默认为size)var result = db.page("""
select * from test_data
""")printf("result = %s", result)println("")/*
要执行的SQL:select count(1) from (select * from test_data) count_
要执行的SQL参数:[]
*/println("----------2、手动分页----------")var result2 = db.page("""
select * from test_data
""",10,20)// 跳过前20条查10条(limit, offset)printf("result2 = %s", result2)println("")/*
要执行的SQL:select count(1) from (select * from test_data) count_
要执行的SQL参数:[]
*/return'分页'
二、基础教程
1. 脚本语法
println("----------三元运算符----------")printf("true = %s",true?"yes":"no")println("")printf("false = %s",false?"yes":"no")println("")printf("null = %s",null?"yes":"no")println("")printf("[] = %s",[]?"yes":"no")println("")printf("{} = %s",{}?"yes":"no")println("")printf("0 = %s",0?"yes":"no")println("")printf("'' = %s", '' ?"yes":"no")println("")/*
true = yes
false = no
null = no
[] = no
{} = no
0 = no
'' = no
*/println("----------类型转换----------")var a ="123"::int;printf("a instanceof Integer = %s", a instanceofInteger)println("")// 123var b ="abc"::int(111);printf("b instanceof Integer = %s", b instanceofInteger)println("")// 111var c ="2020-01-01"::date('yyyy-MM-dd');// 转换为dateprintf("c instanceof Date = %s", c instanceofDate)println("")/*
a instanceof Integer = true
b instanceof Integer = true
c instanceof Date = true
*/println("----------可选链操作符----------")var a =null;var b = a?.name;printf("b = %s", b)println("")var c = a?.getName();printf("c = %s", c)println("")/*
b = null
c = null
*/println("----------扩展运算符----------")var sum =(a, b, c)=> a + b + c;System.out.println(sum(...[1,2,3,4]))/*
6
*/var arr =[3,4,5];System.out.println([1,2,...arr,6,7])/*
[1, 2, 3, 4, 5, 6, 7]
*/var arr =[3,4,5];System.out.println({
key1:1,...arr
})/*
{key1=1, 0=3, 1=4, 2=5}
虽然这些key看起来像数值,但其实是String类型的key,如果把它们转为JSON看起来是这样的:
{"key1":1, "0":3, "1":4, "2":5}
*/println("----------for循环----------")import 'java.lang.System' as System;var list =[1,2,3];// 如果不需要index,也可以写成for(item in list)for(index, item in list){System.out.println(index +":"+ item);}/*
0:1
1:2
2:3
*/var sum =0;for(value in range(0,100)){// 包括0包括100
sum = sum + value;// 不支持`+=、-=、*=、/=、++、--`这种运算。}System.out.printf("sum = %s", sum).println();/*
sum = 5050
*/println("----------while循环----------")var count =100;var sum =0;while(count){
sum = sum + count;
count = count -1;}System.out.printf("sum = %s", sum).println();/*
sum = 5050
*/println("----------循环map----------")import 'java.lang.System' as System;var map ={
key1:123,
key2:456};for(key, value in map){//如果不需要key,也可以写成for(value in map)System.out.println(key +":"+ value);}/*
key1:123
key2:456
*/println("----------Import导入----------")/**
* 导入Java类。
*/import 'java.lang.System' as System;//导入静态类并赋值给system作为变量import 'javax.sql.DataSource' as ds;//从spring中获取DataSource并将值赋值给ds作为变量import 'org.apache.commons.lang3.StringUtils' as string;//导入静态类并赋值给ds作为变量import 'java.text.*' //此写法跟Java一致,在1.3.4中新增System.out.println('调用System打印');//调用静态方法System.out.println(ds);System.out.println(string.isBlank(''));//调用静态方法System.out.println(newSimpleDateFormat('yyyy-MM-dd').format(newDate()));// 2020-01-01/*
调用System打印
HikariDataSource (HikariPool-1)
true
2022-07-28
*/import 'java.util.Date' as Date;//创建之前先导包,不支持.*的操作var date =newDate();System.out.printf("date = %s", date).println();/*
date = Thu Jul 28 19:21:32 CST 2022
*//**
* 导入已定义的模块。
*/importlog;//导入log模块,并定义一个与模块名相同的变量名//import log as logger; //导入log模块,并赋值给变量 logger
log.info('Hello{}', 'Magic API!')
log.debug('Hello{}', 'Magic API!')
log.error('Hello{}', 'Magic API!')println("----------异步调用----------")var val = async db.select('select * from test_data');// 异步调用,返回Future类型var result = val.get();//调用Future的get方法System.out.printf("result = %s", result).println();/*
要执行的SQL:select * from test_data
要执行的SQL参数:[]
*/var list =[];for(index in range(1,10)){
list.add(async (index)=> db.selectInt('select #{index}'));}var result = list.map(item => item.get());// 循环获取结果System.out.printf("result = %s", result).println();/*
result = [{id=1, name=magicApi}, {id=2, name=xiaoDong}]
*/if(name){
exit 400,'参数填写有误'}if(age){
exit 500,'参数填写有误',9}println("----------类型转换----------")var a ="1";var result ={
v1: a::int,
v2: a::int(0),//转换失败时,值为0
v3:"2020-01-01"::date('yyyy-MM-dd')//转为Date}System.out.printf("result = %s", result).println();/*
result = {v1=1, v2=1, v3=Wed Jan 01 00:00:00 CST 2020}
*/println("----------嵌入其它脚本语言----------")var name ="hello";var test =```javascript
name + ' ~ world'
```;var result =test();System.out.printf("result = %s", result).println();/*
result = hello ~ world
*/return"脚本语法"
2. 参数校验
var count = db.selectInt("""
select count(*) from test_data where name = #{name}
""")System.out.printf("count = %s", count).println();/*
要执行的SQL:select count(*) from test_data where name = ?
要执行的SQL参数:[magicApi]
count = 1
*/// count 值应该为0,如果不为0则验证不予通过。assert count ==0:400,'名字已存在';// 上述写法可以转换为if(count !=0){
exit 400,'名字已存在'}
3. 脚本调用 Java
println("----------注入Spring Bean----------")/**
* 使用类名。
*/import 'org.springframework.core.env.Environment' as c_env;var env = c_env.getProperty('magic-api.web');System.out.printf("env = %s", env).println();importcom.qs.springbootmagicapi.controller.ApiController;System.out.printf("ApiController = %s",newApiController().get()).println();/**
* 使用Bean名。
*/import"apiController" as java_api;var result = java_api.get();System.out.printf("result = %s", result).println();/*
env = /magic/web
ApiController = Magic-Api
result = Magic-Api
*/println("----------调用静态方法----------")importcom.qs.springbootmagicapi.utils.ApiUtil;var result =ApiUtil.VALUE;System.out.printf("result = %s", result).println();var result2 =ApiUtil.isEmpty("wy");System.out.printf("result2 = %s", result2).println();/*
result = Magic-Api
result2 = false
*/println("----------调用普通方法----------")// 对于java.util、java.lang 包下的类,可以直接使用。var list =newArrayList();System.out.printf("list = %s", list).println();// 对于其他类需要importimport"java.text.SimpleDateFormat";var dateStr =newSimpleDateFormat("yyyy-MM-dd").format(newDate());System.out.printf("dateStr = %s", dateStr).println();/*
list = []
dateStr = 2022-07-28
*/println("----------调用magic-api的接口----------")/**
* 1、调用本地接口。
* 可以在脚本中直接调用,非http方式。
* 导入定义的GET请求的`/api/sys/user/hello`接口。
*/import '@get:/demo/hello' as m_hello;var message ='World'var hello =m_hello();System.out.printf("hello = %s", hello).println();/*
hello = Hello World
*/println("----------调用magic-api的函数----------")import '@/m/add' as m_add;var add =m_add(1,2);System.out.printf("add = %s", add).println();import '@/m/addTest' as m_addTest;var addTest =m_addTest();System.out.printf("addTest = %s", addTest).println();/*
add = 3
addTest = 30
*/
4. 异步调用
println("----------普通方法----------")// 使用async关键字,会启动一个线程去执行,返回Future,并不等待结果继续执行后续代码var user1 = async db.select("select * from test_data where id = 1");var user2 = async db.select("select * from test_data where id = 2");// 调用get方法表示阻塞等待获取结果var result =[user1.get(), user2.get()];System.out.printf("result = %s", result).println();/*
要执行的SQL:select * from test_data where id = 1
要执行的SQL参数:[]
要执行的SQL:select * from test_data where id = 2
要执行的SQL参数:[]
result = [[{id=1, name=magicApi}], [{id=2, name=xiaoDong}]]
*/println("----------lambda----------")var list =[];var selectData =(id)=> db.select("select * from test_data where id = #{id}");for(index in range(1,10)){
list.add(async selectData(index));}for(index in range(1,10)){
list.add(async (index)=> db.select("select * from test_data where id = #{index}"));}// 以上两种方式都可以var result = list.map(item => item.get());System.out.printf("result = %s", result).println();return result;/*
要执行的SQL:select * from test_data where id = ?
要执行的SQL参数:[5]
要执行的SQL:select * from test_data where id = ?
要执行的SQL参数:[3]
*/return'异步调用'
5. Lambda
println("----------映射(map)----------")var list =[{
sex:0,
name:'小明',
age:19},{
sex:1,
name:'小花',
age:18}];var getAge =(age)=> age >18?'成人':'未成年'// 利用map函数对list进行过滤var result = list.map(item =>{
age:getAge(item.age),
sex: item.sex ==0?'男':'女',
name: item.name
});System.out.printf("result = %s", result).println();/*
result = [{age=成人, sex=男, name=小明}, {age=未成年, sex=女, name=小花}]
*/// 以上代码可以使用linq代替var result = select t.name,
t.age >18?'成人':'未成年' age,
t.sex ==0?'男':'女' sex
from list t;System.out.printf("result = %s", result).println();/*
result = [{t.name=小明, age=成人, sex=男}, {t.name=小花, age=未成年, sex=女}]
*/println("----------过滤(filter)----------")var list =[{
sex:0,
name:'小明'},{
sex:1,
name:'小花'}]// 利用map函数对list进行过滤var result = list.filter(item => item.sex ==0);System.out.printf("result = %s", result).println();/*
result = [{sex=0, name=小明}]
*/// 以上代码可以使用linq代替var result = select * from list t where t.sex =0System.out.printf("result = %s", result).println();/*
result = [{sex=0, name=小明}]
*/println("----------过滤+映射(filter + map)----------")var list =[{
sex:0,
name:'小明'},{
sex:1,
name:'小花'}]// 对list进行过滤,然后进行映射var result = list.filter(item => item.sex ==0).map(item =>{
sex: item.sex ==0?'男':'女',
name: item.name
});System.out.printf("result = %s", result).println();/*
result = [{sex=男, name=小明}]
*/var result = select t.sex ==0?'男':'女' sex,
t.name from list t where t.sex =0System.out.printf("result = %s", result).println();/*
result = [{sex=男, t.name=小明}]
*/println("----------分组(group)----------")// List<Map<String,Object>>var result =[{
xxx:1,
yyy:2,
value:11},{
xxx:1,
yyy:2,
value:22},{
xxx:2,
yyy:2,
value:33}];var res = result.group(item => item.xxx +'_'+ item.yyy)System.out.printf("result = %s", res).println();/*
Map<Object,List<Object>>
{
"1_2": [
{"yyy": 2, "xxx": 1, "value": 11},
{"yyy": 2, "xxx": 1, "value": 22}
],
"2_2": [{"yyy": 2, "xxx": 2, "value": 33 }]
}
*//**
* 自定义聚合对象。
*/var res = result.group(item => item.xxx +'_'+ item.yyy, list =>{
count: list.size(),
sum: list.map(v => v.value).sum(),
avg: list.map(v => v.value).avg()})System.out.printf("result = %s", res).println();/*
Map<Object,Object>
{
"1_2": { "avg": 16.5, "count": 2, "sum": 33 },
"2_2": { "avg": 33, "count": 1, "sum": 33 }
}
*/println("----------关联(join)----------")var year2019 =[{"pt":2019,"item_code":"code_1","sum_price":2234},{"pt":2019,"item_code":"code_2","sum_price":234},{"pt":2019,"item_code":"code_3","sum_price":12340},{"pt":2019,"item_code":"code_4","sum_price":2344}];var year2018 =[{"pt":2018,"item_code":"code_1","sum_price":1234.0},{"pt":2018,"item_code":"code_4","sum_price":1234.0}];var result = year2019.join(year2018,(left, right)=> left.item_code == right.item_code,(left, right)=>{'年份': left.pt,'编号': left.item_code,'今年': left.sum_price,'去年': right ==null?'unknow': right.sum_price,'环比去年增长': right ==null?'-':(((left.sum_price - right.sum_price)/ right.sum_price *100)+"%")});System.out.printf("result = %s", result).println();/*
[
{"年份": 2019, "今年": 2234, "去年": 1234, "环比去年增长": "81.03728%", "编号": "code_1"},
{"年份": 2019, "今年": 234, "去年": "unknow", "环比去年增长": "-", "编号": "code_2"},
{"年份": 2019, "今年": 12340,"去年": "unknow","环比去年增长": "-","编号": "code_3"},
{"年份": 2019, "今年": 2344, "去年": 1234, "环比去年增长": "89.95138%", "编号": "code_4"}
]
*/var result = select
t.pt 年份,
t.item_code 编号,
t.sum_price 今年,ifnull(t1.sum_price,'unknow') 去年,// ifnull 会对参数进行计算,会报错 所以这里采用三元运算符
t1.sum_price ==null?'-':((t.sum_price - t1.sum_price)/ t1.sum_price).asPercent(2) 环比去年增长
from year2019 t
left join year2018 t1 on t.item_code = t1.item_code
System.out.printf("result = %s", result).println();/*
result = [
{年份=2019, 编号=code_1, 今年=2234, 去年=1234.0, 环比去年增长=81.04%},
{年份=2019, 编号=code_2, 今年=234, 去年=unknow, 环比去年增长=-},
{年份=2019, 编号=code_3, 今年=12340, 去年=unknow, 环比去年增长=-},
{年份=2019, 编号=code_4, 今年=2344, 去年=1234.0, 环比去年增长=89.95%}
]
*/return'Lambda'
6. Linq
/*
select
tableAlias.*|[tableAlias.]field[ columnAlias]
[,tableAlias.field2[ columnAlias2][,…]]
from expr[,…] tableAlias
[[left ]join expr tableAlias2 on condition]
[where condition]
[group by tableAlias.field[,...]]
[having condition]
[order by tableAlias.field[asc|desc][,tableAlias.field[asc|desc]]]
[limit expr [offset expr]]
*/var result =
select t.name, t.*
from [{'name':'Gitee'},{'name':'Github'}] t;System.out.printf("result = %s", result).println();/*
result = [{t.name=Gitee, name=Gitee}, {t.name=Github, name=Github}]
*/return'Linq'