动态SQL与模糊查询

动态SQL与模糊查询

创建数据

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);
insert into d_user(name,age) values('Jhon',16);
insert into d_user(name,age) values('Bart',15);
insert into d_user(name,age) values('Lisa',17);

问题分析

查询名字中带“o”的，并且年龄在13–18岁之间的user

普通sql查询

select * from d_user where name like '
%o%' and age between 13 and 18;

+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | Bob  |  13 |
|  4 | Jhon |  16 |
+----+------+-----+
2 rows in set

使用mybatis查询

封装查询条件的bean

package com.bart.mybatis.beans;
/**
 * 查询条件的封装类
 * @author hp
 *
 */
public class ConditionUser {
    @Override
    public String toString() {
        return "ConditionUser [name=" + name + ", minAge=" + minAge
                + ", 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;
    }
    private String name;
    private int minAge;
    private int maxAge;
    public ConditionUser(String name, int minAge, int maxAge) {
        super();
        this.name = name;
        this.minAge = minAge;
        this.maxAge = maxAge;
    }
    public ConditionUser() {
        super();
    }

}

配置mapper

<?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.bart.mybatis.mapper.conditionUserMapper">
    <!-- 
       查询名字带'o'的并且在 minAge到maxAge之间
     -->
    <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>

配置conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 
    development : 开发模式
    work : 工作模式
 -->
    <!-- 加载db.properties -->
    <properties resource="db.properties">
    </properties>

    <!-- 配置别名，可以再mapper中使用，方便操作 -->
    <typeAliases>
        <typeAlias type="com.bart.mybatis.beans.User" alias="_User"/>

        <typeAlias type="com.bart.mybatis.beans.ConditionUser" alias="_ConditionUser"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${name}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- 映射mapper配置文件 -->
    <mappers>
 resource="com/bart/mybatis/mapper/conditionUserMapper.xml"/>
    </mappers>

</configuration>

创建测试

@Test
    public void Test(){
        SqlSessionFactory factory = DBUtil.getSessionFactory();
        SqlSession session = factory.openSession();
        String statement="com.bart.mybatis.mapper.conditionUserMapper.getUser";
        //String name = "null";
        String name = "o";
        ConditionUser cu = new ConditionUser("%"+name+"%",13,18);
        List<User> list = session.selectList(statement,cu);
        System.out.println(list);
        session.close();
    }

结果

• 当String name = "null";
  相当于只查询年龄13–18 的user

[
User [id=2, name=Bob, age=13], 
User [id=3, name=Jack, age=18], 
User [id=4, name=Jhon, age=16], 
User [id=5, name=Bart, age=15], 
User [id=6, name=Lisa, age=17], 
User [id=7, name=Lisa, age=17]
]

• 当String name = "o";相当于查询名字中带“o”的年龄在13–18 之间的user

[User [id=2, name=Bob, age=13],
 User [id=4, name=Jhon, age=16]]

总结

在mapper中配置select查询的时候用到了类似于JSTL的判断语句，当满足该条件时，包含的条件作为查询条件，否则不作为查询条件，实现了SQL的动态查询