动态SQL之 where 标签

动态SQL之 where 标签

where和if一般结合使用：

• 1.若where标签中的 if 条件都不满足，则where标签没有任何功能，即不会添加where关键字
• 2.若where标签中的 if 条件满足，则where标签会自动添加where关键字，并将条件最前方多余的 and去掉
• 注意：where标签不能去掉条件最后多余的and

 DynamicSQLMapper接口

public interface DynamicSQLMapper {

    /**
     * 根据条件来查询员工信息
     * @param emp
     * @return
     */
    List<Emp> getEmpByCondition(Emp emp);
}

MyBatis的映射文件DynamicSQLMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mybatis.mapper.DynamicSQLMapper">
    
    <!--List<Emp> getEmpByCondition(Emp emp);-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <where>
            <if test="empName != null and empName != ''">
                emp_name = #{empName}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="gender != null and gender != ''">
                and gender = #{gender}
            </if>
        </where>
    </select>
    
</mapper>

测试

public class DynamicMapperTest {

    @Test
    public void testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"男");
        List<Emp> list = mapper.getEmpByCondition(emp);
        list.forEach(System.out::println);
    }
}