10.2 MyBatis动态SQL 10.2.1 if标签

10.2.1 if标签

动态SQL通常会做的事情是有条件地包含where子句的一部分。比如:

<?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="mapper.EmployeeMapper">
    <!-- if单个条件 -->
    <select id="selectEmployeeByStateIfId"
        resultType="org.fkit.domain.Employee">
        SELECT * FROM tb_employee WHERE state = 'ACTIVE'
        <!-- 可选条件，如果传进来的参数有id属性，则加上id查询条件 -->
        <if test="id != null "> and id = #{id}
        </if>
    </select>
</mapper>

以上语句提供了一个可选的根据id查找Employee的功能。

• 如果没有传入id,那么所有处于active状态的Employee都会被返回;
• 反之传入了id,那么所有处于active状态以及指定id内容的Employee结果返回。

public interface EmployeeMapper {
    List<Employee> selectEmployeeByStateIfId(HashMap<String, Object> params);
}

以上代码提供了一个和EmployeeMapper.xml中的select元素的id同名的方法,需要注意的是,selectEmployeeByStateIfId接受一个HashMap作为参数。

mybatis参数获取方式

在MyBatis中,#{id}表达式获取参数有两种方式:

• 一是从HashMap中获取集合的property对象;
• 二是从Javabean中获取property对象；

测试if标签

/MyDynamicSQLTest/src/test/OneIfTest.java

package test;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFratoryTools;
import mapper.EmployeeMapper;
public class OneIfTest {
    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);
        List<Employee> employees = employeeMapper.selectEmployeeByStateIfId(params);
        employees.forEach(employee -> System.out.println("    " + employee));
    }
}

运行结果

运行OneIfTest类的main方法,main方法中通过SqlSession的getMapper(Class<T> type)方法获得mapper接口的代理对象EmployeeMapper。调用selectEmployeeByStateIfId方法时会执行EmployeeMapper.xml中id="selectEmployeeByStateIfId"的select元素中定义的SQL语句。控制台显示如下:

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]

可以看到,执行的SQL语句中因为传入了id属性,所以SQL语句中包含了”and id=?“,查询返回state=ACTIVE并且id为1的数据。只得到一个id为1的Emplyee对象。

不传入参数的情况

接下来注释main方法中往HashMap中设置键值对的put方法，如下代码所示:

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);
    List<Employee> employees = employeeMapper.selectEmployeeByStateIfId(params);
    employees.forEach(employee -> System.out.println("    " + employee));
}

运行效果

再次执行main方法,控制台显示如下:

DEBUG [main] ==>  Preparing: select * from tb_employee where state='active' 
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]

可以看到,由于传递的HashMap中没有id属性,故执行的SQL语句中不再包含”and id=?“,查询语句返回了所有state=ACTIVE的数据。得到了多个Employee对象。

if标签中使用多个条件

如果想通过两个或多个条件搜索该怎么办呢?很简单,只要多加入一个或多个条件即可以。

使用多个and语句

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

<!-- 测试多个if标签 -->
<select
    id="selectEmployeeByStateIfLoginnamePassword"
    resultType="domain.Employee">
    select * from tb_employee where state='active'
    <if test="loginname != null and password != null"> and loginname ={loginname} and password ={password}
    </if>
</select>

mapper接口方法

在EmployeeMapper.java接口中添加入下方法:

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

测试类

/MyDynamicSQLTest/src/test/DoubleIfTest.java

package test;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFratoryTools;
import mapper.EmployeeMapper;
public class DoubleIfTest {
    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("loginname", "xiaoming");
        params.put("password", "xiaoming");
        List<Employee> employees = employeeMapper.selectEmployeeByStateIfLoginnamePassword(params);
        employees.forEach(employee -> System.out.println("    " + employee));
    }
}

运行结果

运行测试类DoubleIfTest,控制台显示效果如下:

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

可以看到,执行的SQL语句中因为传入了loginname和password属性,故SQL语句中包含了”and loginname=? and password=?“,查询返回的Emplyee对象就是loginname是xiaoming,并且password是xiaoming的对象。