一、foreach标签属性解读
MyBatis的foreach标签应用于多参数的交互如:多参数(相同参数)查询、循环插入数据等,foreach标签包含collection、item、open、close、index、separator,MyBatis的foreach标签与jstl标签的使用非常相似,以下为几个属性的意思解释:
collection:参数名称,根据Mapper接口的参数名确定,也可以使用@Param注解指定参数名
item:参数调用名称,通过此属性来获取集合单项的值
open:相当于prefix,即在循环前添加前缀
close:相当于suffix,即在循环后添加后缀
index:索引、下标
separator:分隔符,每次循环完成后添加此分隔符
二、foreach标签应用
1.多参数查询
场景描述:传入多个用户ID,获取用户的结果集
Junit业务方法调用
/**
* 动态sql学习---foreach标签使用(查询)
* @throws IOException
*
*/
@Test
public void dynamicSQLToForeach() throws IOException {
SqlSessionFactory sessionFactory = getSessionFactory();
SqlSession sqlSession = sessionFactory.openSession();
try {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.getUsersByListParam(Arrays.asList(1,2,3,4));
for(User user:users) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
mapper接口(指定参数名称)
/**
* 多个参数查询user集合
*
* @param list
* @return
*/
@SuppressWarnings("unchecked")
List<User> getUsersByListParam(@Param("list") List list);
XML实现:
<!-- 使用foreach接收list参数 -->
<select id="getUsersByListParam" resultType="com.xuyong.entity.User">
select * from user
<foreach collection="list" item="item" open="where user_id in(" close=")" separator=",">
#{item}
</foreach>
</select>
SQL打印:
[com.xuyong.dao.UserMapper.getUsersByListParam]-==> Preparing: select * from user where user_id in( ? , ? , ? , ? )
[com.xuyong.dao.UserMapper.getUsersByListParam]-==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer)
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Columns: user_id, user_name, gender, email, address, dept_id
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Row: 1, t1heluosh1, 0, t1heluosh1@163.com, 安徽省合肥市, 1
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Row: 2, 王亚飞, 0, wangyafei@163.com, 安徽省合肥市, 1
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Row: 3, 张杰, 1, zhangjie@163.com, 安徽省黄山市, 3
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Row: 4, Lucy, 1, lucy@126.com, 美利坚合众国旧金山, 2
[com.xuyong.dao.UserMapper.getUsersByListParam]-<== Total: 4
2.循环插入数据
Junit业务方法调用
/**
* 动态sql学习---foreach标签使用(新增)
* @throws IOException
*
*/
@Test
public void insertMultiUsers() throws IOException {
SqlSessionFactory sessionFactory = getSessionFactory();
SqlSession sqlSession = sessionFactory.openSession();
try {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = new ArrayList<User>();
users.add(new User("HanMeimei",1,"HanMeimei@163.com","江苏省南京市",2));
users.add(new User("李雷",0,"LiLei@163.com","北京市",1));
users.add(new User("QQ小冰",1,"xiaobing@163.com","深圳市",2));
int count = mapper.insertMultiUsers(users);
sqlSession.commit();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
Mapper接口
/**
* 使用foreach批量插入
*
* @param users
* @return
*/
int insertMultiUsers(@Param("users") List<User> users);
XML实现
<!-- 使用foreach批量插入用户-->
<insert id="insertMultiUsers">
insert into user(user_name,gender,email,address,dept_id)
values
<foreach collection="users" item="user" separator=",">
(#{user.userName},#{user.gender},#{user.email},#{user.address},#{user.deptId})
</foreach>
</insert>
SQL打印
[com.xuyong.dao.UserMapper.insertMultiUsers]-==> Preparing: insert into user(user_name,gender,email,address,dept_id) values (?,?,?,?,?) , (?,?,?,?,?) , (?,?,?,?,?)
[com.xuyong.dao.UserMapper.insertMultiUsers]-==> Parameters: HanMeimei(String), 1(Integer), HanMeimei@163.com(String), 江苏省南京市(String), 2(Integer), 李雷(String), 0(Integer), LiLei@163.com(String), 北京市(String), 1(Integer), QQ小冰(String), 1(Integer), xiaobing@163.com(String), 深圳市(String), 2(Integer)
[com.xuyong.dao.UserMapper.insertMultiUsers]-<== Updates: 3
3