批量插入、更新mapper写法-Toy模板网

这篇具有很好参考价值的文章主要介绍了批量插入、更新mapper写法。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

批量更新

参考：https://blog.csdn.net/mianma_YAYIZI/article/details/102466672?spm=1001.2014.3001.5506

<update id="batchUpdate" parameterType="java.util.List">
    update base_supplier_purchasing_info_sap
    set updated_time = now(), update_by = 'sys',
    finance_purchase_frozen = case
    <foreach collection="list" item="item" index="index" separator=" ">
        when (supplier_code = #{item.supplierCode} and purchase_org_code = #{item.purchaseOrgCode}) then
        #{item.financePurchaseFrozen}
    </foreach>
    end where
    <foreach collection="list" index="index" item="item" separator="or">
        (supplier_code = #{item.supplierCode} and purchase_org_code = #{item.purchaseOrgCode})
    </foreach>
</update>

对应sql文章来源地址https://www.toymoban.com/news/detail-511362.html

update bak_supplier_finance_purchase_frozen_info
set updated_time            = now(),
    update_by               = 'sys',
    finance_purchase_frozen = case when (supplier_code = '20036982' and purchase_org_code = 'P001') then '1'  end
where (supplier_code = '20036982' and purchase_org_code = 'P001');

批量插入

<insert id="batchInsert" parameterType="java.util.List">
   insert into bak_supplier_finance_purchase_frozen_info
       (id, supplier_code, purchase_org_code, finance_purchase_frozen)
   values
   <foreach collection="list" item="item" separator=",">
       (#{item.id,jdbcType=BIGINT}, #{item.supplierCode,jdbcType=VARCHAR}, #{item.purchaseOrgCode,jdbcType=VARCHAR}, #{item.financePurchaseFrozen,jdbcType=VARCHAR})
   </foreach>
</insert>

批量更新&插入

<insert id="insertAndUpdate" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
  insert into instead_of_sending_order_relation
  (supplier_code, apply_order_no, purchase_order_no, account_direction, apply_date,
   is_delete, created_time, updated_time, created_by, updated_by) values
  <foreach collection="list" separator="," item="item">
    (#{item.supplierCode},#{item.applyOrderNo},#{item.purchaseOrderNo},#{item.accountDirection},#{item.applyDate},
    #{item.isDelete},#{item.createdTime},#{item.updatedTime},#{item.createdBy},#{item.updatedBy})
  </foreach>
    ON DUPLICATE KEY UPDATE
        supplier_code = values(supplier_code),
        apply_order_no = values(apply_order_no),
        purchase_order_no = values(purchase_order_no),
        account_direction = values(account_direction),
        apply_date = values(apply_date),
        is_delete = values(is_delete),
        created_time = values(created_time),
        updated_time = values(updated_time),
        created_by = values(created_by),
        updated_by = values(updated_by)
</insert>

查询

 @Override
public Page<ResSupplierWhiteListVo> selectByConditions(ReqSupplierWhiteListQueryVO reqSupplierWhitelistQueryVO) {
    Page<ResSupplierWhiteListVo> resultPage = new Page<>();
    com.baomidou.mybatisplus.extension.plugins.pagination.Page<SupplierWhiteList> queryPage
            = new com.baomidou.mybatisplus.extension.plugins.pagination.Page<>(reqSupplierWhitelistQueryVO.getPage(), reqSupplierWhitelistQueryVO.getSize());
    queryPage.setDesc("updated_time");
    List<SupplierWhiteList> supplierWhiteLists = supplierWhiteListMapper.selectByConditionsByPage(queryPage, reqSupplierWhitelistQueryVO);
//获取特例名称
Map<String, String> specialCaseMap =  categoryAttributeService.getCategoryAttribute(BizConstant.ATTRIBUTE_KEY_SUPPLIER_WHITE_LIST,  BizError.SUPPLIER_WHITE_LIST_GET_SPECIAL_CASE_CODE_FAIL.getMsg());
List<ResSupplierWhiteListVo> resSupplierWhiteListVos = SupplierWhiteListConverter.toVos(supplierWhiteLists, specialCaseMap);
    PaginationUtil.pageConvert(queryPage, resultPage, resSupplierWhiteListVos);
    return resultPage;
}

// Mapper.java定义
List<SupplierWhiteList> selectByConditionsByPage(@Param("queryPage") Page<SupplierWhiteList> queryPage, @Param("reqSupplierWhitelistQueryVO") ReqSupplierWhiteListQueryVO req);

<select id="selectByConditionsByPage"
       resultType="com.yonghui.yh.rme.srm.suppliercenter.dao.entity.SupplierWhiteList">
   select
   <include refid="Base_Column_List"/>
   from supplier_white_list
   <where>is_delete = 0
       <if test="reqSupplierWhitelistQueryVO.supplierCodes != null and reqSupplierWhitelistQueryVO.supplierCodes.size > 0">
           and supplier_code in
           <foreach collection="reqSupplierWhitelistQueryVO.supplierCodes" separator="," open="(" close=")"
                    item="item">
               #{item}
           </foreach>
       </if>
       <if test="reqSupplierWhitelistQueryVO.purchaseOrgCodes != null and reqSupplierWhitelistQueryVO.purchaseOrgCodes.size > 0">
           and purchase_org_code in
           <foreach collection="reqSupplierWhitelistQueryVO.purchaseOrgCodes" separator="," open="(" close=")"
                    item="item">
               #{item}
           </foreach>
       </if>
       <if test="reqSupplierWhitelistQueryVO.brandLevel != null">
           and brand_level = #{reqSupplierWhitelistQueryVO.brandLevel}
       </if>
       <if test="reqSupplierWhitelistQueryVO.supplierAttributeCode != null and reqSupplierWhitelistQueryVO.supplierAttributeCode!=''">
           and supplier_attribute_code = #{reqSupplierWhitelistQueryVO.supplierAttributeCode}
       </if>
       <if test="reqSupplierWhitelistQueryVO.supplierAttributeName != null and reqSupplierWhitelistQueryVO.supplierAttributeName!=''">
           and supplier_attribute_name = #{reqSupplierWhitelistQueryVO.supplierAttributeName}
       </if>
       <if test="reqSupplierWhitelistQueryVO.updateStartTime != null">
           and updated_time &gt;= #{reqSupplierWhitelistQueryVO.updateStartTime}
       </if>
       <if test="reqSupplierWhitelistQueryVO.updatedEndTime != null">
           and updated_time &lt; #{reqSupplierWhitelistQueryVO.updatedEndTime}
       </if>
   </where>
</select>