仅H2数据库执行Native Query报错问题排查

问题场景 #

一段在MySQL中正常运行的Native Query，在H2数据库执行时触发语法错误，相关代码、日志及配置如下：

Spring Data JPA仓库代码 #

@Query(value = "SELECT r.* FROM rewards r "
      + "INNER JOIN models m ON r.model_id = m.model_pk  "
      + "WHERE m.printer_family = :businessPrinterFamily "
      + "AND r.reward_type IN (:rewardTypes) "
      + "AND IF(:isOpportunities, m.model_pk IN (:businessPrinterModels), TRUE) "
      + "ORDER BY :sortingMethod",
      countQuery = "SELECT r.* FROM rewards r "
          + "INNER JOIN models m ON r.model_id = m.model_pk  "
          + "WHERE m.printer_family = :businessPrinterFamily "
          + "AND r.reward_type IN (:rewardTypes) "
          + "AND IF(:isOpportunities, m.model_pk IN (:businessPrinterModels), TRUE) ",
      nativeQuery = true)
List<Reward> getFilteredRewards(@Param("sortingMethod") String sortingMethod,
      @Param("isOpportunities") boolean isOpportunities,
      @Param("businessPrinterModels") List<Integer> businessPrinterModels,
      @Param("rewardTypes") List<Integer> rewardTypes,
      @Param("businessPrinterFamily") int businessPrinterFamily, Pageable pageable);

H2执行错误日志 #

could not prepare statement; SQL [SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND [*]IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ?"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
SELECT r.* FROM rewards r INNER JOIN models m ON r.model_id = m.model_pk  WHERE m.printer_family = ? AND r.reward_type IN (?, ?) AND IF(?, m.model_pk IN (?), TRUE) ORDER BY ? limit ? [42001-214]

H2数据库配置 #

spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=MySQL
    username: sa
    password:
    driver-class-name: org.h2.Driver
  jpa:
    defer-datasource-initialization: false
  h2:
    console:
      enabled: true
      path: /h2-console

问题原因 #

H2的MySQL兼容模式（MODE=MySQL）并未完全复刻MySQL的所有语法细节，对于将条件表达式（m.model_pk IN (:businessPrinterModels)）作为IF()函数第二个参数的写法，H2的SQL解析器无法识别，触发语法错误。

解决方法 #

将MySQL专属的IF()函数替换为标准SQL的CASE WHEN语句，该语法可同时被MySQL和H2支持：

修改后的查询条件 #

原条件：

AND IF(:isOpportunities, m.model_pk IN (:businessPrinterModels), TRUE)

替换为：

AND CASE WHEN :isOpportunities THEN m.model_pk IN (:businessPrinterModels) ELSE TRUE END

修改后的完整仓库代码 #

@Query(value = "SELECT r.* FROM rewards r "
      + "INNER JOIN models m ON r.model_id = m.model_pk  "
      + "WHERE m.printer_family = :businessPrinterFamily "
      + "AND r.reward_type IN (:rewardTypes) "
      + "AND CASE WHEN :isOpportunities THEN m.model_pk IN (:businessPrinterModels) ELSE TRUE END "
      + "ORDER BY :sortingMethod",
      countQuery = "SELECT r.* FROM rewards r "
          + "INNER JOIN models m ON r.model_id = m.model_pk  "
          + "WHERE m.printer_family = :businessPrinterFamily "
          + "AND r.reward_type IN (:rewardTypes) "
          + "AND CASE WHEN :isOpportunities THEN m.model_pk IN (:businessPrinterModels) ELSE TRUE END ",
      nativeQuery = true)
List<Reward> getFilteredRewards(@Param("sortingMethod") String sortingMethod,
      @Param("isOpportunities") boolean isOpportunities,
      @Param("businessPrinterModels") List<Integer> businessPrinterModels,
      @Param("rewardTypes") List<Integer> rewardTypes,
      @Param("businessPrinterFamily") int businessPrinterFamily, Pageable pageable);

额外注意事项 #

ORDER BY :sortingMethod这种动态排序参数的写法，在H2中可能被当作字符串字面量处理，导致排序逻辑失效。建议改用Spring Data JPA的Sort参数传递排序规则，或通过CASE WHEN实现兼容跨数据库的动态排序。

内容的提问来源于stack exchange，提问作者Felipe Hogrefe Bento