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>