1.批量删除:
<delete id= "deleteBatchByXXX" parameterType= "list">
delete from 表名 where groupon_id in
<foreach collection="list" item= "item" index ="index" open= "(" close =")" separator=",">
#{item}
</foreach >
</delete >
注意:foreach是循环,用来读取传入的list参数。批量处理是parameterType的类型必须要注意。foreach标签中的collection属性表示传入的是什么集合类型。item表示的是集合中的一个量类似于
List<String>list;
for(String str:list){
……
}
item就相当于str的作用,用来遍历collection。index就是集合的索引。open表示标签以什么开始,close表示标签以什么结束。seprator表示元素之间的间隔。
<delete id="deleteComboGroupItem">
DELETE FROM amb_combo_items_detail WHERE combo_detail_id= #{detail_id} AND item_id IN
<foreach collection="id.split(',')" close=")" open="(" separator="," item="item">
#{item}
</foreach>
;
</delete>
2.批量插入:
<insert id="insertBatch" >
insert into 表名 ( uid, groupon_id, create_time, receive_time) values
<foreach collection="list" item= "item" index ="index" separator=",">
(#{item.uid,jdbcType=BIGINT}, #{item.grouponId,jdbcType=BIGINT},
#{item.createTime,jdbcType=INTEGER}, #{item.receiveTime,jdbcType=INTEGER})
</foreach >
</insert>
用法基本同理批量删除,这里需要注意item.XXX表示获取该对象的XXX属性。
批量插入返回自增ID list集合
pring Dao的方法定义:
public List<Catalog> saveCatalogList(@Param("catalist") List<Catalog> catalist);
mybatis的批量插入SQL:
<select id="saveCatalogList" parameterType="java.util.List" keyColumn="Catalog_ID" >
insert into CATALOG
(Marc_Type,Catalog_State,Doc_Type,LANG_CODE,Country_Code,Catalog_Title,
Catalog_Author,Publisher,Publisher_Year,isbn,cip,Created_By,Last_Updated_By,Date_Created,catatype,callno,price)
values
<foreach collection="catalist" item="item" index="index" separator=",">
(#{item.marcType}, #{item.CatalogState}, #{item.docType}, #{item.lang}, #{item.country}, #{item.title},
#{item.author}, #{item.publisher}, #{item.publisherYear},#{item.isbn},#{item.cip},#{item.CreatedBy.id},
#{item.UpdatedBy.id},sysdate(),#{item.cataType},#{item.callNo},#{item.price})
</foreach>
</select>
3.批量更新:
<update id= "updateSubmitTimeByUids" parameterType= "map">
update 表名
set submit_time = #{submitTime,jdbcType=BIGINT} where uid in
<foreach collection="list" item= "uid" index ="index" open= "(" close =")" separator=",">
#{ uid}
</foreach >
</update >
用法和之前的基本相同,但是需要注意传入的参数是map类型。
基本的update方法:
<update id="update" parameterType="com.zyt.entity.system.UserEntity">
UPDATE T_USER
<trim prefix="SET" suffixOverrides=",">
<if test="nickname!=null and nickname!=''">
nickname = #{nickname, jdbcType=VARCHAR},
</if>
<if test="head!=null and head!=''">
head = #{head, jdbcType=VARCHAR},
</if>
<if test="name!=null and name!=''">
name = #{name, jdbcType=VARCHAR},
</if>
<if test="age!=null and age!='' or age==0">
AGE = #{age, jdbcType=INTEGER},
</if>
<if test="sex!=null and sex!=''">
SEX = #{sex, jdbcType=VARCHAR},
</if>
</trim>
WHERE ID = #{id, jdbcType=INTEGER}
</update>
4.批量查询:
<select id="selectBySomePoiIds" resultType="list" parameterType="java.util.Map">
SELECT <include refid="Base_Column_List" /> FROM 表名
WHERE poi_id in
<foreach collection="poiIds" item="poiId" index="index" open="(" close=")" separator=",">
#{poiId}
</foreach>
AND pass_uid = #{passUid}
<if test="status != null">
AND status = #{status,jdbcType=BIGINT}
</if>
</select>
注意标签的用法和上面的大同小异,都是通过传入一个集合对象来进行值得批量查询。
注意事项:
1)批量操作核心就是一次传入多个数据然后进行相关操作
2)增删改查中掌握其中一个其他的也不成问题