mybatis中判断map参数中的list是否为空拼接SQL语句

  在mybatis中传入map参数，map参数中含有list参数，后台会根据map中的list参数是否为空来确定是否拼接SQL

前端代码

export default {
  name: "resourcesNeSpecMapping",
  components: {},
  data() {
    return {
      params: null, //保存传递过来的初始化参数
      nicsSpecMapping: {
        //映射查询的准备数据
        spectype: null
      },
      //表单数据,查询网管网元规格数据的准备数据
      formInLine: {
        factoryName:null,
        emsArray: [], //厂家id对应的专业网管数据集合
        productname: null,
        specIds: [] //存放已经映射过的网元规格数据集合
      },
    };
  }，
  methods: {

       //根据资源系统下网元的厂家id查询映射表中的专业网管集合
      queryEMSByResourceFactoryId(params.factoryId).then(res => {
        console.log(res);
        if (res && res.data && res.data.length !== 0) {
          self.formInLine.emsArray = res.data;
        }
      });

      //直接查询资源系统网元规格与网管网元规格的映射数据
      self.nicsSpecMapping.spectype = "NETWORKELEMENT"; //设置规格类型为网元 规格类型有 NETWORKELEMENT（网元），SHELF（子框），SLOT（槽），CARD（板卡）
      querySpecMappingList(self.nicsSpecMapping).then(res => {
        console.log(res);
        if (res && res.data && res.data.code === 0) {
          // self.formInLine.specIds = res.data.rows;
          //模拟数据
          let item = {};
          item.nicsSpecStr = "1";
          let item1 = {};
          item1.nicsSpecStr = "2";
          let item2 = {};
          item2.nicsSpecStr = "3";
          self.formInLine.specIds = [item,item1,item2];
          //查询网管库下没有做映射关系的网元规格数据
          queryNicsNeForFree(self.nicsNeSpecQueryData).then(res => {
            console.log(res);
            if (res && res.data && res.data.length !== 0) {
              self.netWorkNeSpec.data = res.data; //给网管网元规格列表赋值
            }
          });
        }
      });

后台代码

controller :接收map参数，map参数中含有list参数

    @PostMapping("/queryNicsNeForFree")
    @ResponseBody
    public AjaxResult queryNicsNeForFree(@RequestBody Map map){
        DynamicDataSourceContextHolder.setDB(DataSourceName.SLAVE);
        return nicsNeService.queryNicsNeForFree(map);
    }

service：接收map

    @Autowired
    private NicsNeMapper nicsNeMapper;
    @Override
    public AjaxResult queryNicsNeForFree(Map map) {
        System.out.println(map);
        List<NicsNe> list = nicsNeMapper.queryNicsNeForFree(map);
        return AjaxResult.success(list);
    }

xml层：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
    namespace：必须与对应的接口全类名一致
    id:必须与对应接口的某个对应的方法名一致

 -->

<mapper namespace="com.sccl.modules.instance.nicsne.mapper.NicsNeMapper">
    <!--查询网管库中没有做映射关系的网元规格数据-->
    <select id="queryNicsNeForFree" parameterType="map" resultType="NicsNe">
        select distinct ne.id,ne.PRODUCTNAME,ne.VERSION,ne.EMS
        from v_nics_ne ne where ne.DELETED = 0
        <if test="productname !=null and productname != ''">
            and ne.productname like '%'||#{productname}||'%' 
        </if>
        <if test="emsArray !=null and emsArray.size()>0">
            and ne.ems in
            <foreach item="item" index="index" collection="emsArray"
                     open="(" separator="," close=")">
                #{item.emsName}
            </foreach>
        </if>
        <if test="specIds !=null and specIds.size()>0">
            and ne.id not in
            <foreach item="item" index="index" collection="specIds"
                     open="(" separator="," close=")">
              #{item.nicsSpecStr}
            </foreach>
        </if>
    </select>

</mapper>

后台打印结果

从v_nics_ne视图中查询：打印出的SQL

v_nics_ne 视图：

CREATE OR REPLACE VIEW V_NICS_NE AS
SELECT N.ID,
         N.OPTLOCK,
         N.NAME,
         N.CODE,
         N.EMS,
         N.USERLABEL,
         N.PARSER,
         N.ORIGINAL,
         N.DELETED,
         N.TIMEIDENTIFIER,
         NA.TOPONAME,
         NA.SUBSYSNAME,
         NA.MULTISHELF,
         NA.IS_VIRTUAL,
         NA.REMOTE_NET_ID,
         NA.IS_REMOTE,
         NA.REMOTE_NE_ID,
         NI.VERSION,
         NI.PRODUCTNAME,
         NI.NATIVEEMSNAME,
         NI.OWNER,
         NI.LOCATION,
         NI.COMMUNICATIONSTATE,
         NI.EMSINSYNCSTATE,
         NI.SUPPORTEDRATES,
         NI.ADDITIONALINFO
    FROM T_NICS_NE N
    LEFT JOIN T_NICS_NE_ANALYSIS NA
      ON N.ID = NA.NE_ID
    LEFT JOIN T_NICS_NE_INFO NI
      ON N.ID = NI.NE_ID;

打印结果：

select distinct ne.id,
                ne.PRODUCTNAME,
                ne.VERSION,
                ne.EMS 
                from v_nics_ne ne 
where ne.DELETED = 0 and ne.id not in ( ? , ? , ? ) 

 Parameters: 1(String), 2(String), 3(String)

打印结果1 打印结果2