Mybatis动态SQL之使用foreach完成复杂查询

一、foreach概述

对于一些SQL语句中含有in条件、需要迭代条件集合来生产的情况，就需要使用foreach标签来实现SQL条件的迭代。foreach主要用在构建in条件中，它可以在SQL语句中迭代一个集合。它的属性主要有item、index、collection、separator、close、open

二、foreach迭代数组类型的入参

需求：根据用户角色列表来获取用户信息列表

public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
  select * from smbms_user where userRole in
    <foreach collection="array" item="roleIds" open="(" separator="," close=")">
      #{roleIds}
    </foreach>
</select>

<resultMap id="userMapByRole" type="User">
  <id property="id" column="id"/>
  <result property="userCode" column="userCode"/>
  <result property="userName" column="userName"/>
</resultMap> 

@Test
public void testGetUserByRoleId_foreach_array(){
    SqlSession sqlSession = null;
    List<User> userList = new ArrayList<>();
    Integer[] roleIds = {2, 3};
    try {
        sqlSession = MyBatisUtil.createSqlSession();
        userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        MyBatisUtil.closeSqlSession(sqlSession);
    }
    logger.debug("userList.size-->" + userList.size());
    for (User user:userList){
        logger.debug("user=====>id:" + user.getId()
                + ", userCode:" + user.getUserCode()
                + ", userName:" + user.getUserName()
                + ", userRole:" + user.getUserRole());
    }
}

 foreach标签的基本属性具体信息如下

• item：表示集合中每一个元素进行迭代时的别名
• index：指定一个名称，用于表示在迭代过程中，每次迭代到的位置
• open：表示该语句以什么开始，in条件语句是以“(”开始 
• separator：表示在每次迭代之间以什么符号作为分隔符，in条件语句以“,”作为分隔符
• close：表示该语句以什么结束，in条件语句是以“)”结束
• collection：该属性必需指定，不同情况下，该属性的值是不一样的
  • 若入参为单参数且参数类型是一个List，collection属性值为list
  • 若入参为单参数且参数类型是一个数组，collection属性值为array（此处传入参数Integer[] roleIds为数组类型，故此处collection属性值设为“array”）
  • 若传入参数为多参数，就需要把它们封装为一个Map进行处理

配置文件中的parameterType是可以不配置的，MyBatis会自动把它封装成一个Map传入。

三、foreach迭代List类型的入参

public List<User> getUserByRoleId_foreach_array(List<Integer> roleList);

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
  select * from smbms_user where userRole in
  <foreach collection="list" item="roleList" open="(" separator="," close=")">
    #{roleList}
  </foreach>
</select>

<resultMap id="userMapByRole" type="User">
  <id property="id" column="id"/>
  <result property="userCode" column="userCode"/>
  <result property="userName" column="userName"/>
</resultMap> 

测试关键代码如下

List<User> userList = new ArrayList<>();
List<Integer> roleList = new ArrayList<>();
roleList.add(2);
roleList.add(3);
try {
    sqlSession = MyBatisUtil.createSqlSession();
    userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleList);
}

foreach允许我们指定一个集合，指定开始和结束的字符，也可加入一个分隔符到迭代器中，并能够智能处理该分隔符，不会出现多余的分隔符

四、foreach迭代Map类型的入参

MyBatis入参若为多个参数，除了使用@Param注解，还可以把它们封装为一个Map进行处理

public List<User> getUserByRoleId_foreach_array(Map<String, Object> conditionMap);

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
  select * from smbms_user where gender=#{gender} and userRole in
  <foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
    #{roleMap}
  </foreach>
</select>

<resultMap id="userMapByRole" type="User">
  <id property="id" column="id"/>
  <result property="userCode" column="userCode"/>
  <result property="userName" column="userName"/>
</resultMap> 

测试方法中，把用户列表和性别两个参数封装成一个Map进行方法入参

List<User> userList = new ArrayList<>();
List<Integer> roleList = new ArrayList<>();
roleList.add(2);
roleList.add(3);
Map<String, Object> conditionMap = new HashMap<>();
conditionMap.put("gender", 1);
conditionMap.put("roleIds", roleList);
try {
    sqlSession = MyBatisUtil.createSqlSession();
    userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(conditionMap);
}

由于入参为Map，那么在SQL语句中需根据key分别获得相应的value值，比如SQL语句中的#{gender}获取的是Map中key为“gender”的性别条件，而collection="roleIds"获取的是Map中key为“roleIds”的角色id的集合。

单参数也可以封装为Map进行入参。MyBatis在进行参数入参的时候，都会把它封装成一个Map，而Map的key就是参数名，对应的参数值就是Map的value。若参数为集合的时候，Map的key会根据传入的是List还是数组对象相应的指定为“list”或者“array”。这样的好处是，我们可以自由指定Map的key，如下

public List<User> getUserByRoleId_foreach_array(Map<String, Object> roleMap);

<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
  select * from smbms_user where userRole in
  <foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
    #{roleMap}
  </foreach>
</select>

<resultMap id="userMapByRole" type="User">
  <id property="id" column="id"/>
  <result property="userCode" column="userCode"/>
  <result property="userName" column="userName"/>
</resultMap>

List<User> userList = new ArrayList<>();
List<Integer> roleList = new ArrayList<>();
roleList.add(2);
roleList.add(3);
Map<String, Object> roleMap = new HashMap<>();
roleMap.put("rKey", roleList);
try {
    sqlSession = MyBatisUtil.createSqlSession();
    userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleMap);
}

五、choose（when、otherwise）

对于某些查询需求，虽有多个查询条件，但不想应用所有的条件，只想选择其中一种情况下的查询结果。和Java中switch语句类似，MyBatis提供了choose元素来满足这种需求

需求：根据条件（用户名称、用户角色、用户编码）中的任意一个即可，若前三个条件都没提供，默认提供最后一个条件（创建时间，在指定的年份内）来完成查询操作 

public List<User> getUserList_choose(@Param("userName") String userName,
                                     @Param("userRole") Integer roleId,
                                     @Param("userCode") String userCode,
                                     @Param("creationDate") Date creationData);

<select id="getUserList_choose" resultType="User">
  select * from smbms_user where 1=1
  <choose>
    <when test="userName!=null and userName!=''">
      and userName like connect('%', #{userName}, '%')
    </when>
    <when test="userCode!=null and userCode!=''">
      and userCode like connect('%', #{userCode}, '%')
    </when>
    <when test="userRole!=null">
      and userRole = #{userRole}
    </when>
    <otherwise>
      and YEAR(creationDate) = YEAR(#{creationDate})
    </otherwise>
  </choose>
</select>

String userName="";
Integer roleId=null;
String userCode="";
Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2019-01-01");
userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName, roleId, userCode, creationDate);

choose一般与when、otherwise配套使用。

when元素：当其test属性中条件满足的时候，就会输出when元素中的内容。跟Java中switch效果差不多，同样按照条件的顺序来进行处理，当when中一旦有条件满足，就会跳出choose，即所有的when和otherwise条件中，只有一个条件会输出。

otherwise元素：当when中的所有条件都不满足的时候，就会自动输出otherwise元素中的内容

SQL语句后面加入“where 1=1”的原因是我们不需要再去处理多余的“and”。