Mybatis增删改查之Oracle

Mybatis增删改查之Oracle

一. 查询

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

   <resultMap type="com.xxx.domain.RuleCondition" id="RuleConditionResultMapWithoutBondList">
       <id column="RC_ID" jdbcType="NUMERIC" property="ruleConditionId"/>
       <result column="RULE_CAT1" jdbcType="VARCHAR" property="ruleCatOne"/>
       <result column="RULE_CAT2" jdbcType="VARCHAR" property="ruleCatTwo"/>
       <result column="RC_OPER_TYPE" jdbcType="VARCHAR" property="ruleOperateSymbol"/>
       <result column="RULE_REF" jdbcType="VARCHAR" property="ruleRef"/>
       <result column="START_EFFECT_TIME" jdbcType="VARCHAR" property="effectTimeOfStart"/>
       <result column="END_EFFECT_TIME" jdbcType="VARCHAR" property="effectTimeOfEnd"/>
       <result column="BOND_CODE_1" jdbcType="VARCHAR" property="bondCodeOne"/>
       <result column="BOND_CODE_2" jdbcType="VARCHAR" property="bondCodeTwo"/>
       <result column="BP_THRESHOLD" jdbcType="NUMERIC" property="bpThreshold"/>
       <result column="RC_STATUS" jdbcType="VARCHAR" property="ruleStatus"/>
       <result column="OPERATOR_ID" jdbcType="VARCHAR" property="operatorId"/>
       <result column="LAST_UPDATED_DATE" jdbcType="DATE" property="lastUpdateTime"/>
     </resultMap> 
   
   <select id="getRuleConditionWithoutBondListById" resultMap="RuleConditionResultMapWithoutBondList">
       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}
     </select>
   

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

   <!--collection中的就是查询附带的list的函数
   	property是java中list的属性名
   -->
   <resultMap type="com.xxx.domain.RuleCondition" id="RuleConditionResultMap">
       <id column="RC_ID" jdbcType="NUMERIC" property="ruleConditionId"/>
       <result column="RULE_CAT1" jdbcType="VARCHAR" property="ruleCatOne"/>
       <result column="RULE_CAT2" jdbcType="VARCHAR" property="ruleCatTwo"/>
       <result column="RC_OPER_TYPE" jdbcType="VARCHAR" property="ruleOperateSymbol"/>
       <result column="RULE_REF" jdbcType="VARCHAR" property="ruleRef"/>
       <result column="START_EFFECT_TIME" jdbcType="VARCHAR" property="effectTimeOfStart"/>
       <result column="END_EFFECT_TIME" jdbcType="VARCHAR" property="effectTimeOfEnd"/>
       <result column="BOND_CODE_1" jdbcType="VARCHAR" property="bondCodeOne"/>
       <result column="BOND_CODE_2" jdbcType="VARCHAR" property="bondCodeTwo"/>
       <result column="BP_THRESHOLD" jdbcType="NUMERIC" property="bpThreshold"/>
       <result column="RC_STATUS" jdbcType="VARCHAR" property="ruleStatus"/>
       <result column="OPERATOR_ID" jdbcType="VARCHAR" property="operatorId"/>
       <result column="LAST_UPDATED_DATE" jdbcType="DATE" property="lastUpdateTime"/>
       <collection column="RC_ID" property="bondList" ofType="com.xxx.domain.RuleBond"
                   select="getBondListByRuleConditionId">
       </collection>
     </resultMap>
   
     <resultMap type="com.xxx.domain.RuleBond" id="RuleBondResultMap">
       <id column="RB_ID" jdbcType="NUMERIC" property="ruleBondId"/>
       <id column="RC_ID" jdbcType="NUMERIC" property="ruleConditionId"/>
       <result column="BOND_CODE" jdbcType="VARCHAR" property="bondCode"/>
       <result column="SECURITY_TERM" jdbcType="VARCHAR" property="term"/>
       <result column="BID_STRATEGY_ID" jdbcType="VARCHAR" property="bidStrategyId"/>
       <result column="OFR_STRATEGY_ID" jdbcType="VARCHAR" property="ofrStrategyId"/>
       <result column="STATUS" jdbcType="VARCHAR" property="status"/>
       <result column="OPERATOR_ID" jdbcType="VARCHAR" property="operatorId"/>
       <result column="LAST_UPDATED_DATE" jdbcType="DATE" property="lastUpdateTime"/>
     </resultMap>
   <!--查询语句-->
     <select id="getRuleConditionBOsByEnumValue" resultMap="RuleConditionBOResultMap">
       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>
   <!--附带List的查询语句-->
     <select id="getBondListByRuleConditionId" resultMap="RuleBondResultMap">
       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}
     </select>
   

二. 新增

1. 普通新增

     <insert id="addRuleBond" parameterType="com.xxx.domain.RuleBond">
       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)
     </insert>
   

2. 返回主键(多了一个selectkey)

   <!--selectkey中 keyproperty是写java中属性名称
    	后面的values中将得到的ruleBondId赋值即可-->
   <insert id="addRuleBond" parameterType="com.xxx.domain.RuleBond">
    <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="ruleBondId">
         SELECT SEQ_RULES_BOND.Nextval from DUAL
       </selectKey>
       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>
   

3. 批量新增

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

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

   <insert id="addRuleBondList" parameterType="java.util.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 (
       <foreach close=")" collection="ruleBonds" item="item" index="index" open="(" separator="union ALL">
         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
       </foreach>
         ) t
     </insert>
   

4. 批量新增,存在则插入

   <insert id="generateBaselines" parameterType="java.util.List">
       MERGE INTO RULES_CONDITION t
       USING (
       <foreach collection="ruleConditions" item="item" index="index" separator="union">
         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
       </foreach>) 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)
     </insert>
   

三. 修改

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

1. 普通修改

   <update id="modifyRuleBond" parameterType="com.xxx.domain.RuleBond">
       begin
       update RULES_BOND
       set
       <if test="bidStrategyId!=null">
         BID_STRATEGY_ID=#{bidStrategyId,jdbcType=VARCHAR},
       </if>
       <if test="ofrStrategyId!=null">
         OFR_STRATEGY_ID=#{ofrStrategyId,jdbcType=VARCHAR},
       </if>
       <if test="operatorId!=null">
         OPERATOR_ID=#{operatorId,jdbcType=VARCHAR},
       </if>
       <if test="status!=null">
         STATUS=#{status,jdbcType=VARCHAR},
       </if>
       LAST_UPDATED_DATE=SYSTIMESTAMP
       WHERE RB_ID = #{ruleBondId,jdbcType=NUMERIC};
       end;
     </update>
   

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

   <update id="modifyRuleCondition" parameterType="java.util.List">
       <foreach collection="ruleConditions" item="item" index="index" open="begin" close=";end;" separator=";">
         UPDATE RULES_CONDITION
         <set>
           <if test="item.ruleRef!=null">
             RULE_REF=#{item.ruleRef,jdbcType=VARCHAR},
           </if>
           <if test="item.effectTimeOfStart!=null">
             START_EFFECT_TIME=#{item.effectTimeOfStart,jdbcType=VARCHAR},
           </if>
           <if test="item.effectTimeOfEnd!=null">
             END_EFFECT_TIME= #{item.effectTimeOfEnd,jdbcType=VARCHAR},
           </if>
           <if test="item.bpThreshold!=null">
             BP_THRESHOLD= #{item.bpThreshold,jdbcType=NUMERIC},
           </if>
           <if test="item.ruleStatus!=null">
             RC_STATUS= #{item.ruleStatus,jdbcType=VARCHAR},
           </if>
           <if test="item.operatorId!=null">
             OPERATOR_ID= #{item.operatorId,jdbcType=VARCHAR},
           </if>
           LAST_UPDATED_DATE=default,
         </set>
         WHERE RC_ID = #{item.ruleConditionId,jdbcType=INTEGER}
       </foreach>
     </update>
   

四. 删除

1. 普通删除

   <delete id="deleteRuleBond" parameterType="com.xxx.domain.RuleBond">
       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}
     </delete>
   

2. 批量删除

   ​ 1)批量执行语句

   <delete id="batchDeleteRuleBond" parameterType="java.util.List">
       <foreach collection="ruleBonds" open="begin" close=";end;" item="item" separator=";">
         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}
       </foreach>
     </delete>
   

   ​ 2)综合成一条语句执行

   <delete id="batchDeleteRuleBond" parameterType="java.util.List">
       DELETE FROM RULES_BOND
       WHERE RB_ID IN (
       SELECT A.RB_ID FROM (
       <foreach collection="ruleBonds" item="item" separator="UNION All">
         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}
       </foreach>
       )A
       )
     </delete>
   

   ​