动态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的动态查询