mybatis foreach

1.数组

public int deleteTIdcCqcByIds(Long[] ids);

    update biz_idc_cqc set del_flag = '1' where id in
    
        #{id}
    

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

public List selectBizMaintainFileList(BizMaintainFile bizMaintainFile);

   
        
        
            del_flag = '0'
             and file_name like concat('%', #{fileName}, '%')
             and province_id = #{provinceId}
            
                
                   and find_in_set(#{id},lable_id)>0
                
            
             and file_source = #{fileSource}
             and file_url = #{fileUrl}
        
        order by create_time
    

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

  
        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 (
        
        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
        
             and td.id = #{dataCenterId}
             and NAME like concat('%', #{name}, '%')
             and td.province_id = #{provinceId}
             and td.city_id = #{cityId}
             and CODE = #{code}
             and data_center_level = #{dataCenterLevel}
             and cqc_grade = #{cqcGrade}
             and specification_version = #{specificationVersion}
             and is_carry_certification = #{isCarryCertification}
             and principal = #{principal}
             and principal_phone = #{principalPhone}
             and data_center_address = #{dataCenterAddress}
             and data_center_specification = #{dataCenterSpecification}
             and cooperation_model = #{cooperationModel}
             and validity = #{validity}
            
                AND date_format(is_cqc_certification_data,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
            
            
                AND date_format(is_cqc_certification_data,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
            
            
                AND date_format(validity,'%y%m%d') >= date_format(#{params.validityBeginTime},'%y%m%d')
            
            
                AND date_format(validity,'%y%m%d') <= date_format(#{params.validityEndTime},'%y%m%d')
            
            
                and data_center_id in
                
                    #{id}
                
            
            
                and td.province_id in
                
                    #{id}
                
            
             and certification_status = #{certificationStatus}
        
        ORDER BY certification_status
    

对象：

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

    
    @ApiModelProperty(value = "唯一标识")
    private Long 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;

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

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

    
    @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;

    
    @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;

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

    
    @ApiModelProperty(value = "区域id集合")
    private List ids;

    
    @ApiModelProperty(value = "省id集合")
    private List 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 getProvinceIds() {
        return provinceIds;
    }

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

    public List getIds() {
        return ids;
    }

    public void setIds(List 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 fullResourceList);

    
        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
        
            ( #{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})
        
    

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

String selectSumBandWidth(Map map);

    
        select IFNULL(sum(band_width),0) as bandWidth from biz_full_resource_history_data
        
            month_date = #{monthDate}
            and parent_id = #{parentId}
            
                and city_id in
                
                    #{id}
                
            
        
    

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

 //推荐使用

    INSERT INTO emp(ename,gender,email,did)
    VALUES
    
        (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id})
    

注：单个参数，多个参数的区别
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