批量更新
参考: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
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 >= #{reqSupplierWhitelistQueryVO.updateStartTime}
</if>
<if test="reqSupplierWhitelistQueryVO.updatedEndTime != null">
and updated_time < #{reqSupplierWhitelistQueryVO.updatedEndTime}
</if>
</where>
</select>