Dynamic SQL for Date Range and List Queries

When building dynamic queries in MyBatis, it's common to filter by date ranges and iterate over a list of values. Below is an example showing how to handle both scenarios using MyBatis dynamic SQL with where, if, and foreach tags.

SQL Mapping

<select id="selectQuotaManagement" resultType="QuotaManagementVO">
    SELECT
        us.member_id AS memberId,
        qa.*
    FROM quota_application qa
    LEFT JOIN user_quota us ON us.id = qa.user_quota_id
    <where>
        <if test="params.applicationStatus != null">
            AND qa.application_status = #{params.applicationStatus}
        </if>
        <if test="params.search != null">
            AND us.member_id = #{params.search}
        </if>
        <if test="params.memberIds != null and params.memberIds.size() > 0">
            AND us.member_id IN
            <foreach collection="params.memberIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

        <!-- Date range filtering based on timeType -->
        <if test="params.startDate != null and params.timeType == 1">
            AND qa.create_time >= #{params.startDate}
        </if>
        <if test="params.endDate != null and params.timeType == 1">
            AND qa.create_time <= #{params.endDate}
        </if>
        <if test="params.startDate != null and params.timeType == 2">
            AND qa.update_time >= #{params.startDate}
        </if>
        <if test="params.endDate != null and params.timeType == 2">
            AND qa.update_time <= #{params.endDate}
        </if>
    </where>
    ORDER BY qa.create_time DESC
</select>

Mapper Interface

IPage<QuotaManagementVO> selectQuotaManagementPage(
    @Param("page") Page<?> page,
    @Param("params") QuotaManagementDTO dto
);

DTO Class

package com.example.model.dto;

import com.example.base.PageDTO;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
public class QuotaManagementDTO extends PageDTO implements Serializable {

    @ApiModelProperty("User ID or applicant user")
    private String search;

    @ApiModelProperty("Application status: 0-pending, 1-approved, 2-rejected")
    private Integer applicationStatus;

    @ApiModelProperty("Application type: 0-authorization, 1-quota upgrade")
    private Integer type;

    private List<Long> memberIds;

    @ApiModelProperty("Time type: 1-application time, 2-review time")
    private Integer timeType;

    @ApiModelProperty("Start time")
    private String startDate;

    @ApiModelProperty("End time")
    private String endDate;
}

Key Points

• Use <if> for optional conditions.
• Use <foreach> to iterate over a list for IN queries.
• For date ranges, combine startDate/endDate with timeType to switch between different timestamp columns.
• Avoid DATE_FORMAT to prevent performance issues; compare directly with the column if the format matches the database type.