mybatis-sql优化-参数遍历

桃扇骨 2022-06-06 11:38 176阅读 0赞

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>
           
         )