Mybatis增删改查之Oracle

栏目: Java · 发布时间: 6年前

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

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>

  1. 批量修改(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>

  1. 批量删除

    ​ 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>

    ​


以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍
The Haskell School of Music

The Haskell School of Music

Paul Hudak、Donya Quick / Cambridge University Press / 2018-10-4 / GBP 42.99

This book teaches functional programming through creative applications in music and sound synthesis. Readers will learn the Haskell programming language and explore numerous ways to create music and d......一起来看看 《The Haskell School of Music》 这本书的介绍吧!

码农工具
RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具

  • New
  • 文章
  • 话题
  • 教程
    • 关注 码农网 公众号
    版权所有，保留一切权利！© 2018-2026 码农网 粤ICP备17054400号-3