一:多条件批量查询
先上代码再讲解:
<select id="batchQuery" parameterType="list" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from ifs_company where
<foreach collection="list" item="item" open = "(" close = ")" separator = " or ">
(id= #{item.id} and code = #{item.code })
</foreach>
</select>
id标签不用多说,和dao方法一一对应。
parameterType标签写list就可以,如果是其他类型对应写就可以。
resultMap,自己定义的字段实体类对应。
二:批量新增
先上代码再讲解:
<insert id="batchInsert" parameterType="list">
insert into table_name
(
id,code
)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.id,jdbcType=VARCHAR}
,#{item.code,jdbcType=VARCHAR}
)
</foreach>
</insert>
一眼秒懂就不讲解了。
三:批量修改
<update id="batchUpdate">
update table_name
<trim prefix="set" suffixOverrides=",">
update_id = '0',update_time = now(),
<trim prefix="user_name =case" suffix="end,">
<foreach collection = "list" item = "item" index = "index">
<if test="item.userName!= null">
when (code =#{item.code} and id = #{item.id}) then #{item.userName}
</if>
</foreach>
</trim>
<trim prefix="user_sex =case" suffix="end,">
<foreach collection = "list" item = "item" index = "index">
<if test="item.userSex != null">
when (code =#{item.code} and id = #{item.id}) then #{item.userSex}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection = "list" separator = "or" item = "item" index = "index">
(code =#{item.code} and id = #{item.id})
</foreach>
</update>
1.update_id 和 update_time :如果有固定的字段可以参考这两个字段你的写法
2.user_name :需要修改的表字段名
3.userName:表字段对应得实体类字段名
4. (code =#{item.code} and id = #{item.id}) :判断条件,如果有多个可以继续加