记录一下费了一些功夫的Mybatis复杂动态Sql

记录一下费了一些功夫的Mybatis动态Sql：

满足以下所有条件：

1.查询time_status 为1和2的数据在前面，为3的数据在后面
2.id 不在dsIds这个范围里的数据;
3.按照dead_line 降序
4.如果timeType=1则查询 start_time 在 startMonth 和 endMonth 之间的数据；timeType=2，则查询start_time大于endMonth的数据；
5.如果customerName不为空，则查询customer_name=customerName的数据；
6.origin='talents‘， enable=‘true’ ，status=1

Dao层：

    List<DualSelectEntity>  dsList(@Param("dsIds")List<Long> dsIds,
                                   @Param("type")Integer type,
                                   @Param("customerName")String customerName,
                                   @Param("timeType")int timeType,
                                   @Param("startMonth")long startMonth,
                                   @Param("endMonth")long endMonth);

xml：

 <select id="dsList" resultType="cn.com.newcapec.modules.jyb.entity.DualSelectEntity">
        (SELECT * FROM `j_dual_select`
        where origin='talents'
        and enable='true'
        and status=1
        <if test="dsIds != null and dsIds.size()>0">
            and id not in
            <foreach item="item" index="index" collection="dsIds" open="("  close=")" separator=",">
                #{item}
            </foreach>
        </if>
        and time_status in(1,2)
        and type=#{type}
        <choose>
            <when test="timeType==1">
                and start_time BETWEEN #{startMonth}
                AND #{endMonth}
            </when>
            <when test="timeType==2">
                and start_time>#{endMonth}
            </when>
            <otherwise>
            </otherwise>
        </choose>
        <if test="customerName!=null and customerName!=''">
            and customer_name=#{customerName}
        </if>
        ORDER BY dead_line desc)
        
        union
        
        (SELECT * FROM `j_dual_select`
        where origin='talents'
        and enable='true'
        and status=1
        <if test="dsIds != null and dsIds.size()>0">
            and id not in
            <foreach item="item" index="index" collection="dsIds" open="("  close=")" separator=",">
                #{item}
            </foreach>
        </if>
        and time_status = 3
        and type=#{type}
        <choose>
            <when test="timeType==1">
                and start_time BETWEEN #{startMonth}
                AND #{endMonth}
            </when>
            <when test="timeType==2">
                and start_time>#{endMonth}
            </when>
            <otherwise>
            </otherwise>
        </choose>
        <if test="customerName!=null and customerName!=''">
            and customer_name=#{customerName}
        </if>
        ORDER BY dead_line desc)
        
    </select>