普通查询(返回普通的持久层对象,由于数据库字段风格和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 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>