Mybatis常用标签整理

---

日常开发中，MyBatis中标签有着举足轻重的重要性。以下是一些MyBatis框架中常见的标签及案例：

1. <select> 标签

<!-- 查询语句：resultType="com.example.User" -->
<select id="selectUserById" parameterType="int" resultType="com.example.User">
    SELECT * FROM T_Users WHERE id = #{id}
</select>

<!-- 查询语句：resultType="java.lang.String -->
<select id="queryAliyunPictureName" resultType="java.lang.String">
   SELECT * FROM T_User WHERE USER_ID=#{userId,jdbcType=VARCHAR}
</select>

<!-- 查询语句：parameterType="long" -->
<select id="queryPrizeNum" parameterType="long" resultType="java.lang.Integer">
    SELECT count(*)  FROM T_User_RECORD where user_id= #{userId,jdbcType=BIGINT}
</select>

<!-- 查询语句：resultMap="templateMap" -->
<select id="selectUserById" parameterType="int" resultMap="templateMap">
    SELECT * FROM T_Users WHERE id = #{id}
</select>

<!-- 查询语句：resultType="java.lang.Integer -->
<select id="queryAllUserNum" resultType="java.lang.Integer">
    SELECT count(*)  FROM T_USER_RECORD
</select>

<!-- 查询语句：添加<if> 标签，union语法，foreach语法 -->
<select id="queryUser" parameterType="com.example.UserVO" resultType="com.example.User">
    SELECT USER_ID,USER_NAME,DEPT_CODE,DEPT_NAME 
    FROM
    T_USER
    where STATUS = 'Y'
    <if test="(flag_column== 'N'.toString()) ">
        AND dept_leader = 'Y'
    </if>
    UNION
    SELECT USER_ID,USER_NAME,DEPT_CODE,DEPT_NAME 
    FROM
    T_USER
    where STATUS = 'Y'
    <if test="list!= null and list.size > 0">
        AND
        <foreach collection="list" index="index" item="item" open="(" separator="OR" close=")">
            T.USER_ID =  #{item,jdbcType=VARCHAR}
        </foreach>
    </if>) 
</select>
  
<!-- 执行存储过程 -->
<select id="callPprocedure" parameterType="String" useCache="false" statementType="CALLABLE">
   <![CDATA[
    {call P_PROCEDURE(
    #{name,mode=IN,jdbcType=VARCHAR},
    #{age,mode=OUT,jdbcType=VARCHAR}
    )}
    ]]>
</select>

解释：这是用于执行SQL查询的最基本标签，根据传入的id参数从users表中检索用户信息。

2. <insert> 标签

<!-- 新增user -->
<insert id="insertUser" parameterType="com.example.User">
    INSERT INTO users(name, email, created_at)
    VALUES (#{name,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, SYSDATE)
</insert>


<!-- 从序列中获取key -->
<insert id="addUser" parameterType="com.example.User">
    <selectKey   keyProperty="id" resultType="long" order="BEFORE" >
        SELECT SEQ_USER.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO T_USER(ID,NAME,EMAIL,CREATE_TIME)
    VALUES(#{id,jdbcType=BIGINT},#{name,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},SYSDATE)
</insert>

<!-- MERGE INTO语句 -->
<insert id="addOrUpdateUser" parameterType="com.example.User">
	    MERGE INTO T_USER_WISHS W
	    USING (SELECT
	                #{id,jdbcType=BIGINT} ID,
	                #{name,jdbcType=VARCHAR} name,
	            FROM DUAL ) D
	    ON (W.ID = D.ID)
	    WHEN MATCHED THEN UPDATE SET
	        W.NAME= D.NAME,
	        W.CREATE_TIME = D.CREATE_TIME,
	        W.UPDATE_TIME = SYSDATE
	    WHEN NOT MATCHED THEN
	    INSERT  (
	        W.ID,
	        W.NAME,
	        W.CREATE_TIME
	    )
	    VALUES(
	        D.ID,
	        D.NAME,
	        SYSDATE
	    )
</insert>   

<!-- 批量新增 -->
<insert id="addSUserList" parameterType="java.util.List">
    INSERT INTO
    T_USER(ID,USER_ID,USER_NAME,CREATE_TIME)
    SELECT SEQ_USER.NEXTVAL,T.* FROM (
    <foreach collection="list" item="item" index="index" separator="union all">
        SELECT
        #{item.userId,jdbcType=VARCHAR} USER_ID,
        #{item.userName,jdbcType=VARCHAR} USER_NAME,
        SYSDATE CREATE_TIME        
        FROM DUAL
    </foreach>) T
</insert> 


<!-- 批量merge into -->
<insert id="batchInsertUser">
    MERGE INTO T_USER_SCORE S
    USING (
    <foreach collection="list" item="item" index="index" separator="UNION ALL">
        SELECT
        #{item.userId,jdbcType=VARCHAR} USER_ID,
        #{item.userName,jdbcType=VARCHAR} USER_NAME,
        #{item.score,jdbcType=VARCHAR} SCORE
        from dual
    </foreach>
    ) D ON (S.STAFF_ID = D.STAFF_ID AND S.USER_NAME= D.USER_NAME)
    WHEN MATCHED THEN UPDATE SET
    S.SCORE = D.SCORE,
    S.UPDATE_TIME = SYSDATE
    WHEN NOT MATCHED THEN
    INSERT (
    S.ID,
    S.USER_ID,
    S.USER_NAME,
    S.SCORE,
    S.CREATE_TIME)
    VALUES(
    SEQ_USER_SCORE.NEXTVAL,
    D.USER_ID,
    D.USER_NAME,
    D.SCORE,
    SYSDATE)
</insert>

3. <update> 标签

<!-- 更新user -->
<update id="updateUser" parameterType="com.example.User">
    UPDATE users
    <set>
        name = #{name},
        email = #{email}
    </set>
    WHERE id = #{id}
</update>

<!-- 更新user：使用<if> -->
<update id="updUserByUserId">
    UPDATE T_USER_INFO
    SET AGE= #{age,jdbcType=VARCHAR},
    <if test="finishFlag != null">
        FINISH_FLAG = #{age,jdbcType=VARCHAR},
    </if>
    STATUS = #{status,jdbcType=VARCHAR}
    WHERE USER_ID = #{userId,jdbcType=VARCHAR}
</update>

<!-- 更新user：在where后面使用<if> -->
<update id="updateProtocalFlagExt" parameterType="java.lang.String">
    UPDATE T_USER 
    SET 
        UPDATE_TIME = SYSDATE,
    WHERE USER_FLAG='Y'
    <if test="isFr != null and isFr== 'Y'.toString()">
        AND USER_ID = #{userId,jdbcType=VARCHAR}
    </if>
</update>

<!-- 更新user：使用set标签 -->
<update id="updateUser" parameterType="com.example.User">
    UPDATE T_USER
    <set>
        <if test="status != null and status != ''">
            STATUS = #{status,jdbcType=VARCHAR},
        </if>
        UPDATE_DATE = SYSDATE
    </set>
    WHERE USER_ID = #{userId}
</update>

<!-- 批量更新user -->
<update id="updateUser" >
       <foreach collection="list" item="item" open="begin" close=";end;" separator=";" index="index">
           UPDATE T_USER
               SET age= #{item.age, jdbcType=VARCHAR}
           WHERE USER_ID = #{item.userId, jdbcType=VARCHAR}
       </foreach>
</update>

<!-- 批量更新user -->
<update id="updateUser">
    <foreach collection="items" item="item" separator=";" index="index">
        UPDATE ${item.tablesName} SET ${item.columnsName} = #{item.age}
        WHERE ${item.columnsUser} = #{item.userId}
    </foreach>
</update>

解释：根据id更新用户记录，只有name和email非空时才会更新相应的字段。

4. <delete> 标签

<!-- 删除user -->
<delete id="deleteUser" parameterType="int">
    DELETE FROM users WHERE id = #{id}
</delete>

解释：根据提供的id删除users表中的某条记录。

5. 动态SQL标签

a. <if> 标签

<update id="conditionalUpdate">
    UPDATE users
    <set>
        <if test="name != null">name = #{name},</if>
        <if test="email != null">email = #{email}</if>
    </set>
    WHERE id = #{id}
</update>

解释：仅当传入的参数name或email不为null时，才会更新相应的字段。

b. <choose>, <when>, <otherwise> 标签

<update id="chooseUpdate">
    UPDATE users
    <set>
        <choose>
            <when test="operation == 'updateName'">
                name = #{newName}
            </when>
            <when test="operation == 'updateEmail'">
                email = #{newEmail}
            </when>
            <otherwise>
                status = #{newStatus}
            </otherwise>
        </choose>
    </set>
    WHERE id = #{userId}
</update>

解释：根据传入的操作类型(operation)选择更新不同的字段。

c. <where> 标签

<update id="complexUpdate">
    UPDATE users
    <set>
        last_login = NOW()
    </set>
    <where>
        <if test="id != null">id = #{id}</if>
        <if test="email != null">AND email = #{email}</if>
    </where>
</update>

解释：<where>标签确保生成的SQL中WHERE条件句语法正确，即使条件为空也不会出现多余的AND。

d. <foreach> 标签（循环遍历集合）

<insert id="batchInsertUsers">
    INSERT INTO users(id, name)
    VALUES
    <foreach item="item" index="index" collection="list" separator=",">
        (#{item.id}, #{item.name})
    </foreach>
</insert>

解释：批量插入用户，list参数是一个包含多个用户对象的集合。

6. 引用其他SQL片段

<sql id="userColumns">id, name, email</sql>

<select id="selectAllUsers" resultType="com.example.User">
    SELECT 
    <include refid="userColumns"/>
    FROM users
</select>

解释：定义可重用的SQL片段并通过<include>标签引入。

7. <resultMap> 标签

<resultMap id="userResultMap" type="com.example.User">
    <id property="id" column="user_id"/>
    <result property="name" column="username"/>
    <association property="address" javaType="com.example.Address">
        <id property="addressId" column="address_id"/>
        <result property="street" column="street"/>
    </association>
</resultMap>

解释：用于映射查询结果到Java对象，支持一对一、一对多等复杂映射关系。

       上面整理了MyBatis中一些非常实用和常用的标签。希望能帮助大家。

声明

本内容版权归属于CSDN-小小野猪，任何未经授权的复制、转载、传播、贩卖、转赠等均属违法行为，必将追究法律责任！！！