Mybatis增删改查之Oracle

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

内容简介:(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>


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

查看所有标签

猜你喜欢:

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

运营实战指南

运营实战指南

韩利 / 电子工业出版社 / 2016-9-1 / 49

《运营实战指南》架构清晰,前8章主要通过故事形式深入浅出理解运营,将运营基础知识和概念融入到故事中。第9章讲解运营核心方法论,从目标、关键驱动元素、试错调优、高效运行4部分来完整讲解一个运营项目从0到1的过程。第10章、11章、12章深入讲解了运营人拿业绩最核心的知识点:用户、内容和文案。其中数据分析、活动运营等内容以案例形式穿插在各个章节中。最后两章,主谈运营人在日常生活中如何历练以及一个运营人......一起来看看 《运营实战指南》 这本书的介绍吧!

随机密码生成器
随机密码生成器

多种字符组合密码

SHA 加密
SHA 加密

SHA 加密工具