MyBatis教程：foreach标签生成动态的Sql

2019-07-1722:10:47后端程序开发Comments3,535 views字数 5983阅读模式

如何使用foreach标签生成动态的Sql，主要包含以下3个场景：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

  1. foreach 实现in集合
  2. foreach 实现批量插入
  3. foreach 实现动态update

1. foreach 实现in集合

假设有这样1个需求：根据传入的用户id集合查询出所有符合条件的用户，此时我们需要使用到Sql中的IN，如 id in (1,1001)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

首先，我们在接口SysUserMapper中添加如下方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

/**
 * 根据用户id集合查询用户
 *
 * @param idList
 * @return
 */
List<SysUser> selectByIdList(List<Long> idList);

然后在对应的SysUserMapper.xml中添加如下代码：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

<select id="selectByIdList" resultType="com.zwwhnly.mybatisaction.model.SysUser">
    SELECT id,
    user_name,
    user_password,
    user_email,
    create_time
    FROM sys_user
    WHERE id IN
    <foreach collection="list" open="(" close=")" separator=","
             item="id" index="i">
        #{id}
    </foreach>
</select>

最后，在SysUserMapperTest测试类中添加如下测试方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

@Test
public void testSelectByIdList() {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        List<Long> idList = new ArrayList<Long>();
        idList.add(1L);
        idList.add(1001L);

        List<SysUser> userList = sysUserMapper.selectByIdList(idList);
        Assert.assertEquals(2, userList.size());
    } finally {
        sqlSession.close();
    }
}

运行测试代码，测试通过，输出日志如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id IN ( ? , ? )文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Parameters: 1(Long), 1001(Long)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_time文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

TRACE [main] - <== Row: 1, admin, 123456, admin@mybatis.tk, 2019-06-27 18:21:07.0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

TRACE [main] - <== Row: 1001, test, 123456, test@mybatis.tk, 2019-06-27 18:21:07.0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - <== Total: 2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

通过日志会发现，foreach元素中的内容最终生成的Sql语句为(1,1001)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

foreach包含属性讲解：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

  • open：整个循环内容开头的字符串。
  • close：整个循环内容结尾的字符串。
  • separator：每次循环的分隔符。
  • item：从迭代对象中取出的每一个值。
  • index：如果参数为集合或者数组，该值为当前索引值，如果参数为Map类型时，该值为Map的key。
  • collection：要迭代循环的属性名。

也许有人会好奇，为什么collection的值是list？该值该如何设置呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

上面的例子中只有一个集合参数，我们把collection属性的值设置为了list，其实也可以写成collection，为什么呢？让我们看下DefaultSqlSession中的默认处理逻辑：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

private Object wrapCollection(Object object) {
    DefaultSqlSession.StrictMap map;
    if (object instanceof Collection) {
        map = new DefaultSqlSession.StrictMap();
        map.put("collection", object);
        if (object instanceof List) {
            map.put("list", object);
        }

        return map;
    } else if (object != null && object.getClass().isArray()) {
        map = new DefaultSqlSession.StrictMap();
        map.put("array", object);
        return map;
    } else {
        return object;
    }
}

虽然使用默认值，代码也可以正常运行，但还是推荐使用@Param来指定参数的名字，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

List<SysUser> selectByIdList(@Param("idList") List<Long> idList);
<foreach collection="idList" open="(" close=")" separator=","
         item="id" index="i">
    #{id}
</foreach>

如果参数是一个数组参数，collection可以设置为默认值array，看了上面的源码，应该不难理解。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

/**
 * 根据用户id数组查询用户
 *
 * @param idArray
 * @return
 */
List<SysUser> selectByIdArray(Long[] idArray);
<select id="selectByIdArray" resultType="com.zwwhnly.mybatisaction.model.SysUser">
    SELECT id,
    user_name,
    user_password,
    user_email,
    create_time
    FROM sys_user
    WHERE id IN
    <foreach collection="array" open="(" close=")" separator=","
             item="id" index="i">
        #{id}
    </foreach>
</select>

虽然使用默认值，代码也可以正常运行，但还是推荐使用@Param来指定参数的名字，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

List<SysUser> selectByIdArray(@Param("idArray")Long[] idArray);
<foreach collection="idArray" open="(" close=")" separator=","
         item="id" index="i">
    #{id}
</foreach>

2. foreach 实现批量插入

假设有这样1个需求：将传入的用户集合批量写入数据库。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

首先，我们在接口SysUserMapper中添加如下方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

/**
 * 批量插入用户信息
 *
 * @param userList
 * @return
 */
int insertList(List<SysUser> userList);

然后在对应的SysUserMapper.xml中添加如下代码：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

<insert id="insertList" 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>

最后，在SysUserMapperTest测试类中添加如下测试方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

@Test
public void testInsertList() {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        List<SysUser> sysUserList = new ArrayList<SysUser>();
        for (int i = 0; i < 2; i++) {
            SysUser sysUser = new SysUser();
            sysUser.setUserName("test" + i);
            sysUser.setUserPassword("123456");
            sysUser.setUserEmail("test@mybatis.tk");

            sysUserList.add(sysUser);
        }

        int result = sysUserMapper.insertList(sysUserList);

        for (SysUser sysUser : sysUserList) {
            System.out.println(sysUser.getId());
        }

        Assert.assertEquals(2, result);
    } finally {
        sqlSession.close();
    }
}

运行测试代码，测试通过，输出日志如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Preparing: INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time) VALUES (?,?,?,?,?,?) , (?,?,?,?,?,?)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

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文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - <== Updates: 2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

1035文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

1036文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

3. foreach 实现动态update

假设有这样1个需求：根据传入的Map参数更新用户信息。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

首先，我们在接口SysUserMapper中添加如下方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

/**
 * 通过Map更新列
 *
 * @param map
 * @return
 */
int updateByMap(Map<String, Object> map);

然后在对应的SysUserMapper.xml中添加如下代码：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

<update id="updateByMap">
    UPDATE sys_user
    SET
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    WHERE id = #{id}
</update>

最后，在SysUserMapperTest测试类中添加如下测试方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

@Test
public void testUpdateByMap() {
    SqlSession sqlSession = getSqlSession();

    try {
        SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", 1L);
        map.put("user_email", "test@mybatis.tk");
        map.put("user_password", "12345678");

        Assert.assertEquals(1, sysUserMapper.updateByMap(map));

        SysUser sysUser = sysUserMapper.selectById(1L);
        Assert.assertEquals("test@mybatis.tk", sysUser.getUserEmail());
        Assert.assertEquals("12345678", sysUser.getUserPassword());
    } finally {
        sqlSession.close();
    }
}

运行测试代码，测试通过，输出日志如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Preparing: UPDATE sys_user SET user_email = ? , user_password = ? , id = ? WHERE id = ?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Parameters: test@mybatis.tk(String), 12345678(String), 1(Long), 1(Long)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - <== Updates: 1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id = ?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - ==> Parameters: 1(Long)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

TRACE [main] - <== Columns: id, user_name, user_password, user_email, create_time文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

TRACE [main] - <== Row: 1, admin, 12345678, test@mybatis.tk, 2019-06-27 18:21:07.0文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

DEBUG [main] - <== Total: 1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

上面示例中，collection使用的是默认值_parameter，也可以使用@Param指定参数名字，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html

int updateByMap(@Param("userMap") Map<String, Object> map);
<update id="updateByMap">
    UPDATE sys_user
    SET
    <foreach collection="userMap" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    WHERE id = #{userMap.id}
</update>
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/14064.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/bc/14064.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定