mybatis-sql优化-参数遍历

来源：互联网发布：震曰故人知君翻译编辑：程序博客网时间：2024/05/29 13:28

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

阅读全文

0 0