新H2的jOOQ糟糕的SQL语法

Question

我刚刚更新到Spring Boot 2.7.2和新的H2 2.1.214。

jOOQ版本为3.16.6(专业版(。

从那以后，我得到了一个带有限制查询的语法错误的SQL异常。

如果我理解正确的话，H2中不再支持关键字limit，而是应该使用FETCH FIRST。

dslContext.select( FOO.fields() ).from( FOO ).limit( 1 )
select "FOO".ID" from "FOO" limit ?

Stacktrace:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select ""FOO"".""ID"" from ""FOO"" limit [*]?"; SQL statement:
select "FOO"."ID" from "FOO" limit ? [42000-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.message.DbException.getSyntaxError(DbException.java:247)
at org.h2.command.Parser.getSyntaxError(Parser.java:898)
at org.h2.command.Parser.prepareCommand(Parser.java:572)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
at jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.performQueryExecutionListener(ConnectionProxyLogic.java:112)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.access$000(ConnectionProxyLogic.java:25)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic$1.execute(ConnectionProxyLogic.java:50)
at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:42)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.invoke(ConnectionProxyLogic.java:47)
at net.ttddyy.dsproxy.proxy.jdk.ConnectionInvocationHandler.invoke(ConnectionInvocationHandler.java:25)
at jdk.proxy2/jdk.proxy2.$Proxy140.prepareStatement(Unknown Source)
at jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.performQueryExecutionListener(ConnectionProxyLogic.java:112)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.access$000(ConnectionProxyLogic.java:25)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic$1.execute(ConnectionProxyLogic.java:50)
at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:42)
at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.invoke(ConnectionProxyLogic.java:47)
at net.ttddyy.dsproxy.proxy.jdk.ConnectionInvocationHandler.invoke(ConnectionInvocationHandler.java:25)
at jdk.proxy2/jdk.proxy2.$Proxy140.prepareStatement(Unknown Source)
at org.quickperf.sql.connection.QuickPerfDatabaseConnection.prepareStatement(QuickPerfDatabaseConnection.java:62)
at jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:238)
at jdk.proxy2/jdk.proxy2.$Proxy320.prepareStatement(Unknown Source)
at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)
at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:82)
at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:210)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:307)
... 92 more

1. 这是已知问题吗
2. 是否有重写查询的方法

Answer 1

对于如何将jOOQ与H2一起用作测试数据库，这似乎是一个常见的误解，所以我写了一篇关于jOOQ对此的立场的博客文章。jOOQ问题跟踪器上也要求该功能为#13895

TL；DR:对于jOOQ，请不要使用H2的兼容模式。

有效配置

当使用H2作为不同生产数据库产品的测试数据库产品时，只有一个真正有效的配置：

• 使用jOOQ的SQLDialect.H2和H2本机，无兼容模式。这是由jOOQ测试的集成

你可能会想：

• 将jOOQ的SQLDialect.SQLSERVER和H2与SQL Server兼容模式一起使用。这是jOOQ测试的而非集成(请参阅下面的详细信息(。我不建议这样做，因为您可能会遇到jOOQ假设存在的H2兼容模式的限制，因为jOOQ认为它是一个实际的SQL Server实例
• 在兼容模式下将SQLDialect.H2与H2一起使用实际上没有意义，因为LIMIT在H2中有效，但在SQL Server中无效，因为SQL Server只支持TOP或OFFSET .. FETCH。因此，您不会得到一个好的SQL方言匹配。参见例如：https://github.com/h2database/h2database/issues/3537

兼容性模式和使用H2作为测试数据库的一些背景

jOOQ中的假设是，当您使用H2时，您将以本机方式使用H2(例如，作为内存中的数据库，也在生产中(。使用H2作为一个简单的测试数据库并不是H2的主要用例，即使近年来它非常流行。但是，为什么不使用基于测试容器的方法，只使用生产RDBMS开发/集成测试呢？好处是显而易见的：

• 您没有任何这样的基础设施问题和测试工件
• 您可以使用所有特定于供应商的特性，其中包括表值函数、表值参数、XML/JSON等

H2兼容模式是为了确保您的本机SQL Server查询在H2上工作而不做任何更改。这对于纯粹基于JDBC的应用程序非常有用。但是既然您使用的是jOOQ和SQLDialect.H2，为什么要在H2中保留兼容模式呢？如果您想继续使用H2作为测试数据库产品，jOOQ已经处理了方言之间的翻译。但是，我认为如果你使用测试容器，你的生活会更简单。您甚至可以使用它来生成jOOQ代码，如下所示。