mybatis-sql优化-参数遍历

Mybatis sql优化

场景一：遇到sql根据变量参数求交集的场景

老的方式：

<select id="queryCiPointByTagIds" parameterType="QueryLabels" resultMap="ciPointInfo">
      select * from T_4G_WB_CI_INFO a where 1 = 1
      and a.ci_id in(
           select ci_id from T_4G_DIC_LABEL_INFO t where 1=1
           <if test="label1!=null">
                 INTERSECT
                 select ci_id from T_4G_DIC_LABEL_INFO t  where label_id = 'lable_1'          
           </if>
           <if test="label2!=null">
                 INTERSECT
                 select ci_id from T_4G_DIC_LABEL_INFO t  where label_id = 'lable_2'
           </if>
           )    
 </select>

实体bean:

public class QueryLabels {
      private String areaId;
      private String queryTime;//查询时间（yyyymmdd）
      private String label1;
      private String label2;
      private String label3;
      private Map<String, String> labelsMap = new HashMap<String, String>();

1: 新的改造方式：通过map遍历：

<select id="ardoTest" parameterType="QueryLabels" resultMap="ciPointInfo">
      select * from T_4G_WB_CI_INFO where 1=1 and ci_id in(
           select ci_id from T_4G_DIC_LABEL_INFO t where 1=1
            <foreach collection="labelsMap" index="key" item="value" separator="">
                 INTERSECT
                 select ci_id from T_4G_DIC_LABEL_INFO t where label_id = '${key}'
            </foreach>
      )
 </select>

2: 新的改造方式：通过list遍历：

and a.ci_id in(
         select distinct CI_ID from T_4G_DIC_LABEL_CONFIG where label_id in
         <foreach item="aList" index="index" collection="aList"
              open="(" separator="," close=")"> #{aList}
         </foreach>
       
     )