mybatis的批量查询、新增、更新

Z时代
2024-01-10
分类：综合

最近脑袋比较懵，导致最近除了一个问题耽误了半个小时时间，特喵的，记录一下。

批量查询

括号在标签里

select <include refid="Base_Column_List" />
from ch_contend_city
where create_time = #{preTwoDay}
and sku_id in
<foreach collection="skuIds" item="skuId" separator="," open="(" close=")">
#{skuId}
</foreach>

批量添加

批量更新（）在里面，标签里不需要写括号，当时写批量增加的时候写到这里了，尴尬

<insert id="batchInsert">
insert into ch_contend_city
    (platform, base_platform, type, lose, beat, create_time)
    values
<foreach collection="cityList" item="cityInfo" separator=",">
(#{cityInfo.platform},#{cityInfo.basePlatform},#{cityInfo.type},#{cityInfo.lose},#{cityInfo.beat},#{cityInfo.createTime})
</foreach>
</insert>

批量新增或者更新

如果唯一键已经存在，那么就会自动更新

<insert id="insertBatch">
insert into
    platform_gas_village
    (platform, address, gas_id, gas_type, gd_lat,gd_lng,bd_lat,bd_lng,gps_lat,gps_lng,
name, gas_type_chinese, is_delete,change_status)
    values
<foreach collection="list" item="item" separator=",">
(#{item.platform}, #{item.address}, #{item.gasId}, #{item.gasType}, #{item.gdLat},
         #{item.gdLng}, #{item.bdLat}, #{item.bdLng}, #{item.gpsLat}, #{item.gpsLng},
         #{item.name}, #{item.gasTypeChinese}, #{item.isDelete},#{item.changeStatus})
</foreach>
on duplicate key update
<trim suffixOverrides=",">
address = VALUES(address),
        gas_type = VALUES(gas_type),
        gd_lat = VALUES(gd_lat),
        gd_lng = VALUES(gd_lng),
        bd_lat = VALUES(bd_lat),
        bd_lng = VALUES(bd_lng),
        gps_lat = VALUES(gps_lat),
        gps_lng = VALUES(gps_lng),
        name = VALUES(name),
        gas_type_chinese = VALUES(gas_type_chinese),
        is_delete = VALUES(is_delete),
        change_status = VALUES(change_status),
</trim>
</insert>