動态SQL與模糊查詢

建立資料

create table d_user(  
    id int primary key auto_increment,  
    name varchar(),
    age int()
);  

insert into d_user(name,age) values('Tom',);  
insert into d_user(name,age) values('Bob',);  
insert into d_user(name,age) values('Jack',);
insert into d_user(name,age) values('Jhon',);
insert into d_user(name,age) values('Bart',);
insert into d_user(name,age) values('Lisa',);
           

問題分析

查詢名字中帶“o”的，并且年齡在13–18歲之間的user

普通sql查詢

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

+----+------+-----+
| 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+"%",,);
        List<User> list = session.selectList(statement,cu);
        System.out.println(list);
        session.close();
    }
           

結果

• 當

  String name = "null";

  相當于隻查詢年齡13–18 的user

[
User [id=, name=Bob, age=], 
User [id=, name=Jack, age=], 
User [id=, name=Jhon, age=], 
User [id=, name=Bart, age=], 
User [id=, name=Lisa, age=], 
User [id=, name=Lisa, age=]
]
           

• 當

  String name = "o";

  相當于查詢名字中帶“o”的年齡在13–18 之間的user

[User [id=, name=Bob, age=],
 User [id=, name=Jhon, age=]]
           

總結

在mapper中配置select查詢的時候用到了類似于JSTL的判斷語句，當滿足該條件時，包含的條件作為查詢條件，否則不作為查詢條件，實作了SQL的動态查詢