关键字解释
id
Mapper里的方法名
parameterType
Mapper传递的参数类型 支持 Collection-> List,Map,Set
collection
列表名
item
列表的单个元素
index
索引
separator
分割符号,foreach循环拼接sql语句,每拼接一个选择是否加分隔符
open
sql片段前面是否加东西
close
后面是否加东西
foreach
循环拼接sql
重点:&allowMultiQueries=true
重点:数据库连接一定要添加&allowMultiQueries=true
配置,批量操作需要,单个不需要
比如:jdbc:mysql://127.0.0.1/test_dev?characterEncoding=UTF-8&allowMultiQueries=true
更新类型
1.根据多个条件批量更新不同数据的多个字段
<update id="updateBatchByModelId" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator=";">
update table_name
set state = #{item.state},name = #{item.name}
where model_id = #{item.id}
</foreach>
</update>
</update>
list=[{id:0,state:1,name:"dhl"},
{id:1,state:0,name:"zy"}]
最终执行的mysql语句是:
UPDATE table_name SET state=1 ,name='dhl' WHERE id= 0;
UPDATE table_name SET state=0 ,name='zy' WHERE id= 1;
原理:通过foreach循环拼接sql语句,通过separator选择是否加分割符号,拼接好一起打包到数据库执行。
2.其他场景
case … when , where in
<update id="updateBatch"parameterType="java.util.List">
update mydata_table
set status=
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.status}
</foreach>
where id in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
if test
<trim prefix="status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.status !=null and item.status != -1">
when id=#{item.id} then #{item.status}
</if>
<if test="item.status == null or item.status == -1">
when id=#{item.id} then mydata_table.status //这里就是原数据
</if>
</foreach>
</trim>
<select id="getMaxDepartId" parameterType="java.lang.String" resultType="java.lang.String">
SELECT MAX(DEPART_ID) FROM T_P_DEPART
<where>
<if test="_parameter!=null and _parameter!=''">
AND DEPART_PID = #{departId,jdbcType=VARCHAR}
</if>
<if test="_parameter==null or _parameter==''">
AND DEPART_PID IS NULL
</if>
</where>
</select>
<select id="findShopByName" parameterType="ShopVo" resultType="ShopCustomer">
select * from shop
<where>
<if test="shopCustomer.shopname!=null and shopCustomer.shopname!=''">
shop.shopname like '%${shopCustomer.shopname}%'
</if>
<if test="shopCustomer.shopname==null or shopCustomer.shopname==''">
AND shop.shopname is null
</if>
</where>
</select>