本機查詢僅在H2資料庫中導致錯誤

我有一個在 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

有什么想法嗎？

uj5u.com熱心網友回復：

H2 MySQL 兼容模式沒有提及任何有關IF()功能支持的內容。您的選擇是（至少）：

• 從 H2 請求它
• IF(a, b, c)用標準 SQL替換CASE WHEN a THEN b ELSE c END
• 使用 testcontainers 直接在 MySQL 上而不是在 H2 上運行集成測驗（我在這里寫過關于這個的博客，我強烈推薦它，不管使用的是什么 ORM）
• 使用像 jOOQ 這樣的 SQL 翻譯器，在后臺將您的本地 SQL 翻譯成目標方言。

最快的解決方法是使用CASE. 但就個人而言，我建議直接在 MySQL 上進行集成測驗，除非您將 H2 也用作生產資料庫產品。

標籤：爪哇 mysql 春天 jpa h2

上一篇：從perl陣列中提取資料哈希參考

下一篇：如何在php和mysql中的一個字串中顯示多個資料