MyBatis入门07--实现sql动态模糊查询

根据上篇博客：MyBatis入门06 动态sql语句

接下来实现sql动态模糊查询：

1、提出需求：
实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)

2、 准备数据表和数据:

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);  
insert into d_user(name,age) values('Jack',18);

3、ConditionUser(查询条件实体类)

private String name;
private int minAge;
private int maxAge;

4、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.atguigu.day03_mybaits.test7.userMapper">
    <!-- 
    实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)
     -->


    <select id="getUser" parameterType="ConditionUser" resultType="User">
        select * from d_user where 

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

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

获取SqlSessionFactory 的工具类：

package com.atguigu.day03_mybaits.util;

import java.io.InputStream;

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

import com.atguigu.day03_mybaits.test1.Test;

public class MybatisUtils {

    public static SqlSessionFactory getFactory() {
        String resource = "conf.xml";
        InputStream is = Test.class.getClassLoader().getResourceAsStream(
                resource);

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        return factory;
    }
}

测试代码：

package com.atguigu.day03_mybaits.test7;

import java.util.List;

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

import com.atguigu.day03_mybaits.bean.ConditionUser;
import com.atguigu.day03_mybaits.bean.User;
import com.atguigu.day03_mybaits.util.MybatisUtils;
/*
 * 测试: 动态SQL与模糊查询
 */
public class Test7 {

    public static void main(String[] args) {
        SqlSessionFactory factory = MybatisUtils.getFactory();
        SqlSession session = factory.openSession();

        String statement = "com.atguigu.day03_mybaits.test7.userMapper.getUser";

        String name = "o";
        name = null;

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


        session.close();
    }
}