我们在写复杂的SQL语句时，稍不注意就可能出现错误。

而动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach标签，可组合成非常灵活的SQL语句。

既能提高开发人员的效率，又可以减少出错。

1.foreach标签

1.1foreach实现in集合

• java代码

public void testForEach() {
    List<String> idList = new ArrayList<>();
    idList.add("1");
    idList.add("2");
    idList.add("3");
    List<User> users = userMapper.selectUserByUserIdList(idList);
    users.forEach(System.out::println);
}

• SQL：

<select id="selectUserByUserIdList" resultType="cn.no7player.model.User">
  SELECT
      id, name, age, password
  FROM
      user
  WHERE id IN
    <foreach collection="list" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

说明：如果传入的是List集合，则<foreach>中的 collection默认是"list"
<foreach>标签中collection属性

如果在selectUserByUserIdList( ) 传参时用了 @Param("xxx") 注解，则collection为"xxx"

java代码：

List<User> selectUserByUserIdList(@Param("idLists") List<String> idList);

SQL语句：

<select id="selectUserByUserIdList" resultType="cn.no7player.model.User">
  SELECT
      id, name, age, password
  FROM
      user
  WHERE id IN
    <foreach collection="idLists" open="(" close=")" separator="," item="id" index="i">
        #{id}
    </foreach>
  </select>

1.2foreach实现批量插入

java代码：

public void testForEach2() {
        List<User> userList = new ArrayList<>();
        userList.add(new User(0,"GEM", 28, "999999"));
        userList.add(new User(0,"JAY", 36, "777777"));
        int i = userMapper.insertUsers(userList);
        System.out.println(i);
}

SQL语句：

<insert id="insertUsers" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO
      user(name, age, password)
  VALUES
  <foreach collection="list" separator="," item="user">
      (
      #{user.name},
      #{user.age},
      #{user.pwd}
      )
  </foreach>
</insert>