sql2java:WhereHelper基于Beanshell(bsh)动态生成SQL语句
BeanShell是一个小型嵌入式Java源代码解释器，具有对象脚本语言特性，能够动态地执行标准JAVA语法，并利用在JavaScript和Perl中常见的的松散类型、命令、闭包等通用脚本来对其进行拓展。BeanShell不仅仅可以通过运行其内部的脚本来处理Java应用程序，还可以在运行过程中动态执行你java应用程序执行java代码。因为BeanShell是用java写的，运行在同一个虚拟机的应用程序，因此可以自由地引用对象脚本并返回结果。
基于Beanshell可以实现很多有意思的功能，比如最近的工作中为了给前端提供灵活的数据库条件查询，我利用Beanshell的能力，可以实现了WhereHelper用于根据前端提供的参数，动态生成SELECT查询语句，大大简化了代码复杂度。
本文介绍WhereHelper的使用
WhereHelper
引入依赖
<dependency>
<groupId>com.gitee.l0km</groupId>
<artifactId>sql2java-pagehelper</artifactId>
<version>3.11.1</version>
</dependency>
基于 BeanShell 脚本引擎实现动态生成SQL WHERE 语句
调用示例
/** 创建WhereHelper 实例 */
WhereHelper helper = WhereHelper.builder()
/** 设置是否输出调试信息 */
.debuglog(true)
/** 增加SELECT语句头,不加只生成WHERE语句部分 */
.selectFrom("select * from dc_device")
/** 一般表达式 */
.exp("id>0").and()
/** 有引用变量的表达式 */
.exp("update_time > '${update_time}'").and()
/** like 表达式 */
.exp("name like 'hello%'").and()
/** 有引用变量的like 表达式 */
.exp("name like '${name}%'").and()
/** 一般表达式,输入参数update_time为null时输出 update_time IS NULL */
.equal("update_time").and()
/** IN 语句测试,status字段为String数组 */
.equal("status").and()
.equal("names").and()
/** 一般表达式 */
.equal("physical_address").and()
/** 不为null 或空才输出的表达式 */
.equalIfNonEmpty("name").and()
/** 根据判断条件动态生成的表达式 */
.ifelse("${groupId} != null", "group_id > 100+${id}", "address_type='MAC'")
/** 增加分组查询参数用于生成GROUP BY 表达式*/
.groupBy("name")
/** 增加排序参数用于生成ORDER BY 表达式*/
//.orderBy("version_info DESC")
/** 定义排序参数变量名 */
.orderByVar("order_var")
.build();
/** 输入参数准备 */
DeviceBean bean1 = DeviceBean.builder()
.id(1234)
.name("DEV-01")
.physicalAddress("111111111111")
.addressType("MAC")
.createTime(new Date())
.build();
/** 生成的SQL语句 */
String sql1 = helper
/**
* 根据输入的参数对象提供的SQL查询要求的字段参数定义脚本执行变量,
* SQL查询字段参数可以封装在Java Bean或Map对象，不可为{@code null}
*/
.with(bean1)
/** 为测试目的,添加额外的参数 */
/** 设置status字段,测试字符串数组类型 */
//.defineVariable("names", Arrays.asList("tom","jerry","grace"))
.defineVariable("names", "[\"tom\",\"jerry\",\"grace\"]")
/** 设置status字段,测试字符串数组类型 */
.defineVariable("name", "IBM-dev")
/** 定义排序参数变量 */
.defineVariable("order_var", "version_info")
/** 增加分页查询参数用于生成分页查询语句(MySQL) LIMIT ${row_count} OFFSET ${offset} */
.defineVariable(WhereHelper.VAR_LIMIT_ROW_COUNT, 10)
.defineVariable(WhereHelper.VAR_LIMIT_OFFSET, 1024)
/** 生成SQL语句 */
.where();
// 输出生成的SQL
SimpleLog.log("{}",sql1);
输出生成的SQL语句
[main] (WhereHelperTest.java:105) select * from dc_device WHERE id>0 AND update_time > ‘null’ AND name like ‘hello%’ AND name like ‘IBM-dev%’ AND update_time IS NULL AND status = ‘ENABLE’ AND names IN (‘tom’,‘jerry’,‘grace’) AND physical_address = ‘111111111111’ AND name = ‘IBM-dev’ AND group_id > 100+1234 ORDER BY version_info GROUP BY name LIMIT 10 OFFSET 1024
完整代码参见单元测试代码：WhereHelperTest
WhereHelper 注解
@EnableWhereHelper
gu.sql2java.wherehelper.annotations.EnableWhereHelper是用于服务方法上WhereHelper自动创建SQL语句的注解类。
注解类字段说明如下
| 字段名 | 默认值 | 说明 |
|---|---|---|
| value | “” | SELECT ${column} FROM ${table} 语句 |
| enable | true | 为true启用WhereHelper |
| logicOperator | “AND” | 表达式之间的逻辑操作连接符AND 或 OR |
| debuglog | false | 为true输出调试信息 |
| targetClass | gu.sql2java.BaseRow | 输入参数的目标表对象, 如果只是简单的单表查询,且变量命名都是字段名,就在此定义表记录类型, 以方便WhereHelper准确识别字段类型 |
| varTypeKeys | {} | 变量名列表,与varTypeValues字段一起定义变量类型, 对于非String类型字段和Number类型的字段需要在此定义类型 |
| varTypeValues | {} | 与varTypeKeys字段一起定义变量名的类型,长度必须与varTypeKeys一样, 数组中的每个元素是varTypeKeys对应索引位置的变量名的类型 |
@Equal
gu.sql2java.wherehelper.annotations.Equal WhereHelper 等价表达式注解
| 字段名 | 默认值 | 说明 |
|---|---|---|
| value | “” | 等价比较的字段名 |
| notCheckEmpty | false | 为true 不检查字段参数是否为null或空 |
| not | false | 为true执行不等价比较 |
@Equal用于创建一个等价表达式或不等价，如column_name = $<column_name>
如果column_name为null或空,则表达式为 column_name IS NULL
如果column_name为集合,则为IN表达式 column_name IN (...)
当value定义为name时以下示例根据notCheckEmpty不同创建的不同动态表达式
| value | notCheckEmpty | BeanShell Java表达式 |
|---|---|---|
| name | false | if(isEmpty(${name})) name =${name} |
| name | true | name =${name} |
当name的值和类型不同以及not字段不同。BeanShell 表达式生成不同的最终SQL表达结果
| BeanShell Java表达式 | name | not | SQL 表达 |
|---|---|---|---|
if(isEmpty(${name})) name =${name} | tom, String,Number(非数组，集合) | false | name = ‘tom’ |
if(isEmpty(${name})) name =${name} | null | false | name IS NULL |
if(isEmpty(${name})) name =${name} | [“tom”,“jerry”] (数组，集合) | false | name IN (‘tom’,jerry) |
name = ${name} | tom, String,Number(非数组，集合) | true | name != ‘tom’ |
name = ${name} | null | true | name IS NO NULL |
name = ${name} | [“tom”,“jerry”] (数组，集合) | true | name NOT IN (‘tom’,jerry) |
@Expression
gu.sql2java.wherehelper.annotations.ExpressionWhereHelper 一般表达式注解
用于创建一个普通的SQL表达式，只有一个字段value用于定义表达式，如 @Expression("update_time > '${update_time}'")
相比@Equal,@Expression更加灵活。
@IfElse
gu.sql2java.wherehelper.annotations.IfElseWhereHelper if ... else ... 条件表达式注解,用于更加灵活的动态生成SQL WHERE表达式
| 字段名 | 默认值 | 说明 |
|---|---|---|
| test | “ true ” | 条件判断表达式, |
| doStatement | “” | test表达式执行为true时执行的表达式 |
| elseStatement | “” | test表达式执行为false时执行的表达式 |
@IfElse是更加灵活的动态表达式定义方式。
示例如下：
@IfElse("${groupId} != null", "group_id > 100+${id}", "address_type='MAC'")
如果groupId参数不为null则条件表达式为group_id > 100+${id},否则为address_type='MAC'
@GroupBy
gu.sql2java.wherehelper.annotations.GroupByWhereHelper GROUP BY 表达式注解,用于定义分组查询字段名
| 字段名 | 默认值 | 说明 |
|---|---|---|
| value | {} | 分组查询(GROUP BY) 字段列表 |
| groupByVarname | “group_by_column” | 分组查询(GROUP BY) 字段变量名 |
@OrderBy
gu.sql2java.wherehelper.annotations.OrderByWhereHelper ORDER BY 表达式注解，用于定义排序字段名及排序方式
| 字段名 | 默认值 | 说明 |
|---|---|---|
| value | {} | 排序(ORDER BY) 字段列表,格式 `${column} [ASC |
| orderByVarname | “orderBy” | ORDER BY 字段变量名,用于应用层(前端)定义ORDER BY 字段名的变量名定义 |
变量命名
WhereHelper自动将输入参数的变量命名转为条件表达式引用变量的命名格式(驼峰命名法(camel-case)/蛇形命名法(snake-case)格式)的变量名，以保证执行BeanShell脚本时能正确读取参数值。
例如条件表达式中定义了"create_time > ${create_time}",而输入的参数中没有定义create_time变量而是定义了createTime,则WhereHelper会自动识别将createTime变量名转为create_time
Date 类型
对于java.util.Date的输入参数,在生成SQL表达式时，会根据WhereHelper#timeFormatter(String timeFormatter)方法指定的格式生成日期字符串，如果未指定，则默认的日期格式为：yyyy-MM-dd HH:mm:ss
完整说明参见
https://gitee.com/l0km/sql2java/tree/master/sql2java-pagehelper#wherehelper