mybatis的批量操作记录

1.批量检索(oracle)

<select id="selectById" parameterType="java.util.List" resultMap="BaseResultMap">
  select p.PROD_ID from bf_prod_basic_info p
  where p.PROD_ID in
  <foreach collection="list" item="item" open="(" close=")" separator=",">
    #{item.id}
  </foreach>
</select>
2.批量更新(oracle)

<update id="updateById" parameterType="java.util.List">
  <foreach collection="list" item="item" index="index"  open="begin" close=";end;" separator=";">
    update bf_prod_basic_info p
    <set>
      p.UNIT_WGT=#{item.unitWgt,jdbcType=VARCHAR},p.CREATE_DATE=(select sysdate from dual),
      p.CREATE_TYPE='03'
    </set>
    where p.PROD_ID= #{item.prodId,jdbcType=VARCHAR}
  </foreach>
</update>
(mysql写法不一样!)
<foreach>改成这样:
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
3.批量插入(oracle)

例一:

<insert id="insertWeight" parameterType="java.util.List">

  <foreach collection="list" item="item" index="index"  open="begin" close=";end;" separator=";">
    insert into bf_prod_basic_info
    (PROD_ID,UNIT_WGT,CREATE_DATE,CREATE_TYPE) values
    (
    #{item.prodId,jdbcType=DECIMAL},
    #{item.unitWgt,jdbcType=DECIMAL},
    (select sysdate from dual),
    '03'
    )
  </foreach>
</insert>

例二

</insert>
  <insert id="insertByBatch" parameterType="java.util.List">
      insert into SF_ERPSTATUS_TOOS (ID, DOCTYPE, DOCID,
      OPSTATE, OPUSERCODE, OPUSERNAME,
      OSORDERCODE, OPDATE,UPDATETIME,
      WAREHOUSECODE, IS_UPDATE, REMARK,
      ERROR_TYPE, IDT_ID)
      )
      <foreach collection="list" item="item" index="index" separator="union all">
          (select FU_GET_NEXT_IDENTITY('SF_ERPSTATUS_TOOS', 1), #{item.doctype,jdbcType=VARCHAR}, #{item.docid,jdbcType=INTEGER},
          #{item.opstate,jdbcType=INTEGER}, #{item.opusercode,jdbcType=VARCHAR}, #{item.opusername,jdbcType=VARCHAR},
          #{item.osordercode,jdbcType=VARCHAR}, sysdate,sysdate,
          #{item.warehousecode,jdbcType=VARCHAR}, 0, #{item.remark,jdbcType=VARCHAR},
          #{item.errorType,jdbcType=INTEGER}, #{item.idtId,jdbcType=INTEGER} FROM DUAL)
      </foreach>
  </insert>

4.检索数据,有重复的只选一条,选择标准是判断该属性的大小关系。(oracle)

select * from erp_dimension_update a
where TRANS_STATE=0 <![CDATA[AND ROWNUM <=50 ]]> and not exists
(select 1 from erp_dimension_update
where a.itemname = itemname
and a.trans_lastdatetime>trans_lastdatetime)
Order By a.trans_lastdatetime  asc

5.若存在,则更新(oracle)

<update id="updateById" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index"  open="begin" close=";end;" separator=";">
        update bf_prod_basic_info p
        <set>
            p.UNIT_WGT=#{item.unitWgt,jdbcType=VARCHAR},
            p.CREATE_DATE=(select sysdate from dual),
            p.CREATE_TYPE='03'
        </set>
        where exists
        (select * from bf_prod_basic_info where p.PROD_ID= #{item.id,jdbcType=DECIMAL})
        and p.PROD_ID= #{item.id,jdbcType=DECIMAL}
    </foreach>
</update>
6.若不存在,则插入

(oracle)

<insert id="insertWeight" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index"  open="begin" close=";end;" separator=";">
        insert into bf_prod_basic_info
        (PROD_ID,UNIT_WGT,CREATE_DATE,CREATE_TYPE)
        select
        #{item.id,jdbcType=DECIMAL},
        #{item.unitWgt,jdbcType=DECIMAL},
        (select sysdate from dual),
        '03'
        from dual
         where not exists
        (select * from bf_prod_basic_info p where p.PROD_ID= #{item.id,jdbcType=DECIMAL})
    </foreach>
</insert>
7.Oracle数据库in里面只能批量检索1000条数据,如果想批量检索超过1000的数据,就使用or!(oracle)

<select id="selectById" parameterType="java.util.List" resultMap="ProdBaseResultMap">
    select p.PROD_ID from bf_prod_basic_info p
 	where
    <foreach collection="list" item="item" open="(" close=")" separator="or">
  	 p.PROD_ID in
 	#{item.id,jdbcType=DECIMAL}
    </foreach>
</select>
最大的缺陷就是效率问题!我测试了1000条数据,or花费的事件是in的5倍!

8.批量查询(in和exists的区别)

 select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
    T1数据量小而T2数据量非常大时,T1<<T2 时,1) 的查询效率高。
 select * from T1 where T1.a in (select T2.a from T2) ;
     T1数据量非常大而T2数据量小时,T1>>T2 时,2) 的查询效率高。

8.传多个参数,其中一个是List参数,一个是普通类型参数(oracle)

<select id="testMoreParams" resultType="com.mb.wzl.entity.User">
    select * from USER
    where USERNAME=#{userName}
    or NAME IN
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item.name}
    </foreach>
</select>
dao层:
List  testMoreParams(@Param("list") List list, @Param("userName") String userName);


9.批量插入

<insert id="insertByBatch" parameterType="java.util.List">
    insert into MA_B2C_STATUS (ID, TRANSACTION_TYPE, TRANSACTION_CODE,
    TRANSACTION_NUMBER, ORDER_SN, WAREH_CODE,
    DOC_STATE, CREATE_DATE, IS_UPDATE,
    REMARK)
    <foreach collection="list" item="item" index="index" separator="union all">
        ( select
        (SELECT SYS_GUID () from dual),
        #{item.transactionType,jdbcType=VARCHAR},
        #{item.transactionCode,jdbcType=VARCHAR},
        #{item.transactionNumber,jdbcType=DECIMAL},
        #{item.orderSn,jdbcType=VARCHAR},
        #{item.warehCode,jdbcType=VARCHAR},
        #{item.docState,jdbcType=VARCHAR},
        (select sysdate from dual),
        #{item.isUpdate,jdbcType=DECIMAL},
        #{item.remark,jdbcType=VARCHAR}
        from dual)
    </foreach>
</insert>




10.批量删除

delete from wms_dgnrpt_hq01w850 d
where
d.order_sn in
<foreach collection="list" index="index" item="item" open="("  separator="," close=")" >
  #{item.orderSn,jdbcType=VARCHAR}
</foreach>














评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值