Mybatis 批量 foreach merge into用法

发表于   |   分类于   |  

业务要求

批量插入时间价格表，如果某个套餐的某天的价格存在，则更新，不存在则插入

实现思路

  • oracle 9i及其以后版本支持的merge into语句，用于实现insertOrUpdate的功能
  • mybatis的动态SQL 语法foreach循环插入，待插入的实体bean的List通过查询数据库dual形成表，通过foreach的separator属性设置每次循环的隔离词为union连接每次形成的表为一个总表
  • 总表于条件匹配，符合条件update，不符合insert
  • 更新的数据来源于java.util.List中的实体的属性

缺陷

采用union 拼接sql,导致问题sql过长，耗时，性能不好，且达到一定量会超出sql长度限制，不推荐；
一般20字段以内的表新增，数据2000条以内可用，经过测试。

注意 union all会把所有结果集拼接在一起，union也会拼接所有结果，但会去除重复

mapper配置实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
<insert id="merge" parameterType="java.util.List">
    merge into route_package_vst_time_price r using 
    (    
        <foreach collection="list" index="index" item="item" open="" close="" separator="union">
            select 
                #{item.unitId jdbcType=NUMERIC} unitId,
                #{item.specDate jdbcType=DATE} specDate,
                #{item.stockType jdbcType=VARCHAR} stockType,
                #{item.stock jdbcType=NUMERIC} stock,
                #{item.totalStock jdbcType=NUMERIC} totalStock,
                #{item.oversellFlag jdbcType=VARCHAR} oversellFlag,
                #{item.adultOnSaleFlag jdbcType=VARCHAR} adultOnSaleFlag,
                #{item.childOnSaleFlag jdbcType=VARCHAR} childOnSaleFlag,
                #{item.gapOnSaleFlag jdbcType=VARCHAR} gapOnSaleFlag,
                #{item.adultSettlementPrice jdbcType=NUMERIC} adultSettlementPrice,
                #{item.adultPrice jdbcType=NUMERIC} adultPrice,
                #{item.childSettlementPrice jdbcType=NUMERIC} childSettlementPrice,
                #{item.childPrice jdbcType=NUMERIC} childPrice,
                #{item.gapSettlementPrice jdbcType=NUMERIC} gapSettlementPrice,
                #{item.gapPrice jdbcType=NUMERIC} gapPrice,
                #{item.aheadBookTime jdbcType=NUMERIC} aheadBookTime,
                #{item.bookLimitType jdbcType=VARCHAR} bookLimitType
            from dual
         </foreach>
    )tmp
    on ( 
        tmp.unitId = r.UNIT_ID 
        and tmp.specDate = r.SPEC_DATE
        )
    when matched THEN
    update
    <set>
        <if test="item.stockType != null and item.stockType != ''">
            r.STOCK_TYPE = tmp.stockType,
        </if>
        <if test="item.stock != null and item.stock != ''">
            r.STOCK = tmp.stock,
        </if>
        <if test="item.totalStock != null and item.totalStock != ''">
            r.TOTAL_STOCK = tmp.totalStock,
        </if>
        <if test="item.oversellFlag != null and item.oversellFlag != ''">
            r.OVERSELL_FLAG = tmp.oversellFlag,
        </if>
        <if test="item.adultOnSaleFlag != null and item.adultOnSaleFlag != ''">
            r.ADULT_ON_SALE_FLAG = tmp.adultOnSaleFlag,
        </if>
        <if test="item.childOnSaleFlag != null and item.childOnSaleFlag != ''">
            r.CHILD_ON_SALE_FLAG = tmp.childOnSaleFlag,
        </if>
        <if test="item.gapOnSaleFlag != null and item.gapOnSaleFlag != ''">
            r.GAP_ON_SALE_FLAG = tmp.gapOnSaleFlag,
        </if>
        <if test="item.adultSettlementPrice != null and item.adultSettlementPrice != ''">
            r.ADULT_SETTLEMENT_PRICE = tmp.adultSettlementPrice,
        </if>
        <if test="item.adultPrice != null and item.adultPrice != ''">
            r.ADULT_PRICE = tmp.adultPrice,
        </if>            
        <if test="item.childSettlementPrice != null and item.childSettlementPrice != ''">
            r.CHILD_SETTLEMENT_PRICE = tmp.childSettlementPrice,
        </if>        
        <if test="item.childPrice != null and item.childPrice != ''">
            r.CHILD_PRICE =tmp.childPrice ,
        </if>            
        <if test="item.gapSettlementPrice != null and item.gapSettlementPrice != ''">
            r.GAP_SETTLEMENT_PRICE = tmp.gapSettlementPrice,
        </if>            
        <if test="item.gapPrice != null and item.gapPrice != ''">
            r.GAP_PRICE = tmp.gapPrice,
        </if>    
        <if test="item.aheadBookTime != null and item.aheadBookTime != ''">
            r.AHEAD_BOOK_TIME = tmp.aheadBookTime,
        </if>    
        <if test="item.bookLimitType != null and item.bookLimitType != ''">
            r.BOOK_LIMIT_TYPE = tmp.bookLimitType,
        </if>    
        r.UPDATE_TIME = sysdate
    </set>
    when not matched THEN
    insert 
     <trim prefix="(" suffix=")" suffixOverrides="," > 
        TIME_PRICE_ID ,
        UNIT_ID ,
        SPEC_DATE ,
        STOCK_TYPE ,
        STOCK ,
        TOTAL_STOCK ,
        OVERSELL_FLAG ,
        ADULT_ON_SALE_FLAG ,
        CHILD_ON_SALE_FLAG,
        GAP_ON_SALE_FLAG,
        ADULT_SETTLEMENT_PRICE,
        ADULT_PRICE,
        CHILD_SETTLEMENT_PRICE,
        CHILD_PRICE,
        GAP_SETTLEMENT_PRICE,
        GAP_PRICE,
        AHEAD_BOOK_TIME,
        BOOK_LIMIT_TYPE,
        UPDATE_TIME,
        CREATE_TIME
     </trim>
     <trim  prefix="values (" suffix=")" suffixOverrides=",">
        route_packagevsttimeprice_seq.nextval,
        tmp.unitId,
        tmp.specDate  ,
        tmp.stockType ,
        tmp.stock ,
        tmp.totalStock,
        tmp.oversellFlag  ,
        tmp.adultOnSaleFlag ,
        tmp.childOnSaleFlag ,
        tmp.gapOnSaleFlag,
        tmp.adultSettlementPrice ,
        tmp.adultPrice,
        tmp.childSettlementPrice,
        tmp.childPrice ,
        tmp.gapSettlementPrice  ,
        tmp.gapPrice,
        tmp.aheadBookTime ,
        tmp.bookLimitType ,
        sysdate,
        sysdate
    </trim> 
</insert>