jOOQ在PostgreSQL模式下无法正确地将SQL翻译为H2数据库的语句。

Question

问题出在Postgres的"ON CONFLICT"语法上。

版本（maven依赖）：

- postgresql: 42.2.9 - jooq: 3.12.3 - h2database: 1.4.200

// mocking connection
final Connection connection = DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;Mode=PostgreSQL", "sa", "");
final Settings settings = new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);
Mockito.doReturn(DSL.using(connection, SQLDialect.POSTGRES, settings)).when(mockDbConn).getDSLContext();

// java code for upsert:
dc.insertInto(MY_TABLE)
    .columns(MY_TABLE.TOKEN, MY_TABLE.NAME, MY_TABLE.EMAIL)
    .values(token, name, email)
    .onDuplicateKeyUpdate()
    .set(MY_TABLE.EMAIL, email)
    .execute();

得到以下错误日志（似乎问题可能是由于 [*] （第4行↓）引起的）。 我不明白它为什么出现，也不知道如何解决它。

-- Syntax error in SQL statement:
INSERT INTO PUBLIC.MY_TABLE (TOKEN, NAME, EMAIL)
VALUES (?, ?, ?)
ON CONFLICT ([*]TOKEN, NAME) -- line 4
DO UPDATE SET EMAIL = EXCLUDED.EMAIL;

-- expected "DO";
-- SQL statement:
insert into public.my_table (token, name, email)
values (?, ?, ?)
on conflict (token, name)
do update set email = excluded.email;

-- [42001-200]

如果我从SQLDialect.POSTGRES切换到SQLDialect.H2方言，会发生以下情况：

-- Column "EXCLUDED.EMAIL" not found; SQL statement:
merge into public.my_table using (select 1 one)
on (public.my_table.token = cast(? as varchar) and public.my_table.name = cast(? as varchar))
when matched then update set public.my_table.email = excluded.email
when not matched then insert (token, name, email)
values (cast(? as varchar), cast(? as varchar), cast(? as varchar))

-- [42122-200]

- Habib Mohammad

---

[*]只是H2附加到查询中的标记，用于指示SQL中包含第一个解析错误的位置。您应该在jOOQ中使用H2方言，H2的兼容模式提供了非常有限的兼容性，H2不是其他数据库的仿真器。但是，jOOQ为H2生成的查询也无效；EXCLUDED虚拟表是来自PostgreSQL的ON CONFLICT子句，不能在标准的MERGE命令中使用。 - Evgenij Ryazanov

Answer 1

您在使用jOOQ API时混合了3种方言：

1. SQLDialect.MYSQL 方言，它是产生 onDuplicateKeyUpdate() 语法的方言。虽然可以在各种方言上模拟此语法，但通常最好使用本地语法（如果可用，则使用 SQL 标准的 MERGE 或 PostgreSQL 中的 ON CONFLICT）。 2. SQLDialect.POSTGRES 方言，这是您的生产目标方言。 3. SQLDialect.H2 方言，您正在使用它进行集成测试。

考虑到您可能只针对 PostgreSQL 作为生产数据库产品，这是很复杂的。我强烈建议您在集成测试中使用 testcontainers，这将允许您从方程式中删除 H2。此外，一旦您确定仅针对 PostgreSQL 作为目标方言，您可以避免使用 onDuplicateKeyUpdate() 语法，并使用 jOOQ 的本机 onConflict() 语法支持以获得更可预测的结果。

如果您继续混合上述 3 种方言，则通常会遇到某些限制，其中 jOOQ 或 H2 无法模拟您正在使用的语法。仅当您确实需要在生产中支持这 3 个方言时，才可以接受这种情况。

- Lukas Eder