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

发表于 更新于 分类于 ， ， Waline： 本文字数： 5.3k 阅读时长 ≈ 5 分钟

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

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

测试choose标签

select标签

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

1
2
3
4
5
6
7
8
9
10
11
12
13
<!-- 测试choose标签 -->
<select id="selectEmployeeChoose" parameterType="hashmap"
    resultType="org.fkit.domain.Employee">
    SELECT * FROM tb_employee WHERE state = 'ACTIVE'
    <!-- 如果传入了id，就根据id查询，没有传入id就根据loginname和password查询，否则查询sex等于男的数据 -->
    <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);

测试

提供多个条件时的运行效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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));
    }
}

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

1
2
3
4
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参数,如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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));
}

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

1
2
3
4
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参数,如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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));
}

运行效果:

1
2
3
4
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的对象.

不提供参数时的运行效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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));
}

运行效果:

1
2
3
4
5
6
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='男'“的对象.