主页 / coding / 问题 / 78601368
Accepted
funkrusher
Asked: 2024-06-10 17:03:17 +0800 CST

Jooq 为 Row-Value-Expression 生成运行时错误

  • 772

使用 jooq 语法时出现运行时错误row()，我不确定问题出在哪里。从row()查询中删除该部分后，它可以正确执行。以下代码示例会产生错误：

record ProductItem(Long productId, BigDecimal price, UserItem creator) {
}
record UserItem(Integer userId, String email) {
}

return dsl()
    .select(
        PRODUCT.PRODUCTID,
        PRODUCT.PRICE,
        row(
            PRODUCT.fk_product_creatorId().USERID,
            PRODUCT.fk_product_creatorId().EMAIL
        ).mapping(UserItem::new))
    .fetch(Records.mapping(ProductItem::new));

执行上述 Jooq-Query 时，它会崩溃并出现以下运行时异常：

2024-06-10 10:58:30,580 DEBUG [org.joo.too.LoggerListener] (executor-thread-1) Executing query          : 
select
  testshop2.product.productId,
  testshop2.product.price,
  alias_2438556.userId as nested.userId,
  alias_2438556.email as nested.email
2024-06-10 10:58:30,581 WARN  [org.mar.jdb.mes.ser.ErrorPacket] (executor-thread-1) Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
  alias_2438556.email as nested.email' at line 4
2024-06-10 10:58:30,581 DEBUG [org.joo.too.LoggerListener] (executor-thread-1) Exception                : org.jooq.exception.DataAccessException: SQL [select
  testshop2.product.productId,
  testshop2.product.price,
  alias_2438556.userId as nested.userId,
  alias_2438556.email as nested.email]; (conn=129) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
  alias_2438556.email as nested.email' at line 4
    at org.jooq_3.19.9.MARIADB.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:3607)
    at org.jooq.impl.Tools.translate(Tools.java:3595)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:827)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:362)
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:301)
    at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:322)
    at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:3256)
    at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:360)
    at org.jooq.impl.ResultQueryTrait.fetch(ResultQueryTrait.java:1465)
    at org.fk.product.repository.ProductRepository.query2(ProductRepository.java:77)
    at org.fk.product.manager.ProductManager.query(ProductManager.java:64)
    at org.fk.product.manager.ProductManager_ClientProxy.query(Unknown Source)
    at org.fk.product.controller.ProductExamplesControllerV1.query(ProductExamplesControllerV1.java:43)
    at org.fk.product.controller.ProductExamplesControllerV1$quarkusrestinvoker$query_8cfa215d81ea3f2aa9f868d5bca87ea66e97652c.invoke(Unknown Source)
    at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
    at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
    at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
    at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:599)
    at org.jboss.threads.EnhancedQueueExecutor$Task.doRunWith(EnhancedQueueExecutor.java:2516)
    at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2495)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1521)
    at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:11)
    at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:11)
    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
    at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLSyntaxErrorException: (conn=129) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.userId,
  alias_2438556.email as nested.email' at line 4
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:289)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:378)
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:172)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:915)
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:854)
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:773)
    at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:697)
    at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:93)
    at org.mariadb.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:276)
    at io.agroal.pool.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:301)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219)
    at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4975)
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:236)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
    ... 21 more

我尝试重现 jooq 创建的 SQL，它看起来像这样。似乎尤其是v8.userId似乎产生了错误。当v8-parts 被删除时，SQL 也可以正确执行，不会出现错误。

 select
    testshop2.product.productId as v0,
    testshop2.product.price as v2,
    alias_2438556.userId as v8.userId,
    alias_2438556.email as v8.email
  from (
    testshop2.product
      left outer join testshop.user as alias_2438556
        on testshop2.product.creatorId = alias_2438556.userId
    );

使用的版本是：

  • 乔克 3.19.9
  • 玛丽亚数据库 10.10.7
  • 夸库斯 3.9.5

如果需要更多详细信息，请提供信息。

另请参阅 Row-Value-Expression 的参考：

jooq

1 个回答

  1. Best Answer

    为嵌套属性生成的标识符类似于"nested.email"，使用 jOOQ 对此类嵌套对象的默认“点符号”。这也是 的一个历史性功能DefaultRecordMapper，它支持此符号以反射方式自动映射嵌套数据。

    此处假设标识符被引号括起来（这在 jOOQ 中始终是默认设置）。您可能将 设置Settings.renderQuotedNames为其他值，这就是您获得无效nested.email标识符的原因。如果这样做，您仍然可以将 更改Settings.namePathSeparator为其他值，例如"__"，这样未加引号的嵌套标识符（如 ）nested__email仍将与 一起使用DefaultRecordMapper并生成有效的 SQL。

    但是如果您没有充分的理由删除标识符的引用，那么我建议保持该功能处于开启状态。

    • 1

相关问题