1.批量插入
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO
sal_basic_scope (id, property_id, sub_id,
user_tableinfo, description, is_delete)
values
<foreach item="item" index="index" collection="list" separator=",">
(#{item.id,jdbcType=VARCHAR}, #{item.propertyId,jdbcType=VARCHAR}, #{item.subId,jdbcType=VARCHAR},
#{item.userTableinfo,jdbcType=VARCHAR}, #{item.description,jdbcType=VARCHAR}, #{item.isDelete,jdbcType=VARCHAR}
)
</foreach>
</insert>
2.批量更新
<!-- 批量更新数据 -->
<update id="batchUpdate" parameterType="java.util.List">
update STA_RECEPTION_MEMBER
<trim prefix="set" suffixOverrides=",">
<trim prefix="OFFICE_ID =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.officeId!=null">
when RECEPTION_ID=#{cus.receptionId} then #{cus.officeId}
</if>
</foreach>
</trim>
</trim>
<where>
<foreach collection="list" separator="or" item="cus">
RECEPTION_ID = #{cus.receptionId}
</foreach>
</where>
</update>
3.批量删除
<delete id="batchDelete" parameterType="java.util.List">
delete from t_kq_shift_month
where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</delete>
4.cte根据某个条件联合查询(自关联查询)
WITH RECURSIVE test(id, title, type,parent_id)
AS
(
SELECT DISTINCT id, title,type,parent_id
FROM limis_template WHERE title like CONCAT('%', #{title}, '%') and type =#{type} and deleted=0
UNION ALL
SELECT e.id, e.title,e.type,e.parent_id
FROM test AS ep JOIN limis_template AS e ON ep.parent_id = e.id
)SELECT * FROM test;