有一个复杂的SQL查询，如下：
select * from TEST_PROJECT project where EXISTS (select pgroup.projectid from TEST_PROJECT_GROUP pgroup where pgroup.projectid=project.id and groupid in (@groupids)) $condition
上面语句中的@groupids应该如何传参呢？
直接传个List，最终的SQL语句会是groupid in ('["id1","id2"]')。
如果用for循环拼接出来id1','id2这种，应该是可以，但是觉的不太安全。
是否有更好的办法呢？谢谢。
测试代码
@Test
public void test_sql_in() {
Sql sql = Sqls.create("select * from t_user where nm in (@ids)");
sql.setParam("ids", new String[]{"wendal", "zozoh", "pangwu86"});
System.out.println(sql);
}
输出
select * from t_user where nm in ('wendal','zozoh','pangwu86')
已经是最新版了
<!-- nutz -->
<dependency>
<groupId>org.nutz</groupId>
<artifactId>nutz</artifactId>
<version>1.r.54</version>
</dependency>
代码：
public List<CesProjectInfo> getProjectByGroup(List<String> groupIds, Condition condition){
groupIds.add("1111");
Sql sql = this.dao.sqls().create("cesProjectDao.getProjectByGroup");
sql.setParam("groupids", groupIds);
sql.setCondition(condition);
return this.search(sql);
}
在debug里面的语句：
select * from CES_CASEMS_PROJECT project where EXISTS (select pgroup.projectid from CES_CASEMS_PROJECT_GROUP pgroup where pgroup.projectid=project.id and groupid in ('["group_test", "1111"]'))
@wendal in 传入set.toArray()，结果仍然是类似groupid in ('["group_test", "1111"]')
我现在使用版本 1.b.52，有关系吗？
老点的版本,可以用变量方式塞进去
@Test
public void test_sql_in2() {
Sql sql = Sqls.create("select * from t_user where nm in ($ids)");
StringBuilder sb = new StringBuilder();
String[] ids = new String[]{"wendal", "zozoh"};
for (String id : ids) {
sb.append("\"").append(Sqls.escapeFieldValue(id)).append("\"").append(",");
}
sql.setVar("ids", sb.substring(0, sb.length()-1));
System.out.println(sql);
System.out.println(sql.toPreparedStatement());
}
@wendal 我还是报错
ON A.Emp_Id = B.Emp_Id WHERE Org_Code IN (@OrgCodes)
sql.setParam("OrgCodes", divisionCodes);
然后打印出来的sql是
ON A.Emp_Id = B.Emp_Id WHERE Org_Code IN ('["811097E11D514BEC95AE3D66FCD5C18E", "