使用MyBatis动态SQL实现多条件组合查询数据列表

前端HTML界面搜索框以及条件下拉框
MySQL查询语句SELECT

<select id="listPage" parameterType="java.util.Map" resultType="dac.order.api.vo.orderSearch.SearchOrderCoreVo">

        select
        od.delivery_weight,
        opd.pre_delivery_weight,
        os.settle_weight,
        os.settle_amount,
        ot.transfer_weight,
        ot2.freight_invoice_method,
        ot2.freight_invoice_method_name,
        ot2.delivery_address_name,
        oc.*
        from
        <include refid="tables"/>便于复用SQL表
        <where>
            <include refid="where"/>便于服用判断条件
        </where>
        <choose>
            <when test="orderBy == null || orderBy == ''">
                order by oc.create_time desc
            </when>
            <otherwise>
                order by ${orderBy}
                <choose>
                    <when test=" asc ">
                        asc
                    </when>
                    <otherwise>
                        desc
                    </otherwise>
                </choose>
            </otherwise>
        </choose>
        limit #{pageIndex}, #{pageSize}  分页
    </select>

<sql id="where">
        oc.app_id = #{appId}
        and oc.order_type not in ('生产预合同')
        and oc.deleted = 'N'

        <if test=" buyerAccessIds != null and buyerAccessIds.size > 0 ">
            and oc.buyer_id in
            <foreach item="item" index="index" collection="buyerAccessIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" providerAccessIds != null and providerAccessIds.size > 0 ">
            and oc.provider_id in
            <foreach item="item" index="index" collection="providerAccessIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" searchKey != null and searchKey != '' ">
            and (
            oc.frame_order_code like concat(#{searchKey}, '%') or
            oc.steel_order_code like concat(#{searchKey}, '%')
            )
        </if>

        <if test=" serialCode != null and serialCode != '' ">
            and oc.serial_code = #{serialCode}
        </if>

        <if test=" buyer != null and buyer.size > 0 ">
            and oc.buyer_id in
            <foreach item="item" index="index" collection="buyer" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" provider != null and provider.size > 0 ">
            and oc.provider_id in
            <foreach item="item" index="index" collection="provider" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" orderStatus != null and orderStatus.size > 0 ">
            and oc.order_status in
            <foreach item="item" index="index" collection="orderStatus" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" paymentMethod != null and paymentMethod.size > 0 ">
            and oc.payment_type in
            <foreach item="item" index="index" collection="paymentMethod" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" deliveryAddress != null and deliveryAddress.size > 0 ">
            and ot2.delivery_address_code in
            <foreach item="item" index="index" collection="deliveryAddress" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <if test=" lack != null ">
            <if test=" lack == 1 ">
                and oc.lack_weight > 0
            </if>
            <if test=" lack == 0 ">
                and oc.lack_weight <![CDATA[ <= ]]> 0
            </if>
        </if>

        <if test=" deliveryMonthMin != null and deliveryMonthMin != '' ">
            and date_format(oc.delivery_date, '%Y%m') >= replace(#{deliveryMonthMin}, '-', '')
        </if>

        <if test=" deliveryMonthMax != null and deliveryMonthMax != '' ">
            and date_format(oc.delivery_date, '%Y%m') <![CDATA[ <= ]]> replace(#{deliveryMonthMax}, '-', '')
        </if>

        <if test=" orderMonthMin != null and orderMonthMin != '' ">
            and date_format(oc.order_date, '%Y%m') >= replace(#{orderMonthMin}, '-', '')
        </if>

        <if test=" orderMonthMax != null and orderMonthMax != '' ">
            and date_format(oc.order_date, '%Y%m') <![CDATA[ <= ]]> replace(#{orderMonthMax}, '-', '')
        </if>

        <if test=" createTimeMin != null and createTimeMin != '' ">
            and oc.create_time >= #{createTimeMin}
        </if>

        <if test=" createTimeMax != null and createTimeMax != '' ">
            and oc.create_time <![CDATA[ <= ]]> #{createTimeMax}
        </if>

        <if test=" frameOrderCode != null and frameOrderCode.size > 0 ">
            and oc.frame_order_code in
            <foreach item="item" index="index" collection="frameOrderCode" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

    </sql>