6.1 Mybatis实现动态SQL——<if></if>使用

当我们用模糊匹配某字段时，在mybatis可以通过动态sql来实现。
例如users表如下：

下面在mybatis中实现

SELECT * FROM users WHERE name LIKE '%z%' AND age>15 AND age<30

1、建立users表对应的User类

package com.lzj.mybaits.test1;

public class User {

    private int id;
    private String name;
    private float age;

    public User() {
        super();
    }

    public User(int id, String name, float age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getAge() {
        return age;
    }

    public void setAge(float age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

}

2、在select查询语句中，有三个条件：name的取值，age的最小值，age的最大值，下面把查询条件封装成一个条件类

package com.lzj.mybaits.test1;

public class UserCondition {

    private String name;
    private float minAge;
    private float maxAge;

    public UserCondition() {
        super();
    }

    public UserCondition(String name, float minAge, float maxAge) {
        super();
        this.name = name;
        this.minAge = minAge;
        this.maxAge = maxAge;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMinAge() {
        return minAge;
    }

    public void setMinAge(float minAge) {
        this.minAge = minAge;
    }

    public float getMaxAge() {
        return maxAge;
    }

    public void setMaxAge(float maxAge) {
        this.maxAge = maxAge;
    }

    @Override
    public String toString() {
        return "UserCondition [name=" + name + ", minAge=" + minAge + ", maxAge=" + maxAge + "]";
    }

}

3、定义sql的userMapper.xml映射文件

<?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="com.lzj.mybaits.test1.userMapper">

    <select id="getUsers" parameterType="UserCondition" resultType="User">
        select * from users where 
        <!--该if条件中的第一个name和第三个name对应UserCondition类中的属性：第1个name相当于java中的代码，不需要加#{}，第3个name代表引用传入UserCondition对象中的name属性，需要加#{}；第2个name 为sql中的字段名-->
        <if test='name != "%null%"'>
            name like #{name} and
        </if>
        <!--当传入的UserCondition对象中的name属性值为"%null%"时，sql语句中就只按age的条件进行查询-->
        <if test='name == "%null%"'>
        </if>
        age between #{minAge} and #{maxAge}
    </select>

</mapper>

4、创建测试类

package com.lzj.mybaits.test1;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybaitsTest {

    public static void main(String[] args) throws Exception {
        String resource = "conf.xml";
        InputStream in = MybaitsTest.class.getClassLoader().getResourceAsStream(resource);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        /*自动提交事务*/
        SqlSession session = factory.openSession(true);
        String statement = "com.lzj.mybaits.test1.userMapper.getUsers";
        String name = "z";
        /*如果String name = null;
         * 在创建UserCondition对象后，对象中的name值为"%null%",所以在映射文件中要与"%null%"判断*/
        List<User> users = session.selectList(statement, new UserCondition("%" + name + "%", 15, 30));
        System.out.println(new UserCondition("%" + name + "%", 15, 30));
        System.out.println(users);
        session.close();
    }

}

输出结果

UserCondition [name=%z%, minAge=15.0, maxAge=30.0]
[User [id=3, name=lzj, age=25.0], User [id=4, name=zhangsan, age=21.0], User [id=5, name=zhangsan, age=21.0]]