mybatis foreach

1.数组

public int deleteTIdcCqcByIds(Long[] ids);

<update id="deleteTIdcCqcByIds" parameterType="String">
    update biz_idc_cqc set del_flag = '1' where id in
    <foreach item="id" collection="array" open="(" separator="," close=")">
        #{id}
    </foreach>
</update>

2.查询的接口，参数为对象，其中一个属性lableIds是List ,并的关系

public List<BizMaintainFile> selectBizMaintainFileList(BizMaintainFile bizMaintainFile);

   <select id="selectBizMaintainFileList" parameterType="BizMaintainFile" resultMap="BizMaintainFileResult">
        <include refid="selectBizMaintainFileVo"/>
        <where>
            del_flag = '0'
            <if test="fileName != null  and fileName != ''"> and file_name like concat('%', #{fileName}, '%')</if>
            <if test="provinceId != null  and provinceId != ''"> and province_id = #{provinceId}</if>
            <if test="lableIds != null  and lableIds != ''">
                <foreach item="id" collection="lableIds" index="index" separator=" ">
                   and find_in_set(#{id},lable_id)>0
                </foreach>
            </if>
            <if test="fileSource != null  and fileSource != ''"> and file_source = #{fileSource}</if>
            <if test="fileUrl != null  and fileUrl != ''"> and file_url = #{fileUrl}</if>
        </where>
        order by create_time
    </select>

3.传过来一个对象，对象的一个字段属性为list，ids为list<String>,provinceIds为List<String>

  <select id="selectAllTIdcCqcList" resultType="com.idc.business.domain.BizIdcCqc">
        SELECT
            ts.id,
            td.id AS data_center_id,
            IFNULL( certification_status, 3 ) certification_status ,
            td.city_id,
            td.NAME,
            td.province_id,
            td.CODE,
            ts.data_center_level,
            ts.cqc_grade,
            ts.is_cqc_certification_data,
            ts.specification_version,
            ts.is_carry_certification,
            ts.principal,
            ts.principal_phone,
            ts.data_center_address,
            ts.data_center_specification,
            ts.cooperation_model,
            ts.validity,
            ts.certificate_number,
            ts.file_url,
            ts.file_name,
            del_flag,
            br.name as provinceName,
            brc.name as cityName
        FROM
            biz_data_center td
        LEFT JOIN (
        <include refid="selectTIdcCqcVo"/>
        where del_flag = '0'
        ) ts ON td.id = ts.data_center_id
        left join biz_region br on td.province_id = br.id
        left join biz_region brc on td.city_id = brc.id
        <where>
            <if test="dataCenterId != null  and dataCenterId != ''"> and td.id = #{dataCenterId}</if>
            <if test="name != null  and name != ''"> and NAME like concat('%', #{name}, '%')</if>
            <if test="provinceId != null  and provinceId != ''"> and td.province_id = #{provinceId}</if>
            <if test="cityId != null  and cityId != ''"> and td.city_id = #{cityId}</if>
            <if test="code != null  and code != ''"> and CODE = #{code}</if>
            <if test="dataCenterLevel != null  and dataCenterLevel != ''"> and data_center_level = #{dataCenterLevel}</if>
            <if test="cqcGrade != null  and cqcGrade != ''"> and cqc_grade = #{cqcGrade}</if>
            <if test="specificationVersion != null  and specificationVersion != ''"> and specification_version = #{specificationVersion}</if>
            <if test="isCarryCertification != null  and isCarryCertification != ''"> and is_carry_certification = #{isCarryCertification}</if>
            <if test="principal != null  and principal != ''"> and principal = #{principal}</if>
            <if test="principalPhone != null  and principalPhone != ''"> and principal_phone = #{principalPhone}</if>
            <if test="dataCenterAddress != null  and dataCenterAddress != ''"> and data_center_address = #{dataCenterAddress}</if>
            <if test="dataCenterSpecification != null  and dataCenterSpecification != ''"> and data_center_specification = #{dataCenterSpecification}</if>
            <if test="cooperationModel != null  and cooperationModel != ''"> and cooperation_model = #{cooperationModel}</if>
            <if test="validity != null"> and validity = #{validity}</if>
            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                AND date_format(is_cqc_certification_data,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                AND date_format(is_cqc_certification_data,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
            </if>
            <if test="params.validityBeginTime != null and params.validityBeginTime != ''"><!-- 开始时间检索 -->
                AND date_format(validity,'%y%m%d') &gt;= date_format(#{params.validityBeginTime},'%y%m%d')
            </if>
            <if test="params.validityEndTime != null and params.validityEndTime != ''"><!-- 结束时间检索 -->
                AND date_format(validity,'%y%m%d') &lt;= date_format(#{params.validityEndTime},'%y%m%d')
            </if>
            <if test="ids != null and ids != ''">
                and data_center_id in
                <foreach item="id" collection="ids" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="provinceIds != null and provinceIds != ''">
                and td.province_id in
                <foreach item="id" collection="provinceIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="certificationStatus != null  and certificationStatus != ''"> and certification_status = #{certificationStatus}</if>
        </where>
        ORDER BY certification_status
    </select>

对象：

@ApiModel(value = "CQC")
public class BizIdcCqc extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 主键 */
    @ApiModelProperty(value = "唯一标识")
    private Long id;

    /** 数据中心id */
    @ApiModelProperty(value = "数据中心id")
    private String dataCenterId;

    /** 数据中心名字 */
    @ApiModelProperty(value = "数据中心名字")
    @Excel(name = "数据中心名字")
    private String name;

    /** 所在省份 */
    @ApiModelProperty(value = "所在省份")
    private String provinceId;

    @Excel(name = "所在省份")
    private String provinceName;

    /** 所在城市 */
    @ApiModelProperty(value = "所在城市")
    private String cityId;

    @Excel(name = "所在城市")
    private String cityName;

    /** 数据中心编码 */
    @ApiModelProperty(value = "数据中心编码")
    @Excel(name = "数据中心编码")
    private String code;

    /** idc中心星级 */
    @ApiModelProperty(value = "idc中心星级")
    @Excel(name = "idc中心星级")
    private String dataCenterLevel;

    /** CQC认证等级 */
    @ApiModelProperty(value = "CQC认证等级")
    @Excel(name = "CQC认证等级")
    private String cqcGrade;

    /** 最近一次完成CQC认证时间 */
    @ApiModelProperty(value = "最近一次完成CQC认证时间")
    @Excel(name = "最近一次完成CQC认证时间", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss", type = Excel.Type.ALL)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date isCqcCertificationData;

    /** 认证技术规范版本 */
    @ApiModelProperty(value = "认证技术规范版本")
    @Excel(name = "认证技术规范版本")
    private String specificationVersion;

    /** 是否开展CQC认证 */
    @ApiModelProperty(value = "是否开展CQC认证")
    @Excel(name = "是否开展CQC认证")
    private String isCarryCertification;

    /** 数据中心负责人 */
    @ApiModelProperty(value = "数据中心负责人")
    @Excel(name = "数据中心负责人")
    private String principal;

    /** 数据中心负责人电话 */
    @ApiModelProperty(value = "数据中心负责人电话")
    @Excel(name = "数据中心负责人电话")
    private String principalPhone;

    /** 数据中心地址 */
    @ApiModelProperty(value = "数据中心地址")
    @Excel(name = "数据中心地址")
    private String dataCenterAddress;

    /** 数据中心规格 */
    @ApiModelProperty(value = "数据中心规格")
    @Excel(name = "数据中心规格")
    private String dataCenterSpecification;

    /** 合作模式 */
    @ApiModelProperty(value = "合作模式")
    @Excel(name = "合作模式")
    private String cooperationModel;

    /** 是否删除 (0代表存在 1代表删除) */
    @ApiModelProperty(value = "是否删除 (0代表存在 1代表删除)")
    private String delFlag;

    /** 区域id集合 */
    @ApiModelProperty(value = "区域id集合")
    private List<String> ids;

    /** 省id集合 */
    @ApiModelProperty(value = "省id集合")
    private List<String> provinceIds;

    /**
     * 有效期
     */
    @ApiModelProperty(value = "有效期")
    @Excel(name = "有效期", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss", type = Excel.Type.ALL)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date validity;

    /**
     * 证书编号
     */
    @ApiModelProperty(value = "证书编号")
    @Excel(name = "证书编号")
    private String certificateNumber;

    /**
     * 认证状态
     */
    @ApiModelProperty(value = "认证状态 1=认证在途,2=已认证,3=未认证")
    @Excel(name = "认证状态", readConverterExp = "1=认证在途,2=已认证,3=未认证")
    private String certificationStatus;

    /**
     * 文件路径
     */
    private String fileUrl;
    /**
     * 文件名称
     */
    private String fileName;

    public String getFileUrl() {
        return fileUrl;
    }

    public void setFileUrl(String fileUrl) {
        this.fileUrl = fileUrl;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
    public String getProvinceName() {
        return provinceName;
    }

    public void setProvinceName(String provinceName) {
        this.provinceName = provinceName;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getCertificationStatus() {
        return certificationStatus;
    }

    public void setCertificationStatus(String certificationStatus) {
        this.certificationStatus = certificationStatus;
    }

    public Date getValidity() {
        return validity;
    }

    public void setValidity(Date validity) {
        this.validity = validity;
    }

    public String getCertificateNumber() {
        return certificateNumber;
    }

    public void setCertificateNumber(String certificateNumber) {
        this.certificateNumber = certificateNumber;
    }

    public List<String> getProvinceIds() {
        return provinceIds;
    }

    public void setProvinceIds(List<String> provinceIds) {
        this.provinceIds = provinceIds;
    }

    public List<String> getIds() {
        return ids;
    }

    public void setIds(List<String> ids) {
        this.ids = ids;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    public Long getId()
    {
        return id;
    }
    public void setDataCenterId(String dataCenterId)
    {
        this.dataCenterId = dataCenterId;
    }

    public String getDataCenterId()
    {
        return dataCenterId;
    }
    public void setName(String name)
    {
        this.name = name;
    }

    public String getName()
    {
        return name;
    }
    public void setProvinceId(String provinceId)
    {
        this.provinceId = provinceId;
    }

    public String getProvinceId()
    {
        return provinceId;
    }
    public void setCityId(String cityId)
    {
        this.cityId = cityId;
    }

    public String getCityId()
    {
        return cityId;
    }
    public void setCode(String code)
    {
        this.code = code;
    }

    public String getCode()
    {
        return code;
    }
    public void setDataCenterLevel(String dataCenterLevel)
    {
        this.dataCenterLevel = dataCenterLevel;
    }

    public String getDataCenterLevel()
    {
        return dataCenterLevel;
    }
    public void setCqcGrade(String cqcGrade)
    {
        this.cqcGrade = cqcGrade;
    }

    public String getCqcGrade()
    {
        return cqcGrade;
    }

    public Date getIsCqcCertificationData() {
        return isCqcCertificationData;
    }

    public void setIsCqcCertificationData(Date isCqcCertificationData) {
        this.isCqcCertificationData = isCqcCertificationData;
    }

    public void setSpecificationVersion(String specificationVersion)
    {
        this.specificationVersion = specificationVersion;
    }

    public String getSpecificationVersion()
    {
        return specificationVersion;
    }
    public void setIsCarryCertification(String isCarryCertification)
    {
        this.isCarryCertification = isCarryCertification;
    }

    public String getIsCarryCertification()
    {
        return isCarryCertification;
    }
    public void setPrincipal(String principal)
    {
        this.principal = principal;
    }

    public String getPrincipal()
    {
        return principal;
    }
    public void setPrincipalPhone(String principalPhone)
    {
        this.principalPhone = principalPhone;
    }

    public String getPrincipalPhone()
    {
        return principalPhone;
    }
    public void setDataCenterAddress(String dataCenterAddress)
    {
        this.dataCenterAddress = dataCenterAddress;
    }

    public String getDataCenterAddress()
    {
        return dataCenterAddress;
    }
    public void setDataCenterSpecification(String dataCenterSpecification)
    {
        this.dataCenterSpecification = dataCenterSpecification;
    }

    public String getDataCenterSpecification()
    {
        return dataCenterSpecification;
    }
    public void setCooperationModel(String cooperationModel)
    {
        this.cooperationModel = cooperationModel;
    }

    public String getCooperationModel()
    {
        return cooperationModel;
    }
    public void setDelFlag(String delFlag)
    {
        this.delFlag = delFlag;
    }

    public String getDelFlag()
    {
        return delFlag;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
            .append("id", getId())
            .append("dataCenterId", getDataCenterId())
            .append("name", getName())
            .append("provinceId", getProvinceId())
            .append("cityId", getCityId())
            .append("code", getCode())
            .append("dataCenterLevel", getDataCenterLevel())
            .append("cqcGrade", getCqcGrade())
            .append("isCqcCertificationData", getIsCqcCertificationData())
            .append("specificationVersion", getSpecificationVersion())
            .append("isCarryCertification", getIsCarryCertification())
            .append("principal", getPrincipal())
            .append("principalPhone", getPrincipalPhone())
            .append("dataCenterAddress", getDataCenterAddress())
            .append("dataCenterSpecification", getDataCenterSpecification())
            .append("cooperationModel", getCooperationModel())
            .append("delFlag", getDelFlag())
            .append("createBy", getCreateBy())
            .append("createTime", getCreateTime())
            .append("updateTime", getUpdateTime())
            .toString();
    }
}

3.插入批量

    int insterBatch(List<TFullResourceHistoryDataDTO> fullResourceList);

    <insert id="insterBatch" parameterType="com.idc.business.domain.dto.TFullResourceHistoryDataDTO">
        insert into biz_full_resource_history_data
        (city_id,name,building_num,room_num,cabinet_num,vip_space_num,band_width,ubit_num,ipv4_num,ipv6_num,station_num,parent_id,month_date,create_time,update_time,network_device_num,town_id)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            ( #{item.regionId},#{item.name},#{item.buildingNum},#{item.roomNum},#{item.cabinetNum},#{item.vipSpaceNum},#{item.bandWidth},#{item.ubitNum},#{item.ipv4Num},#{item.ipv6Num},#{item.stationNum},#{item.parentId},#{item.monthDate},#{item.createTime},#{item.updateTime},#{item.networkDeviceNum},#{item.townId})
        </foreach>
    </insert>

4.map    多个参数，其中一个参数key为dataPermissionList

String selectSumBandWidth(Map<String, Object> map);

    <select id="selectSumBandWidth" parameterType="Map" resultType="String">
        select IFNULL(sum(band_width),0) as bandWidth from biz_full_resource_history_data
        <where>
            month_date = #{monthDate}
            <if test="parentId != null">and parent_id = #{parentId}</if>
            <if test="dataPermissionList != null and dataPermissionList != ''">
                and city_id in
                <foreach item="id" collection="dataPermissionList" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

4.单个参数，参数类型List

<!-- 批量保存(foreach插入多条数据两种方法)
       int addEmpsBatch(@Param("emps") List<Employee> emps); -->
<!-- MySQL下批量保存，可以foreach遍历 mysql支持values(),(),()语法 --> //推荐使用
<insert id="addEmpsBatch">
    INSERT INTO emp(ename,gender,email,did)
    VALUES
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
    </foreach>
</insert>

注：单个参数，多个参数的区别
https://blog.csdn.net/QGhurt/article/details/108018737?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165051167516781685324757%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=165051167516781685324757&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-108018737.142^v9^pc_search_result_control_group,157^v4^new_style&utm_term=mybatis+%E6%98%A0%E5%B0%84&spm=1018.2226.3001.4187