JFinal的 in 参数在SQL模板中解析的完美解决方案

1. 分析

在JFinal中,SQL模板目前对in参数( xxx in (?, ?, ?.... ?) )的解决方案大多如下:

select * from xxx where id in(
#for(x : ids)
#if(!for.first) , #end #para(x)
#end
)

这种方式属于很无奈的解决方案 ,而且当参数个数较少时,还应该能拼写成(id=? OR id=? OR ...... OR id=?)的方式。

那么,有没有更好的解决方案呢?答案是一定的!

2. 解决方案

2.1 in参数

在sql模板中,自定义一个标签来自动生成 ? 占位符,并自动生成与占位符个数相匹配的参数。

SQL模板:WHERE t1.id IN (#inparas(list))

参数:list = [1, 2, 3]

解析结果:

WHERE t1.id IN (?, ?, ?)

#inparas就是处理in参数的自定义标签。

2.1 or参数

SQL模板:WHERE #orparas("t1.id", list)

参数:list = [1, 2, 3]

解析结果:

WHERE t1.id=? OR t1.id=? OR t1.id=?

"t1.id"为字段,#orparas就是处理or参数的自定义标签。

3. 自定义标签

3.1 InParasDirective.java

public class InParasDirective extends Directive {

	private int index = -1;

	private boolean flag = false;

	public void setExprList(ExprList exprList) {
		if (exprList.length() == 0) {
			throw new ParseException("The parameter of #inparas directive can not be blank", location);
		}

		if (exprList.length() == 1) {
			Expr expr = exprList.getExpr(0);
			if (expr instanceof Const && ((Const) expr).isInt()) {
				index = ((Const) expr).getInt();
				if (index < 0) {
					throw new ParseException("The index of inparas array must greater than -1", location);
				}
			}
		}

		this.exprList = exprList;
	}

	@Override
	public void exec(Env env, Scope scope, Writer writer) {
		SqlPara sqlPara = (SqlPara) scope.get(SqlKit.SQL_PARA_KEY);
		if (sqlPara == null) {
			throw new TemplateException("#inparas directive invoked by getSqlPara(...) method only", location);
		}

		Object[] paraArray = this.exprList.evalExprList(scope);

		for (int i = 0; i < paraArray.length; i++) {
			Object para = paraArray[i];

			if (para == null) { // 优先处理参数为Null的情况
				this.addPara(sqlPara, para, writer);
			} else if (para instanceof Collection<?>) { // 处理参数为Collection的情况
				Collection<?> paraCollection = (Collection<?>) para;
				for (Object obj : paraCollection) {
					this.addPara(sqlPara, obj, writer);
				}
			} else if (para.getClass().isArray()) { // 处理参数为Array的情况
				Object[] objArray = (Object[]) para;
				for (Object object : objArray) {
					this.addPara(sqlPara, object, writer);
				}
			} else { // 处理参数为一般类型的情况
				this.addPara(sqlPara, para, writer);
				;
			}

		}
	}

	private void addPara(SqlPara sqlPara, Object val, Writer writer) {
		write(writer, flag ? ", ?" : "?");
		sqlPara.addPara(val);
		flag = true;
	}

}

3.2 OrParasDirective.java

public class OrParasDirective extends Directive {

	private boolean flag = false;

	public void setExprList(ExprList exprList) {
		if (exprList.length() == 0) {
			throw new ParseException("The parameter of #orparas directive can not be blank", location);
		}

		this.exprList = exprList;
	}

	@Override
	public void exec(Env env, Scope scope, Writer writer) {
		SqlPara sqlPara = (SqlPara) scope.get(SqlKit.SQL_PARA_KEY);
		if (sqlPara == null) {
			throw new TemplateException("#orparas directive invoked by getSqlPara(...) method only", location);
		}

		Object[] paraArray = this.exprList.evalExprList(scope);

		String paraName = null;
		for (int i = 0; i < paraArray.length; i++) {
			Object para = paraArray[i];

			if (i == 0) {
				if (!(para instanceof String) || StrKit.isBlank((String) para)) {
					throw new ParseException("The first parameter must be a string", location);
				}
				paraName = (String) para;
				continue;
			}

			if (para == null) { // 优先处理参数为Null的情况
				this.addPara(sqlPara, para, paraName, writer);
			} else if (para instanceof Collection<?>) { // 处理参数为Collection的情况
				Collection<?> paraCollection = (Collection<?>) para;
				for (Object obj : paraCollection) {
					this.addPara(sqlPara, obj, paraName, writer);
				}
			} else if (para.getClass().isArray()) { // 处理参数为Array的情况
				Object[] objArray = (Object[]) para;
				for (Object object : objArray) {
					this.addPara(sqlPara, object, paraName, writer);
				}
			} else { // 处理参数为一般类型的情况
				this.addPara(sqlPara, para, paraName, writer);
				;
			}

		}
	}

	private void addPara(SqlPara sqlPara, Object val, String paraName, Writer writer) {
		write(writer, (flag ? " OR " : ""));
		flag = true;
		if (val == null) {
			write(writer, paraName + " IS NULL");
		} else {
			write(writer, paraName + " = ?");
			sqlPara.addPara(val);
		}
	}

}

4. 测试

自定义的标签在使用前需要先注册进JFinal的SQL模板引擎(注意:不是HTML的模板引擎):

ActiveRecordPlugin arp = new ActiveRecordPlugin( ... );

arp.getEngine().addDirective("inparas", InParasDirective.class);

arp.getEngine().addDirective("orparas", OrParasDirective.class);

4.1 SQL模板

标签中的参数可以来自于注入参数、常量、表达式,这些参数均能混合处理,所以,对这些参数做全面的测试就很重要了,请各位仔细观察下面的模板。

4.1.1 in参数模板

#sql("test1")
SELECT * FROM table1 t1 WHERE 
t1.id IN (#inparas(array))
#end

#sql("test2")
SELECT * FROM table1 t2 WHERE 
t2.id IN (#inparas(list))
#end

#sql("test3")
SELECT * FROM table1 t3 WHERE 
t3.id IN (#inparas(1, 2 + "", 3L, 2 + 2, "5"))
#end

#sql("test4")
SELECT * FROM table1 t4 WHERE 
t4.id IN (#inparas(1, 2 + "", [3L, 2 + 2, "5"]))
#end

#sql("test5")
SELECT * FROM table1 t5 WHERE 
t5.id IN (#inparas(1, 2 + "", [3L, 2 + 2, "5"], array, list, val))
#end

4.2.2 or参数模板

注意,模板中的部分参数特意使用null值:

#sql("test1")
SELECT * FROM table1 t1 WHERE 
t1.name = "test" AND (#orparas("t1.id", array))
#end

#sql("test2")
SELECT * FROM table1 t2 WHERE 
t2.name = "test" AND (#orparas("t2.id", list))
#end

#sql("test3")
SELECT * FROM table1 t3 WHERE 
t3.name = "test" AND (#orparas("t3.id", 1, 2 + "", 3L, 2 + 2, null))
#end

#sql("test4")
SELECT * FROM table1 t4 WHERE 
t4.name = "test" AND (#orparas("t4.id", null, 2 + "", [3L, 2 + 2, "5"]))
#end

#sql("test5")
SELECT * FROM table1 t5 WHERE 
t5.name = "test" AND (#orparas("t5.id", 1, 2 + "", [null, 2 + 2, "5"], array, list, val))
#end

4.3 注入参数

准备参数:

List<Object> list = new ArrayList<>();
list.add("listVal1");
list.add("listVal2");
list.add("listVal3");

Object[] array = new Object[] { "arrayVal1", "arrayVal2", "arrayVal13" };

// List参数和Array参数再加上个普通参数组合成Kv
Kv.by("array", array).set("list", list).set("val", "valPara");

测试代码:

	private void testSql(String sqlKey) {
		//System.out.println("SQL模板:" + Db.getSql(sqlKey));
		SqlPara sqlPara = Db.getSqlPara(sqlKey, Kv.by("array", array).set("list", list).set("val", "valPara"));
		System.out.println("解析SQL:" + sqlPara.getSql());
		this.printPara(sqlPara.getPara());
	}

	private void printPara(Object[] paras) {
		System.out.print("解析参数:");
		if (paras == null) {
			System.out.println("null");
			return;
		}
		for (Object p : paras) {
			System.out.print(p);
			System.out.print(", ");
		}
		System.out.println();
		System.out.println("------------------------------------------------------------------");
		System.out.println();
	}

4.4 测试结果

4.4.1 in参数测试结果

解析SQL:SELECT * FROM table1 t1 WHERE t1.id IN (?, ?, ?)

解析参数:arrayVal1, arrayVal2, arrayVal13
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t2 WHERE t2.id IN (?, ?, ?)

解析参数:listVal1, listVal2, listVal3
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t3 WHERE t3.id IN (?, ?, ?, ?, ?)

解析参数:1, 2, 3, 4, 5
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t4 WHERE t4.id IN (?, ?, ?, ?, ?)

解析参数:1, 2, 3, 4, 5
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t5 WHERE t5.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

解析参数:1, 2, 3, 4, 5, arrayVal1, arrayVal2, arrayVal13, listVal1, listVal2, listVal3, valPara
------------------------------------------------------------------

4.4.2 or参数测试结果

解析SQL:SELECT * FROM table1 t1 WHERE t1.name = "test" AND (t1.id = ? OR t1.id = ? OR t1.id = ?)

解析参数:arrayVal1, arrayVal2, arrayVal13
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t2 WHERE t2.name = "test" AND (t2.id = ? OR t2.id = ? OR t2.id = ?)

解析参数:listVal1, listVal2, listVal3
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t3 WHERE t3.name = "test" AND (t3.id = ? OR t3.id = ? OR t3.id = ? OR t3.id = ? OR t3.id IS NULL)

解析参数:1, 2, 3, 4
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t4 WHERE t4.name = "test" AND (t4.id IS NULL OR t4.id = ? OR t4.id = ? OR t4.id = ? OR t4.id = ?)

解析参数:2, 3, 4, 5
------------------------------------------------------------------

解析SQL:SELECT * FROM table1 t5 WHERE t5.name = "test" AND (t5.id = ? OR t5.id = ? OR t5.id IS NULL OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ? OR t5.id = ?)

解析参数:1, 2, 4, 5, arrayVal1, arrayVal2, arrayVal13, listVal1, listVal2, listVal3, valPara
------------------------------------------------------------------

可以看到,参数为空时被替换成IS NULL语法,完美!

 

代码只经过了单元测试,没有实际项目使用过,各位在使用时发现问题请一定要告知,谢谢。。。


评论区

JFinal

2020-05-23 18:12

源代码贴出来更好

糊搞

2020-05-23 18:17

@JFinal 那我重写一贴吧

JFinal

2020-05-23 18:18

@糊搞 不用重写,贴子有修改功能

糊搞

2020-05-23 18:34

@JFinal 修改好了

JFinal

2020-05-23 18:48

@糊搞 谢谢分享,赞

杜福忠

2020-05-23 21:41

chcode

2020-05-24 10:39

其实我觉第一种写法也没什么,通俗易懂

chcode

2020-05-24 10:40

#for(stepId : _PARA_ARRAY_[0])
#para(stepId)#(for.last ? "": ",")
#end
)
可以优化下不需要if指令

prelove

2020-05-24 17:19

exec(Env env, Scope scope, Writer writer)最后要加一行this.flag = false;让标志归位,不然嘿嘿。。。

久伴轻尘

2020-05-25 11:14

感谢分享,收藏一波

zlsj80

2020-05-25 15:34

The field SqlKit.SQL_PARA_KEY is not visible

糊搞

2020-05-25 15:56

@zlsj80 贴上模板代码和完整报错信息吧

zlsj80

2020-05-25 16:10

@糊搞 SqlKit调用的是jfinal里自带的吗,如果是则SQL_PARA_KEY常量没法访问到,是要修改源代码常量的修饰符么,4.8版jFinal

糊搞

2020-05-25 16:11

@zlsj80 com.jfinal.plugin.activerecord.sql.SqlKit

zlsj80

2020-05-25 16:18

@糊搞 4.8版中这个类里这个常量非public修饰,你可以看下jFinal源码

糊搞

2020-05-25 17:46

@zlsj80
package com.jfinal.plugin.activerecord.sql;
public class OrParasDirective { ... ... }

看懂了没?

xukua

2020-05-26 09:35

return dao.find("select * from shopAttribute where shopAttributeId in ("+ids+")");
这样不香吗?那么复杂做什么

北流家园网

2020-05-26 09:38

不错,顶一下

糊搞

2020-05-26 09:45

@xukua 有点经验的都知道这种拼装方式有SQL注入安全风险,最保险的还是占位符参数

xukua

2020-05-28 10:37

@糊搞 sql 注入 不应该在获取参数是就统一做判断的吗

糊搞

2020-05-28 11:03

@xukua 去百度一下SQL注入风险吧,不是说你获取参数做了验证之后就没有风险的,有经验的人随便就能抓漏洞搞定你的数据库。。。