在sql编写过程中常会遇到in语句,对于in语句在不同的环境下查询结果会不一样,出现似花非花的结果。
先贴一段代码(自己的思路):
//1、在controller中,手动添加参数list:
List<Integer> list = new ArrayList<>();
if (student.getClass() == 1) {
list.add(16);
}
if (student.getClass() == 2) {
list.add(17);
}
if (student.getClass() == 3) {
list.add(18);
}
String strip = StringUtils.strip(list.toString(),"[]");
//2、调用方法查询数据
findStudentPage(Paging<StudentInfo> page, strip);
//3、在service中,拼接sql
public Paging<StudentInfo> findStudentPage(Paging<StudentInfo> page, String strip) throws Exception {
String sql = "SELECT * FROM student_info";
List<String> paramList = new ArrayList<String>();
StringBuffer whereSQL = new StringBuffer();
whereSQL.append(" WHERE STUDENT_AGE IN (?)");
paramList.add(strip);
sql += whereSQL.toString();
return baseDao.findListByPage(sql.toString(), new Mapper(StudentInfo.class), page, paramList.toArray());
}
代码贴完了,工程跑起来,切到sql:
SELECT * FROM student_info WHERE STUDENT_AGE IN (16,17,18);
将sql拿到数据库,查询数据有4条,可是代码跑完,代码只查出了2条,而且都是对应GAME_VERSION = 16的,很纳闷,调了很多遍,结果没变。请教大神,得到如下回答(继续贴代码):
//1、在controller中,手动添加参数list:
List<Integer> list = new ArrayList<>();
if (student.getClass() == 1) {
list.add(16);
}
if (student.getClass() == 2) {
list.add(17);
}
if (student.getClass() == 3) {
list.add(18);
}
String strip = StringUtils.strip(list.toString(),"[]");
//2、调用方法查询数据
findStudentPage(Paging<StudentInfo> page, strip);
//3、在service中,拼接sql
public Paging<StudentInfo> findStudentPage(Paging<StudentInfo> page, String strip) throws Exception {
String sql = "SELECT * FROM student_info";
List<String> paramList = new ArrayList<String>();
StringBuffer whereSQL = new StringBuffer();
//strip不可能为空,故没做判断
String studentClass= SqlUtil.getParamsInSQL(strip, paramList);
whereSQL.append(" WHERE STUDENT_AGE IN ("+ studentClass+ ")");
sql += whereSQL.toString();
return baseDao.findListByPage(sql.toString(), new Mapper(StudentInfo.class), page, paramList.toArray());
}
同样切到sql:
SELECT * FROM student_info WHERE STUDENT_AGE IN (16,17,18);
但是,结果对了,数据库里是4条,代码查询结果也是4条。
最终解释:
在大神的方法中处理in语句用了一个方法getParamsInSQL,这个方法如下(可以直接CV作为工具方法):
/**
* 将in条件中的值转化成?形式
* 例:a,b,c,d 改为 ?,?,?,? returnparam = [a,b,c,d]
* @param params 参数字符串
* @param returnparam 返回参数列表
*/
public static String getParamsInSQL(String params, List<String> returnparam) {
StringBuffer insql = new StringBuffer();
if (params != null && params.length() > 0 && params.split(",").length > 0) {
for (String param : params.split(",")) {
if (insql.length() > 0) {
insql.append(",");
}
insql.append("?");
returnparam.add(param);
}
}
return insql.toString();
}
通过这个方法,将手动拼接的参数 strip 最终转换成了 "16,17,18",这种转换是,边占位边添值,即:
for(...) {
insql.append("?");
returnparam.add(param);
}
所以,在in语句中的值是多个,而如果用第一种方式直接将参数 strip通过一个占位加到sql中,数据库会解释为一个整体,
SELECT * FROM student_info WHERE STUDENT_AGE IN ("16,17,18");
而不是多个,所以只会出现16对应的数据。
在这里,尤其要注意的一点是,第一种方式有极大的sql注入的风险,在这里是自己手动拼接的参数,风险较低,如果参数是直接由页面传递的,sql注入必然存在。
所以建议大家以后用第二种方式安全准确。