mybatis常用脚本示例

mysql:

1.批量插入

    <insert id="insertProcessSyncList">
        INSERT INTO t_process_sync
            (id,loan_kind,loan_side,process_id,external_id,step,is_hangup,is_init,last_success_time,last_failure_time,remark,handle_count)
        VALUES
        <foreach collection="list" item="element" index="index" separator=",">
            (
            #{element.id},
            #{element.loanKind},
            #{element.loanSide},
            #{element.processId},
            #{element.externalId},
            #{element.step},
            #{element.isHangup},
            #{element.isInit},
            #{element.lastSuccessTime},
            #{element.lastFailureTime},
            #{element.remark},
            #{element.handleCount}
            )
        </foreach>
    </insert>

2.部分字段更新

    <update id="updateProcessBatch"  parameterType="com.vcredit.fts.process.model.dto.UpdateProcessRequest">
        UPDATE t_process_sync
        <trim prefix="set" suffixOverrides=",">
            <if test="step!=null">step=#{step},</if>
            <if test="isHangup!=null">is_hangup=#{isHangup},</if>
            <if test="isInit!=null">is_init=#{isInit},</if>
            <if test="remark!=null">remark=#{remark},</if>
            <if test="handleCount!=null">handle_count=#{handleCount},</if>
            <if test="deleted!=null">deleted=#{deleted},</if>
            <if test="lastSuccessTime!=null">last_success_time=#{lastSuccessTime},</if>
            <if test="lastFailureTime!=null">last_failure_time=#{lastFailureTime},</if>
        </trim>
        WHERE process_id=#{processId} AND external_id IN
              <foreach collection="externalIds" separator="," item="item" open="(" close=")">#{item}</foreach>
    </update>

3.条件查询

    <select id="getProcessSyncList" resultType="com.vcredit.fts.process.model.po.ProcessSyncPO">
        SELECT * FROM t_process_sync
        WHERE process_id=#{processId}
        AND step=#{step}
        AND is_init IN(-1,1)
        <if test="limitTime!=null and limitTime!=''">
            AND (last_success_time IS NULL OR last_success_time &lt; #{limitTime})
        </if>
        AND handle_count &lt;= #{handleCount}
        AND is_hangup=0
        AND (last_failure_time IS NULL OR last_failure_time &lt; (CURRENT_TIMESTAMP - INTERVAL 10 MINUTE))
        AND deleted=0 LIMIT #{queryCount}
    </select>

执行多头语句，网上说法是需要在数据库连接上加上【allowMultiQueries=true】 ，多条语句使用分号分隔，我还没试验过。

如：jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

<delete id="deleteRecipeVideoInfo" parameterType="int"> 
delete from tb_parent where id = #{id}; 
delete from tb_child where pid = #{id} 
</delete>

 

sqlserver:

1.条件查询

    <select id="getUserCenterSyncInfo" resultType="com.vcredit.vts.entity.bo.UserCenterSyncData">
        SELECT TOP ${num} a.Bid,a.LoanKind,b.IdentityNo,b.Mobile,c.RegisterId,c.VcreditCustId
        FROM Loan.apply.ApplyInfo a WITH(NOLOCK)
        JOIN Loan.apply.ApplyInfoCustomer b WITH(NOLOCK) ON b.Bid = a.Bid
        LEFT JOIN Loan.business.BusinessRelatedInfo c WITH(NOLOCK) ON c.Bid = a.Bid
        WHERE a.LoanKind = #{loanKind} AND a.Bid > ${lastBid}
             AND ( ISNULL(c.RegisterId, '') = '' OR ISNULL(c.VcreditCustId, '') = '' )
        ORDER BY a.Bid;
    </select>

    <select id="getUserCenterSyncStatistics" resultType="com.vcredit.vts.entity.bo.UserCenterSyncStatistics">
        SELECT a.LoanKind,COUNT(1) AllCount,
        COUNT(CASE WHEN ISNULL(c.RegisterId, '') = '' THEN 1 END) NotSyncCount,
        COUNT(CASE WHEN c.RegisterId='-999' THEN 1 END) SyncFailureCount
        FROM Loan.apply.ApplyInfo a WITH(NOLOCK)
        LEFT JOIN Loan.business.BusinessRelatedInfo c WITH(NOLOCK) ON c.Bid = a.Bid
        WHERE a.LoanKind IN <foreach collection="loanKinds" separator="," item="item" open="(" close=")">#{item}</foreach>
        GROUP BY a.LoanKind;
    </select>

2.执行复杂语句

    <!--保存并更新数据-->
    <insert id="saveUserCenterInfo" parameterType="com.vcredit.vts.entity.bo.UserCenterSyncData">
        USE [Test];
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;

        CREATE TABLE #sync_tmp([bid]  INT,[vcredit_cust_id] [varchar] (50),[register_id] [varchar] (50));
        INSERT #sync_tmp ( bid, vcredit_cust_id, register_id )
        VALUES
        <foreach collection="syncDataList" separator="," item="item" open="" close=";">( #{item.Bid}, #{item.VcreditCustId}, #{item.RegisterId} )</foreach>

        -- 补BusinessRelatedInfo表缺失的记录
        UPDATE b SET b.RegisterId = a.register_id,b.VcreditCustId = a.vcredit_cust_id
        FROM #sync_tmp a WITH(NOLOCK)
        JOIN business.BusinessRelatedInfo b WITH(ROWLOCK) ON a.bid = b.Bid
        WHERE ISNULL(b.RegisterId, '') = '' OR ISNULL(b.VcreditCustId, '') = '';

        -- 补BusinessRelatedInfo表没有的记录
        INSERT INTO business.BusinessRelatedInfo(Bid,RegisterId,VcreditCustId,CreateTime,UpdateTime)
        SELECT a.bid,a.register_id,a.vcredit_cust_id,GETDATE(),GETDATE() FROM #sync_tmp a WITH(NOLOCK)
        LEFT JOIN business.BusinessRelatedInfo b WITH(NOLOCK) ON a.bid = b.Bid
        WHERE B.Id IS NULL AND a.register_id=(SELECT MAX(register_id) FROM #sync_tmp WHERE bid=a.bid);

        DROP TABLE #sync_tmp;
        COMMIT TRANSACTION;
    </insert>

 

常见问题：

1.语句执行异常，参数p0附近有语法错误：

#{xxx}会自动给参数两边加上引号，所以如果是数字或常量需要改为${xxx}; 不过${xxx}存在SQL注入的风险，正常情况下还是建议使用#{xxx}。