-- 数据库命字段名不规范,历史遗留物。迟点再改。
SELECT
*
FROM
stock
WHERE
(stock.changjia LIKE '%冷板%' OR
stock.pinzhong LIKE '%冷板%' OR
stock.guige LIKE '%冷板%' OR
stock.caizhi LIKE '%冷板%' OR
stock.bianhao LIKE '%冷板%' OR
stock.zhongliang LIKE '%冷板%' OR
stock.cangku LIKE '%冷板%' OR
stock.beizhu LIKE '%冷板%')
AND
(stock.changjia LIKE '%DC03%' OR
stock.pinzhong LIKE '%DC03%' OR
stock.guige LIKE '%DC03%' OR
stock.caizhi LIKE '%DC03%' OR
stock.bianhao LIKE '%DC03%' OR
stock.zhongliang LIKE '%DC03%' OR
stock.cangku LIKE '%DC03%' OR
stock.beizhu LIKE '%DC03%')
AND
(stock.changjia LIKE '%Q3498%' OR
stock.pinzhong LIKE '%Q3498%' OR
stock.guige LIKE '%Q3498%' OR
stock.caizhi LIKE '%Q3498%' OR
stock.bianhao LIKE '%Q3498%' OR
stock.zhongliang LIKE '%Q3498%' OR
stock.cangku LIKE '%Q3498%' OR
stock.beizhu LIKE '%Q3498%')
我有一个关键词的参数:用户传进来然后用空格隔开的list,里面放的就需要查询的关键词。
for list{
每次循环,需要组装这个sql。
}
我想学习一下SqlPara ,如果不用addSqlTemplate管理的情况下。直接在程序中怎么用什么来做占位符呢?如果直接用String + 关键词拼接的话,会有注入漏洞吧?
解决办法:public void searchSteelByFilter() {
String query = getPara("query");
Ret ret = new Ret();
if (query != null && !"".equals(query)) {
List queryLists = Splitter.on(" ").trimResults().splitToList(query.replaceAll(" +", " ").trim());
String sql = "SELECT * FROM stock WHERE";
SqlPara para = new SqlPara();
int i = 1;
for (String string : queryLists) {
sql = sql + "(stock.changjia LIKE concat('%', ?, '%') OR stock.pinzhong LIKE concat('%', ?, '%') OR stock.guige LIKE concat('%', ?, '%') OR stock.caizhi LIKE concat('%', ?, '%') OR stock.bianhao LIKE concat('%', ?, '%') OR stock.zhongliang LIKE concat('%', ?, '%') OR stock.cangku LIKE concat('%', ?, '%') OR stock.beizhu LIKE concat('%', ?, '%'))";
for (int j = 0; j
para.addPara(string);
}
//判断是否最后一个,最后一个就不需要凭借AND了
if (i != queryLists.size()) {
sql = sql + "AND";
}
i = i + 1;
}
List stockList = Stock.dao.find(sql,para.getPara());
renderJson(stockList);
}else {
ret.put("erro", "请输入内容!");
renderJson(ret);
}
}