在根据刘增辉老师编写的《Mybatis从入门到精通》一书中， "4.4 foreach用法"一节中， 对于foreach标签与Map<K, V>参数的案例，我自己扩展了一些，希望能够帮到小伙伴们。
项目目录结构如下图图一所示：
图一
在这里，为了节省时间，关于pojo层的 SysUser.java文件、 test层的 BaseMapperTest.java文件，我就不做展示。对于这2个文件的代码，可参见我上一篇博客： Mybatis/Ibatis框架--动态SQL， foreach标签与List、数组参数案例
1、SysUserMapper.java文件代码如下：
/**
* foreach标签，Map<K, V>参数，更新用户信息.
*
* @param userMap
* @return
*
* @author moon 2019/02/16 21:07
*/
Integer updateSysUserByUserMap(Map<String, Object> userMap);
/**
* foreach标签，Map<K, V>参数，批量插入用户信息.
*
* @param userMap
* @return
*
* @author moon 2019/02/16 17:37
*/
Integer insertSysUserMap4(@Param(value = "userMaps") Map<String, Object> userMap);
/**
* foreach标签，Map<K, V>参数，批量插入用户信息.
*
* @param userMap
* @return
*
* @author moon 2019/02/16 17:37
*/
Integer insertSysUserMap2(@Param(value = "userMaps") Map<String, Object> userMap);
/**
* foreach标签，Map<K, V>参数，批量插入用户信息.
*
* @param userMap
* @return
*
* @author moon 2019/02/16 17:37
*/
Integer insertSysUserMap(@Param(value = "userMaps") Map<String, Object> userMap);
/**
* foreach标签， foreach标签实现in集合(或数组)动态查询SysUser信息.
* 根据用户id集合查询
*
* @param idMap
* @param userNameMap
* @return
*
* @author moon 2019/02/16 16:07
*/
List<SysUser> selectSysUserByIdMapAndUsernameMap(@Param(value="idMaps") Map<String, Object> idMap, @Param(value = "userNameMaps") Map<String, Object> userNameMap);
/**
* foreach标签， foreach标签实现in集合(或数组)动态查询SysUser信息.
* 根据用户id集合查询
*
* @param idMap
* @return
*
* @author moon 2019/02/16 16:02
*/
List<SysUser> selectSysUserByIdMap(@Param(value = "idMaps") Map<String, Object> idMap);
// List<SysUser> selectSysUserByIdMap(Map<String, Object> idMap); // 没有通过 @Param注解指定参数名，则在xml配置中，collection中默认填写什么，我暂时未知! @moon 2019/02/16 17:52
2、SysUserMapper.xml文件代码如下：
<!-- foreach标签，Map<K, V>参数，更新SysUser信息. @moon 2019/02/16 21:08 -->
<update id="updateSysUserByUserMap" parameterType="java.util.Map">
UPDATE sys_user
SET
<foreach collection="_parameter" item="val" index="keyy" separator=",">
${keyy}=#{val}
</foreach>
WHERE id=#{id}
</update>
<!-- foreach标签，Map<K, V>参数，插入数据. @moon 2019/02/16 18:01 -->
<!--
注意: 1、验证 ${key}不可被 #{key}替代，否则报错. @moon 2019/02/16 19:19
2、对比 #{userMaps[${key}]}、#{value}
-->
<insert id="insertSysUserMap4" parameterType="java.util.Map">
INSERT INTO sys_user
<foreach collection="userMaps" item="keyy" index="key2" open="(" close=")" separator=",">
${key2}
</foreach>
VALUES
<foreach collection="userMaps" item="valuee" index="value_2" open="(" close=")" separator=",">
#{valuee}
</foreach>
</insert>
<!-- foreach标签，Map<K, V>参数，插入数据. @moon 2019/02/16 18:01 -->
<!--
注意: 1、验证 ${key}不可被 #{key}替代，否则报错. @moon 2019/02/16 19:19
2、#{userMaps[${key}]}
-->
<insert id="insertSysUserMap2" parameterType="java.util.Map">
INSERT INTO sys_user
<foreach collection="userMaps.keys" item="keyy" open="(" close=")" separator=",">
${keyy}
</foreach>
VALUES
<foreach collection="userMaps.values" item="valuee" open="(" close=")" separator=",">
#{valuee}
</foreach>
</insert>
<!-- foreach标签，Map<K, V>参数，插入数据. @moon 2019/02/16 18:01 -->
<!--
注意: 1、验证 ${key}不可被 #{key}替代，否则报错. @moon 2019/02/16 19:19
2、对比 #{userMaps[${key}]}、#{value}
-->
<insert id="insertSysUserMap" parameterType="java.util.Map">
INSERT INTO sys_user
<foreach collection="userMaps.keys" item="key" open="(" close=")" separator=",">
${key}
</foreach>
VALUES
<foreach collection="userMaps.keys" item="key" open="(" close=")" separator=",">
#{userMaps[${key}]}
</foreach>
</insert>
<!-- foreach标签，多个Map<K, T>参数，实现IN集合，动态插入数据. @moon 2019/02/16 16:17 -->
<!--
讲解：在传递Map<K, V>参数时，foreach标签中的index参数可省略.
-->
<select id="selectSysUserByIdMapAndUsernameMap" 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="idMaps" open="(" close=")" separator="," item="id" index="idMaps.key">
#{id}
</foreach>
OR user_name IN
<foreach collection="userNameMaps" open="(" close=")" separator="," item="userName" index="userNameMaps.key">
#{userName}
</foreach>
</select>
<!-- foreach标签，单个Map<K, T>参数，实现IN集合，动态插入数据. @moon 2019/02/16 16:16 -->
<!--
讲解：在传递Map<K, V>参数时，foreach标签中的index参数可省略.
-->
<select id="selectSysUserByIdMap" 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="idMaps" open="(" close=")" separator="," item="id" index="idMaps.key">
#{id}
</foreach>
</select>
在SysUserMapper.xml文件中，updateSysUserByUserMap 更新语句SQL可知， 使用foreach标签，传递的参数为Map<K,V>集合时， foreach标签的collection属性默认值为：_parameter .
3、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标签，Map<K, V>参数， 更新用户信息.
*
* @author moon 2019/02/16 21:11
*/
@Test
public void testUpdateUserByUserMap() {
// 创建sqlSession
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
Map<String, Object> userMap = new HashMap<String, Object>();
// userMap.put("idd", 1006L); // 这种写法报错，通过该测试可知 foreach标签+Map<K,V>参数 更新信息时，有缺陷! @moon 2019/02/16 21:27
userMap.put("id", 1006L);
userMap.put("user_email", "test123@mybatis.tk");
userMap.put("user_password", "12345678");
// 更新 SysUser信息.
Integer result = userMapper.updateSysUserByUserMap(userMap);
System.out.println("result值为： " + result);
Assert.assertEquals(new Integer(1), result);
// 查询更新后的SysUser信息
SysUser sysUser = userMapper.selectSysUserById(1006L);
System.out.println("sysUser值为： " + sysUser.toString());
Assert.assertEquals("12345678", sysUser.getUserPassword());
} finally {
// 提交事务
// sqlSession.commit();
// 对事务进行回滚
sqlSession.rollback();
// 关闭 sqlSession
sqlSession.close();
}
}
/**
* foreach标签，List<T>集合参数，实现批量插入用户信息.
* foreach标签，Map<K, V>集合 参数，实现插入用户信息.
*
* @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标签，单参数Map<K, V>集合批量插入用户信息测试 ------------------------------------");
Map<String, Object> userMap = new HashMap<String, Object>();
userMap.put("user_name", "王安石");
userMap.put("user_password", "123456");
userMap.put("user_email", "Wangym@mybatis.tk");
userMap.put("user_info", "Map<K, V>参数，插入数据测试.");
userMap.put("head_img", new byte[] {0, 1, 2, 3});
Integer result2 = userMapper.insertSysUserMap(userMap);
Assert.assertEquals(new Integer(1), result2);
SysUser sysUser2 = new SysUser();
sysUser2.setUserName("王安石");
System.out.println("验证是否插入了数据：" + userMapper.selectSysUserByIdOrUserName(sysUser2));
System.out.println("------------------------------- Map参数，另一种INSERT传值方式--insertSysUserMap2(...)函数. -------------------------------");
Map<String, Object> userMap2 = new HashMap<String, Object>();
userMap2.put("user_name", "王小二");
userMap2.put("user_password", "123456");
userMap2.put("user_email", "Wangxe@mybatis.tk");
userMap2.put("user_info", "Map<K, V>参数，插入数据测试.");
userMap2.put("head_img", new byte[] {0, 1, 2, 3});
Integer result3 = userMapper.insertSysUserMap2(userMap2);
Assert.assertEquals(new Integer(1), result3);
SysUser sysUser3 = new SysUser();
sysUser3.setUserName("王小二");
System.out.println("验证是否插入了数据：" + userMapper.selectSysUserByIdOrUserName(sysUser3));
System.out.println("------------------------------- Map参数，另一种INSERT传值方式--insertSysUserMap4(...)函数. -------------------------------");
Map<String, Object> userMap4 = new HashMap<String, Object>();
userMap4.put("user_name", "李时珍");
userMap4.put("user_password", "123456");
userMap4.put("user_email", "Lisi@mybatis.tk");
userMap4.put("user_info", "Map<K, V>参数，插入数据测试.");
userMap4.put("head_img", new byte[] {0, 1, 2, 3});
Integer result5 = userMapper.insertSysUserMap4(userMap4);
Assert.assertEquals(new Integer(1), result5);
SysUser sysUser5 = new SysUser();
sysUser5.setUserName("李时珍");
System.out.println("验证是否插入了数据：" + userMapper.selectSysUserByIdOrUserName(sysUser5));
} 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标签，单参数Map<K, V>集合测试 ---------------------------");
// 创建 Map<K, V> idMap集合
Map<String, Object> idMap = new HashMap<String, Object>();
idMap.put("id1", 1L);
idMap.put("id2", 1005L);
List<SysUser> sysUserMap = userMapper.selectSysUserByIdMap(idMap);
System.out.println("sysUserMap值为： " + sysUserMap.toString());
// 创建 Map<K, V> userNameMap集合
Map<String, Object> userNameMap = new HashMap<String, Object>();
System.out.println("--------------------------- foreach标签，多参数Map<K, V>集合测试 ---------------------------");
userNameMap.put("userName1", "test");
userNameMap.put("userName2", "李四");
userNameMap.put("userName3", "test01");
List<SysUser> sysUserMap2 = userMapper.selectSysUserByIdMapAndUsernameMap(idMap, userNameMap);
System.out.println("sysUserMap2值为： " + sysUserMap2.toString());
System.out.println("sysUserMap2的数量为： " + sysUserMap2.size());
} finally {
// 提交事务
// sqlSession.commit();
// 回滚事务
sqlSession.rollback();
// 关闭 sqlSession
sqlSession.close();
}
}
}
4、testSelectSysUserByParams(...)函数运行的SQL日志如下所示，SQL日志是非常有必要仔细观看的，特别是在程序运行错误的时候。
--------------------------- foreach标签，单参数Map<K, V>集合测试 ---------------------------
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: 1005(Long), 1(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: 1005, 张三, 123456, Zhangsan@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
DEBUG [main] - <== Total: 2
sysUserMap值为： [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=1005, userName=张三, userPassword=123456, userEmail=Zhangsan@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]]
--------------------------- foreach标签，多参数Map<K, V>集合测试 ---------------------------
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: 1005(Long), 1(Long), 李四(String), test01(String), test(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: 1005, 张三, 123456, Zhangsan@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
TRACE [main] - <== Row: 1006, 李四, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
DEBUG [main] - <== Total: 5
sysUserMap2值为： [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=1005, userName=张三, userPassword=123456, userEmail=Zhangsan@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 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]]
sysUserMap2的数量为： 5
testInsertSysUserByUser(...)函数运行的SQL日志如下所示，SQL日志是非常有必要仔细观看的，特别是在程序运行错误的时候。
------------------------------------ foreach标签，单参数Map<K, V>集合批量插入用户信息测试 ------------------------------------
DEBUG [main] - ==> Preparing: INSERT INTO sys_user ( user_password , user_email , user_info , user_name , head_img ) VALUES ( ? , ? , ? , ? , ? )
DEBUG [main] - ==> Parameters: 123456(String), Wangym@mybatis.tk(String), Map<K, V>参数，插入数据测试.(String), 王安石(String), [B@76a4d6c(byte[])
DEBUG [main] - <== Updates: 1
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 1=1 and user_name = ?
DEBUG [main] - ==> Parameters: 王安石(String)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1035, 王安石, 123456, Wangym@mybatis.tk, <<BLOB>>, <<BLOB>>, null
DEBUG [main] - <== Total: 1
验证是否插入了数据：SysUser [id=1035, userName=王安石, userPassword=123456, userEmail=Wangym@mybatis.tk, userInfo=Map<K, V>参数，插入数据测试., headImg=[0, 1, 2, 3], createTime=null]
------------------------------- Map参数，另一种INSERT传值方式--insertSysUserMap2(...)函数. -------------------------------
DEBUG [main] - ==> Preparing: INSERT INTO sys_user ( user_password , user_email , user_info , user_name , head_img ) VALUES ( ? , ? , ? , ? , ? )
DEBUG [main] - ==> Parameters: 123456(String), Wangxe@mybatis.tk(String), Map<K, V>参数，插入数据测试.(String), 王小二(String), [B@10b48321(byte[])
DEBUG [main] - <== Updates: 1
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 1=1 and user_name = ?
DEBUG [main] - ==> Parameters: 王小二(String)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1036, 王小二, 123456, Wangxe@mybatis.tk, <<BLOB>>, <<BLOB>>, null
DEBUG [main] - <== Total: 1
验证是否插入了数据：SysUser [id=1036, userName=王小二, userPassword=123456, userEmail=Wangxe@mybatis.tk, userInfo=Map<K, V>参数，插入数据测试., headImg=[0, 1, 2, 3], createTime=null]
------------------------------- Map参数，另一种INSERT传值方式--insertSysUserMap4(...)函数. -------------------------------
DEBUG [main] - ==> Preparing: INSERT INTO sys_user ( user_password , user_email , user_info , user_name , head_img ) VALUES ( ? , ? , ? , ? , ? )
DEBUG [main] - ==> Parameters: 123456(String), Lisi@mybatis.tk(String), Map<K, V>参数，插入数据测试.(String), 李时珍(String), [B@49e202ad(byte[])
DEBUG [main] - <== Updates: 1
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 1=1 and user_name = ?
DEBUG [main] - ==> Parameters: 李时珍(String)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1038, 李时珍, 123456, Lisi@mybatis.tk, <<BLOB>>, <<BLOB>>, null
DEBUG [main] - <== Total: 1
验证是否插入了数据：SysUser [id=1038, userName=李时珍, userPassword=123456, userEmail=Lisi@mybatis.tk, userInfo=Map<K, V>参数，插入数据测试., headImg=[0, 1, 2, 3], createTime=null]
testUpdateUserByUserMap(...)函数运行的SQL日志如下所示，SQL日志是非常有必要仔细观看的，特别是在程序运行错误的时候。
DEBUG [main] - ==> Preparing: UPDATE sys_user SET user_email=? , user_password=? , id=? WHERE id=?
DEBUG [main] - ==> Parameters: test123@mybatis.tk(String), 12345678(String), 1006(Long), 1006(Long)
DEBUG [main] - <== Updates: 1
result值为： 1
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 = ?
DEBUG [main] - ==> Parameters: 1006(Long)
TRACE [main] - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <== Row: 1006, 李四, 12345678, test123@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-02-15 17:40:41.0
DEBUG [main] - <== Total: 1
sysUser值为： SysUser [id=1006, userName=李四, userPassword=12345678, userEmail=test123@mybatis.tk, userInfo=普通用户, headImg=[1, 2, 3], createTime=Fri Feb 15 17:40:41 CST 2019]
祝各位小伙伴学习愉快. Lucky........
690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
