Mybatis之动态SQL 模糊查询

原理

在实现模糊查询时我们需要考虑下面几个问题：
1：动态SQL语句
2：传入参数处理
3：查询结果处理

实现Demo目的：从User表中模糊查询姓名和年龄，姓名可能未知，年龄在minAge到maxAge之间

下面给出一个简单的动态查询案例

实体类

User实体

package com.mybatis.bean;

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 + "]";
    }

}

封装User传入参数

package com.mybatis.bean;

public class ConditionUser {
    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();
    }
    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 + "]";
    }


}

配置文件

首先是关联关系及sql语句的配置
*重点在于下面SQL语句的 动态化 实现逻辑方式*

<?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.mybatis.bean.userMapper">
    <!-- 动态查询和模糊查询：参数类型为一个封装的对象 -->
    <select id="getUser2" parameterType="com.mybatis.bean.ConditionUser"
        resultType="com.mybatis.bean.User">
            select * from d_user where age>=#{minAge} and age&lt;=#{maxAge}
            <if test='name!="%null%"'>and name like #{name}</if>
    </select>
</mapper>

然后要注册到配置文件中

<mappers>
        <mapper resource="com/mybatis/bean/userMapper.xml" />
    </mappers>

测试

package com.mybatis.test;

import java.util.List;

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

import com.mybatis.bean.ConditionUser;
import com.mybatis.bean.User;
import com.mybatis.utils.MybatisUtils;

public class Test7 {

    @Test
    public void test() {
        SqlSession session = MybatisUtils.getSession();
        /*      
         * 实际开发中我们的name和age可能是从页面传入过来的，
         * 同时要在sql语句中考虑用户可能查询的是一种条件的模糊查询，所以考虑
         * 不同模糊条件查询的组合情况，例如这里需要将name和age抽离出来，实现解耦
         * */
        String Statement = "com.mybatis.bean.userMapper.getUser2";
        ConditionUser parameter = new ConditionUser("%null%", 10, 20);

        List<User> users = session.selectList(Statement, parameter);
        for (Object user : users) {
            System.out.println(user);
        }

        session.close();
    }

}

结果及说明

2016-12-11 15:18:09,497 [main] DEBUG [com.mybatis.bean.userMapper.getUser2] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@73d1386a]
2016-12-11 15:18:09,498 [main] DEBUG [com.mybatis.bean.userMapper.getUser2] - ==>  Preparing: select * from d_user where age>=? and age<=? 
2016-12-11 15:18:09,592 [main] DEBUG [com.mybatis.bean.userMapper.getUser2] - ==> Parameters: 10(Integer), 20(Integer)
User [id=1, name=Tom, age=12]
User [id=2, name=Bob, age=13]
User [id=3, name=Jack, age=18]

从上面我么可以看出对于动态的查询我们主要需要考虑对于传入不同参数如何实现SQL语句的动态化处理，所以最简单的实现思路就是直接在数据库可视化工具中编写SQL语句进行测试，然后根据需要模糊查询的逻辑进行Where的选择以及if的判断来实现动态查询。