MyBatis常用的小技巧

1、MyBatis使用in语句，入参为字符串或集合时的写法

1、当levels为字符串时，如："1,2,3,4,5"，使用in (${param})

<select id="xxxxxxxx" resultType="java.lang.String">
    select username from user where status = 0

    <if test="levels != null and '' != levels">
        and level in
        
        (${levels})
    </if>
</select>

2、当levels为List时，如：[1,2,3,4,5]，使用in foreach遍历
<select id="xxxxxxxx" resultType="java.lang.String">
    select username from user where status = 0

    <if test="levels.size() > 0">
        and level in

        <foreach item="level" index="index" collection="levels" open="(" close=")" separator=",">
            #{level}
        </foreach>
    </if>
</select>

2、MyBatis新增insert语句执行完返回该条记录自增的ID

<insert id="xxxxxxxx" parameterType="com.example.ProductPo">
    insert into product (name, type_id, price, create_time, update_time)
    value (#{name}, #{type_id}, #{price}, #{create_time}, #{update_time})
    <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Long" order="AFTER">
        select LAST_INSERT_ID() as id
    </selectKey>
</insert>

3、MyBatis使用like concat实现模糊查询，还能防止SQL注入

<select id="fn" resultType="java.lang.Long">
    select
        id
    from
        t_xxx
    where name like concat('%', #{keyName}, '%')
    order by name
</select>