Mybatis常用標簽整理

更新時間：2024年04月01日 16:41:02 作者：小小野豬

日常開發(fā)中，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>
<!-- 執(zhí)行存儲過程 -->
<select id="callPprocedure" parameterType="String" useCache="false" statementType="CALLABLE">
   <![CDATA[
    {call P_PROCEDURE(
    #{name,mode=IN,jdbcType=VARCHAR},
    #{age,mode=OUT,jdbcType=VARCHAR}
    )}
    ]]>
</select>

解釋：這是用于執(zhí)行SQL查詢的最基本標簽，根據(jù)傳入的id參數(shù)從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>

解釋：根據(jù)id更新用戶記錄，只有name和email非空時才會更新相應(yīng)的字段。

4. <delete> 標簽

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

解釋：根據(jù)提供的id刪除users表中的某條記錄。

5. 動態(tài)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>

解釋：僅當傳入的參數(shù)name或email不為null時，才會更新相應(yīng)的字段。

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>

解釋：根據(jù)傳入的操作類型(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條件句語法正確，即使條件為空也不會出現(xiàn)多余的AND。

d. <foreach> 標簽（循環(huán)遍歷集合）

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

解釋：批量插入用戶，list參數(shù)是一個包含多個用戶對象的集合。

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>

解釋：用于映射查詢結(jié)果到Java對象，支持一對一、一對多等復(fù)雜映射關(guān)系。

上面整理了MyBatis中一些非常實用和常用的標簽。希望能幫助大家。

聲明