java JOOQ BadSqlGrammarException

ohfgkhjo  于 2023-05-05  发布在  Java
关注(0)|答案(1)|浏览(142)

我想为我的Sping Boot 应用程序创建一个使用JOOQ发出请求的存储库。为此，我生成了必要的代码并编写了一个函数。但是当调用函数时，会发生关于无效请求的错误。
我使用以下代码生成了代码。

package com.example.jooccodegen;

import org.jooq.codegen.GenerationTool;
import org.jooq.meta.jaxb.*;

public class JoocCodeGenApplication {

    public static void main(String[] args) throws Exception {
        Database database = new Database()
                .withName("org.jooq.meta.extensions.liquibase.LiquibaseDatabase")
                .withProperties(
                        new Property().withKey("rootPath").withValue("migrations"),
                        new Property().withKey("scripts").withValue("master.xml")
                );

        Generate options = new Generate()
                .withGeneratedAnnotation(true)
                .withGeneratedAnnotationDate(false)
                .withNullableAnnotation(true)
                .withNullableAnnotationType("org.jetbrains.annotations.Nullable")
                .withNonnullAnnotation(true)
                .withNonnullAnnotationType("org.jetbrains.annotations.NotNull")
                .withJpaAnnotations(false)
                .withValidationAnnotations(true)
                .withSpringAnnotations(true)
                .withConstructorPropertiesAnnotation(true)
                .withConstructorPropertiesAnnotationOnPojos(true)
                .withConstructorPropertiesAnnotationOnRecords(true)
                .withFluentSetters(false)
                .withDaos(false)
                .withPojos(true)
                .withJooqVersionReference(false);

        Target target = new Target()
                .withPackageName("scrapper.domains.jooq")
                .withDirectory("scrapper/src/main/java/ru/tinkoff");

        Configuration configuration = new Configuration()
                .withGenerator(
                        new Generator()
                                .withDatabase(database)
                                .withGenerate(options)
                                .withTarget(target)
                )
                .withJdbc(new Jdbc()
                        .withDriver("org.postgresql.Driver")
                        .withUrl("jdbc:postgresql://localhost:5432/link_bot")
                        .withUser("postgres")
                        .withPassword("123"));

        GenerationTool.generate(configuration);
    }

}

它是根据以下DB方案生成的

CREATE TABLE chat_link
(
    chat_id BIGINT NOT NULL,
    link_id BIGINT NOT NULL,
    PRIMARY KEY (chat_id, link_id),
    FOREIGN KEY (chat_id) REFERENCES chat (chat_id),
    FOREIGN KEY (link_id) REFERENCES link (link_id)
);

CREATE TABLE chat
(
    chat_id    BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    tg_chat_id BIGINT                           NOT NULL UNIQUE,

    CONSTRAINT PK_chat_chat_id PRIMARY KEY (chat_id)
);

CREATE TABLE link
(
    link_id     BIGINT       NOT NULL GENERATED ALWAYS AS IDENTITY,
    url        varchar(255) NOT NULL UNIQUE,
    last_update TIMESTAMP    NOT NULL,

    CONSTRAINT PK_link_link_id PRIMARY KEY (link_id)
);

但当我尝试使用以下函数时

package ru.tinkoff.scrapper.repository.JOOQ;

import lombok.RequiredArgsConstructor;
import org.jooq.Batch;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.stereotype.Repository;
import ru.tinkoff.scrapper.enyity.LinkEntity;
import ru.tinkoff.scrapper.repository.LinkRepository;
import static scrapper.domains.jooq.tables.Link.LINK;
import static scrapper.domains.jooq.tables.Chat.CHAT;
import static scrapper.domains.jooq.tables.ChatLink.CHAT_LINK;
import scrapper.domains.jooq.tables.records.LinkRecord;

import java.net.URI;
import java.net.URISyntaxException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

import static org.jooq.impl.DSL.count;

@Repository
@RequiredArgsConstructor
public class LinkJOOQRepository implements LinkRepository {
    private final DSLContext create; 
public List<LinkEntity> findAllOrderByDate() {

        var res = create
                .selectFrom(LINK)
                .orderBy(LINK.LAST_UPDATE)
                .fetch();
        
        return res.stream().map(this::fromTableToEntity).collect(Collectors.toList());
    }
}

出现以下错误

2023-04-29T12:22:00.851+03:00 ERROR 21212 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select "LINK"."LINK_ID", "LINK"."URL", "LINK"."LAST_UPDATE" from "LINK"]
    at org.jooq_3.17.12.POSTGRES.debug(Unknown Source) ~[na:na]
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245) ~[spring-jdbc-6.0.3.jar:6.0.3]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-6.0.3.jar:6.0.3]
    at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.translate(JooqExceptionTranslator.java:94) ~[spring-boot-autoconfigure-3.0.1.jar:3.0.1]
    at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:81) ~[spring-boot-autoconfigure-3.0.1.jar:3.0.1]
    at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:55) ~[spring-boot-autoconfigure-3.0.1.jar:3.0.1]
    at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:276) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2837) ~[jooq-3.17.12.jar:na]
    at ru.tinkoff.scrapper.repository.JOOQ.LinkJOOQRepository.findAllOrderByDate(LinkJOOQRepository.java:61) ~[classes/:na]
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752) ~[spring-aop-6.0.3.jar:6.0.3]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703) ~[spring-aop-6.0.3.jar:6.0.3]
    at ru.tinkoff.scrapper.repository.JOOQ.LinkJOOQRepository$$SpringCGLIB$$0.findAllOrderByDate(<generated>) ~[classes/:na]
    at ru.tinkoff.scrapper.service.LinkUpdateService.update(LinkUpdateService.java:43) ~[classes/:na]
    at ru.tinkoff.scrapper.service.LinkUpdaterScheduler.update(LinkUpdaterScheduler.java:21) ~[classes/:na]
    at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:578) ~[na:na]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) ~[spring-context-6.0.3.jar:6.0.3]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-6.0.3.jar:6.0.3]
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:577) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358) ~[na:na]
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:1589) ~[na:na]
Caused by: org.postgresql.util.PSQLException: ERROR: The "LINK" relation does not exist
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql-42.5.1.jar:42.5.1]
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177) ~[postgresql-42.5.1.jar:42.5.1]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4590) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:236) ~[jooq-3.17.12.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:341) ~[jooq-3.17.12.jar:na]
    ... 26 common frames omitted
Java

1条答案

按热度按时间
5lhxktic

5lhxktic1#

jOOQ的LiquibaseDatabase的当前（jOOQ 3.18）实现在从内存中的H2数据库生成代码之前使用该数据库来simulate a migration。在H2中，标识符默认为大写，而在PostgreSQL中，它们默认为小写。
防止这种不匹配的最简单方法是防止在运行时生成带引号的标识符，使用Settings.renderQuotedNames，将其设置为RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED。
由于您使用的是Sping Boot ，因此here's how to inject a custom configuration of the jOOQ DSLContext

import org.jooq.conf.RenderQuotedNames;
import org.jooq.impl.DefaultConfiguration;
import org.springframework.boot.autoconfigure.jooq.*;
import org.springframework.context.annotation.*;
 
@Configuration
public class Config {
    @Bean
    public DefaultConfigurationCustomizer configurationCustomiser() {
        return (DefaultConfiguration c) -> c.settings()
            .withRenderQuotedNames(
                RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED
            );
    }
}
赞(0）分享  回复(0）举报  2023-05-05
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com