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 &＃61; #{ruleConditionId,jdbcType&＃61;NUMERIC}

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

select&＃61;"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 &＃61; #{enumValue,jdbcType&＃61;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 &＃61; t2.BOND_CODE

WHERE RC_ID &＃61; #{ruleConditionId,jdbcType&＃61;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&＃61;NUMERIC},

#{bondCode,jdbcType&＃61;VARCHAR},

#{bidStrategyId,jdbcType&＃61;VARCHAR},

#{ofrStrategyId,jdbcType&＃61;VARCHAR},

#{status,jdbcType&＃61;VARCHAR},

#{operatorId,jdbcType&＃61;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&＃61;NUMERIC},

#{ruleConditionId,jdbcType&＃61;NUMERIC},

#{bondCode,jdbcType&＃61;VARCHAR},

#{bidStrategyId,jdbcType&＃61;VARCHAR},

#{ofrStrategyId,jdbcType&＃61;VARCHAR},

#{status,jdbcType&＃61;VARCHAR},

#{operatorId,jdbcType&＃61;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&＃61;NUMERIC},

#{item.bondCode,jdbcType&＃61;VARCHAR},

#{item.bidStrategyId,jdbcType&＃61;VARCHAR},

#{item.ofrStrategyId,jdbcType&＃61;VARCHAR},

#{item.status,jdbcType&＃61;VARCHAR},

#{item.operatorId,jdbcType&＃61;VARCHAR},

systimestamp

from dual

) t

批量新增,存在则插入

MERGE INTO RULES_CONDITION t

USING (

select #{item.ruleConditionId,jdbcType&＃61;NUMERIC} id,

#{item.ruleCatOne,jdbcType&＃61;VARCHAR} cat1,

#{item.ruleCatTwo,jdbcType&＃61;VARCHAR} cat2,

#{item.bondCodeOne,jdbcType&＃61;VARCHAR} code1,

#{item.bondCodeTwo,jdbcType&＃61;VARCHAR} code2,

#{item.ruleOperateSymbol,jdbcType&＃61;VARCHAR} symbol,

#{item.operatorId,jdbcType&＃61;VARCHAR} u

from DUAL

) t1

ON (t.RULE_CAT1 &＃61; t1.cat1 AND t.RULE_CAT2 &＃61; t1.cat2 AND t.RC_OPER_TYPE &＃61; t1.symbol)

WHEN MATCHED THEN

UPDATE SET t.BOND_CODE_1 &＃61; t1.code1,t.BOND_CODE_2 &＃61; t1.code2,t.LAST_UPDATED_DATE &＃61; 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, &＃39;1&＃39;, t1.code1, t1.code2, &＃39;0&＃39;, t1.u,default)

三. 修改

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

普通修改

begin

update RULES_BOND

set

BID_STRATEGY_ID&＃61;#{bidStrategyId,jdbcType&＃61;VARCHAR},

OFR_STRATEGY_ID&＃61;#{ofrStrategyId,jdbcType&＃61;VARCHAR},

OPERATOR_ID&＃61;#{operatorId,jdbcType&＃61;VARCHAR},

STATUS&＃61;#{status,jdbcType&＃61;VARCHAR},

LAST_UPDATED_DATE&＃61;SYSTIMESTAMP

WHERE RB_ID &＃61; #{ruleBondId,jdbcType&＃61;NUMERIC};

end;

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

UPDATE RULES_CONDITION

RULE_REF&＃61;#{item.ruleRef,jdbcType&＃61;VARCHAR},

START_EFFECT_TIME&＃61;#{item.effectTimeOfStart,jdbcType&＃61;VARCHAR},

END_EFFECT_TIME&＃61; #{item.effectTimeOfEnd,jdbcType&＃61;VARCHAR},

BP_THRESHOLD&＃61; #{item.bpThreshold,jdbcType&＃61;NUMERIC},

RC_STATUS&＃61; #{item.ruleStatus,jdbcType&＃61;VARCHAR},

OPERATOR_ID&＃61; #{item.operatorId,jdbcType&＃61;VARCHAR},

LAST_UPDATED_DATE&＃61;default,

WHERE RC_ID &＃61; #{item.ruleConditionId,jdbcType&＃61;INTEGER}

四. 删除

普通删除

delete

from RULES_BOND

where RB_ID &＃61; #{ruleBondId}

AND TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, &＃39;yyyy-MM-dd hh24:mi:ss&＃39;), &＃39;yyyy-MM-dd hh24:mi:ss&＃39;) &＃61; #{lastUpdateTime,jdbcType&＃61;TIMESTAMP}

批量删除

​ 1)批量执行语句

DELETE FROM RULES_BOND

WHERE RB_ID &＃61; #{item.ruleBondId} and TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, &＃39;yyyy-MM-dd hh24:mi:ss&＃39;), &＃39;yyyy-MM-dd hh24:mi:ss&＃39;) &＃61; #{item.lastUpdateTime,jdbcType&＃61;TIMESTAMP}

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

DELETE FROM RULES_BOND

WHERE RB_ID IN (

SELECT A.RB_ID FROM (

SELECT * FROM RULES_BOND

WHERE RB_ID &＃61; #{item.ruleBondId} AND TO_TIMESTAMP(TO_CHAR(LAST_UPDATED_DATE, &＃39;yyyy-MM-dd hh24:mi:ss&＃39;), &＃39;yyyy-MM-dd hh24:mi:ss&＃39;) &＃61; #{item.lastUpdateTime,jdbcType&＃61;TIMESTAMP}

)A

)

​