postgresql mybatis 批量update

有些时候需要批量更新，在java中for循环更新效率太慢，通过sql批量执行可以大幅度提升性能。

批量更新demo：

< update id="batchUpdate">
          UPDATE eplus_project_standard
          SET device_type_id = contents.device_type_id,type = contents.type,project_id = contents.project_id,summary = contents.summary,detail = contents.detail,period_type = contents.period_type,period_value = contents.period_value,index = contents.index
          FROM (
          VALUES
           <foreach collection ="standards" item="stand" index="index"
           open="" close= "" separator= ",">
              (#{stand.deviceType.id},<trim suffix="::jsonb"> #{stand.standard}</trim >,#{stand.type},#{stand.projectId},#{stand.id},#{stand.summary},#{stand.detail},#{stand.periodType},#{stand.periodValue},#{stand.index})
           </foreach>
          ) as
          contents(device_type_id,standard,type,project_id,id,summary,detail,period_type,period_value,index)
          WHERE
          contents.id
          = eplus_project_standard.id;
     </update >

但是有些时候，列的类型为特殊类型。列的value为null，则sql判断不出value应该是什么类型，会抛出错误。我们在sql中指定类型就可以了。

demo：

<update id= "updateBatch">
          update ps_fair_places
           <set >
              fair_id=place.fairId,organization_id=place.orgId,status=place.status,current_interviewer=place.currentInterviewerId,current_participant=place.currentParticipantId,jobs =place.jobs,channel=place.channel:: jsonb,allow=place.allow,interview_id=place.currentInterviewId,start_time=place.startTime:: timestamp without time zone,end_time=place.endTime:: timestamp without time zone
           </set>
          from (
          values
           <foreach collection ="fairPlaces" item="p" open= "" close =""
               separator= ",">
               <trim prefix ="(" suffix=")">
                   #{p.id},<choose >
                         <when test ="p.fair != null">#{p.fair.id},</ when>
                         <otherwise >null,</otherwise>
                    </choose>
                    <choose >
                         <when test ="p.org != null">#{p.org.id},</otherwise>
                    </choose>
                   #{p.status},<choose >
                         <when test ="p.currentInterviewer != null">
                             #{p.currentInterviewer.id},</when>
                         <otherwise > null,</otherwise>
                    </choose>
                    <choose >
                         <when test ="p.currentParticipant != null">
                             #{p.currentParticipant.id},</otherwise>
                    </choose>
                    <choose >
                         <when test ="p.jobs != null">
                              <trim prefix ="ARRAY[" suffix= "]">
                                   <foreach collection="p.jobs" item ="job" separator=","> #{job.id}
                                   </foreach>
                              </trim>,</when>
                         <otherwise >
                             null,</otherwise>
                    </choose>
                    <choose >
                         <when test ="p.channel != null">
                             #{p.channel},</otherwise>
                    </choose>
                   #{p.allow},#{p.currentInterviewId},#{p.startTime},#{p.endTime}
               </trim>
           </foreach>
          ) as place(
          id,fairId,orgId,status,currentInterviewerId,currentParticipantId,jobs,channel,allow,currentInterviewId,startTime,endTime
          )
          where place.id = ps_fair_places.id
     </update >

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!