Mybatis实现(指标状态)的动态sql

Mybatis实现(指标/状态)的动态sql

零、前言

97后小同事跟我抱怨，指标老是变化，今天指标有正常、低风险、高风险、明天指标有正常、低风险、中风险、高风险；后天指标的参数变化，导致一个功能老是需要修改sql.甲方脑子都有*。一切恐惧皆因火力不足。

我们通常会遇到指标会调整的问题，如指标<=0.1 为正常；0.1<指标<=0.2为低危；0.2<指标<=0.4为中危；0.4<指标为高危；后面根据业务调整指标进行调整。如果不想修改sql也不需要重启服务的情况下快速修改指标呢。则需要使用动态sql.使用mybatis的foreach配合case-when-then-else-end就可以实现，foreach不仅仅可以用来做批量插入，也可以用来做动态sql哦！

一、数据&规则

1.数据

2.规则

0~2(包括2):为正常，2~4(包括4)为低危；4~6(包括6)为中危；大于6为高危

二、代码实现

1.Java

@GetMapping("test")
@ResponseBody
public List<Map> test(){
    //todo 从数据库或缓存中读取指标数据
  
  	//模拟
    //需要保证指标的顺序所以需要使用LinkedHashMap，保证顺序也可以减少sql的负责度。
    Map<String,Integer> map = new LinkedHashMap<>();
    map.put("正常",2);
    map.put("低风险",4);
    map.put("中风险",6);
    //最后的else使用null作为判断
    map.put("高风险",null);
    
  return otherMapper.BDFX2(map);
}

//dao层
public interface OtherMapper {
    public List<Map> BDFX2(@Param("map") Map<String,Integer> map);
}

2.mapper.xml

<select id="BDFX" resultType="java.util.Map">
        SELECT
        num,
        <foreach collection="map.entrySet()" item="value" index="index" separator=" " open="CASE" close="end type">
            <choose>
                <when test="value != null">
                    when num &lt;= #{value} then #{index}
                </when>
                <otherwise>
                    else #{index}
                </otherwise>
            </choose>
        </foreach>
        FROM
        (
        SELECT
        1 num UNION ALL
        SELECT
        2 num UNION ALL
        SELECT
        3 num UNION ALL
        SELECT
        4 num UNION ALL
        SELECT
        5 num UNION ALL
        SELECT
        6 num UNION ALL
        SELECT
        7 num
        ) a
</select>

三、效果

四、原理

<!-- 最终sql为：case when num<=2 then '正常' when num<=4 then '低风险' when num<=6 then '中风险' else '高风险' end type ;type 为别名可以修改为其他的 -->
<!-- separator为每次循环后追加的内容(最后一次循环不追加) open为开头 close为结尾 -->
<!-- 如果是<或<=则为从小到大 如果是>=或>则从大到小，null放最后-->
<!-- map.entrySet()获取entry[(k,v)...(k,v)]的集合，collection为传过来的map;其中item为map的v;index为map的k;-->
<foreach collection="map.entrySet()" item="value" index="index" separator=" " open="CASE" close="end type">
     <choose>
       	 <!-- if -->
         <when test="value != null">
             when num &lt;= #{value} then #{index}
         </when>
         <!-- else -->
         <otherwise>
             else #{index}
         </otherwise>
    </choose>
</foreach>

五、批量插入

顾名思义批量插入，结合insert into tablename () values()使用

1.dao层代码

public int insertDemos(@Param("list")List<Demo> demo);

2.mapper.xml

<insert id="insertDemos">
  insert into tablename(c1,c2,c3,c4) values
  <foreach collection="list" item="demo" index="index" separator="," >
  	(#{demo.c1},#{demo.c2},#{demo.c3},#{demo.c4})
  </foreach> 
</insert>

六、动态插入

一个对象在创建时有可能只需要部分的属性或者业务确定只需要某些属性我们可以使用动态的方式进行插入

1.dao层代码

public int insertDemos(@Param("map")Map<Demo> demo);

2.mapper.xml

<insert id="insertDemos">
  insert into tablename 
   <!--map的所有的key-->
   <foreach collection="map.keys" item="k" iopen="(" separator="," close=")" >
  	#{k}
  </foreach>
  values
  <!--map的所有的value和key的对应的顺序一致-->
  <foreach collection="map.value" item="v" iopen="(" separator="," close=")" >
  	#{v}
  </foreach>
</insert>

七、最后

Mybatis的foreach可以处理List,Map,数组。只要是能有固定形式的都可以使用foreach来实现动态SQL。