Spring Boot接口未传code参数触发SQL语法异常排查

问题重现 #

测试API端点时，不传code参数会触发SQL语法异常，错误提示WHERE)附近存在语法问题；传入正确/错误code时返回正常结果，直接在MySQL CLI执行带null参数的同类查询也能正常运行。

错误日志 #

2022-08-24 11:51:54.283  WARN 9836 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2022-08-24 11:51:54.283 ERROR 9836 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE) FROM rates WHERE (date BETWEEN '2022-01-01' AND '2022-03-01') AND ((null ' at line 1
2022-08-24 11:51:54.292 ERROR 9836 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api/v1] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE) FROM rates WHERE (date BETWEEN '2022-01-01' AND '2022-03-01') AND ((null ' at line 1

相关代码 #

Repo层代码

@Query("SELECT * FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR rate_code = :code) AND rate > 0)", nativeQuery = true)
fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>

Controller层代码

@GetMapping("/historic")
fun ratesForDateRange(
    @RequestParam startDate: Optional<Date>,
    @RequestParam endDate: Optional<Date>,
    @RequestParam(required = false) code: String?,
    @RequestParam(required = false) pageNumber: Int?
): Page<RateEntity> {
    val lastDate = Date.valueOf(START_DATE)
    val page = pageNumber ?: 0
    println(code)
    return repo.getByDateRange(
        startDate.orElse(lastDate),
        endDate.orElse(UtilFunctions.getCurrentSQLDate()),
        code,
        PageRequest.of(page, 50)
    )
}

问题原因 #

当使用原生查询配合Pageable分页参数时，Spring Data JPA会自动生成对应的COUNT查询来统计总记录数。当code参数为null时，自动生成的COUNT查询在解析动态条件时出现逻辑错误，生成了WHERE)这种无效语法，最终导致SQL执行失败。

直接在MySQL CLI执行正常是因为手动编写的查询不涉及Spring Data自动生成COUNT查询的逻辑，而API调用时分页必须依赖COUNT查询，因此触发了问题。

解决方案 #

方法1：手动指定COUNT查询 #

在@Query注解中添加countQuery属性，手动编写正确的统计查询，完全避免Spring Data自动生成错误的COUNT语句：

@Query(
    value = "SELECT * FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR rate_code = :code) AND rate > 0)",
    countQuery = "SELECT COUNT(*) FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR rate_code = :code) AND rate > 0)",
    nativeQuery = true
)
fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>

方法2：改用JPQL查询（实体映射正确时优先使用） #

如果RateEntity已正确映射rates表，改用JPQL可以让Spring Data更好地处理动态参数和分页逻辑，从根源避免原生查询的COUNT生成问题：

@Query("SELECT r FROM RateEntity r WHERE r.date BETWEEN :startDate AND :endDate AND (:code IS NULL OR r.rateCode = :code) AND r.rate > 0")
fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>

注意：JPQL中使用实体属性名（如rateCode）而非数据库列名（rate_code），需确保实体字段与数据库列的映射关系正确。

方法3：调整原生查询条件结构 #

修改原生查询的条件顺序，简化逻辑，帮助Spring Data的COUNT查询生成器正确解析：

@Query("SELECT * FROM rates WHERE date BETWEEN :startDate AND :endDate AND rate > 0 AND (:code IS NULL OR rate_code = :code)", nativeQuery = true)
fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>

这种方法可靠性不如前两种，但在部分场景下可以快速解决问题。

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