Java 处理 sql in 多条件搜索时单引号双引号问题

朱雀 2021-10-08 07:48 625阅读 0赞

当多条件搜索时：用in 怎么转换

比如 当传过来的参数是一个字符串 ：str="a,b,c"；

用sql ............ in(str)/in("a,b,c")显然是不对的 我们最终需要的是 in('a','b','c')；

这就需要将str:"a,b,c"转成'a','b'，'c'

@Override public List < Object > selectJobByToAndT1OfChooseMach(String t0, String t1, String str) throws Exception {
        String sql = "";
        StringBuilder sb = new StringBuilder();
        if (str != "" && str.indexOf(',') != -1) {
            String as[] = str.split(",");
            for (int i = 0; i < as.length; i++) {
                sb.append("'" + as[i] + "'"); //拼接单引号,到数据库后台用in查询.
                if (i != as.length - 1) { //前面的元素后面全拼上",",最后一个元素后不拼
                    sb.append(",");
                }
            }
            sql = "select jobCode,machCode,mouldCode,jobManager,jobAvailability,quality,jobPerformance,startTime,endTime,jobStatus,goodUnits,scheduledProducedUnits from DBJobProduceStatus where  (machCode in(" + str + ") and jobStatus='false' and startTime<='" + t1 + "' and  now() >='" + t0 + "') or (machCode in(" + str + ") and jobStatus='true' and startTime<'" + t1 + "' and endTime>'" + t0 + "')";
            sql = sql.replace(str, sb);
        } else if (str != "" && str.indexOf(',') == -1) {
            sql = "select jobCode,machCode,mouldCode,jobManager,jobAvailability,quality,jobPerformance,startTime,endTime,jobStatus,goodUnits,scheduledProducedUnits from DBJobProduceStatus where  (machCode in('" + str + "') and jobStatus='false' and startTime<='" + t1 + "' and  now() >='" + t0 + "') or (machCode in('" + str + "') and jobStatus='true' and startTime<'" + t1 + "' and endTime>'" + t0 + "')";
        } else {
            sql = "select jobCode,machCode,mouldCode,jobManager,jobAvailability,quality,jobPerformance,startTime,endTime,jobStatus,goodUnits,scheduledProducedUnits from DBJobProduceStatus where  (machCode in('') and jobStatus='false' and startTime<='" + t1 + "' and  now() >='" + t0 + "') or (machCode in('') and jobStatus='true' and startTime<'" + t1 + "' and endTime>'" + t0 + "')";
        }
    
        Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
        List < Object > resultList = session.createSQLQuery(sql).list();
    
        return resultList;
    }

注：传过来的str是一个字符串:例如：str="a,b,c"；

最终效果是：'a','b','c'