10.2 MyBatis动态SQL 10.2.2 choose(when, otherwise)
- 测试choose标签

10.2 MyBatis动态SQL 10.2.2 choose(when, otherwise)

情况MyBatis提供了choose标签,它有点像Java中的switch语句。

测试choose标签

select标签

在EmployeeMapper.xml中添加入下select标签:

<!-- 测试choose标签 -->
<select
    id="selectEmployeeUserChoose"
    resultType="domain.Employee">
    select * from tb_employee where state='active'
    <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='男'</otherwise>
    </choose>
</select>

这个select标签表示,提供了id参数就按id查找,提供了loginname和password参数就按loginname和password查找,若两者都没有提供,就返回所有sex等于男的Employee.

mapper接口方法

在接口中天对应的方法:

1	List<Employee> selectEmployeeUserChoose(HashMap<String, Object> params);

测试类

提供多个条件

public class ChooseTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        // 1.获取SqlSession实例
        sqlSession = SqlSessionFratoryTools.getSqlSession();
        // 2.获取mapper接口的代理对象
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        // 3.创建参数列表
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("id", 1);
        params.put("loginname", "xiaowang");
        params.put("password", "xiaowang");
        List<Employee> employees = employeeMapper.selectEmployeeUserChoose(params);
        employees.forEach(employee -> System.out.println("    " + employee));
    }
}

运行程序,控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee where state='active' and id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]

可以看到虽然两个条件都提供,但只会执行第一个条件.也就是说choose标签中,写在前面的when标签优先匹配.并且匹配成功之后不会再匹配.所以当满足多个条件时,只会执行第一个when标签中的SQL语句.这和只提供第一个条件的效果是一样的.

提供第一个条件

现在注释掉loginname和password参数,保留id参数,如下代码:

public static void main(String[] args)
{
    SqlSession sqlSession = null;
    // 1.获取SqlSession实例
    sqlSession = SqlSessionFratoryTools.getSqlSession();
    // 2.获取mapper接口的代理对象
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    // 3.创建参数列表
    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("id", 1);
    // params.put("loginname", "xiaowang");
    // params.put("password", "xiaowang");
    List<Employee> employees = employeeMapper.selectEmployeeUserChoose(params);
    employees.forEach(employee -> System.out.println("    " + employee));
}

再次执行,控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee where state='active' and id=? 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
    Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]

可以看到这和上面提供多个条件的效果一致。

提供第二个条件

注释掉id参数,保留loginname和password参数,如下代码:

public static void main(String[] args)
{
    SqlSession sqlSession = null;
    // 1.获取SqlSession实例
    sqlSession = SqlSessionFratoryTools.getSqlSession();
    // 2.获取mapper接口的代理对象
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    // 3.创建参数列表
    HashMap<String, Object> params = new HashMap<String, Object>();
    // params.put("id", 1);
    params.put("loginname", "xiaowang");
    params.put("password", "xiaowang");
    List<Employee> employees = employeeMapper.selectEmployeeUserChoose(params);
    employees.forEach(employee -> System.out.println("    " + employee));
}

运行效果:

DEBUG [main] ==>  Preparing: select * from tb_employee where state='active' and loginname = ? and password = ? 
DEBUG [main] ==> Parameters: xiaowang(String), xiaowang(String)
DEBUG [main] <==      Total: 1
    Employee [id=2, loginname=xiaowang, password=xiaowang, name=小王, sex=男, age=21, phone=123456789123, sal=6800.0, state=active]

可以看到,HashMap里面传递的参数只包括loginname和password,所以SQL语句是按照loginname和password查找,查询返回的Emplyee对象就是loginname是xiaowang,并且password是xiaowang的对象.

不提供参数

public static void main(String[] args)
{
    SqlSession sqlSession = null;
    // 1.获取SqlSession实例
    sqlSession = SqlSessionFratoryTools.getSqlSession();
    // 2.获取mapper接口的代理对象
    EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    // 3.创建参数列表
    HashMap<String, Object> params = new HashMap<String, Object>();
    // params.put("id", 1);
    // params.put("loginname", "xiaowang");
    // params.put("password", "xiaowang");
    List<Employee> employees = employeeMapper.selectEmployeeUserChoose(params);
    employees.forEach(employee -> System.out.println("    " + employee));
}

运行效果:

DEBUG [main] ==>  Preparing: select * from tb_employee where state='active' and sex='男' 
DEBUG [main] ==> Parameters: 
DEBUG [main] <==      Total: 2
    Employee [id=1, loginname=xiaoming, password=xiaoming, name=小明, sex=男, age=19, phone=123456789123, sal=9800.0, state=active]
    Employee [id=2, loginname=xiaowang, password=xiaowang, name=小王, sex=男, age=21, phone=123456789123, sal=6800.0, state=active]

可以看到,由于没有传递任何参数,故SQL语句执行的是otherwise标签里面的查询条件,即”and sex='男'“,查询返回的Emplyee对象就是”sex='男'“的对象.

原文链接: 10.2 MyBatis动态SQL 10.2.2 choose(when, otherwise)标签