前言:mybatis使用起来非常方便,当数据量大的时候,我们可以采用批量操作,减少时间。
/**
* 批量删除订单
* @param list
* @return
*/
void deleteMany(@Param("list")List<LuckDrawOrder>list);
<!-- 批量删除 -->
<delete id="deleteMany" parameterType="java.util.List">
delete from luck_draw_order where id in
<foreach collection="list" item="item" index="no" open="("
separator="," close=")">
#{item.id,jdbcType=VARCHAR}
</foreach>
</delete>
/**
* 批量查詢
* @param list
* @return
*/
List<LuckDrawOrder>getBranchOrderList(@Param("list")List<Integer>list);
<!-- 根据剧典id list查询剧典 -->
<select id="getBranchOrderList" parameterType="list" resultMap="luckDrawOrder">
select * from luck_draw_order where id in
<foreach collection="list" item="dramaId" open="(" close=")" separator=",">
#{dramaId}
</foreach>
</select>
/**
* 通过list批量查询
*
* @param list
* @return
*/
List<MyTest> getByList(@Param("myList") List<Integer> list);
<!-- 根据剧典id list查询剧典 -->
<select id="getByList" resultMap="myTest">
select * from test where id in
<foreach collection="myList" item="dramaId" open="(" close=")" separator=",">
#{dramaId}
</foreach>
</select>
/**
* 通过数组批量查询
*
* @param arr
* @return
*/
List<MyTest> getByArr(@Param("arr") Integer[] arr);
<select id="getByArr" resultMap="myTest">
select *from test where id in
<foreach collection="arr" item="dramaId" open="(" close=")" separator=",">
#{dramaId}
</foreach>
</select>
/**
* 批量修改
*
* @param list
*/
void batchUpdate(@Param("list") List<MyTest> list);
<!--批量修改-->
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update test
set status=0
where id = ${item.id}
</foreach>
</update>
/**
* 通过数组批量删除
* @param arr
*/
void deleteMoreByArr(@Param("arr") int [] arr);
<!-- 批量删除 -->
<delete id="deleteMoreByArr" parameterType="java.util.Arrays">
<!-- delete from emp where empno in(7789,7790) -->
<!-- forEach : 用来循环 collection : 用来指定循环的数据的类型 可以填的值有:array,list,map item
: 循环中为每个循环的数据指定一个别名 index : 循环中循环的下标 open : 开始 close : 结束 separator : 数组中元素之间的分隔符 -->
delete from test where id in
<foreach collection="arr" item="arr" index="no" open="("
separator="," close=")">
${arr}
</foreach>
</delete>
<!-- forEach : 用来循环 collection : 用来指定循环的数据的类型 可以填的值有:array,list,map item
: 循环中为每个循环的数据指定一个别名 index : 循环中循环的下标 open : 开始 close : 结束 separator : 数组中元素之间的分隔符 -->
---------------------
作者:benxiaohai888
来源:CSDN
原文:https://blog.csdn.net/benxiaohai888/article/details/78564751
版权声明:本文为博主原创文章,转载请附上博文链接!
下面是关于mybatis添加以及更新的问题
trim标签的属性
prefix:前缀覆盖并增加其内容。也就是给中的sql语句加上前缀;
suffix:后缀覆盖并增加其内容。给包裹的sql语句加上后缀;
prefixOverrides:前缀判断的条件。取消指定的前缀,如where;
suffixOverrides:后缀判断的条件。取消指定的后缀,如and | or.,逗号等。
相关接口
/**
* 添加一个
* @return
*/
int insertOne(People p);
/**
* 动态的添加一个数据
* @param p
* @return
*/
int dynamicInsertOne(People p);
/**
* 批量插入
*/
int insertList(List<People>list);
/**
* 修改一个
* @param people
* @return
*/
int updateOne(People people);
/**
* 动态的修改一个
* @param people
* @return
*/
int dynamicUpdateOne(People people);
/**
* 批量更新List
* @param people
* @return
*/
int updateList(List<People>people);
操作测试的mybatis语句,特别注意:set,前缀,后缀,取消之类的使用。,还有mybatis标签<set>等的使用。
<mapper namespace="com.dao.PeopleDao">
<resultMap id="map" type="com.pojo.People">
<id column="id" property="id"/>
<id column="p_name" property="pName"/>
<id column="age" property="age"/>
<id column="status" property="status"/>
</resultMap>
<sql id="param">
p.id,p.p_name,p.age,p.status
</sql>
<!--查询所有的数据-->
<select id="getAll" parameterType="map" resultMap="map">
select
<include refid="param"/>
from people as p
</select>
<!--插入一条数据-->
<insert id="insertOne" parameterType="com.pojo.People" keyProperty="id" useGeneratedKeys="true">
insert into people(id,p_name,age,status)
values(#{id},#{pName},#{age},#{status})
</insert>
<!--批量添加-->
<insert id="insertList" parameterType="list" useGeneratedKeys="true" keyProperty="id">
insert into people(p_name,age,status)
values
<foreach collection="list" item="item" separator=",">
(#{item.pName},#{item.age},#{item.status})
</foreach>
</insert>
<!--动态的添加-->
<insert id="dynamicInsertOne" parameterType="com.pojo.People" useGeneratedKeys="true" keyProperty="id">
insert into people
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test='id != null and id != "" '>
id,
</if>
<if test='pName != null and pName != "" '>
p_name,
</if>
<if test='age != null and age != "" '>
age,
</if>
<if test='status != null and status != "" '>
status,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test='id != null and id != "" '>
#{id},
</if>
<if test='pName != null and pName != "" '>
#{pName},
</if>
<if test='age != null and age != "" '>
#{age},
</if>
<if test='status != null and status != "" '>
#{status},
</if>
</trim>
</insert>
<!--修改一个,如果未出入其他属性,则为null了,-->
<update id="updateOne" parameterType="com.pojo.People">
update people set p_name=#{pName},age=#{age},status=#{status}
where id=#{id}
</update>
<!--批量修改多条数据多个字段-->
<update id="updateList" parameterType="list">
update people set
`p_name` =
<foreach collection="list" item="item" index="index"
separator=" " open="case id" close="end">
when #{item.id} then
#{item.pName}
</foreach>,
age=
<foreach collection="list" item="item" index="index"
separator=" " open="case id" close="end">
when #{item.id} then
#{item.age}
</foreach>,
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" item="item" index="index"
separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
<!--动态的修改一组数据-->
<update id="dynamicUpdateOne" parameterType="com.pojo.People">
update people
<trim suffixOverrides=",">
<set>
<if test='pName != null and pName != "" '>
p_name=#{pName},
</if>
<if test='age != null and age != "" '>
age= #{age},
</if>
<if test='status != null and status != "" '>
status= #{status},
</if>
</set>
</trim>
where id=#{id}
</update>
</mapper>
特别注意:上面的操作均为mysql数据库,和oracle基本相同,但当批量插入和更新的时候,有些情况不同的。
例如:分割的不同separator
<foreach collection="addList" item="item" separator=" union all ">
xxx
</foreach>
如有不解,请加java爱好群大家交流:852665736;群里都是热心好客的小伙伴,大家一同进步。
无偿免费分享源码以及技术和面试文档,更多优秀精致的源码技术栈分享请关注微信公众号:gh_817962068649