接口优化：在for循环里反复执行sql的场景

1. 当接口返回的字段数量较多的情况下，没办法通过一个主sql获取所有的字段，需要通过主sql获取部分字段的值，再通过主sql获取的结果集作为查询条件，将其他的字段也查询出来。

2.这时候，如果项目里已经有了包含我们接下来要查询的字段的实体类以及Dao.xml，Dao.xml里写sql，mapper接口里写对应的方法声明，service类里写对应的实现；

1）Dao.xml

<select id="getdSCUserCode" parameterType="java.util.List" resultMap="BaseResultMap">
        select
            mainno, dbname, usercode, id
        from prplbpmmain
        where (dbname,mainno) in
        <foreach collection="registNoAndDBNames" item="item" index="item" open="(" separator="," close=")">
            (#{item.DBName},#{item.registNo})
        </foreach>
        and nodeid = 500
        and valid = '1'
        and cancelstate = '0'
    </select>

 2）Dao接口

List<PrpLbpmMain> getdSCUserCode(@Param("registNoAndDBNames") List<RegistNoAndDBName> registNoAndDBNames);

3）Service

List<PrpLbpmMain> HXprpLbpmMains = prpLbpmMainDao.getdHXUserCode(registNoAndDBNames);
HXprpLbpmMains = new ArrayList<>(new HashSet<>(HXprpLbpmMains));
Map<String, PrpLbpmMain> HXprpLbpmMainsMap = (HashMap<String, PrpLbpmMain>) HXprpLbpmMains.stream().collect(
        Collectors.toMap(
                   PrpLbpmMain -> PrpLbpmMain.getMainno() + PrpLbpmMain.getDBName(),
                                Function.identity(),
                                (key1, key2) -> key1
               )
);



if(HXprpLbpmMainsMap!=null && HXprpLbpmMainsMap.size()>0) {
      PrpLbpmMain HXprpLbpmMain = HXprpLbpmMainsMap.get(bpmId + dbname);
      if (HXprpLbpmMain != null) {
          rswjExcelDataVo.setHXuserCode(HXprpLbpmMain.getUserCode());
        }
}

3. 如果我们获取的字段没有对应的实体类，那么需要可任意考虑建一个实体类，需要获取的几个字段为成员变量，写获取字段的sql，将主sql获取的结果集中的部分数据拼成String，并作为查询条件来使用：

public class DSRWCount {
    private String registNo;
    private String DBName;
    private int count;
}


List<String> registNoList = RSWJExcelDataList1.stream().map(RSWJExcelDataVo::getRegistNo)
.collect(Collectors.toList());
 
registNoList = new ArrayList<>(new HashSet<>(registNoList));

List<String> dbnameList = RSWJExcelDataList1.stream().map(RSWJExcelDataVo::getDBName).
collect(Collectors.toList());
                
dbnameList = new ArrayList<>(new HashSet<>(dbnameList));



StringBuffer stringBuffer3 = new StringBuffer();
                stringBuffer3.append("select registno, dbname, count(*) from prpldeflossmain where   ");
                if (registNoList.size() > 0) {
                    String registStr = registNoList.stream().collect(Collectors.joining("','"));
                    stringBuffer3.append(" registno in ( '").append(registStr).append("' ) ");
                }

                if (dbnameList.size() > 0) {
                    String dbnameStr = dbnameList.stream().collect(Collectors.joining("','"));
                    stringBuffer3.append(" AND dbname in ( '").append(dbnameStr).append("' )  ");
                }

                if (businessIdList.size() > 0) {
                    String businessIdStr = businessIdList.stream().collect(Collectors.joining("','"));
                    stringBuffer3.append(" AND id in ( '").append(businessIdStr).append("' ) group by id, registno, dbname ");
                }

                params5.put("sql", stringBuffer3);

                List<DSRWCount> dsrwCountList = rswjExcelDataMapper.getdsrwcount(params5);
                dsrwCountList = new ArrayList<>(new HashSet<>(dsrwCountList));

                Map<String, DSRWCount> dsrwCountMap = new HashMap<>();
                dsrwCountMap = dsrwCountList.stream().collect(
                        Collectors.toMap(
                                DSRWCount -> DSRWCount.getRegistNo() + DSRWCount.getDBName(),
                                Function.identity(),
                                (key1, key2) -> key1
                        )
                );



if(dswcCountMap!=null && dswcCountMap.size()>0 && dsrwCountMap!=null && dsrwCountMap.size()>0){
                        DSWCCount dswcCount = dswcCountMap.get(registNo + dbname);
                        DSRWCount dsrwCount = dsrwCountMap.get(registNo + dbname);
                        if(dswcCount!=null && dsrwCount!=null) {
                            if (dswcCount.getCount() == dsrwCount.getCount() && dsrwCount.getCount() > 0) {
                                rswjExcelDataVo.setEndlossflag("是");
                            } else {
                                rswjExcelDataVo.setEndlossflag("否");
                            }
                        }
                    }