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”。

巴特西