10.2 MyBatis动态SQL 10.2.3 where标签

前面几个例子已经很好地解决了动态SQL问题。现在回到之前的if示例,这次我们将state='ACTIVE'也设置成动态的条件,看看会发生什么。

<select
    id="selectEmployeeByStateIfId"
    resultType="domain.Employee">
    select * from tb_employee where
    <if test="state!=null">state=#{state}</if>
    <if test="id!=null">and id=#{id}</if>
</select>

如果传入state参数,则执行正常。
如果没有传入参数,则会执行SQL语句:

select * from tb_employee where 

如果只是传入id,则会执行SQL语句:

select * from tb_employee where and id=? 

也就是说,如果没有传入state参数,会导致执行失败。这个问题不能简单地用条件语句来解决。MyBatis有一个简单的处理方法,只要简单地修改就能得到想要的效果.

where标签实例

EmployeeMapper.xml

在EmployeeMapper.xml中添加如下标签.

<!-- 使用where标签 -->
<select id="selectEmployeeUserWhere"
    resultType="org.fkit.domain.Employee">
    SELECT * FROM tb_employee
    <where>
        <if test="state != null "> state = #{state}</if>
        <if test="id != null "> and id = #{id}</if>
        <if test="loginname != null and password != null">
            and loginname = #{loginname} and password = #{password}
        </if>
    </where>
</select>

where元素知道只有在一个以上的if条件有值的情况下才会插入where子句。而且,若最后的内容是AND或OR开头,where元素会去除多余的AND或OR。

EmployeeMapper接口

在EmployeeMapper接口中添加如下方法:

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

测试类

/MyDynamicSQLTest/src/test/WhereTest.java

public class WhereTest {
    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("state", "active");
         params.put("id", 1);
         params.put("loginname", "xiaowang");
         params.put("password", "xiaowang");
        List<Employee> employees = employeeMapper.selectEmployeeUseWhere(params);
        employees.forEach(employee -> System.out.println("    " + employee));
    }
}

设置所有参数

运行测试类,控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee WHERE state=? and id=? and loginname=? and password=? 
DEBUG [main] ==> Parameters: active(String), 1(Integer), xiaowang(String), xiaowang(String)
DEBUG [main] <==      Total: 0

这里我们设置了state,id,loginname和password四个参数,执行的SQL语句为:select * from tb_employee WHERE state=? and id=? and loginname=? and password=?

设置部分参数

注释掉main方法中的state属性,如下代码所示:

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("state", "active");
    params.put("id", 1);
    params.put("loginname", "xiaowang");
    params.put("password", "xiaowang");
    List<Employee> employees = employeeMapper.selectEmployeeUseWhere(params);
    employees.forEach(employee -> System.out.println("    " + employee));
}

再次运行控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee WHERE id=? and loginname=? and password=? 
DEBUG [main] ==> Parameters: 1(Integer), xiaowang(String), xiaowang(String)
DEBUG [main] <==      Total: 0

此时我们设置了id,loginname,password这三个参数,这三个参数满足where标签中的if标签的条件,则Mybatis会生成where子句,并将if标签体中的代码作为where子句的条件.
得到的SQL语句为:select * from tb_employee WHERE id=? and loginname=? and password=?

接着注释掉id参数,再次运行控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee WHERE 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]

可以看到执行的SQL语句为select * from tb_employee WHERE loginname=? and password=?

不传入参数

注释掉所有参数,再次运行,控制台输出如下:

DEBUG [main] ==>  Preparing: select * from tb_employee 
DEBUG [main] ==> Parameters: 
DEBUG [main] <==      Total: 4
    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]
    Employee [id=3, loginname=xiaoli, password=xiaoli, name=小丽, sex=女, age=23, phone=123456789123, sal=7800.0, state=active]
    Employee [id=4, loginname=xiaofang, password=xiaofang, name=小芳, sex=女, age=22, phone=123456789123, sal=8800.0, state=active]

可以发现,不传入参数时,where标签中没有任何匹配的参数,这时,生成的SQL语句中将不带有where子句。