Mybatis-xml语法备忘

# 批量更新

<update id="batchUpdate">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        update table
        <set>
            <if test="item.cloumn1 != null">
                cloumn1 = #{item.cloumn1},
            </if>
            cloumn2 = #{item.cloumn1}
        </set>
        where id = #{item.id}
    </foreach>
</update>

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

# 小于号处理

方式一：
	<![CDATA[ a < 1 ]]>
方式二：
	< 改为 &lt;

1
2
3
4

# in大于1000

 where (字段1 in
<foreach collection="codes" item="code"  open="(" close=")" index="index">
    <if test="index != 0">
        <choose>
            <when test="index % 1000 == 999">) OR user_code IN (</when>
            <otherwise>,</otherwise>
        </choose>
    </if>
	#{code}
</foreach>
)

1
2
3
4
5
6
7
8
9
10
11

# 字段/条件分装

    <sql id="Base_Column_List">
        id
    </sql>
	<select id="pageList" resultType="VO">
        SELECT
            <include refid="Base_Column_List"/>
        FROM a
        WHERE 1 = 1
	</select>

1
2
3
4
5
6
7
8
9

# 更新空报错问题

带上类型
#{code, jdbcType=VARCHAR}

1
2

# 调用存储过程

<![CDATA[
   {CALL P_XX(
  		#{code,jdbcType=VARCHAR,mode=IN},
         #{P_rest,jdbcType=CURSOR,mode=OUT,resultMap=vo,javaType=java.sql.ResultSet}
     )}
]]>
<resultMap type="VO" id="vo">
    <result property="code"  column="code" />
</resultMap>

1
2
3
4
5
6
7
8
9

# 行转列

# oracle 19G
LISTAGG(DISTINCT decode ( a.code,1, to_char(a.name), NULL ), ',' ) WITHIN GROUP (ORDER BY a.name) name
# oracle 11G
GROUP_CONCAT( DISTINCT IF ( sa.code = 1, a.name, NULL ) ) name,

1
2
3
4

# 复合操作

# 根据selectKey的结果确认新增或修改
<insert id="insertOrUpdate">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(1) from user where phone = #{phone}
        </selectKey>
        <if test="count > 0">
            update user
            set name=#{name}
            where  phone = #{phone}
        </if>
        <if test="count==0">
            INSERT INTO
            user
            (id,phone,name)
            VALUES
            (#{id},#{phone},#{name})
        </if>
    </insert>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上次更新: 2026/3/11 21:47:04

← Feign远程调用邮件发送自定义Excel→