本机查询仅在H2数据库中导致错误

Question

我有一个查询在MySQL数据库上运行，但在h2上不运行。

这是我的存储库：

@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]

在日志中，我得到以下内容：

2022-09-15 09:35:15.647 ERROR 267713 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : 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

有什么问题吗？

Answer 1

H2 MySQL兼容模式没有提及任何关于IF()功能支持的内容。你的选择是(至少(：

• 从H2请求
• 用标准SQLCASE WHEN a THEN b ELSE c END替换IF(a, b, c)
• 使用testcontainers直接在MySQL上运行集成测试，而不是在H2上运行(我在这里写过这方面的博客，我强烈推荐它，不管使用的是ORM(
• 使用像jOOQ这样的SQL翻译器将您的本地SQL翻译成目标方言。免责声明：我为jOOQ背后的公司工作

最快的修复方法是使用CASE。但就我个人而言，我建议直接在MySQL上进行集成测试，除非您将H2也用作生产数据库产品。