关于sql语句中IN（？）中参数不确定个数的解决方案

UNIQUE1627098817

已于 2022-12-13 21:50:37 修改

阅读量577

点赞数 1

分类专栏： SQL 文章标签： sql mysql

于 2022-12-13 21:47:59 首次发布

本文链接：https://blog.csdn.net/UNIQUE1627098817/article/details/128308487

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

关于sql语句中IN（？）中参数不确定个数的解决方案

核心：将需要的参数拼接成字符串丢给IN函数

@Override
    public  List<FlowInstDto> getBacklogTaskByPage(PageObj pageObj, FlowDto flowDto) {
        String teamNameListStr1="";
        AuthInfo authInfo= SessionContent.getAuthInfoLocal();
        List<RoleDto> roleDtoList= authInfo.getRoles();
        boolean Monitor=false;
        boolean dutymanager=false;
        boolean staffInfo=false;
        for (RoleDto roleDto:
                roleDtoList) {
            if("Monitor".equals(roleDto.getRoleCode())){
                Monitor=true;
            }else if("dutymanager".equals(roleDto.getRoleCode())){
                dutymanager=true;
            }else if("staffInfo".equals(roleDto.getRoleCode())){
                staffInfo=true;
            }
        }
        Long staffId= authInfo.getStaffId();
        String dutyStaffId = Long.toString(staffId);
//根据值班经理id查出所属区域班组
        List<TeamConfigureDto> teamNameList=teamConfigureDao.getClassMsg(dutyStaffId);
        ArrayList<String> teamNameList1=new ArrayList<>();
        if (dutymanager){
            for (int i = 0; i <teamNameList.size() ; i++) {
                teamNameList1.add(teamNameList.get(i).getClassName());
            }
        }
        ArrayList<Object> params = new ArrayList<Object>();
        params.add(SessionContent.getAuthInfoLocal().getStaffId());
        if (teamNameList1.size()!=0){
            String teamNameListStr="";
            for (int i = 0; i < teamNameList1.size(); i++) {
                teamNameListStr+="'"+teamNameList1.get(i)+"'"+",";
            }
            teamNameListStr1=teamNameListStr.substring(0,teamNameListStr.length()-1);
//            params.add(teamNameListStr1);
        }else{
            //查出所有班组名字
            List<String> allTeamNames = platformClassScheduleDao.queryAllTeamName();
            String teamNameListStr="";
            for (int i = 0; i <allTeamNames.size() ; i++) {
                teamNameListStr+="'"+allTeamNames.get(i)+"'"+",";
            }
            teamNameListStr1=teamNameListStr.substring(0,teamNameListStr.length()-1);
        }
        String sql= "select otfi.FLOW_INST_ID,otfi.FLOW_NO,otfi.FLOW_NODE_INST_ID,otni.NODE_ID,otni.NODE_NO,otni.NODE_NAME,otfi.FLOW_NAME,otfi.CREATE_STAFF,ss.OPERATOR_NAME,otfi.END_TIME,otfi.CREATE_TIME,ot.TEAM_NAME  from  oms_tbl_flow_inst otfi\n" +
                "    left join (select distinct n.FLOW_INST_ID from  oms_tbl_node_inst n where  n.NODE_DEAL_PER=? ) mynodeInst\n" +
                "        on otfi.FLOW_INST_ID = mynodeInst.FLOW_INST_ID\n" +
                "    left join oms_tbl_node_inst otni on otfi.FLOW_NODE_INST_ID = otni.FLOW_NODE_INST_ID\n" +
                "    left join sys_staff ss on ss.STAFF_ID=otfi.CREATE_STAFF \n" +
                "    left join `oms_team_staff_rel` otsr on ss.STAFF_ID=otsr.STAFF_ID\n"+
                "    left join `oms_team` ot on ot.TEAM_ID=otsr.TEAM_ID\n"+
                "    where otfi.FLOW_INST_ID>0 AND ot.TEAM_NAME IN("+teamNameListStr1+")\n" +
                "    ORDER BY otfi.CREATE_TIME DESC";
        if (CommonUtil.hasValue(flowDto.getFlowNo())) {
            sql += "  and otfi.FLOW_NO = ? ";
            params.add(flowDto.getFlowNo());
        }
        return this.queryByPage(sql, params.toArray(), new RowMapper<FlowInstDto>() {
            @Override
            public FlowInstDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                FlowInstDto dto = new FlowInstDto();
                dto.setFlowInstId(rs.getLong("FLOW_INST_ID"));
                dto.setFlowNo(rs.getString("FLOW_NO"));
                dto.setFlowNodeInstId(rs.getLong("FLOW_NODE_INST_ID"));
                dto.setNodeId(rs.getLong("NODE_ID"));
                dto.setNodeNo(rs.getString("NODE_NO"));
                dto.setNodeName(rs.getString("NODE_NAME"));
                dto.setFlowName(rs.getString("FLOW_NAME"));
                dto.setCreateStaff(rs.getLong("CREATE_STAFF"));
                dto.setCreateStaffName(rs.getString("OPERATOR_NAME"));
                dto.setEndTime(rs.getTimestamp("END_TIME"));
                dto.setCreateTime(rs.getTimestamp("CREATE_TIME"));
                return dto;
            }
        }, pageObj);
    }