帆软sql编写样例

拼接条件

where后面一定存在参数

– 字符串参数
select * from t_student t where t.stu_name = ‘${stuName}’

– 数字参数
select * from t_student t where t.id=${id}

– 模糊查询
select * from t_student t where t.stu_name like ‘%${name}%’

where后面可能存在参数, 参数有值才拼接, 没值查所有

表达式内部使用双引号拼接字符串。$内部非帆软关键字、函数的其他变量会被认为是要传递的参数, 直接使用变量名称即可, 不用将变量再放仅$中

name参数有值时拼接name的过滤条件, 否则不过滤name字段. eg: select * from t_student t where 1=1
1	正确案例	错误案例
2	${if(len(name)==0, "", " and t.stu_name = ' "+name+" ' ")}	${if(len(${name})==0, "", " and t.stu_name = ' "+${name}+" ' ")}

参数存在才拼接, 为了方便现在where后面拼接一个1=1
1. 参数为字符串, 需要构造参数所需的引号, 在变量本身包含或者在sql中拼接均可
select * from t_student t where 1=1 ${if(len(outname)==0,""," and t.stu_name like '%"+outname+"%' ")}
2. 参数为数字, 直接拼接参数即可
select * from t_student t where 1=1 ${if(len(id)==0,""," and t.id="+id)}
3. 组合参数
select * from t_student t
where 1=1
${if(len(id)==0,""," and t.id="+id)}
${if(len(aname)==0,""," and t.address like'%"+aname+"%' ")}
-- name是多个候选值拼接而成的最终字符串, 如果参数值是字符串, 需要将引号拼接在里面, 如: name='carl','changez'
${if(len(name)==0,""," and t.stu_name in("+name+")")}
${if(len(birthday)==0,""," and t.birthday > str_to_date(' "+birthday+" ', '%Y-%m-%d %H:%i:%s')")}