mybatis+oracle添加数据时如果数据存在就更新,如果不存在就插入


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

<!-- 普通的批量插入数据
注意:

  mybatis的jdbcType的值必须是全部大写,#{item.billCode,jdbcType=VARCHAR}

  Mybatis中jdbcType的整数类型应该为NUMERIC,而不是oracle中的整形NUMBER

-->
insert all
<foreach collection="list" item="item" index="index" >
into T_INTERF_XUE_BILL_TLJ
( bill_code, SEND_DATE, PIECE_NUMBER, BILL_WEIGHT,
REGISTER_DATE, REGISTER_MAN, REGISTER_MAN_CODE, REGISTER_SITE, REGISTER_SITE_CODE
)
values
(
#{item.billCode,jdbcType=VARCHAR} ,
#{item.sendDate,jdbcType=DATE} ,
#{item.pieceNumber,jdbcType=NUMERIC} ,
#{item.billWeight,jdbcType=NUMERIC} ,
#{item.registerDate,jdbcType=DATE} ,
#{item.registerMan,jdbcType=VARCHAR} ,
#{item.registerManCode,jdbcType=VARCHAR} ,
#{item.registerSite,jdbcType=VARCHAR},
#{item.registerSiteCode,jdbcType=VARCHAR}
)
</foreach>
select 1 from dual
</insert>

但是大部分业务是这样的,添加数据时如果数据存在就更新,如果不存在就插入,


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

        merge into T_INTERF_XUE_BILL_TLJ t
        using(
        <foreach collection="list" item="item" index="index" separator="union">
            select
            #{item.billCode,jdbcType=VARCHAR} bill_code ,
            #{item.sendDate,jdbcType=DATE} send_date,
            #{item.pieceNumber,jdbcType=NUMERIC} piece_number ,
            #{item.billWeight,jdbcType=NUMERIC} bill_weight,
            #{item.registerDate,jdbcType=DATE} register_date,
            #{item.registerMan,jdbcType=VARCHAR} register_man,
            #{item.registerManCode,jdbcType=VARCHAR} register_man_code,
            #{item.registerSite,jdbcType=VARCHAR} register_site,
            #{item.registerSiteCode,jdbcType=VARCHAR} register_site_code
            from dual
        </foreach>) t1
        on (t.bill_code = t1.bill_code)
        when matched then
        update set
        t.send_date = t1.send_date,
        t.piece_number = t1.piece_number,
        t.bill_weight = t1.bill_weight,
        t.register_date = t1.register_date,
        t.register_man = t1.register_man,
        t.register_man_code = t1.register_man_code,
        t.register_site = t1.register_site,
        t.register_site_code = t1.register_site_code
        when not matched then
        insert
        (bill_code,send_date,piece_number,bill_weight,register_date,register_man,
         register_man_code,register_site,register_site_code)
        values
        (t1.bill_code,t1.send_date,t1.piece_number,t1.bill_weight,t1.register_date,
         t1.register_man,t1.register_man_code,t1.register_site,t1.register_site_code)
    </insert>

 上述sql格式如下:

merge into 要修改的表名  别名1

using (select  要修改的字段1,要修改的字段2,  关联的字段 from 表名) 别名2

on (别名1.关联字段 = 别名2. 关联字段)

when matched  then update  set

 别名1.字段 = 别名2.字段

 别名1.字段 = 别名2.字段

 

mybatis+oracle添加数据时如果数据存在就更新,如果不存在就插入

上一篇:Eclipse简单汉化方法


下一篇:linux常用命令