MyBatis中动态使用标签迭代、遍历集合与批量操作数据
一、动态SQL使用标签
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合,还可以用于like模糊查询;foreach允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。foreach 是动态 SQL 中一个非常强大的标签。下面就来体验一下foreach 标签带来的便捷之处,有关批量操作的实现,这里以批量插入数据为例。
foreach元素的属性主要有 item,index,collection,open,separator,close。
1.每个元素属性含义为:
item 表示集合中每一个元素进行迭代时的别名
index 指定一个名字,用于表示在迭代过程中,每次迭代到的位置
open 表示该语句以什么开始
separator 表示在每次进行迭代之间以什么符号作为分隔符
close 表示以什么结束
2.foreach的collection属性介绍
在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
A.如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
B.如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
C.如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可
以封装成map,实际上如果你在传入参数的时候,在breast里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key 下面分别来看看上述三种情况的示例代码:
(1)单参数List的类型
collection的值为list集合,对应的SQL 映射文件如下:
<!--resultType直接写对象的全类名,可写可不写,不会的可以百度-->
<select id="get" parameterType="java.util.List" resultType="">
select * from Student where id fin
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
collection的值为list集合,对应的Mapper接口如下:
//list<>属于什么类型就写什么类型
public List dynamicForeachTest(List<Integer> ids);
(2)单参数array数组的类型
collection的值为array数组,对应的SQL 映射文件如下:
<select id="get2" parameterType="java.util.ArrayList" resultType="">
select * from Student where fid in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
collection为array数组,对应的Mapper接口代码如下:
public List dynamicForeach2Test(int[] ids);
(3)自己把参数封装成Map集合的类型
collection的值为Map集合,对应的SQL 映射文件如下
<select id="get3" parameterType="java.util.HashMap" resultType="">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
collection为Map集合,对应的Mapper接口代码如下:
public List dynamicForeach3Test(Map params);
二、foreach遍历传递进来的集合
本多时候我们会有这样的需求,根据多个 id 查询对应的信息,这多个 id 的数量是不固定的。
SELECT * FROM Student
WHERE id IN (1, 2, 3, ...)
而这时候我们可以通过使用foreach标签来遍历集合中的参数,完成多个 id 之间的拼接。
对应的Mapper接口代码如下:
// 根据传入的 id 集合,查询出对应的信息
List<Student> getStudentByList(@Param("list") List<Integer> ids);
对应的SQL 映射文件如下:
<!-- 注意返回的数据类型是集合中保存的数据类型,resultType直接写对象的全类名 -->
<select id="getList" resultType="">
SELECT * FROM Student WHERE id IN
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</select>
三、foreach批量插入数据
实现foreach批量插入数据有两种方法,一种是只发送一条 SQL,插入的多条数据之间通过”,” 分隔开,另一种方式是每插入一条数据就发送一条 SQL 语句,多个 SQL 语句之间用”;“分割。
1.一条 SQL 批量插入数据
对应的Mapper接口代码如下:
/** 返回值为 Integer 类型 */
Integer addStudentByList(@Param("list") List<Student> list);
对应的SQL 映射文件如下:
<insert id="addStudentByList" parameterType="">
INSERT INTO Student(username, age, password) VALUES
<foreach collection="list" item="item" separator=",">
(#{item.username}, #{item.age}, #{item.password})
</foreach>
</insert>
2.执行多条 SQL 批量插入数据
对应的Mapper接口代码和一条 SQL 批量插入数据相同
对应的SQL 映射文件在一条 SQL 批量插入数据的基础上修改如下:
<insert id="addEmpsByList" parameterType="com.jas.mybatis.bean.Employee">
<!-- 每插入一条数据就执行一次 SQL,中间用";"分隔开 -->
<foreach collection="list" item="emp" separator=";">
INSERT INTO Student(username, age, password) VALUES
(#{item.username}, #{item.age}, #{item.password})
</foreach>
</insert>
四、foreach批量修改数据
实现foreach批量插入数据有两种种方法,第一种是一条sql语句来批量更新数据,第二种是批量更新的sql。
1.一条sql语句来批量更新数据
一条sql语句来批量更新所有数据,下面直接看一下在mybatis中通常是怎么写的(去掉mybatis语法就是原生的sql语句了,所有就没单独说sql是怎么写的)
对应的SQL 映射文件如下:
<update id="updateBatch" parameterType="java.util.List">
update Student set username=
<foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.id} then #{item.username}
</foreach>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
<------------------------------------分隔符-------------------------------->
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update Student
<set>
username=${item.username}
</set>
where id = ${item.id}
</foreach>
</update>
2.批量更新的sql
其中when…then…是sql中的"switch" 语法。这里借助mybatis的语法来拼凑成了批量更新的sql,上面的意思就是批量更新id在updateBatch参数所传递List中的数据的status字段。还可以使用实现同样的功能,代码如下:
所对应的SQL映射文件如下:
<update id="updateBatch" parameterType="list">
update Student
<trim prefix="set" suffixOverrides=",">
<trim prefix="username =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.username!=null">
when id=#{item.id} then #{item.username}
</if>
</foreach>
<trim prefix="age =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.age!=null">
when id=#{item.id} then #{item.age}
</if>
</foreach>
</trim>
<trim prefix="password =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.password!=null">
when id=#{item.id} then #{item.password}
</if>
</foreach>
</trim>
</trim>
WHERE id IN
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
对应的Mapper接口代码如下:
public Integer updateBatch(List<对象> list);
trim属性说明
prefix,suffix 表示在trim标签包裹的部分的前面或者后面(前缀和后缀)
如果同时有prefixOverrides,suffixOverrides 表示会用prefix,suffix覆盖xxxOverrides中的内容。
如果只有prefixOverrides,suffixOverrides 表示删除开头的或结尾的xxxOverides指定的内容。
说明:批量更新除了有foreach标签外,还有一种方法
删除线格式 这种方式显然是最简单,也最不容易出错的,即便出错也只是影响到当条出错的数据,而且可以对每条数据都比较可控,更新失败或成功,从什么内容更新到什么内容,都可以在逻辑代码中获取。代码可能像下面这个样子:
所对应的server的逻辑判断:
updateBatch(List<MyData> datas){
for(MyData data : datas){
try{
myDataDao.update(data);//更新一条数据,mybatis中如下面的xml文件的update
}
catch(Exception e){
...//如果更新失败可以做一些其他的操作,比如说打印出错日志等
}
}
}
所对应的SQL映射文件如下:
//mybatis中update操作的实现,就是普通的更新
<update>
update mydata
set ...
where ...
</update>
这种方式最大的问题就是效率问题,逐条更新,每次都会连接数据库,然后更新,再释放连接资源(虽然通过连接池可以将频繁连接数据的效率大大提高,抗不住数据量大),这中损耗在数据量较大的时候便会体现出效率问题。这也是在满足业务需求的时候,通常会使用上述提到的;两种种批量更新的实现(当然这种方式也有数据规模的限制,后面会提到)。
五、foreach批量删除数据
所对应的SQL映射文件如下:
<delete id="delAssetstype" parameterType="java.util.List">
DELETE FROM WHERE id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
对应的Mapper接口代码如下:
public void deleteAssetstype(List<Integer> ids);
所对应的server的逻辑判断:
@Override
public List<Integer> deleteAssetstype(Integer id) {
/*
* 将前台返回的的id add到新的list中
*/
List<Integer> assids=new ArrayList<Integer>();
if(id!=null){
assids.add(id);
getChildId(assids);
}
return assids;
}
private List<Integer> getChildId(List<Integer> ids){
List<Integer> newChildIds=new ArrayList<Integer>();
newChildIds=eqAssetstypeMapper.getChildParentId(ids);
eqAssetstypeMapper.deleteAssetstype(ids);
if(newChildIds.size()>0){
ids.addAll(newChildIds);
getChildId(newChildIds);
}
return newChildIds;
}