动态sql_＜if＞＜where＞＜trim＞标签

对应sql对象

public class Fz03 {
    private int id;
    private String name;
    private int age;
    private String x;
  // 构
    ..........................................
}

mapper接口

List<Map<String,Object>> fz03selectname(Fz03 fz03);

mapper.xml

<select id="fz03selectname" resultType="map">
        select * from fz03
        <where>
            <if test="id != 0">
                id = #{id}
            </if>
            <if test="name != null and name !=''">
                and name = #{name}
            </if>
            <if test="age != 0">
                and age = #{age}
            </if>
            <if test="x != null and x != ''">
                and x = #{x}
            </if>
        </where>
</select>

解析

<if>标签

if 标签中的 text id != 0 为一条条件语句 , 表示传参Fz03中的id不为0的情况下才执行该条语句

当多个条件时可以用and << test="name != null and name !=''" >>

<where>标签

where主要的作用是当出现

<!-- id=0 条件不满足不执行 -->
<if test="id != 0">
    id = #{id}
</if>
<if test="name != null and name !=''">
    <!-- 该条件满足 但sql语句中有and -->
    and name = #{name}
</if>

因此会出现语法错误 报错的情况

<where>标签就是解决去除这个多余的and标签

但<where>标签只能取出前面的and 但and出现在后面时是无法去除的

例:

<if test="id != 0">
    id = #{id} and
</if>
<if test="name != null and name !=''">
    name = #{name}
</if>

and在后无法去除,所以会报语法错误

<trim>标签

该标签可以解决无法去除结尾的and情况

<!-- 在前面补where          去除结尾的and  -->
<trim prefix="where" suffixOverrides="and">
    <if test="id != 0">
        id = #{id} and
    </if>
    <if test="name != null and name !=''">
        name = #{name} and
    </if>
    <if test="age != 0">
        age = #{age} and
    </if>
    <if test="x != null and x != ''">
        x = #{x}
    </if>
</trim>

开始调用

有null的情况

@Test
    public void a3() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_x.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);
        // 先以只有x属性初始化的对象为传参
        List<Map<String,Object>> list = usermapper.fz03selectname(new Fz03(0,null,0,"男"));

        System.out.println(list);

        session.close();
        inputStream.close();
    }

运行结果

全部为空的情况(相当于查询所有)

List<Map<String,Object>> list = usermapper.fz03selectname(new Fz03(0,null,0,null));

运行结果