MyBatis-14MyBatis动态SQL之【foreach】

2021-08-17 10:58:52 浏览数 (1)
  • 概述
  • foreach实现in集合
      • 1.需求
      • 2.UserMapper接口增加接口方法
      • 3.UserMapper.xml增加动态SQL
      • 4.单元测试
  • foreach实现批量插入
  • 前提
      • 1.需求
      • 2.UserMapper接口增加接口方法
      • 3.UserMapper.xml增加动态SQL
      • 4.单元测试
  • foreach实现动态update
    • 不使用@Param注解指定参数名的情况
      • 1.UserMapper接口
      • 2.UserMapper.xml动态SQL
    • 使用@Param注解指定参数名的情况
      • 1.UserMapper接口
      • 2.UserMapper.xml动态SQL
      • 3,单元测试

概述

SQL语句中有时候会使用IN关键字，比如 id in (1,2,3,4)。

虽然可以使用{ids}方式直接获取值，但{ids}不能防止SQL注入， 想要避免SQL注入就需要用#{}的方式，这时就要配合使用foreach标签来满足需求.

foreach可以对数组、Map或者实现了Iterable接口（比如List、Set）的对象进行遍历。 数组在处理的时候可以转换为List对象。 因此foreach遍历的对象可以分为两大类

  • Iterable类型
  • Map类型。

这两种类型在遍历循环时情况是不一样的，我们通过如下3个示例来讲解foreach的用法

foreach实现in集合

foreach实现in集合（或者数组）是最简单和常见的一种情况

1.需求

根据id集合查出所有符合条件的用户

2.UserMapper接口增加接口方法

代码语言：javascript复制
/**
     * 
     * 
     * @Title: selectSysUserByIdList
     * 
     * @Description: 根据用户ID集合查询用户
     * 
     * @param ids
     * @return
     * 
     * @return: List
     */
    List selectSysUserByIdList(List ids);

3.UserMapper.xml增加动态SQL

代码语言：javascript复制
    "selectSysUserByIdList" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        WHERE id in 
            "list" item="userId" open="(" close=")" separator="," index="i">
                #{userId}

foreach的属性

  • collection 必填，值为要迭代循环的属性名。 情况有很多种
  • item 变量名，值为从迭代对象中取出的每一个值
  • index 索引的属性名，在集合数组请鲁昂下为当前索引值，的那个迭代循环的对象是Map类型时，这个值为Map的key(键值）
  • open 整个循环内容开头的字符串
  • close 整个循环内容结尾的字符串
  • separator 每次循环的分隔符

4.单元测试

代码语言：javascript复制
    @Test
    public void selectSysUserByIdListTest() {
        logger.info("selectSysUserByIdListTest");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 模拟idList
            List idList = new ArrayList();
            idList.add(1L);
            idList.add(1001L);
            // 调用接口方法
            List userList = userMapper.selectSysUserByIdList(idList);
            // userList不为空
            Assert.assertNotNull(userList);
            // userList > 0
            Assert.assertTrue(userList.size() > 0);
            // 期望返回2条数据，符合数据库中记录
            Assert.assertEquals(2, userList.size());
            logger.info(userList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }

日志

代码语言：javascript复制
2018-04-23 01:49:29,686  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-23 01:49:29,692  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-23 01:49:29,696  INFO [main] (UserMapperTest.java:729) - selectSysUserByIdListTest
2018-04-23 01:49:30,203 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE id in ( ? , ? ) 
2018-04-23 01:49:30,267 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long), 1001(Long)
2018-04-23 01:49:30,295 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-23 01:49:30,296 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <>, <>, 2018-04-13 21:12:47.0
2018-04-23 01:49:30,304 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1001, artisan, 123456, test@artisan.com, <>, <>, 2018-04-13 21:12:47.0
2018-04-23 01:49:30,305 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 2
2018-04-23 01:49:30,306  INFO [main] (UserMapperTest.java:747) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=测试用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-23 01:49:30,314  INFO [main] (UserMapperTest.java:752) - sqlSession close successfully

foreach实现批量插入

前提

如果数据库支持批量插入，就可以通过foreach实现。 批量插入是SQL-92新增的特性，目前支持的数据库有DB2、SQL Server 2008 、PostgreSql8.2 、MySQL、SQLite3.7.11 以及H2.

语法

代码语言：javascript复制
insert into tablename(column-a,[column-b,....])
    values('value-1a',['value-1b',...]),
    ('value-2a',['value-2b',...]),
    ('value-3a',['value-3b',...]),
    ......

从上述语法部分可以看到，后面是一个值的循环，因此可以通过foreach来实现循环插入。

1.需求

批量插入用户

2.UserMapper接口增加接口方法

代码语言：javascript复制
/**
     * 
     * 
     * @Title: insertSysUserList
     * 
     * @Description: 批量新增用户
     * 
     * @param sysUserList
     * @return
     * 
     * @return: int
     */
    int insertSysUserList(List sysUserList);

3.UserMapper.xml增加动态SQL

代码语言：javascript复制
"insertSysUserList"  keyProperty="id" useGeneratedKeys="true">
        insert into sys_user(
            user_name, 
            user_password, 
            user_email, 
            user_info, 
            head_img, 
            create_time)
        values
        <foreach collection="list" item="sysUser" separator=",">
            (
                #{sysUser.userName}, 
                #{sysUser.userPassword}, 
                #{sysUser.userEmail}, 
                #{sysUser.userInfo}, 
                #{sysUser.headImg, jdbcType=BLOB},
                #{sysUser.createTime, jdbcType=TIMESTAMP}
            )
        foreach>

通过item指定了循环变量名后，在引用值的时候使用的是“属性.属性”的方式，如上所示sysUser.userName。

4.单元测试

代码语言：javascript复制
@Test
    public void insertSysUserListTest() {
        logger.info("insertSysUserListTest");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            // 模拟userList
            List userList = new ArrayList();

            for (int i = 0; i < 5; i  ) {
                SysUser sysUser = new SysUser();
                sysUser.setUserName("artisanTest_"   i);
                sysUser.setUserPassword("123456_"   i);
                sysUser.setUserEmail("artisan_"   i   "@artisan.com");
                sysUser.setUserInfo("测试用户"   i);
                // 模拟头像
                sysUser.setHeadImg(new byte[] { 1, 2, 3 });
                sysUser.setCreateTime(new Date());

                // 添加到SysUser
                userList.add(sysUser);
            }

            // 新增用户 ,返回受影响的行数
            int result = userMapper.insertSysUserList(userList);
            // 返回批量的自增主键 配合 keyProperty="id" useGeneratedKeys="true" 这两个属性
            for (SysUser sysUser : userList) {
                logger.info(sysUser.getId());
            }
            // 只插入一条数据 ,期望是5
            Assert.assertEquals(5, result);

            // 重新查询
            List sysUserList = userMapper.selectAll();
            // 根据数据库之前的2条记录，加上本次新增的5条（虽未提交但还是在一个会话中，所以可以查询的到）
            Assert.assertNotNull(sysUserList);
            Assert.assertEquals(7, sysUserList.size());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 为了保持测试数据的干净，这里选择回滚
            // 由于默认的sqlSessionFactory.openSession()是不自动提交的
            // 除非显式的commit，否则不会提交到数据库
            sqlSession.rollback();
            logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成");

            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }

日志

代码语言：javascript复制
2018-04-23 15:31:28,500  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-23 15:31:28,505  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-23 15:31:28,508  INFO [main] (UserMapperTest.java:761) - insertSysUserListTest
2018-04-23 15:31:29,091 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: insert into sys_user( user_name, user_password, user_email, user_info, head_img, create_time) values ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) 
2018-04-23 15:31:29,183 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: artisanTest_0(String), 123456_0(String), artisan_0@artisan.com(String), 测试用户0(String), java.io.ByteArrayInputStream@2f0496f0(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_1(String), 123456_1(String), artisan_1@artisan.com(String), 测试用户1(String), java.io.ByteArrayInputStream@56517ead(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_2(String), 123456_2(String), artisan_2@artisan.com(String), 测试用户2(String), java.io.ByteArrayInputStream@53bc21(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_3(String), 123456_3(String), artisan_3@artisan.com(String), 测试用户3(String), java.io.ByteArrayInputStream@79641ab1(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_4(String), 123456_4(String), artisan_4@artisan.com(String), 测试用户4(String), java.io.ByteArrayInputStream@1b1498ba(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp)
2018-04-23 15:31:29,190 DEBUG [main] (BaseJdbcLogger.java:145) - <==    Updates: 5
2018-04-23 15:31:29,191  INFO [main] (UserMapperTest.java:789) - 1032
2018-04-23 15:31:29,191  INFO [main] (UserMapperTest.java:789) - 1033
2018-04-23 15:31:29,191  INFO [main] (UserMapperTest.java:789) - 1034
2018-04-23 15:31:29,192  INFO [main] (UserMapperTest.java:789) - 1035
2018-04-23 15:31:29,192  INFO [main] (UserMapperTest.java:789) - 1036
2018-04-23 15:31:29,196 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: select a.id , a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime from sys_user a 
2018-04-23 15:31:29,197 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 
2018-04-23 15:31:29,231 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-23 15:31:29,231 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <>, <>, 2018-04-13 21:12:47.0
2018-04-23 15:31:29,243 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1001, artisan, 123456, test@artisan.com, <>, <>, 2018-04-13 21:12:47.0
2018-04-23 15:31:29,247 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1032, artisanTest_0, 123456_0, artisan_0@artisan.com, <>, <>, 2018-04-23 15:31:29.0
2018-04-23 15:31:29,248 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1033, artisanTest_1, 123456_1, artisan_1@artisan.com, <>, <>, 2018-04-23 15:31:29.0
2018-04-23 15:31:29,249 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1034, artisanTest_2, 123456_2, artisan_2@artisan.com, <>, <>, 2018-04-23 15:31:29.0
2018-04-23 15:31:29,250 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1035, artisanTest_3, 123456_3, artisan_3@artisan.com, <>, <>, 2018-04-23 15:31:29.0
2018-04-23 15:31:29,251 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1036, artisanTest_4, 123456_4, artisan_4@artisan.com, <>, <>, 2018-04-23 15:31:29.0
2018-04-23 15:31:29,251 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 7
2018-04-23 15:31:29,255  INFO [main] (UserMapperTest.java:807) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成
2018-04-23 15:31:29,256  INFO [main] (UserMapperTest.java:810) - sqlSession close successfully

foreach实现动态update

这部分我们主要介绍当参数类型是Map的时候，foreach如何实现动态UPDATE

当参数是Map类型的时候，foreach标签的index属性值对应的不是索引值，而是Map中的key, 利用这个key就可以动态实现UPDATE了。

不使用@Param注解指定参数名的情况

1.UserMapper接口

代码语言：javascript复制
void updateSysUserByMap(Map<String, Object> map);

这里没有使用@Parma注解指定参数名，因而MyBatis在内部的上线文中使用默认值 _parameter 最为该参数的key ,所以xml中也必须使用_parameter。

2.UserMapper.xml动态SQL

代码语言：javascript复制
id="updateSysUserByMap">
        update sys_user 
        set 
        "_parameter"  item="value"  index="key"  separator=",">
            ${key} = #{value}
        
        where id = #{id}

这里的key作为列名，对应的值作为该列的值，通过foreach将需要更新的字段拼接在SQL语句中。

使用@Param注解指定参数名的情况

1.UserMapper接口

代码语言：javascript复制
void updateSysUserByMapWithParam(@Param("userMap") Map<String, Object> map);

2.UserMapper.xml动态SQL

代码语言：javascript复制
id="updateSysUserByMapWithParam">
        update sys_user 
        set 
        "userMap"  item="value"  index="key"  separator=",">
            ${key} = #{value}
        
        where id = #{userMap.id}

3,单元测试

代码语言：javascript复制
@Test
    public void updateSysUserByMapTest() {
        logger.info("updateSysUserByMapTest");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            // 模拟Map
            Map<String, Object> userMap = new HashMap<String, Object>();
            // 查询条件，同时也是where后面的更新字段， 必须存在
            userMap.put("id", 1L);
            // 更新其他字段
            userMap.put("user_email", "map@artisan.com");
            userMap.put("user_name", "ARTISAN_ADMIN");

            // 调用接口,更新数据
            // userMapper.updateSysUserByMap(userMap);
            // 或者
            userMapper.updateSysUserByMapWithParam(userMap);

            // 根据当前id 查询用户
            SysUser sysUser = userMapper.selectSysUserById(1L);
            Assert.assertEquals("map@artisan.com", sysUser.getUserEmail());
            Assert.assertEquals("ARTISAN_ADMIN", sysUser.getUserName());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 为了保持测试数据的干净，这里选择回滚
            // 由于默认的sqlSessionFactory.openSession()是不自动提交的
            // 除非显式的commit，否则不会提交到数据库
            sqlSession.rollback();
            logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成");

            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }
代码语言：javascript复制
2018-04-23 16:27:06,658  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-23 16:27:06,661  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-23 16:27:06,664  INFO [main] (UserMapperTest.java:820) - updateSysUserByMapTest
2018-04-23 16:27:07,243 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: update sys_user set id = ? , user_name = ? , user_email = ? where id = ? 
2018-04-23 16:27:07,319 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long), ARTISAN_ADMIN(String), map@artisan.com(String), 1(Long)
2018-04-23 16:27:07,325 DEBUG [main] (BaseJdbcLogger.java:145) - <==    Updates: 1
2018-04-23 16:27:07,327 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 
2018-04-23 16:27:07,328 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long)
2018-04-23 16:27:07,364 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-23 16:27:07,365 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, ARTISAN_ADMIN, 123456, map@artisan.com, <>, <>, 2018-04-13 21:12:47.0
2018-04-23 16:27:07,370 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-23 16:27:07,373  INFO [main] (UserMapperTest.java:852) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成
2018-04-23 16:27:07,374  INFO [main] (UserMapperTest.java:855) - sqlSession close successfully
javascript sql xml 单元测试 编程算法

0 人点赞