Mybatis 的常用技巧

发表于2021-01-26更新于2022-01-27阅读次数

多条记录合并为一行

假如有表 stu, 有 name 列如下

name
小明
小李
小米

现在要把 name 列拼接为一行, 如 小明,小李,小米, 可以使用如下 sql

1
SELECT GROUP_CONCAT(name SEPARATOR ',') AS names FROM stu;

批量插入

1
2
3
4
5
6
7
8
9
<insert id="batchInsert" parameterType="com.xxx.UserDTO"
        useGeneratedKeys="true" keyProperty="id">
    INSERT INTO t_user
    (name, parent_name, create_time)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.parentName}, now())
    </foreach>
</insert>

批量更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<update id="batchUpdate" parameterType="com.xxx.UserDTO">
    UPDATE t_user
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="item">
                <if test="item.name !=null">
                    when id=#{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="parent_name =case" suffix="end,">
            <foreach collection="list" item="item">
                <if test="item.parentName !=null">
                    when id=#{item.id} then #{item.parentName}
                </if>
            </foreach>
        </trim>
    </trim>
    where id in
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

in() 有序查询

1
2
3
4
5
6
7
8
9
10
<select id="findByIdsWithSorted" parameterType="java.lang.Long" resultType="com.xxx.UserDTO">
    SELECT *
    from t_user WHERE id IN
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
    <foreach collection="list" item="item" open="order by field(id," close=")" separator=",">
        #{item}
    </foreach>
</select>