mybatis批量操作数据库

柔情只为你懂 2023-10-03 14:06 11阅读 0赞

# 1、批量查询（传入的集合需判断不为空） #

<select id="selectLockList" resultMap="BaseResultMap">
              SELECT
                <include refid="Base_Column_List"/>
            FROM
            	DOC_REPORT_SERVICE_LOCK
            WHERE
            <foreach collection="selectLockList" index="index" item="docReportServiceLock" open="(" close=")" separator="or">
               (  IS_DELETE=0
            <if test="docReportServiceLock.status != null">
            AND STATUS=#{docReportServiceLock.status,jdbcType=NUMERIC}
            </if>
            <if test="docReportServiceLock.examCheckSoid != null">
            AND EXAM_CHECK_SOID = #{docReportServiceLock.examCheckSoid,jdbcType=NUMERIC}
            </if>
            <if test="docReportServiceLock.requestSoid != null">
            AND REQUEST_SOID = #{docReportServiceLock.requestSoid,jdbcType=NUMERIC}
            </if>
            <if test="docReportServiceLock.registrationSheetSoid != null">
            AND REGISTRATION_SHEET_SOID = #{docReportServiceLock.registrationSheetSoid,jdbcType=NUMERIC}
            </if>
            <if test="docReportServiceLock.policySoid != null">
            AND POLICY_SOID = #{docReportServiceLock.policySoid,jdbcType=NUMERIC}
            </if>
             <if test="docReportServiceLock.businessClassSoid != null">
            AND BUSINESS_CLASS_SOID = #{docReportServiceLock.businessClassSoid,jdbcType=NUMERIC}
            </if>
            <if test="docReportServiceLock.customerSoid != null">
            AND CUSTOMER_SOID = #{docReportServiceLock.customerSoid,jdbcType=NUMERIC}
            </if>
                )
            </foreach>
    
        </select>

当传入集合不为空时输出sql为：

SELECT
    	REPORT_SERVICE_LOCK_SOID,
    	[STATUS],
    	UNLOCK_USER_SOID,
    	LOCK_USER_SOID,
    	UNLOCK_DATE_TIME,
    	LOCK_DATE_TIME,
    	REQUEST_SOID,
    	BUSINESS_CLASS_SOID,
    	POLICY_SOID,
    	REGISTRATION_SHEET_SOID,
    	EXAM_CHECK_SOID,
    	CREATOR,
    	CREATE_DATE,
    	UPDATE_USER,
    	UPDATE_DATE,
    	IS_DELETE,
    	CUSTOMER_SOID 
    FROM
    	DOC_REPORT_SERVICE_LOCK 
    WHERE
    	(
    		( IS_DELETE = 0 AND BUSINESS_CLASS_SOID = 27508088835670016 AND CUSTOMER_SOID = 2222 ) 
    		OR ( IS_DELETE = 0 AND BUSINESS_CLASS_SOID = 123456        AND CUSTOMER_SOID = 2222 ) 
    	);

入参为空时会报错

2、批量添加

<insert id="addLockList">
        insert into DOC_REPORT_SERVICE_LOCK (REPORT_SERVICE_LOCK_SOID, [STATUS], UNLOCK_USER_SOID,
          LOCK_USER_SOID, UNLOCK_DATE_TIME, LOCK_DATE_TIME,
          REQUEST_SOID, BUSINESS_CLASS_SOID, POLICY_SOID,
          REGISTRATION_SHEET_SOID, EXAM_CHECK_SOID, CREATOR,
          CREATE_DATE, UPDATE_USER, UPDATE_DATE,
          IS_DELETE,CUSTOMER_SOID)
        values
           <foreach collection="docReportServiceLockList" item="lock" separator=",">
             (#{lock.reportServiceLockSoid,jdbcType=NUMERIC},#{lock.status,jdbcType=NUMERIC},
              #{lock.unlockUserSoid,jdbcType=NUMERIC}, #{lock.lockUserSoid,jdbcType=NUMERIC},
              #{lock.unlockDateTime,jdbcType=TIMESTAMP}, #{lock.lockDateTime,jdbcType=TIMESTAMP},
              #{lock.requestSoid,jdbcType=NUMERIC}, #{lock.businessClassSoid,jdbcType=NUMERIC},
              #{lock.policySoid,jdbcType=NUMERIC},#{lock.registrationSheetSoid,jdbcType=NUMERIC},
              #{lock.examCheckSoid,jdbcType=NUMERIC},#{lock.creator,jdbcType=NUMERIC},
              #{lock.createDate,jdbcType=TIMESTAMP}, #{lock.updateUser,jdbcType=NUMERIC},
              #{lock.updateDate,jdbcType=TIMESTAMP},#{lock.isDelete,jdbcType=NUMERIC},
              #{lock.customerSoid,jdbcType=NUMERIC})
           </foreach>
        </insert>

输出sql：

INSERT INTO DOC_REPORT_SERVICE_LOCK (
    	REPORT_SERVICE_LOCK_SOID,
    	[STATUS],
    	UNLOCK_USER_SOID,
    	LOCK_USER_SOID,
    	UNLOCK_DATE_TIME,
    	LOCK_DATE_TIME,
    	REQUEST_SOID,
    	BUSINESS_CLASS_SOID,
    	POLICY_SOID,
    	REGISTRATION_SHEET_SOID,
    	EXAM_CHECK_SOID,
    	CREATOR,
    	CREATE_DATE,
    	UPDATE_USER,
    	UPDATE_DATE,
    	IS_DELETE,
    	CUSTOMER_SOID 
    )
    VALUES
    	(
    		53929189824266240,
    		45989833557934080,
    		NULL,
    		41284770956574720,
    		NULL,
    		'2021-08-19 11:45:32.276',
    		NULL,
    		27508088835670016,
    		NULL,
    		NULL,
    		NULL,
    		41284770956574720,
    		'2021-08-19 11:45:32.276',
    		41284770956574720,
    		'2021-08-19 11:45:32.276',
    		0,
    		53864558103625728 
    	),
    	(
    		53929189832654848,
    		45989833557934080,
    		NULL,
    		41284770956574720,
    		NULL,
    		'2021-08-19 11:45:32.277',
    		53521535058575360,
    		1986,
    		20593255959759872,
    		53521537117978624,
    		53521538267217920,
    		41284770956574720,
    		'2021-08-19 11:45:32.277',
    		41284770956574720,
    		'2021-08-19 11:45:32.277',
    		0,
        NULL 
    	);

3、批量修改

<update id="deleteLockList">
           <foreach collection="docReportServiceLockList" item="lock" open="begin" close="end;" separator=";">
                update DOC_REPORT_SERVICE_LOCK
                set  [STATUS] = #{lock.status,jdbcType=NUMERIC},
                     UNLOCK_USER_SOID = #{lock.unlockUserSoid,jdbcType=NUMERIC},
                     UNLOCK_DATE_TIME = #{lock.unlockDateTime,jdbcType=TIMESTAMP},
                     UPDATE_USER = #{lock.updateUser,jdbcType=NUMERIC},
                     UPDATE_DATE = #{lock.updateDate,jdbcType=TIMESTAMP},
                     IS_DELETE = #{lock.isDelete,jdbcType=NUMERIC}
                where IS_DELETE=0
                      <if test="lock.examCheckSoid != null">
                      AND EXAM_CHECK_SOID = #{lock.examCheckSoid,jdbcType=NUMERIC}
                      </if>
                      <if test="lock.requestSoid != null">
                      AND REQUEST_SOID = #{lock.requestSoid,jdbcType=NUMERIC}
                      </if>
                      <if test="lock.registrationSheetSoid != null">
                      AND REGISTRATION_SHEET_SOID = #{lock.registrationSheetSoid,jdbcType=NUMERIC}
                      </if>
                      <if test="lock.policySoid != null">
                      AND POLICY_SOID = #{lock.policySoid,jdbcType=NUMERIC}
                      </if>
                       <if test="lock.businessClassSoid != null">
                      AND BUSINESS_CLASS_SOID = #{lock.businessClassSoid,jdbcType=NUMERIC}
                      </if>
                      <if test="lock.customerSoid != null">
                      AND CUSTOMER_SOID = #{lock.customerSoid,jdbcType=NUMERIC}
                      </if>
           </foreach>

BEGIN
      UPDATE DOC_REPORT_SERVICE_LOCK 
    		SET [STATUS] = 45989877979807744,
    		UNLOCK_USER_SOID = 123456,
    		UNLOCK_DATE_TIME = '2021-08-16 08:00:00.0',
    		UPDATE_USER = 41284770956574720,
    		UPDATE_DATE = '2021-08-16 08:00:00.0',
    		IS_DELETE = 1 
    	WHERE
    		IS_DELETE = 0 
    		AND EXAM_CHECK_SOID = 1 
    		AND REQUEST_SOID = 1 
    		AND REGISTRATION_SHEET_SOID = 1 
    		AND POLICY_SOID = 20593487892188160 
    		AND BUSINESS_CLASS_SOID = 1986;
    		
    	UPDATE DOC_REPORT_SERVICE_LOCK 
    		SET [STATUS] = 45989877979807744,
    		UNLOCK_USER_SOID = 123456,
    		UNLOCK_DATE_TIME = '2021-08-16 08:00:00.0',
    		UPDATE_USER = 41284770956574720,
    		UPDATE_DATE = '2021-08-16 08:00:00.0',
    		IS_DELETE = 1 
    	WHERE
    		IS_DELETE = 0 
    		AND BUSINESS_CLASS_SOID = 27508088835670016 
    		AND CUSTOMER_SOID = 2222 
    	END;