关于mybatis:啦la

80次阅读

共计 11753 个字符，预计需要花费 30 分钟才能阅读完成。

`<?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>`

正文完
mybatis
发表至： mybatis
2020-12-11
 0
关于mybatis:Mybatis-常用注解中的-SQL-注入
关于mybatis:Mybatis的where标签竟然还有这么多不知道的
关于mybatis:超经典的-25-道-MyBatis-面试题
关于mybatis:mybatis框架的理解