mybatis Oracle 批量插入更新及模糊查询

佚名 7年前 (2019-05-06) 随笔 1578人围观抢沙发百度已收录

1.批量插入

    <insert id="save" parameterType="java.util.List" useGeneratedKeys="false">
        insert into
        MER_CREDIT_SYSTEM(ID,TITLE,CREDIT_LIMIT,CREATE_TIME,UPDATE_TIME,CREATE_USER,UPDATE_USER,MERCHANT_ID)
        SELECT SEQ_MERCREDITSYSTEM_ID.nextval ID,a.* from (
        <foreach collection="list" item="item" index="index" separator="union all">
            (select
            #{item.title,jdbcType=VARCHAR} TITLE,#{item.creditLimit,jdbcType=INTEGER} CREDIT_LIMIT,sysdate
            CREATE_TIME,sysdate UPDATE_TIME,
            #{item.createUser,jdbcType=VARCHAR} CREATE_USER,
            #{item.updateUser,jdbcType=VARCHAR} UPDATE_USER,#{item.merchantId,jdbcType=INTEGER} MERCHANT_ID from dual)
        </foreach>)a </insert>

2.批量更新

    <update id="updateByMerchantId" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            UPDATE MER_CREDIT_SYSTEM
            <set>
                <if test="item.title != null">
                    TITLE = #{item.title,jdbcType=VARCHAR},
                </if>

                <if test="item.creditLimit != null">
                    CREDIT_LIMIT = #{item.creditLimit,jdbcType=INTEGER},
                </if>
                <if test="item.updateUser != null">
                    UPDATE_USER = #{item.updateUser,jdbcType=VARCHAR},
                </if>
                    UPDATE_TIME = sysdate
            </set>
            where id = #{item.id,jdbcType=INTEGER} and MERCHANT_ID=#{item.merchantId,jdbcType=INTEGER}
        </foreach>
    </update>

3.模糊查询

        <where> mi.MERCHANT_ID=#{map.merchantId} and uu.STATUS=0
            <if test="map.keyName != null and map.keyName != ''"> and uu.NICK_NAME LIKE '%' || #{map.keyName} || '%'
            </if>
            <if test="map.value != null and map.value != ''">
                and uc.CREDIT <![CDATA[>]]> #{map.value}
            </if>
        </where>