oracle 增删改查 日志,Mybatis增删改查之Oracle

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="getBondListByRuleConditionId">

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

)

​