MYSQL的动态SQL

if语法

    <select id="findExistBookset" resultType="int"
        parameterType="AccountsAdjustAccountSet002Dto">
        SELECT COUNT(1) FROM BOK_BOOKSET
        WHERE STATUS='E'
        AND BOOKSET_NAME = #{booksetName}
        <if test="pageModel eq modifyModel">
            AND BOOKSET_ID != #{booksetId}
        </if>

    </select>

  select 
  NVL(sum(PRIN_AMT),0) PRIN_AMT
  from
  SEC_ORG_DEAL
  where
  STATUS IN ('A','E')
  AND ENTRUST_ID = #{entrustId}
  <if test="workflowId != null and workflowId != ''">
AND WORKFLOW_ID != #{workflowId}

</if>

choose, when, otherwise语法

SELECT ASSET_ID assetId,
      SEC_ID secId,
      ISIN_ID isinId,
      SEC_ABBR secAbbr,
      SEC_NAME secName,
      INT_RULE_ID intRuleId,
      RESET_RULE_ID resetRuleId,
      UPDATE_TIME updateTime
 FROM 
<choose>
<when test="tempStorage == '01'">
SEC_INFO_TMP T
</when>
<otherwise>
<if test="hisNo != null and hisNo != ''">
SEC_INFO_HIS T
</if>
<if test="hisNo == null or hisNo == ''">
SEC_INFO T
</if>
</otherwise>
</choose>

where, set语法，解决首尾问题

select
                SUBJECT_NO id,
                SUBJECT_ID object,
                SUBJECT_NAME text,
                '0'||SUBJECT_LEVEL as col
            from
                BOK_SUBJECT
            <where>
                <if test="booksetId != null and booksetId != ''">
                    and BOOKSET_ID = #{booksetId}
                </if>
                and STATUS = 'E'

            </where>

 update WFL_AGENT
        <set>
        <if test="startDate != null and startDate != ''">
               START_DATE = #{startDate,jdbcType=DATE},
        </if>
        <if test="agentType != null and agentType != ''">
               AGENT_TYPE = #{agentType,jdbcType=VARCHAR},
            </if>
            <if test="agentId != null and agentId != ''">
               AGENT_ID = #{agentId,jdbcType=VARCHAR},
            </if>
            <if test="workflowId != null and workflowId != ''">
               WORKFLOW_ID = #{workflowId,jdbcType=VARCHAR}
            </if>
        </set>
        where USER_ID = #{userId,jdbcType=VARCHAR}

        and START_DATE = #{startDateToUpdate,jdbcType=DATE}

最强大的foreach语句   collection是参数，一般是LIST 或者数组   item是迭代的元素

用于批量insert

<insert id="insertSjyzProjAssetInfo" parameterType="java.util.List">
insert into SJYZ_PROJ_ASSET_INFO (
PROJ_ASSET_INFO_ASSET_ID
,PROJ_ASSET_INFO_ASSET_NAME
,PROJ_ASSET_INFO_FINANCING_SIZE
,PROJ_ASSET_INFO_CCY
,INV_RATE_QUO_UPDATE_TIME
   )
   values
<foreach collection="list" item="INFO" index="index" open="" close="" separator=",">
   (
#{INFO.PROJ_ASSET_INFO_ASSET_ID,jdbcType=VARCHAR}
,#{INFO.PROJ_ASSET_INFO_ASSET_NAME,jdbcType=VARCHAR}
,#{INFO.PROJ_ASSET_INFO_FINANCING_SIZE,jdbcType=DECIMAL}
,#{INFO.PROJ_ASSET_INFO_CCY,jdbcType=VARCHAR}
,#{INFO.INV_RATE_QUO_UPDATE_TIME,jdbcType=TIMESTAMP}
      )
</foreach>

  </insert>

用于查询，多个参数实现IN

SELECT NULL AS hisNo,
T.WORKFLOW_ID workflowId,
T.ASSET_ID AS assetId,
T.SEC_ID AS secId,
T.SEC_ABBR secAbbr,
T.SEC_TYPE secType
FROM SEC_INFO_TMP T
WHERE 
T.CREATE_USER = #{loginUserId}
<if test="assetId != null and assetId != ''">
AND T.SEC_ID LIKE '%'||#{assetId}||'%'
</if>
<if test="couponSpecies != null and couponSpecies.size() > 0">
AND T.COUPON_SPECIES IN
<foreach item="item" collection="couponSpecies" index="index"
open="(" separator="," close=")">
'${item}'
</foreach>

</if>

SELECT ASSET_ID 
 FROM PROJ_ASSET_INFO
WHERE STATUS IN ('A','P')
<if test="assetIds != null and assetIds.length > 0">
  AND ASSET_ID IN 
  <foreach item="item" collection="assetIds" index="index"
open="(" separator="," close=")">
#{item}
  </foreach>
</if>

 include语法  静态包含

<select id="selectSecInfoList" resultType="java.lang.Integer">
<include refid="secInfoList"/>

</select>

<sql id = "secInfoList">
SELECT distinct
secInfo.hisNo,
secInfo.workflowId,
secInfo.assetId,
secInfo.secId,
secInfo.secAbbr,
secInfo.secType,
secInfo.vdate
secInfo ORDER BY secInfo.assetId, secInfo.workflowId DESC
</sql>