Mybatis增删改查之Oracle

摘要：增删改查之一查询普通查询返回普通的持久层对象由于数据库字段风格和不同所以建立一个映射带有自定义对象的查询带了一个中的就是查询附带的的函数是中的属性名查询语句附带的查询语句二新增普通新增返回主键多了一个中是写中属性

一. 查询

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

    
    
    
    
    
    
    
    
    
    
    
    
    
   


    select RC_ID,
           RULE_CAT1,
           RULE_CAT2,
           RC_OPER_TYPE,
           RULE_REF,
           START_EFFECT_TIME,
           END_EFFECT_TIME,
           BOND_CODE_1,
           BOND_CODE_2,
           BP_THRESHOLD,
           RC_STATUS,
           LAST_UPDATED_DATE
    FROM RULES_CONDITION
    WHERE RC_ID = #{ruleConditionId,jdbcType=NUMERIC}
  

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

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  

  
    
    
    
    
    
    
    
    
    
  

  
    select RC_ID,
           RULE_CAT1,
           RULE_CAT2,
           RC_OPER_TYPE,
           RULE_REF,
           START_EFFECT_TIME,
           END_EFFECT_TIME,
           BOND_CODE_1,
           BOND_CODE_2,
           BP_THRESHOLD,
           RC_STATUS,
           LAST_UPDATED_DATE
    FROM RULES_CONDITION
    WHERE RULE_CAT1 = #{enumValue,jdbcType=VARCHAR}
  

  
    select RB_ID,
           RC_ID,
           t1.BOND_CODE,
           t2.SECURITY_TERM,
           BID_STRATEGY_ID,
           OFR_STRATEGY_ID,
           t1.STATUS,
           t1.LAST_UPDATED_DATE
    FROM RULES_BOND t1
           left join BOND_BASIS_INFO t2 on t1.BOND_CODE = t2.BOND_CODE
    WHERE RC_ID = #{ruleConditionId,jdbcType=NUMERIC}
  

二. 新增

普通新增

  
    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
    )
  

​