mybatis动态sql中if标签使用_mybatis select if

1. insert语句

• prefix 前缀
• suffix 后缀
• suffixOverrides=“,” 不保留最后的逗号

<insert id="addLicense">
        insert into ocr_auth_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="licensekey != null ">
                `license_key`,
            </if>
            <if test="deviceId != null ">
                `device_id`,
            </if>
            <if test="licenseName != null ">
                `license_name`,
            </if>
            <if test="authType != null ">
                `auth_type`,
            </if>
            <if test="lastAuthStatus != null ">
                `last_auth_status`,
            </if>
            <if test="lastAuthTime != null ">
                `last_auth_time`,
            </if>
            <if test="expiredTime != null ">
                `expired_time`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="licensekey != null ">
                #{licensekey},
            </if>
            <if test="deviceId != null">
                #{deviceId},
            </if>
            <if test="licenseName != null ">
                #{licenseName},
            </if>
            <if test="authType != null ">
                #{authType},
            </if>
            <if test="lastAuthStatus != null ">
                #{lastAuthStatus},
            </if>
            <if test="lastAuthTime != null ">
                #{lastAuthTime},
            </if>
            <if test="expiredTime != null ">
                #{expiredTime},
            </if>
        </trim>
    </insert>
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

2. update语句

<update id="updateLicense">
        update ocr_auth_info
        <set>
            <if test="licensekey != null">
                license_key=#{licensekey},
            </if>
            <if test="licenseName != null">
                license_name=#{licenseName},
            </if>
            <if test="authType != null">
                auth_type=#{authType},
            </if>
            <if test="lastAuthStatus != null">
                last_auth_status=#{lastAuthStatus},
            </if>
            <if test="lastAuthTime != null">
                last_auth_time=#{lastAuthTime},
            </if>
            <if test="expiredTime != null">
                expired_time=#{expiredTime}
            </if>
        </set>
        where device_id = #{deviceId}

    </update>
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

3. select 语句

• where 1=1 防止if标签全不成立

<select id="querylist"
            resultType="com.jhlinux.clothes.entity.ClothesList">

        SELECT c.id,c.number,c.type,c.status,c.times_cleaned,c.create_time,f.deadline,f.number total FROM `clothes` c
        LEFT JOIN config f on c.type = f.type
        where 1=1
        <if test="status != null">
            and c.status = #{status}
        </if>

        <if test="type != null and type != ''">
            and c.type = #{type}
        </if>
        <if test="number != null and number != ''">
            and c.number = #{number}
        </if>
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17