Mybatis 增删改查之 Oracle
一. 查询
普通查询 (返回普通的持久层对象, 由于数据库字段风格和 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>
带有自定义对象的查询 (带了一个 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>
二. 新增
普通新增
<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>
返回主键 (多了一个 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>
批量新增
参照网上写了一下, 一直报缺失表达式, 原来是 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>
批量新增, 存在则插入
<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 不用加)
普通修改
<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>
批量修改 (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>
四. 删除
普通删除
<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) 批量执行语句
<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>