where语句用使用choose-when-otherwise
接上文:
有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。而使用if标签时,只要test中的表达式为 true,就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if标签是与(and)的关系,而 choose 是或(or)的关系。
choose标签是按顺序判断其内部when标签中的test条件出否成立,如果有一个成立,则 choose 结束。当 choose 中所有 when 的条件都不满则时,则执行 otherwise 中的sql。类似于Java 的 switch 语句,choose 为 switch,when 为 case,otherwise 则为 default。
需求:
查询sys_user表:当参数id有值的时候优先使用id查询,当id没有值的时候判断用户名是否有值,如果有值就用用户名模糊查询,如果用户名也没有值,就使sql查询无结果。
在UserMapper.java中添加如下代码:
/**
* 根据用户id或者用户名查询
* @param user
* @return
*/
SysUser selectByIdOrUserName(SysUser user);
在UserMapper.xml中添加如下配置:
<select id="selectByIdOrUserName" resultType="tk.mybatis.simple.model.SysUser">
SELECT * from sys_user
where 1 = 1
<choose>
<when test="id != null">
AND id = #{id}
</when>
<when test="userName != null and userName != ''">
AND user_name = #{userName}
</when>
<otherwise>
AND 1 = 2
</otherwise>
</choose>
</select>
注意:在以上的查询中,如果没有otherwise这个限制条件,所有的用户都会被查询出来,因为我们在接口中使用了SysUser作为返回值,所以当实际查询结果是多个时就会报错。添加otherwise条件后,由于where条件不满足,因此在这种情况下就查询不到结果。
测试代码如下:
@Test
public void testSelectByIdOrUserName(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser user = new SysUser();
user.setId(1L);
user.setUserName("admin");
SysUser u1 = userMapper.selectByIdOrUserName(user);
Assert.assertNotNull(u1);
user.setId(null);
SysUser u2 = userMapper.selectByIdOrUserName(user);
Assert.assertNotNull(u2);
user.setUserName(null);
SysUser u3 = userMapper.selectByIdOrUserName(user);
Assert.assertNull(u3);
}finally {
}
}
测试结果如下(注意里面的黑色字体,sql,参数和结果):
[DEBUG] 2018-04-29 11:32:20,857 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: SELECT * from sys_user where 1 = 1 AND id = ?
[DEBUG] 2018-04-29 11:32:20,904 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 1(Long)
[TRACE] 2018-04-29 11:32:20,962 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
[TRACE] 2018-04-29 11:32:20,962 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Row: 1, admin, 123456, admin@mybatis.tk, <>, <>, 2018-04-24 17:08:34.0
[DEBUG] 2018-04-29 11:32:20,962 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Total: 1
[DEBUG] 2018-04-29 11:32:20,962 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: SELECT * from sys_user where 1 = 1 AND user_name = ?
[DEBUG] 2018-04-29 11:32:20,962 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: admin(String)
[TRACE] 2018-04-29 11:32:20,993 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
[TRACE] 2018-04-29 11:32:20,993 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Row: 1, admin, 123456, admin@mybatis.tk, <>, <>, 2018-04-24 17:08:34.0
[DEBUG] 2018-04-29 11:32:20,993 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Total: 1
[DEBUG] 2018-04-29 11:32:20,993 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: SELECT * from sys_user where 1 = 1 AND 1 = 2
[DEBUG] 2018-04-29 11:32:20,993 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters:
[DEBUG] 2018-04-29 11:32:21,040 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Total: 0