16、mybatis学习——mybatis的动态sql之<if>、<where>和<trim>

Student.java:

 StudentMapper接口定义方法：

 StudentMapper配置文件进行配置

     <select id="getStuByIf" resultType="student">
         select * from student where
         <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用
             OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的
             从参数中取值判断;遇见特殊符号应该写转义字符例如&（&amp;）符号 -->
         <if test="id!=null">
             id = #{id}
         </if>
         <if test="name!=null &amp;&amp; name.trim()!=''">
             and name = #{name}
         </if>
     </select>

测试1（id和name都有值）

    //测试动态sql的if
    @Test
    public void testGetStuByIf() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.getStuByIf(new Student(1, "小明"));
        System.out.println(student);
        sqlSession.close();
    }

测试结果的语句：

 测试2（当id有值，name为空时）

    //测试动态sql的if
    @Test
    public void testGetStuByIf() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.getStuByIf(new Student(1, ""));
        System.out.println(student);
        sqlSession.close();
    }

测试结果语句为：

此时有一个问题当测试方法中传参的id为空时

    //测试动态sql的if
    @Test
    public void testGetStuByIf() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.getStuByIf(new Student(null, "小明"));
        System.out.println(student);
        sqlSession.close();
    }

测试结果为

 此时sql语句出错（通过查看StudentMapper的sql配置可找到原因）

所以我们需要解决此问题：

方案一：在where后面加一个1=1

即StudentMapper配置改为

     <select id="getStuByIf" resultType="student">
         select * from student where 1=1
         <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用
             OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的
             从参数中取值判断;遇见特殊符号应该写转义字符例如&符号和'符号 -->
         <if test="id!=null">
             id = #{id}
         </if>
         <if test="name!=null &amp;&amp; name.trim()!=''">
             and name = #{name}
         </if>
     </select>

此时测试结果为

方案二：将条件包括在<where>标签中

mybatis中的<where>标签会把多出来的and或者or自动去掉

即StudentMapper的配置文件改为：

     <select id="getStuByIf" resultType="student">
         select * from student
         <where>
             <!-- test:判断表达式；里面使用的是OGNL表达式，可百度查询OGNL的使用
                 OGNL会自动进行字符串与数字的转换判断；字符串"0"和数字0是一样的
                 从参数中取值判断;遇见特殊符号应该写转义字符例如&符号和'符号 -->
             <if test="id!=null">
                 id = #{id}
             </if>
             <if test="name!=null &amp;&amp; name.trim()!=''">
                 and name = #{name}
             </if>
         </where>
     </select>

测试结果

 

方案三：通过<trim>标签去掉条件中前面或者后面多余的字符

　　为了测试方便在StudentMapper接口中再定义一个方法

 　　StudentMapper的配置文件改为：

      <select id="getStuByTrim" resultType="student">
         select * from student
         <!-- 后面多出来的and或者or <where>标签不能解决
             则此时使用<trim>标签,<trim>标签体中是整个字符串拼串后的结果
              prefix=""：给拼串后的字符串加一个前缀
              prefixOverrides=""：去掉整个字符串前面多余的字符
              同理也有
              suffix=""：给拼串后的字符串加一个后缀
              suffixOverrides="" 去掉整个字符串后面多余的字符-->
         <trim prefix="where" prefixOverrides="and"  >
             <if test="id!=null">
                 id = #{id}
             </if>
             <if test="name!=null &amp;&amp; name.trim()!=''">
                 and name = #{name}
             </if>
         </trim>
     </select>

测试方法：

    //测试动态sql的<trim>
    @Test
    public void testGetStuByTrim() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.getStuByTrim(new Student(null, "小明"));
        System.out.println(student);
        sqlSession.close();
    }

测试结果