mybatis的动态sql及模糊查询

1、动态sql

使用类似于jstl表达式来实现

 

2、模糊查找

用一个对象来封装条件

 

步骤：

1）新建一个条件实体

package com.hy.mybatis.entity;

public class ConditionUser {
    private String name;
    
    private int minAge;
    
    private int maxAge;

    @Override
    public String toString() {
        return "ConditionUser [name=" + name + ", minAge=" + minAge
                + ", maxAge=" + 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;
    }
    
}

 

2）写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.hy.mybatis.mapper.User">

<select id="getUser" parameterType="ConditionUser" resultType="DUser">
    select * from d_user where 1=1
    <if test="name != '%null%'">
        and name like ${name}
    </if>
    and age between #{minAge} and #{maxAge}
</select>

</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.hy.mybatis.mapper.User">

<select id="getUser" parameterType="ConditionUser" resultType="DUser">
    select * from d_user where 1=1
    <if test="name != '%null%'">
        and name like ${name}
    </if>
    and age between #{minAge} and #{maxAge}
</select>

</mapper>

3）测试：

@Test
    public void testGetUsers() {
        SqlSession session = MyBatisUtil.getSqlSession();
        String statement = "com.hy.mybatis.mapper.User.getUser";
        List<DUser> users = session.selectList(statement, new ConditionUser("%"+null+"%", 13, 18));
        System.out.println(users);
    }