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
更正：之前发布的源码中，用于处理OR字串的flag属性是全局共享的，如文档所示：
 |
因此，修改源码中的flag属性为局部变量，以保证一个SQL中多次使用此自定义标签时处理首个OR字串不会出错。
感谢JFinal网站的@prelove 和OSChina的@cankongyun
public class InParasDirective extends Directive {
private int index = -1;
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);
int flag = 0; // IN参数个数
for (int i = 0; i < paraArray.length; i++) {
Object para = paraArray[i];
if (para == null) { // 优先处理参数为Null的情况
flag = this.addPara(sqlPara, para, writer, flag);
} else if (para instanceof Collection<?>) { // 处理参数为Collection的情况
Collection<?> paraCollection = (Collection<?>) para;
for (Object obj : paraCollection) {
flag = this.addPara(sqlPara, obj, writer, flag);
}
} else if (para.getClass().isArray()) { // 处理参数为Array的情况
Object[] objArray = (Object[]) para;
for (Object object : objArray) {
flag = this.addPara(sqlPara, object, writer, flag);
}
} else { // 处理参数为一般类型的情况
flag = this.addPara(sqlPara, para, writer, flag);
}
}
}
private int addPara(SqlPara sqlPara, Object val, Writer writer, int flag) {
write(writer, flag > 0 ? ", ?" : "?");
sqlPara.addPara(val);
return flag + 1;
}3.2 OrParasDirective.java
注意：同上一个自定义标签一样，flag属性改为局部变量。
public class OrParasDirective extends Directive {
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);
int flag = 0; // OR参数个数
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的情况
flag = this.addPara(sqlPara, para, paraName, writer, flag);
} else if (para instanceof Collection<?>) { // 处理参数为Collection的情况
Collection<?> paraCollection = (Collection<?>) para;
for (Object obj : paraCollection) {
flag = this.addPara(sqlPara, obj, paraName, writer, flag);
}
} else if (para.getClass().isArray()) { // 处理参数为Array的情况
Object[] objArray = (Object[]) para;
for (Object object : objArray) {
flag = this.addPara(sqlPara, object, paraName, writer, flag);
}
} else { // 处理参数为一般类型的情况
flag = this.addPara(sqlPara, para, paraName, writer, flag);
}
}
}
private int addPara(SqlPara sqlPara, Object val, String paraName, Writer writer, int flag) {
write(writer, (flag > 0 ? " OR " : ""));
if (val == null) {
write(writer, paraName + " IS NULL");
} else {
write(writer, paraName + " = ?");
sqlPara.addPara(val);
}
return flag + 1;
}
}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语法，完美！
代码只经过了单元测试，没有实际项目使用过，各位在使用时发现问题请一定要告知，谢谢。。。
