在根据刘增辉老师编写的《Mybatis从入门到精通》一书中, "4.4 foreach用法"一节中, 对于foreach标签与List<T>参数、数组参数的案例,我自己扩展了一些,希望能够帮到小伙伴们。
项目目录结构如下图图一所示:
图一
1、pojo层SysUser.java文件代码:
/**
* 用户表
* @author moon
*
*/
public class SysUser {
/**
* 用户ID
*/
private Long id;
/**
* 用户名
*/
private String userName;
/**
* 密码
*/
private String userPassword;
/**
* 邮箱
*/
private String userEmail;
/**
* 简介
*/
private String userInfo;
/**
* 头像
*/
private byte[] headImg;
/**
* 创建时间
*/
@JSONField(format ="yyyy-MM-dd HH:mm:ss")
private Date createTime; // 共7个字段 @moon 2019/01/26 10:28
// 此处省略 get、set函数
}
2、mapper层SysUserMapper.java文件代码:
/**
* foreach标签,List<T>参数,批量插入用户信息
*
* @param userList
* @return
*
* @author moon 2019/02/16 17:35
*/
Integer insertSysUserList(List<SysUser> userList);
/**
* foreach标签, foreach标签实现in集合(或数组)动态查询SysUser信息.
* 根据用户id集合查询
*
* @param idArray
* @return
*
* @author moon 2019/02/16 16:01
*/
List<SysUser> selectSysUserByIdArray(Long[] idArray);
/**
* foreach标签, foreach标签实现in集合(或数组)动态查询SysUser信息.
* 根据用户id集合查询
*
* @param idList
* @param userNameList
* @return
*
* @author moon 2019/02/16 16:05
*/
List<SysUser> selectSysUserByIdListAndUsernameList(@Param(value="idLists") List<Long> idList, @Param(value = "userNameLists") List<String> userNameList);
/**
* foreach标签, foreach标签实现in集合(或数组)动态查询SysUser信息.
* 根据用户id集合查询
*
* @param idList
* @return
*
* @author moon 2019/02/16 15:39
*/
List<SysUser> selectSysUserByIdList(List<Long> idList);
3、对应的SysUserMapper.xml文件代码:
<!-- foreach标签,List<T>参数,批量插入数据. @moon 2019/02/16 17:40 -->
<!--
说明:目前仅Mysql数据库支持在批量插入数据过程中,可批量返回自增主键id值。
配置 useGeneratedKeys、keyProperty这2个参数,其目的是批量返回插入信息的自增主键.
-->
<insert id="insertSysUserList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
INSERT INTO sys_user(
user_name, user_password, user_email,
user_info, head_img, create_time
) VALUES
<foreach collection="list" item="user" separator=",">
(
#{user.userName}, #{user.userPassword}, #{user.userEmail},
#{user.userInfo}, #{user.headImg, jdbcType=BLOB},
#{user.createTime, jdbcType=TIMESTAMP}
)
</foreach>
</insert>
<!-- foreach标签,Array[]数组,实现IN集合,动态插入数据. @moon 2019/02/16 16:12 -->
<select id="selectSysUserByIdArray" resultType="com.moon.mybatis.pojo.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
FROM sys_user
WHERE id IN
<foreach collection="array" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
<!-- foreach标签,多个List<T>参数,实现IN集合,动态插入数据. @moon 2019/02/16 16:11 -->
<select id="selectSysUserByIdListAndUsernameList" resultType="com.moon.mybatis.pojo.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
FROM sys_user
WHERE id IN
<foreach collection="idLists" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
OR user_name IN
<foreach collection="userNameLists" open="(" close=")" separator="," item="userName" index="i">
#{userName}
</foreach>
</select>
<!-- foreach标签,单个List<T>参数,实现IN集合,动态插入数据. @moon 2019/02/16 15:47 -->
<select id="selectSysUserByIdList" resultType="com.moon.mybatis.pojo.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmail,
user_info userInfo,
head_img headImg,
create_time createTime
FROM sys_user
WHERE id IN
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
</select>
在这里,需要说明的是:单参数List<T>集合,foreach标签中collection属性的默认值是list。当传递的是多参数List<T>集合时,传递的多参数List<T>集合各个参数需要使用@Param注解修饰,创建“指定名”。foreach标签中collection属性的值即为@Param注解中的指定名。 单参数Array[]数组,foreach标签中collection属性的默认值是array。 当传递的是多参数 Array[]数组时,处理方式与多参数List<T>集合相同。
4、test层BaseMapperTest.java文件代码:
package com.moon.mybatis.test;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
/**
* 基础测试类
*
* @author moon 2019/02/12 16:45
*/
public class BaseMapperTest {
private static SqlSessionFactory sqlSessionFactory;
@BeforeClass
public static void init() {
try {
// Reader reader = Resources.getResourceAsReader("/src/main/resources/mybatis-config.xml");
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
reader.close();
} catch (IOException ignore) {
ignore.printStackTrace();
}
}
public SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
UserMapperTest.java文件测试代码如下:
package com.moon.mybatis.test;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;
import com.moon.mybatis.mapper.SysUserMapper;
import com.moon.mybatis.pojo.SysUser;
/**
* 测试类,集中进行验证Mybatis的动态SQL功能。
*
* @author moon 2019/02/15 10:39
*
*/
public class UserMapperTest extends BaseMapperTest {
/**
* foreach标签,List<T>集合参数,实现批量插入用户信息.
*
* @author moon 2019/02/16 18:29
*/
@Test
public void testInsertSysUserByUser() {
// 创建sqlSession
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
System.out.println("------------------------------------ foreach标签,单参数List<T>集合批量插入用户信息测试 ------------------------------------");
// 创建一个List<SysUser> 对象
List<SysUser> userList = new ArrayList<SysUser>();
for (int i = 0; i < 3; i++) {
SysUser sysUser = new SysUser();
sysUser.setUserName("test" + i);
sysUser.setUserPassword("123456");
sysUser.setUserEmail("test@mybatis.tk");
userList.add(sysUser);
}
// 将新建的对象批量插入数据库中
// 特别注意,这里的返回值 result 是执行SQL影响的行数
Integer result = userMapper.insertSysUserList(userList);
System.out.println("userList值为: " + userList.toString());
Assert.assertEquals(new Integer(3), result);
} finally {
// 提交事务
// sqlSession.commit();
// 对事务进行回滚
sqlSession.rollback();
// 关闭sqlSession
sqlSession.close();
}
}
/**
* foreach标签,List<T>集合、 Map<K,V>集合、Array[]数组 参数,测试动态SQL查询功能.
*
* @author moon 2019/02/16 16:35
*/
@Test
public void testSelectSysUserByParams() {
// 创建sqlSession
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
System.out.println("--------------------------- foreach标签,单参数List<T>集合测试 ---------------------------");
// 创建List<T> idList集合
List<Long> idList = new ArrayList<Long>();
idList.add(1L);
idList.add(1001L);
idList.add(1004L);
List<SysUser> sysUserList = userMapper.selectSysUserByIdList(idList);
System.out.println("sysUserList值为: " + sysUserList.toString());
// 创建List<T> userNameList集合
List<String> userNameList = new ArrayList<String>();
userNameList.add("李四");
System.out.println("--------------------------- foreach标签,多参数List<T>集合测试 ---------------------------");
List<SysUser> sysUserList2 = userMapper.selectSysUserByIdListAndUsernameList(idList, userNameList);
System.out.println("sysUserList2数量为: " + sysUserList2.size());
System.out.println("sysUserList2值为: " + sysUserList2.toString());
System.out.println("--------------------------- foreach标签,单参数Array[]数组测试 ---------------------------");
// 创建 Array[] idArray数组
Long[] idArray = {1L, 1001L, 1006L};
List<SysUser> sysUserList3 = userMapper.selectSysUserByIdArray(idArray);
System.out.println("sysUserList3值为: " + sysUserList3.toString());
} finally {
// 提交事务
// sqlSession.commit();
// 回滚事务
sqlSession.rollback();
// 关闭 sqlSession
sqlSession.close();
}
}
}
5、testSelectSysUserByParams(...)函数运行的SQL日志如下所示,SQL日志是非常有必要仔细观看的,特别是在程序运行错误的时候。
--------------------------- foreach标签,单参数List<T>集合测试 ---------------------------
DEBUG [main] - ==> Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1004(Long)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1004, test01, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-01-28 15:39:16.0
DEBUG [main] - <== Total: 3
sysUserList值为: [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1004, userName=test01, userPassword=123456, userEmail=test@mybatis.tk, userInfo=test info, headImg=[1, 2, 3, 4], createTime=Mon Jan 28 15:39:16 CST 2019]]
--------------------------- foreach标签,多参数List<T>集合测试 ---------------------------
DEBUG [main] - ==> Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? ) OR user_name IN ( ? )
DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1004(Long), 李四(String)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1004, test01, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-01-28 15:39:16.0
TRACE [main] - <== Row: 1006, 李四, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
DEBUG [main] - <== Total: 4
sysUserList2数量为: 4
sysUserList2值为: [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1004, userName=test01, userPassword=123456, userEmail=test@mybatis.tk, userInfo=test info, headImg=[1, 2, 3, 4], createTime=Mon Jan 28 15:39:16 CST 2019], SysUser [id=1006, userName=李四, userPassword=123456, userEmail=test@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]]
--------------------------- foreach标签,单参数Array[]数组测试 ---------------------------
DEBUG [main] - ==> Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime FROM sys_user WHERE id IN ( ? , ? , ? )
DEBUG [main] - ==> Parameters: 1(Long), 1001(Long), 1006(Long)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-04-01 17:00:58.0
TRACE [main] - <== Row: 1006, 李四, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
DEBUG [main] - <== Total: 3
sysUserList3值为: [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@mybatis.tk, userInfo=管理员, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@mybatis.tk, userInfo=测试用户, headImg=null, createTime=Fri Apr 01 17:00:58 CST 2016], SysUser [id=1006, userName=李四, userPassword=123456, userEmail=test@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]]
testInsertSysUserByUser(...)函数运行的SQL日志如下所示,SQL日志是非常有必要仔细观看的,特别是在程序运行错误的时候。
------------------------------------ foreach标签,单参数List<T>集合批量插入用户信息测试 ------------------------------------
DEBUG [main] - ==> Preparing: INSERT INTO sys_user( user_name, user_password, user_email, user_info, head_img, create_time ) VALUES ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? )
DEBUG [main] - ==> Parameters: test0(String), 123456(String), test@mybatis.tk(String), null, null, null, test1(String), 123456(String), test@mybatis.tk(String), null, null, null, test2(String), 123456(String), test@mybatis.tk(String), null, null, null
DEBUG [main] - <== Updates: 3
userList值为: [SysUser [id=1025, userName=test0, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null], SysUser [id=1026, userName=test1, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null], SysUser [id=1027, userName=test2, userPassword=123456, userEmail=test@mybatis.tk, userInfo=null, headImg=null, createTime=null]]
祝各位小伙伴学习顺利、愉快。 Lucky.....