概述
使用JdbcTemplate
,采用占位符写法,防sql注入攻击,带有in
条件的处理
拼接方法
/**
* <pre>
* 获取 PreparedStatement 需要的in sql 子句
* 如:
* 空集合:('')
* [1, 2, 3] (?,?,?)
* </pre>
* @param params
* @return
*/
public static String getInClauseStr(Collection<String> params) {
StringBuilder inClause = new StringBuilder("(");
boolean last = false;
if (params == null || params.isEmpty()) {
inClause.append("'')");
return inClause.toString();
}
else {
for (int i = 0; i < params.size(); i++) {
inClause.append("?");
if (i == params.size() - 1) {
last = true;
}
// 最后一个占位符不需要逗号
if (!last) {
inClause.append(",");
}
}
inClause.append(")");
return inClause.toString();
}
}
查询语句:
List<String> list = Arrays.asList("1", "2", "3");
String query = "select * from User where user_id in " + getInClauseStr(list);
Object[] params = new Object[0];
if (list != null && !list.isEmpty()) {
params = list.toArray();
}
List<Map<String, Object>> list = jdbcTemplate.queryForList(query, params);