10.2 MyBatis动态SQL 10.2.1 if标签
- 10.2.1 if标签
  - mybatis参数获取方式
  - 测试if标签
- 使用多个条件

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="domain.Employee">
        select * from tb_employee where state='active'
        <if test="id!=null">and id=#{id}</if>
    </select>
</mapper>

以上语句提供了一个可选的根据id查找Employee的功能。如果没有传入id,那么所有处于”active“状态的Employee都会被返回;反之若传入了id,那么就会把查找id内容的Employee结果返回。

1
2
3

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

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

mybatis参数获取方式

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

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

测试if标签

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=?“,查询返回的Emplyee对象就是id为1的对象。
接下来注释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的数据。

使用多个条件

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

select标签

在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接口中添加入下方法:

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

测试类

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语句中因为传入了lginnane和password属性,故SQL语句中包含了”and loginname=? and password=?“,查询返回的Emplyee对象就是loginname是xiaoming,并且password是xiaoming的对象。

原文链接: 10.2 MyBatis动态SQL 10.2.1 if标签