六、Mybatis动态SQL和模糊查询

6.1准备数据表

---

create table d_user(
id int primary key auto_increment,
name varchar(10),
age int(3)
);
insert into d_user(name,age) values('Tom',12);
insert into d_user(name,age) values('Bob',13);

6.2ConditionUser(查询条件实体类)

ConditionUser.java

package com.entity;

public class ConditionUser {

    private String name;
    private int minAge;
    private int maxAge;
    public ConditionUser() {
        super();
    }
    public ConditionUser(String name, int minAge, int 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 int getMinAge() {
        return minAge;
    }
    public void setMinAge(int minAge) {
        this.minAge = minAge;
    }
    public int getMaxAge() {
        return maxAge;
    }
    public void setMaxAge(int maxAge) {
        this.maxAge = maxAge;
    }
    @Override
    public String toString() {
        return "ConditionUser [name=" + name + ", minAge=" + minAge
                + ", maxAge=" + maxAge + "]";
    }
}

6.3User实体类

User.java

package com.entity;

public class User {

    private int id;
    private String name;
    private int age;
    public User()
    {

    }
    public User(int id, String name, int 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 int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

}

6.4user2Mapper.xml映射文件

user2Mapper.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.config.user2Mapper">
    <!-- 
    实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间) 
    -->
    <select id="getUser" parameterType="com.entity.ConditionUser" resultType="com.entity.User">
        select * from d_user where 

        <if test='name!="%null%"'>
          name like #{name} and 
        </if>

        age between #{minAge} and #{maxAge}
    </select>
</mapper>

别忘了在config.xml中注册。

6.5测试类

Test6.java

package com.test;

import java.io.IOException;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;



import com.entity.ConditionUser;
import com.entity.User;
import com.util.MybatisUtil;

public class Test6 {

    @Test
    public void testDynamicSql() throws IOException
    {
        SqlSession session=MybatisUtil.getSession();
        String statement="com.config.user2Mapper.getUser";

        String name="o";
        //name=null;
        ConditionUser parameter=new ConditionUser("%"+name+"%",13,18);
        List<User> list=session.selectList(statement, parameter);
        session.close();
        System.out.println(list);
    }

}