postgresql mybatis 批量update

原创
2017/02/24 16:59
阅读数 7.8K

有些时候需要批量更新,在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},</ when>
                         <otherwise >null, </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},
                         </when>
                         <otherwise > null, </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},
                         </when>
                         <otherwise > null, </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 >

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部