组合特定参数时MyBatis动态SQL在PostgreSQL中触发语法错误

在使用MyBatis生成动态SQL时遇到以下异常场景：

• 报错触发条件：同时传入airlineCode、flightNumber和flightDate，或仅传入airlineCode+flightNumber时，PostgreSQL抛出语法错误：

  org.postgresql.util.PSQLException: ERROR: syntax error at or near "flight_number"

• 正常运行场景：仅传入flightNumber，或airlineCode搭配日期范围时，SQL可正常执行。

对应的MyBatis映射语句如下：

<select id="fetchAllUpdAirRetroactiveCostList" resultMap="airUpdRetroactiveCostVOs">
    SELECT
        airline_code,
        contract_code,
        flight_number,
        flight_date,
        contract_route_code,
        airline_fare_basis_code,
        currency,
        adult_cost,
        child_cost,
        infant_cost,
        cabin_class,
        ops_req_id,
        unique_retro_id
    FROM ITRVL_AIR_RETRO_UPD
    <where>
        TENANT_ID = :tenant_id
        <if test="searchVO.airlineCode != null and searchVO.airlineCode != ''">
            AND airline_code = #{searchVO.airlineCode}
        </if>
        <if test="searchVO.contractCode != null and searchVO.contractCode != ''">
            AND contract_code = #{searchVO.contractCode}
        </if>
        <if test="searchVO.flightNumber != null">
            AND flight_number = #{searchVO.flightNumber}
        </if>
        <if test="searchVO.fromDate != null and searchVO.toDate != null">
            AND flight_date BETWEEN CAST(#{searchVO.fromDate} AS DATE) AND CAST(#{searchVO.toDate} AS DATE)
        </if>
    </where>
</select>

注：flightNumber在Java的searchVO中为Integer类型，怀疑MyBatis生成了AND flight_number = 这类无值的畸形SQL片段。

核心问题定位 #

你的怀疑方向正确：当flightNumber为Integer类型时，若传入值为0（Integer默认值或业务允许的合法值），<if test="searchVO.flightNumber != null">判断会通过，但多参数组合场景下可能出现参数绑定异常，导致生成无值的SQL片段，触发PostgreSQL语法错误。

安全处理可选参数的方法 #

1. 完善Integer类型参数的判断逻辑 #

针对Integer类型参数，需结合业务规则补充判断：

• 如果业务中flightNumber不能为0，修改条件为：

  <if test="searchVO.flightNumber != null and searchVO.flightNumber != 0">
      AND flight_number = #{searchVO.flightNumber}
  </if>
  

• 如果业务允许0作为合法值，在Java层对searchVO的flightNumber做前置校验，确保只有明确传入有效值时才进入SQL条件，避免无意义的参数传递。

2. 统一参数绑定语法 #

语句中TENANT_ID = :tenant_id混用了:和#{}的参数引用方式，统一使用#{}进行预编译绑定，减少潜在的绑定异常：

TENANT_ID = #{tenant_id}

3. 用<trim>标签做兜底（处理无效片段） #

<where>标签仅能去除开头的AND/OR，无法处理AND flight_number = 这类畸形片段。使用<trim>标签可更精细控制SQL生成：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    TENANT_ID = #{tenant_id}
    <if test="searchVO.airlineCode != null and searchVO.airlineCode != ''">
        AND airline_code = #{searchVO.airlineCode}
    </if>
    <!-- 其他条件同上 -->
</trim>

更优的PostgreSQL动态SQL构建方式 #

1. 使用MyBatis-Plus条件构造器（推荐） #

若项目允许引入MyBatis-Plus，可通过QueryWrapper简化动态条件构建，避免手动编写XML中的<if>判断，降低出错概率：

QueryWrapper<AirRetroactiveCost> wrapper = new QueryWrapper<>();
wrapper.eq("TENANT_ID", tenantId);
if (StringUtils.isNotBlank(searchVO.getAirlineCode())) {
    wrapper.eq("airline_code", searchVO.getAirlineCode());
}
if (searchVO.getFlightNumber() != null) {
    wrapper.eq("flight_number", searchVO.getFlightNumber());
}
if (searchVO.getFromDate() != null && searchVO.getToDate() != null) {
    wrapper.between("flight_date", searchVO.getFromDate(), searchVO.getToDate());
}
List<AirRetroactiveCost> list = airRetroactiveCostMapper.selectList(wrapper);

2. PostgreSQL原生函数辅助（小数据量场景） #

利用PostgreSQL的COALESCE函数简化条件，但仅适用于数据量较小的表（会触发全表扫描）：

<select id="fetchAllUpdAirRetroactiveCostList" resultMap="airUpdRetroactiveCostVOs">
    SELECT
        -- 字段列表
    FROM ITRVL_AIR_RETRO_UPD
    WHERE TENANT_ID = #{tenant_id}
        AND airline_code = COALESCE(#{searchVO.airlineCode, jdbcType=VARCHAR}, airline_code)
        AND contract_code = COALESCE(#{searchVO.contractCode, jdbcType=VARCHAR}, contract_code)
        AND flight_number = COALESCE(#{searchVO.flightNumber, jdbcType=INTEGER}, flight_number)
        AND (flight_date BETWEEN CAST(#{searchVO.fromDate, jdbcType=DATE} AS DATE) AND CAST(#{searchVO.toDate, jdbcType=DATE} AS DATE) 
             OR #{searchVO.fromDate} IS NULL OR #{searchVO.toDate} IS NULL)
</select>

内容的提问来源于stack exchange，提问作者Rijo Thomas Mathew