Mybatis增删改查之Oracle-白红宇

Mybatis增删改查之Oracle

阅读量：6150 次

发布时间：2019-06-21

本文共 6554 字，大约阅读时间需要 21 分钟。

Mybatis增删改查之Oracle

一. 查询

普通查询(返回普通的持久层对象,由于数据库字段风格和java不同,所以建立一个map映射)

带有自定义对象的查询(带了一个List)

二. 新增

普通新增


      
           insert into RULES_BOND    (RB_ID,    RC_ID,    BOND_CODE,    BID_STRATEGY_ID,    OFR_STRATEGY_ID,    STATUS,    OPERATOR_ID,    LAST_UPDATED_DATE)    values (SEQ_RULES_BOND.nextVal,    #{ruleConditionId,jdbcType=NUMERIC},    #{bondCode,jdbcType=VARCHAR},    #{bidStrategyId,jdbcType=VARCHAR},    #{ofrStrategyId,jdbcType=VARCHAR},    #{status,jdbcType=VARCHAR},    #{operatorId,jdbcType=VARCHAR},    systimestamp)

返回主键(多了一个selectkey)


      
       
       
              SELECT SEQ_RULES_BOND.Nextval from DUAL    
           insert into RULES_BOND    (RB_ID,    RC_ID,    BOND_CODE,    BID_STRATEGY_ID,    OFR_STRATEGY_ID,    STATUS,    OPERATOR_ID,    LAST_UPDATED_DATE)    values (#{ruleBondId,jdbcType=NUMERIC},    #{ruleConditionId,jdbcType=NUMERIC},    #{bondCode,jdbcType=VARCHAR},    #{bidStrategyId,jdbcType=VARCHAR},    #{ofrStrategyId,jdbcType=VARCHAR},    #{status,jdbcType=VARCHAR},    #{operatorId,jdbcType=VARCHAR},    systimestamp)

批量新增

参照网上写了一下,一直报缺失表达式,原来是insert into后面是不需要 values的;

还有就是关于Oracle返回主键List ,我在网上暂时还没找到能正确执行的例子 ,求大佬告知


      
           insert into RULES_BOND    (RB_ID,    RC_ID,    BOND_CODE,    BID_STRATEGY_ID,    OFR_STRATEGY_ID,    STATUS,    OPERATOR_ID,    LAST_UPDATED_DATE    )    SELECT  SEQ_RULES_BOND.NEXTVAL,t.*    FROM (    
       
              select      #{item.ruleConditionId,jdbcType=NUMERIC},      #{item.bondCode,jdbcType=VARCHAR},      #{item.bidStrategyId,jdbcType=VARCHAR},      #{item.ofrStrategyId,jdbcType=VARCHAR},      #{item.status,jdbcType=VARCHAR},      #{item.operatorId,jdbcType=VARCHAR},       systimestamp      from dual    
             ) t

批量新增,存在则插入


      
           MERGE INTO RULES_CONDITION t    USING (    
       
              select #{item.ruleConditionId,jdbcType=NUMERIC} id,      #{item.ruleCatOne,jdbcType=VARCHAR} cat1,      #{item.ruleCatTwo,jdbcType=VARCHAR} cat2,      #{item.bondCodeOne,jdbcType=VARCHAR} code1,      #{item.bondCodeTwo,jdbcType=VARCHAR} code2,      #{item.ruleOperateSymbol,jdbcType=VARCHAR} symbol,      #{item.operatorId,jdbcType=VARCHAR} u      from DUAL    
       ) t1    
           ON (t.RULE_CAT1 = t1.cat1 AND t.RULE_CAT2 = t1.cat2 AND t.RC_OPER_TYPE = t1.symbol)    
           WHEN MATCHED THEN    UPDATE SET t.BOND_CODE_1 = t1.code1,t.BOND_CODE_2 = t1.code2,t.LAST_UPDATED_DATE = default    
           WHEN NOT MATCHED THEN    INSERT(RC_ID, RULE_CAT1, RULE_CAT2, RC_OPER_TYPE, RULE_REF, BOND_CODE_1, BOND_CODE_2,RC_STATUS,OPERATOR_ID,LAST_UPDATED_DATE)    VALUES (SEQ_RULES_CONDITION.nextval, t1.cat1, t1.cat2, t1.symbol, '1', t1.code1, t1.code2, '0', t1.u,default)

三. 修改

(begin,end最好还是加上,之前报错一直找不到错,加上begin,end就好了;end前后都加分号";",begin不用加)

普通修改


      
           begin    update RULES_BOND    set    
       
              BID_STRATEGY_ID=#{bidStrategyId,jdbcType=VARCHAR},    
           
       
              OFR_STRATEGY_ID=#{ofrStrategyId,jdbcType=VARCHAR},    
           
       
              OPERATOR_ID=#{operatorId,jdbcType=VARCHAR},    
           
       
              STATUS=#{status,jdbcType=VARCHAR},    
           LAST_UPDATED_DATE=SYSTIMESTAMP    WHERE RB_ID = #{ruleBondId,jdbcType=NUMERIC};    end;

批量修改(begin,end加在 foreach的open和close处,记得加上分号)


          
       
              UPDATE RULES_CONDITION      
                
         
                    RULE_REF=#{item.ruleRef,jdbcType=VARCHAR},        
                 
         
                    START_EFFECT_TIME=#{item.effectTimeOfStart,jdbcType=VARCHAR},        
                 
         
                    END_EFFECT_TIME= #{item.effectTimeOfEnd,jdbcType=VARCHAR},        
                 
         
                    BP_THRESHOLD= #{item.bpThreshold,jdbcType=NUMERIC},        
                 
         
                    RC_STATUS= #{item.ruleStatus,jdbcType=VARCHAR},        
                 
         
                    OPERATOR_ID= #{item.operatorId,jdbcType=VARCHAR},        
                 LAST_UPDATED_DATE=default,      
              WHERE RC_ID = #{item.ruleConditionId,jdbcType=INTEGER}

四. 删除

普通删除


      
           delete    from RULES_BOND    where RB_ID = #{ruleBondId}      AND TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = #{lastUpdateTime,jdbcType=TIMESTAMP}

批量删除

1)批量执行语句


          
       
              DELETE FROM RULES_BOND      WHERE RB_ID = #{item.ruleBondId} and TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = #{item.lastUpdateTime,jdbcType=TIMESTAMP}

2)综合成一条语句执行


      
           DELETE FROM RULES_BOND    WHERE RB_ID IN (    SELECT A.RB_ID FROM (    
       
              SELECT * FROM RULES_BOND      WHERE RB_ID = #{item.ruleBondId} AND TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss') = #{item.lastUpdateTime,jdbcType=TIMESTAMP}    
           )A    )