NamedParameterJdbcTemplate使用占位符异常：查询返回空结果排查

问题场景 #

在Spring Boot后端持久层方法中，使用NamedParameterJdbcTemplate执行带命名参数的SQL时返回空集合，触发NotFoundException；但通过replaceAll将SQL中的占位符替换为具体值后，查询可正常返回预期结果。

持久层方法代码 #

public Map<TournamentHorse, Horse> getGenerateFirstRound(long id) throws NotFoundException {
    LOG.trace("getGenerateFirstRound({})");

    MapSqlParameterSource parameterSource = new MapSqlParameterSource();
    parameterSource.addValue("idTour", id + "");
    Map<TournamentHorse, Horse> returnMap = new HashMap<>();

    String newReq = SQL_GET_GENERATE_FIRST_ROUND.replaceAll(":idTour", id + "");

    jdbcNamed.query(SQL_GET_GENERATE_FIRST_ROUND, parameterSource, result -> {
      TournamentHorse tournamentHorse = new TournamentHorse()
              .setTournamentId(result.getLong("tournament_id"))
              .setHorseId(result.getLong("horse_id"))
              .setEntryNumber(result.getObject("entryNumber") == null ? null : result.getLong("entryNumber"))
              .setRoundReached(1L);

      Horse horse = new Horse()
              .setId(result.getLong("id"))
              .setName(result.getString("name"))
              .setSex(Sex.valueOf(result.getString("sex")))
              .setDateOfBirth(result.getDate("date_of_birth").toLocalDate())
              .setHeight(result.getFloat("height"))
              .setWeight(result.getFloat("weight"))
              .setBreedId(result.getLong("breed_id"));

      returnMap.put(tournamentHorse, horse);
    });

    if (returnMap.isEmpty()) {
      throw new NotFoundException("Tournament participants not found");
    }
    if (returnMap.size() != 8) {
      LOG.error("Tournament participants wrong quantity of horses: size=" + returnMap.size());
      throw new FatalException("Tournament participants wrong quantity of horses: size=" + returnMap.size());
    }

    return returnMap;
}

SQL常量定义 #

private static final String SQL_GET_GENERATE_FIRST_ROUND =
        // get all participants points
        "WITH PARTICIPANTS_WITH_POINTS as ("
        + "With Points as (SELECT th.horse_id, h.name, h.date_of_birth, sum(CASE WHEN th.roundReached = 4 THEN 5 "
        + "WHEN th.roundReached = 3 THEN 3 "
        + "WHEN th.roundReached = 2 THEN 1 "
        + "ELSE 0 END) points "
        + "FROM " + TABLE_NAME_PARTICIPANT + " th "
        + "join " + TABLE_NAME_HORSE + " h on th.horse_id = h.id "
        + "join " + TABLE_NAME + " t on t.id = th.TOURNAMENT_ID and t.START_DATE between "
        + "(SELECT DATEADD('MONTH', -12, START_DATE) FROM " + TABLE_NAME + " WHERE id=:idTour) "
        + "AND "
        + "(SELECT START_DATE FROM " + TABLE_NAME + " WHERE id=:idTour) "
        + "group by th.horse_id) "
        + "SELECT th.TOURNAMENT_ID, th.HORSE_ID, th.ENTRYNUMBER, th.ROUNDREACHED, NAME, Points "
        + "FROM " + TABLE_NAME_PARTICIPANT + " th "
        + "join Points p on th.HORSE_ID = p.horse_id and (th.TOURNAMENT_ID=:idTour) "
        + "order by p.points desc, p.NAME asc), "
        // rank the 8 horses
        + "RankedEntries as (SELECT TOURNAMENT_ID, horse_id, name, points, "
        + "row_number() over (order by points desc, name asc) as rank, "
        + "COUNT(*) over () as total_count from PARTICIPANTS_WITH_POINTS), "
        // create the entity number based on RankedEnties
        + "CrossPaired AS ("
        + "SELECT TOURNAMENT_ID, HORSE_ID, name, rank, total_count, "
        + "case when rank <= (total_count / 2) THEN (rank * 2) - 1 "
        + "else (total_count + 1 - rank) * 2 "
        + "end as entryNumber "
        + "from RankedEntries) "
        + "select cp.TOURNAMENT_ID, cp.HORSE_ID, entryNumber, h.* from CrossPaired cp join "
        + TABLE_NAME_HORSE + " h on h.id = cp.HORSE_ID order by entryNumber asc;";

已尝试操作 #

• 更换占位符名称，无变化
• 调试JDBC日志，将日志输出的预编译SQL替换?为对应值后，在数据库控制台可正常执行
• 重启服务，问题依旧
• 改用JdbcTemplate，问题仍然存在

解决方案及排查方向 #

1. 修正参数类型匹配 #

当前代码将long类型的id转为字符串传入参数源，但数据库中id字段应为数值类型（如BIGINT），类型不匹配会导致查询过滤失效。修改为直接传入数值类型：

parameterSource.addValue("idTour", id);

2. 优化SQL子查询写法 #

原SQL中多次嵌套子查询使用:idTour参数，部分JDBC驱动对嵌套CTE中的参数解析支持有限。可提前查询目标赛事的日期参数，避免嵌套子查询：

// 先查询目标赛事的起始日期
LocalDate startDate = jdbcNamed.queryForObject(
    "SELECT START_DATE FROM " + TABLE_NAME + " WHERE id=:idTour",
    new MapSqlParameterSource("idTour", id),
    LocalDate.class
);
LocalDate oneYearAgo = startDate.minusYears(1);

// 构建参数源
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("idTour", id);
parameterSource.addValue("oneYearAgo", oneYearAgo);
parameterSource.addValue("startDate", startDate);

对应修改SQL中的日期范围部分：

join " + TABLE_NAME + " t on t.id = th.TOURNAMENT_ID and t.START_DATE between :oneYearAgo AND :startDate

3. 拆平嵌套CTE #

原SQL使用了嵌套的WITH语句（PARTICIPANTS_WITH_POINTS内部嵌套Points），部分驱动对这种嵌套结构的参数解析存在问题。尝试将嵌套CTE拆平为同级CTE：

WITH Points as (SELECT th.horse_id, h.name, h.date_of_birth, sum(CASE WHEN th.roundReached = 4 THEN 5 
WHEN th.roundReached = 3 THEN 3 
WHEN th.roundReached = 2 THEN 1 
ELSE 0 END) points 
FROM " + TABLE_NAME_PARTICIPANT + " th 
join " + TABLE_NAME_HORSE + " h on th.horse_id = h.id 
join " + TABLE_NAME + " t on t.id = th.TOURNAMENT_ID and t.START_DATE between :oneYearAgo AND :startDate
group by th.horse_id),
PARTICIPANTS_WITH_POINTS as (
SELECT th.TOURNAMENT_ID, th.HORSE_ID, th.ENTRYNUMBER, th.ROUNDREACHED, NAME, Points 
FROM " + TABLE_NAME_PARTICIPANT + " th 
join Points p on th.HORSE_ID = p.horse_id and (th.TOURNAMENT_ID=:idTour) 
order by p.points desc, p.NAME asc),
... -- 后续CTE保持不变

4. 开启驱动级日志排查 #

启用JDBC驱动的DEBUG级别日志（如MySQL的com.mysql.cj.jdbc包），查看实际发送给数据库的参数值和完整SQL，确认参数是否被正确传递到所有需要的位置。

内容的提问来源于stack exchange，提问作者e4e5Ke2