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