mybatis踩坑记录之if标签_mybatisplus if标签

mybatis踩坑记录之if标签

参考网址:

https://mp.weixin.qq.com/s?__biz=MzAwMjk5Mjk3Mw==&mid=2247494777&idx=2&sn=324b2cbcba0f8aeef12d42cee4a9c382&chksm=9ac3525badb4db4d27bf2a97506404087712293d4d7308a01c186b1f65bd5a3aa7092a8921c6&mpshare=1&scene=23&srcid=0107OvSYpJ4U87Qon2fSjRp4&sharer_sharetime=1610370060848&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8#rd

https://blog.csdn.net/weixin_44144211/article/details/109347443?ops_request_misc=&request_id=&biz_id=102&utm_term=mybaits%25E4%25B8%25ADif%25E6%25A0%2587%25E7%25AD%25BE&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-1-109347443.first_rank_v2_pc_rank_v29

帖子参考

<!-- 以下为错误写法,会抛NumberFormatException异常 -->
<if test="username == 'U'">

<!-- 正确写法如下两种 -->
<if test="username == 'U'.toString()">
<if test='username == "U"'>
1
2
3
4
5
6

测试

1.准备测试环境

springboot整合mybatis-plus

说明:springboot整合mybatis也是可以的

• sql建表脚本

CREATE TABLE `demo` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1
2
3
4

测试数据

MariaDB [test]> select * from demo;
+------+-------+
| id   | name  |
+------+-------+
|    1 | root  |
|    1 | NULL  |
| NULL | root  |
|    2 | root2 |
+------+-------+
4 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
9
10

• 实体类

@Data
@TableName("demo")
public class Demo {
    private Integer id;
    private String name;
}

1
2
3
4
5
6
7

• mapper接口

package com.shaoming.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shaoming.model.entity.Demo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Auther: shaoming
 * @Date: 2021/1/11 15:37
 * @Description:
 */
public interface DemoMapper extends BaseMapper<Demo> {
    /**
     * 测试<if test></if>的踩坑记录
     * @param name
     * @return
     */
    List<Demo> selectByName(@Param("name") String name);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

• mapper.xml

<?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.shaoming.mapper.DemoMapper">

    <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test='name=="all"'>
              1=1
            </if>
        </where>
    </select>
</mapper>

1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.需求分析(需求一)

• 具体需求

如果查询name的值为admin 那么就查询所有

• 测试方法

package com.shaoming;

import com.shaoming.mapper.DemoMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

/**
 * @Auther: shaoming
 * @Date: 2021/1/11 15:38
 * @Description:
 */
@SpringBootTest
public class DaoTest {
    @Autowired
    private DemoMapper demoMapper;
  
    @Test
    public void testMybatisOfIf(){
        demoMapper.selectByName("all").forEach(System.out::println);
    }
}
    /**
     * 控制台打印
     Demo(id=1, name=root)
     Demo(id=1, name=null)
     Demo(id=null, name=root)
     Demo(id=2, name=root2)
     */

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

• mapper.xml的三种写法

第一种

    <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test='name=="all"'>
              1=1
            </if>
        </where>
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10

第二种

    <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test="name='all'.toString()">
              1=1
            </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

第三种

去除1=1, 1=1表示where条件恒成立,就是查询所有,如果去掉也是可以的

    <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test="name='all'.toString()">
           
            </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

3.演变的需求分析(需求二)

如果name的值不是all,那么就按照实际条件进行查询

第一种写法

 <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test="name!='all'">
               name = #{name}
            </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

第二种写法

  <select id="selectByName" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
            <if  test="name!='all'.toString()">
               name = #{name}
            </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

测试方法

//查询name为root的值记录 
@Test
    public void testMybatisOfIf(){
        demoMapper.selectByName("root").forEach(System.out::println);
    }
    /**
     * 控制台打印
     Demo(id=1, name=root)
     Demo(id=null, name=root)
     */
1
2
3
4
5
6
7
8
9
10

4.需求三

临时通过比较需求二和需求三,突然想起来test后面比较的如果是Integer

特别注意

if标签把空字符串和数字0判断成了相等

<if test="a==''">
</if>
上面代码等价于
<if test="a==0">
</if>
1
2
3
4
5

如果比较的是其他数字

等于

 <select id="selectById" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
          <if test="id==1">
              id is null
          </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

 @Test
    public void testid(){
        demoMapper.selectById(1).forEach(System.out::println);
    }
        /**
控制台打印
Demo(id=null, name=root)
*/
1
2
3
4
5
6
7
8

不等于

<select id="selectById" resultType="com.shaoming.model.entity.Demo">
        select * from demo
        <where>
          <if test="id!=1">
              id is null
          </if>
        </where>
    </select>
1
2
3
4
5
6
7
8

 @Test
    public void testid(){
        demoMapper.selectById(2).forEach(System.out::println);
    }
/**
控制台打印
Demo(id=null, name=root)
*/
1
2
3
4
5
6
7
8

5.总结

1.比较需求一和需求二

<!-- if标签字符窜等于的书写方式 --> 
<if  test="name='all'.toString()">
<!-- if标签字符窜不等于的书写方式 -->    
 <if  test="name!='all'">
1
2
3
4

mybaits中if标签等于和不等于书写规则是不同,建议使用上面这种方法书写,容易记忆

2.需求三是数值类型

注意当和0比较时候的特殊性