当前位置：首页 > news >正文

mybatis 脚本

news 来源：原创 2024/5/19 6:45:35

1，批量更新

注意：当 parameterType="java.util.List" 的时候，collection="list"

<update id="resultFeedback" parameterType="java.util.List">
    UPDATE tm_esim_social_result 
       SET result_status = 
             <foreach collection="list" item="item" separator=" " open="(CASE id" close="ELSE result_status END),">
                WHEN #{item.id,jdbcType=VARCHAR} THEN #{item.resultStatus,jdbcType=VARCHAR}
             </foreach>
           fail_reason = 
             <foreach collection="list" item="item" separator=" " open="(CASE id" close="ELSE fail_reason END),">
                WHEN #{item.id,jdbcType=VARCHAR} THEN #{item.failReason,jdbcType=VARCHAR}
             </foreach>
           state = 
             <foreach collection="list" item="item" separator=" " open="(CASE id" close="ELSE state END),">
                WHEN #{item.id,jdbcType=VARCHAR} THEN #{item.state,jdbcType=VARCHAR}
             </foreach>
           feedback_time = NOW(),
           update_by = #{item.updateBy,jdbcType=VARCHAR},
           update_date = NOW()
     WHERE id IN 
         <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=VARCHAR}
         </foreach>
</update>

当parameterType为一个对象的时候，注意，foreach 的 colletion 属性为这个对象下面的属性

<update id="feedbackAll" parameterType="com.sf.esim.domain.payable.payDo">             
    UPDATE table01
       SET state = #{state,jdbcType=VARCHAR}                                                        
     WHERE id IN                                                                      
    <foreach collection="transferIdList" index="index" item="item" open="(" separator="," close=")">
          #{item,jdbcType=VARCHAR}                                                                  
    </foreach>                                                                                      
</update>

批量增加：

<insert id="addListPayable" parameterType="com.sf.haha.domain.payable.PayableDo">
    INSERT INTO tm_esim_payable 
    ( 
      tm_esim_payable_id,
      pay_the_city,
      account,
      bussi_type
     )
    VALUES 
    <foreach collection="list" item="item" separator=",">  
     (
       #{item.tmEsimPayableId},
       #{item.payTheCity},
       #{item.account},
       #{item.bussiType}
       )
    </foreach>  
</insert>

这个我还没有测试过，懒得测试，因为其实大概知道怎么着就可以的了

<update id="updatePersonalUnitPayById" parameterType="com.sf.esim.domain.deducted.DeductingDetailDo">
        <foreach collection="list" index="index" item="item">
            update tm_esim_deducting_detail
            <set>
                personal_pay_edit = #{item.personalPayEdit}
                ,unit_pay_edit =#{item.unitPayEdit}
                ,edit_declare = #{item.editDeclare}
            </set>
            where deducting_detail_id = #{item.deductingDetailId};
        </foreach>
    </update>

如果list的泛型是string，则collection为list

    <select id="getOrgName" parameterType="java.util.List" resultType="java.lang.String">
        SELECT GROUP_CONCAT(ORG_NAME) FROM zthr_etl_hr_org_business WHERE ORG_ID IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item,jdbcType=VARCHAR}     
           </foreach> 
    </select>

<set>标签，自动去掉最后一个逗号

DECLARE                                                                                                   
    v_id NUMBER;                                                                                          
BEGIN                                                                                                     
    SELECT t3.id into v_id FROM (                                                                         
      SELECT t.id FROM pmp_agnt_base_info t WHERE t.card_no = #{cardNo} ORDER BY t.create_time DESC       
    )t3 WHERE ROWNUM = 1;                                                                                 
                                                                                                          
    IF v_id IS NOT NULL THEN                                                                              
          UPDATE pmp_agnt_base_info                                                                         
              <set>                                                                                         
                  <if test="@Ognl@isNotEmpty(quasiDrivingType)">driving_license = #{quasiDrivingType},</if> 
                  <if test="@Ognl@isNotEmpty(idCard)">driving_license_no = #{idCard},</if>                  
                  <if test="@Ognl@isNotEmpty(vin)">vehicle_no = #{vin},</if>                                
              </set>                                                                                        
            WHERE id = v_id;                                                                                 
            UPDATE pmp_agnt_sign_info                                                                        
              <set>                                                                                         
                  <if test="@Ognl@isNotEmpty(quasiDrivingType)">driving_license = #{quasiDrivingType},</if> 
                  <if test="@Ognl@isNotEmpty(idCard)">driving_license_no = #{idCard},</if>                  
                  <if test="@Ognl@isNotEmpty(vin)">vehicle_no = #{vin},</if>                                
              </set>                                                                                        
            WHERE base_id = v_id;                                                                            
            INSERT INTO pmp_agnt_record_sign (record_card_no,update_time) VALUES (#{cardNo},SYSDATE);        
    END IF;                                                                                                     
COMMIT;                                                                                                   
END;