有些时候,我们不想用所有的条件语句,而只想从中择其一二.针对这种情况,MyBatis提供了choose元素,他有点像Java中的Switch语句.

如果提供ID就以ID为条件查找,提供LOGINNAME和PASSWORD就以LOGINNAME和PASSWORD为条件查找:

1.mapper

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.fkit.mapper.EmployeeMapper">
    <select id="selectEmployeeByIdLike" resultType="org.fkit.domain.Employee">
        SELECT * FROM tb_employee WHERE STATE='ACTIVE'
        <!-- 可选条件,如果传入参数有ID属性,则加上ID查询条件;如果传入LOGINNAME和PASSWORD就按LOGINNAME和PASSWORD查询,否则查询sex=m的数据 -->
        <choose>
            <when test="id != null">
             AND ID=#{id}
            </when>
            <when test="loginname != null and password != null">
             AND LOGINNAME=#{loginname} AND PASSWORD=#{password}
            </when>
            <otherwise>
             AND sex='M'
            </otherwise>
        </choose>
    </select>
</mapper>

 

2.控制器

    @GetMapping("/mysql/DynamicSQL/choose")
    public void dynamicSQLChoose()
    {
        //获得SqlSession实例
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        //获得Mapper接口代理对象
        EmployeeMapper em = sqlSession.getMapper(EmployeeMapper.class);
        //创建一个HashMap存储参数
        HashMap<String,Object> params = new HashMap<String,Object>();
        params.put("loginname", "ccc");
        params.put("password", "123");
        //调用Employee接口的selectEmployeeByIdLike方法
        List<Employee> list = em.selectEmployeeByIdLike(params);
        //查看结果
        list.forEach(employee->System.out.println(employee.getName()));
    }

输出:

16:01:40,728 DEBUG selectEmployeeByIdLike:159 - ==>  Preparing: SELECT * FROM tb_employee WHERE STATE='ACTIVE' AND LOGINNAME=? AND PASSWORD=?
16:01:40,763 DEBUG selectEmployeeByIdLike:159 - ==> Parameters: ccc(String), 123(String)
16:01:40,787 DEBUG selectEmployeeByIdLike:159 - <==      Total: 1
CCC