关于mybatis:啦la

`<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="RegulationDO">
<sql id="queryParam">
<where> <if test="id != null and id != ''">
r.ID = #{id}

     </if>

<if test="isLock != null and isLock != ''">
AND r.IS_LOCK = #{isLock}

     </if>

<if test="checkPoint != null and checkPoint != ''">
AND r.CHECK_POINT = #{checkPoint}

     </if>

<if test="riskEventType != null and riskEventType != ''">
AND r.RISK_EVENT_TYPE = #{riskEventType}

     </if>

<choose> <when test="ruleTypeArray != null and ruleTypeArray.length > 0">
AND r.RULE_TYPE in

           <foreach collection="ruleTypeArray" index="index" item="item" open="(" separator="," close=")">

#{item}

           </foreach>

</when> <when test="(ruleTypeArray == null or ruleTypeArray.length == 0) and ruleType != null and ruleType != ''">
AND r.RULE_TYPE = #{ruleType}

        </when>

<when test="ruleType == null or ruleType == ''">
AND r.RULE_TYPE != '30' AND r.RULE_TYPE != '20'

           </when>

</choose> <if test="ruleRunType != null and ruleRunType != ''">
AND r.RULE_RUN_TYPE = #{ruleRunType}

     </if>

<if test="ruleName != null and ruleName != ''" >
AND r.RULE_NAME like '%'||#{ruleName}||'%'

     </if>

<if test="ruleDesc != null and ruleDesc != ''" >
AND r.RULE_DESC = #{ruleDesc}

     </if>

<if test="transType != null and transType != ''">
AND r.TRANS_TYPE = #{transType}

     </if>

<if test="ruleClassify != null and ruleClassify != ''">
AND r.RULE_CLASSIFY = #{ruleClassify}

     </if>

<if test="createOper != null and createOper != ''" >
AND r.CREATE_OPER = #{createOper}

     </if>

<if test="updateOper != null and updateOper != ''" >
AND r.UPDATE_OPER = #{updateOper}

     </if>

<if test="inuseBegTime != null and inuseBegTime != ''" >
AND r.INUSE_BEG_TIME = to_date(#{inuseBegTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss')

     </if>

<if test="inuseEndTime != null and inuseEndTime != ''" >
AND r.INUSE_END_TIME = to_date(#{inuseEndTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss')

     </if>

<if test="minAmount != null and minAmount != ''" >
AND r.MIN_AMOUNT = #{minAmount}

     </if>

<if test="checkCustomerId != null and checkCustomerId != ''" >
AND r.CHECK_CUSTOMER_ID = #{checkCustomerId}

     </if>

<if test="checkCardId != null and checkCardId != ''" >
AND r.CHECK_CARD_ID = #{checkCardId}

     </if>

<if test="ruleContent != null and ruleContent != ''" >
AND r.RULE_CONTENT = #{ruleContent}

     </if>

<if test="runMode != null and runMode != ''" >
AND r.RUN_MODE = #{runMode}

     </if>

<if test="createTime != null and createTime != ''" >
AND r.CREATE_TIME = to_date(#{createTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss')

     </if>

<if test="updateTime != null and updateTime != ''" >
AND r.UPDATE_TIME = to_date(#{updateTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss')

     </if>

<if test="payType != null and payType != ''" >
AND r.PAY_TYPE = #{payType}

     </if>

<if test="grade != null and grade != ''" >
AND r.GRADE = #{grade}

     </if>

<if test="riskRuleId != null and riskRuleId != ''" >
AND r.RISKRULEID = #{riskRuleId}

     </if>

<if test="isInUse != null and isInUse != ''">
AND r.IS_IN_USE = #{isInUse}

        </if>

<if test='isInUse != null and isInUse == "1"'>
AND r.INUSE_END_TIME > sysdate

        </if>

<if test="ruleResult != null and ruleResult != ''">
AND r.RULE_RESULT = #{ruleResult}

        </if>

<if test="safetyFlag != null and safetyFlag != ''">
AND r.SAFETY_FLAG = #{safetyFlag}

        </if>

<if test="authority != null and authority != ''">
<choose> <when test="authority == '安全付科技'">
AND (r.AUTHORITY = #{authority} OR r.AUTHORITY = '' OR r.AUTHORITY IS NULL)

              </when>

<otherwise> AND r.AUTHORITY = #{authority}

              </otherwise>

</choose> </if> </where> </sql>
<sql id="queryInfo">
r.ID id,r.IS_LOCK isLock,

  r.CHECK_POINT checkPoint,  r.RISK_EVENT_TYPE riskEventType,  r.RULE_TYPE ruleType,  r.RULE_RUN_TYPE ruleRunType,  r.RULE_NAME ruleName,  r.RULE_DESC ruleDesc,  r.TRANS_TYPE transType,  r.RULE_CLASSIFY ruleClassify,  r.CREATE_OPER createOper,  r.CREATE_TIME createTime,  r.UPDATE_OPER updateOper,    to_char(r.UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss') updateTime,  r.INUSE_BEG_TIME inuseBegTime,  r.INUSE_END_TIME inuseEndTime,  r.MIN_AMOUNT minAmount,  r.CHECK_CUSTOMER_ID checkCustomerId,  r.CHECK_CARD_ID checkCardId,  r.RULE_CONTENT ruleContent,  r.RUN_MODE runMode,  r.PAY_TYPE payType,  r.GRADE grade,  r.RISKRULEID riskRuleId,  r.RULE_RESULT ruleResult,  r.RULE_ACTION ruleAction,  r.ICE_TYPE iceType,  r.RULE_CONTENT_DESC ruleContentDesc,    r.RISK_EXTRA_MSG riskExtraMsg,    r.IS_IN_USE isInUse,    r.SELECTOR_ITEMS selectorItems,    r.final_rule_content finalRuleContent,    r.RULE_REAL_NAME ruleRealName,    r.SAFETY_FLAG safetyFlag,    r.AUTHORITY authority

</sql>
<select id="queryTheRuleName" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO"
resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
<![CDATA[

     SELECT * FROM tc_rc_regulations WHERE rule_name=#{ruleName} AND check_point=#{checkPoint}    ]]>

</select>
<select id="queryRuleName" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO"
resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
<![CDATA[

     SELECT * FROM tc_rc_regulations WHERE rule_name=#{ruleName} AND id <>#{id} AND check_point=#{checkPoint}    ]]>

</select>
<select id="queryByRuleName" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>
FROM TC_RC_REGULATIONS r

    WHERE RULE_NAME like '%'||#{ruleName}||'%'    order by inuseBegTime desc

</select>
<select id="queryById" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>
FROM TC_RC_REGULATIONS r

    WHERE Id=#{id}    order by updateTime desc

</select>
<select id="queryByRiskRuleId" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>
FROM TC_RC_REGULATIONS r

    WHERE RISKRULEID=#{riskRuleId}

</select>
<select id="queryAll" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>
FROM TC_RC_REGULATIONS r

    <include refid="queryParam" />

</select> <!-- 新增 -->
<insert id="add" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO" useGeneratedKeys="true" keyProperty="ID">
<![CDATA[

  INSERT INTO TC_RC_REGULATIONS   (ID, IS_LOCK, CHECK_POINT, RISK_EVENT_TYPE, RULE_TYPE, RULE_RUN_TYPE, RULE_NAME, RULE_DESC, TRANS_TYPE, RULE_CLASSIFY, CREATE_OPER, CREATE_TIME,  UPDATE_OPER, UPDATE_TIME, INUSE_BEG_TIME , INUSE_END_TIME,   RULE_CONTENT,  RUN_MODE,  PAY_TYPE, GRADE,RISKRULEID,  RULE_RESULT,RULE_ACTION,ICE_TYPE, RULE_CONTENT_DESC,CHECK_CUSTOMER_ID,CHECK_CARD_ID,RISK_EXTRA_MSG,IS_IN_USE,SELECTOR_ITEMS,RULE_REAL_NAME,SAFETY_FLAG,AUTHORITY)  VALUES  (#{id,jdbcType=VARCHAR},'0',#{checkPoint,jdbcType=VARCHAR},#{riskEventType,jdbcType=VARCHAR},#{ruleType,jdbcType=VARCHAR},  #{ruleRunType,jdbcType=VARCHAR},#{ruleName,jdbcType=VARCHAR},#{ruleDesc,jdbcType=VARCHAR},  #{transType,jdbcType=VARCHAR},#{ruleClassify,jdbcType=VARCHAR},#{createOper,jdbcType=VARCHAR},sysdate,#{updateOper,jdbcType=VARCHAR},  sysdate,to_date(#{inuseBegTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),  to_date(#{inuseEndTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),#{ruleContent,jdbcType=VARCHAR},  #{runMode,jdbcType=VARCHAR},#{payType,jdbcType=VARCHAR},#{grade,jdbcType=VARCHAR},  SEQ_RISK_RULE_ID.NEXTVAL,#{ruleResult,jdbcType=VARCHAR},#{ruleAction,jdbcType=VARCHAR},  #{iceType,jdbcType=VARCHAR},#{ruleContentDesc,jdbcType=VARCHAR},#{checkCustomerId,jdbcType=VARCHAR},#{checkCardId,jdbcType=VARCHAR},#{riskExtraMsg,jdbcType=VARCHAR},#{isInUse,jdbcType=CHAR},#{selectorItems,jdbcType=CHAR},  #{ruleRealName,jdbcType=VARCHAR},#{safetyFlag,jdbcType=VARCHAR},#{authority,jdbcType=VARCHAR})   ]]>

<!-- <selectKey resultType="java.lang.Integer" keyProperty="ID">
SELECT LAST_INSERT_ID(); </selectKey> --> </insert>
<select id="query" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>, ra.APPROVE_STATUS approveStatus

    FROM TC_RC_REGULATIONS r left join TC_RC_REGULATION_APPROVE ra    on r.id = ra.regulation_id AND ra.APPROVE_STATUS='0'    <include refid="queryParam" /> 

order by r.UPDATE_TIME desc
</select>
<select id="queryOne" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
SELECT <include refid="queryInfo"/>
FROM TC_RC_REGULATIONS r

    <include refid="queryParam" />

</select>
<update id="update" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
update TC_RC_REGULATIONS

  <set>

<if test="isLock != null and isLock != ''">
IS_LOCK = #{isLock},

     </if>

<if test="checkPoint != null and checkPoint != ''">
CHECK_POINT = #{checkPoint},

     </if>

<if test="riskEventType != null and riskEventType != ''">
RISK_EVENT_TYPE = #{riskEventType},

     </if>

<if test="ruleType != null and ruleType != ''">
RULE_TYPE = #{ruleType},

     </if>

<if test="ruleRunType != null and ruleRunType != ''">
RULE_RUN_TYPE = #{ruleRunType},

     </if>

<if test="ruleName != null and ruleName != ''" >
RULE_NAME = #{ruleName},

     </if>

<if test="transType != null and transType != ''">
TRANS_TYPE = #{transType},

     </if>

<if test="ruleClassify != null and ruleClassify != ''">
RULE_CLASSIFY = #{ruleClassify},

     </if>

<if test="updateOper != null and updateOper != ''" >
UPDATE_OPER = #{updateOper},

     </if>

UPDATE_TIME = sysdate,

     <if test="inuseBegTime != null and inuseBegTime != ''" >

INUSE_BEG_TIME = to_date(#{inuseBegTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),

     </if>

<if test="inuseEndTime == null or inuseEndTime == '' " >
INUSE_END_TIME = null,

     </if>

<if test="inuseEndTime != null and inuseEndTime != ''" >
INUSE_END_TIME = to_date(#{inuseEndTime,jdbcType=VARCHAR},'yyyy-mm-dd hh24:mi:ss'),

     </if>        

<if test="ruleContent != null and ruleContent != ''" >
RULE_CONTENT = #{ruleContent},

     </if>

<if test="ruleContentDesc != null and ruleContentDesc != ''" >
RULE_CONTENT_DESC = #{ruleContentDesc},

        </if>

<if test="ruleResult != null and ruleResult != ''" >
RULE_RESULT = #{ruleResult},

        </if>

RULE_ACTION = #{ruleAction},

        <if test="iceType != null and iceType != ''" >

ICE_TYPE = #{iceType},

        </if>

<if test="riskExtraMsg != null and riskExtraMsg !=''">
RISK_EXTRA_MSG = #{riskExtraMsg},

        </if>

<if test="isInUse != null and isInUse != ''">
IS_IN_USE = #{isInUse},

        </if>

<if test="selectorItems != null and selectorItems != ''">
SELECTOR_ITEMS = #{selectorItems},

        </if>

<if test="ruleDesc != null and ruleDesc != ''">
RULE_DESC = #{ruleDesc,jdbcType=VARCHAR},

     </if>

<if test="minAmount != null and minAmount != ''">
MIN_AMOUNT = #{minAmount,jdbcType=VARCHAR},

      </if>

<if test="checkCustomerId != null and checkCustomerId != ''" >
CHECK_CUSTOMER_ID = #{checkCustomerId,jdbcType=VARCHAR},

        </if>

<if test="checkCardId != null and checkCardId != ''" >
CHECK_CARD_ID = #{checkCardId,jdbcType=VARCHAR},

        </if>

<if test="runMode != null and runMode != ''">
RUN_MODE = #{runMode,jdbcType=VARCHAR},

        </if>

<if test="payType != null and payType != ''">
PAY_TYPE = #{payType,jdbcType=VARCHAR},

      </if>

<if test="grade != null and grade != ''">
GRADE = #{grade,jdbcType=VARCHAR},

      </if>

<if test="finalRuleContent != null and finalRuleContent != ''">
final_rule_content = #{finalRuleContent,jdbcType=VARCHAR},

      </if>

<if test="ruleRealName != null and ruleRealName != ''" >
RULE_REAL_NAME = #{ruleRealName},

      </if>

<if test="safetyFlag != null and safetyFlag != ''" >
SAFETY_FLAG = #{safetyFlag},

      </if>

<if test="authority != null and authority != ''" >
AUTHORITY = #{authority},

      </if>   </set> 

where ID =#{id,jdbcType=VARCHAR}
</update>
<update id="updatePushResult" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
update TC_RC_REGULATIONS T set T.push_code = #{pushCode,jdbcType=VARCHAR},T.riskRuleId = #{grade,jdbcType=VARCHAR}

  where ID =#{id,jdbcType=VARCHAR}

</update>
<update id="updateIsLock" parameterType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
update TC_RC_REGULATIONS T set T.IS_LOCK = '1'

  <if test="authority != null and authority != ''">

, AUTHORITY = #{authority,jdbcType=VARCHAR}

  </if>  

where ID =#{id,jdbcType=VARCHAR}
</update>

<select id="count" resultType="java.lang.Integer">
SELECT COUNT(1) FROM TC_RC_REGULATIONS r

  <include refid="queryParam" />

</select> <select id="queryWithModule" parameterType="java.util.Map" resultType="com.pinganfu.risk.rcomss.common.dal.model.regulation.RegulationDO">
select r.id, r.rule_content ruleContent, r.riskruleid, r.final_rule_content finalRuleContent, r.inuse_end_time inuseEndTime,

         r.RULE_ACTION ruleAction     from tc_rc_regulations r    left join t_rule_module_relation a      on a.rule_id = r.riskruleid      and a.type = #{type,jdbcType=VARCHAR}  <where>

<if test="moduleId != null and moduleId != ''">
a.module_id = #{moduleId,jdbcType=VARCHAR}

     </if>

</where> </select> <update id="updateQuickDisable" parameterType="java.util.Map">
update TC_RC_REGULATIONS T

     set T.IS_IN_USE = '0',          T.IS_LOCK = '0',         T.UPDATE_OPER = #{updateOper, jdbcType=VARCHAR},         T.UPDATE_TIME = sysdate   where riskRuleId = #{riskRuleId,jdbcType=VARCHAR}

</update>
</mapper>`