首页 > Java > Jooq 和 postgres:纯 sql 中的 pg_trgm 运算符产生错误'运算符不存在”

Jooq 和 postgres:纯 sql 中的 pg_trgm 运算符产生错误'运算符不存在”

王林
发布: 2024-02-22 13:13:06
转载
1025 人浏览过

php小编苹果为您介绍一则Java相关的问题:在使用Jooq和PostgreSQL时,可能会出现“运算符不存在”错误,原因是在纯SQL中使用pg_trgm运算符不被识别。本文将详细解释这一问题的背景和解决方法。

问题内容

我正在使用的技术:java、spring boot、jooq、带有 pg_trgm 扩展的 postgres、r2dbc。

我尝试使用 pg_trgm 运算符在 postgres 上进行简单搜索,但 jooq 抛出错误。

代码示例:

string searchkeyword = "something";
dsl.select(tables.example.id)
          .from(tables.example)
          .where(dsl.condition("{0} <<% {1}", dsl.val(searchkeyword), tables.example.text_field))
登录后复制

或者更简单:

dsl.resultquery("select 'a' <<% 'a';")
登录后复制

产生错误 operator 不存在:字符变化 <<% text

堆栈跟踪:

org.jooq.exception.dataaccessexception: sql [select 'a' <<% 'a';]; operator does not exist: unknown <<% unknown

original stack trace:
        at org.jooq.impl.tools.translate(tools.java:3470)
        at org.jooq.impl.tools.translate(tools.java:3448)
        at org.jooq.impl.tools.translate(tools.java:3432)
        at org.jooq.impl.r2dbc$forwarding.lambda$onerror$0(r2dbc.java:252)      at org.jooq.impl.internal$1.oncomplete(internal.java:497)
        at reactor.core.publisher.strictsubscriber.oncomplete(strictsubscriber.java:123)
        at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)
        at reactor.core.publisher.fluxpeekfuseable$peekfuseablesubscriber.oncomplete(fluxpeekfuseable.java:277)
        at reactor.core.publisher.operators$multisubscriptionsubscriber.oncomplete(operators.java:2060)
        at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:209)
        at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:209)
        at reactor.pool.simpledequepool.mayberecycleanddrain(simpledequepool.java:533)
        at reactor.pool.simpledequepool$queuepoolrecyclerinner.oncomplete(simpledequepool.java:765)
        at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)
        at reactor.core.publisher.operators.complete(operators.java:137)        at reactor.core.publisher.monoempty.subscribe(monoempty.java:46)        at reactor.core.publisher.mono.subscribe(mono.java:4490)
        at reactor.pool.simpledequepool$queuepoolrecyclermono.subscribe(simpledequepool.java:877)       at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:240)      at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:203)         at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)      at reactor.core.publisher.fluxpeek$peeksubscriber.oncomplete(fluxpeek.java:260)         at reactor.core.publisher.operators.complete(operators.java:137)        at reactor.core.publisher.monoempty.subscribe(monoempty.java:46)        at reactor.core.publisher.mono.subscribe(mono.java:4490)        at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:263)      at reactor.core.publisher.monoignorethen.subscribe(monoignorethen.java:51)      at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:240)      at reactor.core.publisher.monoignorethen$thenignoremain.oncomplete(monoignorethen.java:203)         at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.oncomplete(reactorcontexttestexecutionlistener.java:130)      at reactor.core.publisher.monoignoreelements$ignoreelementssubscriber.oncomplete(monoignoreelements.java:89)        at reactor.core.publisher.fluxhandlefuseable$handlefuseablesubscriber.oncomplete(fluxhandlefuseable.java:236)       at reactor.core.publisher.operators$monosubscriber.complete(operators.java:1840)        at reactor.core.publisher.monosupplier.subscribe(monosupplier.java:62)      at reactor.core.publisher.mono.subscribe(mono.java:4490)        at reactor.core.publisher.monoignorethen$thenignoremain.subscribenext(monoignorethen.java:263)      at reactor.core.publisher.monoignorethen.subscribe(monoignorethen.java:51)      at reactor.core.publisher.internalmonooperator.subscribe(internalmonooperator.java:64)      at reactor.core.publisher.monodefer.subscribe(monodefer.java:53)        at reactor.core.publisher.mono.subscribe(mono.java:4490)        at org.jooq.impl.r2dbc$abstractnonblockingsubscription.lambda$cancel0$4(r2dbc.java:663)         at java.base/java.util.concurrent.atomic.atomicreference.updateandget(atomicreference.java:210)         at org.jooq.impl.r2dbc$abstractnonblockingsubscription.cancel0(r2dbc.java:647)      at org.jooq.impl.r2dbc$abstractsubscription.complete(r2dbc.java:213)        at org.jooq.impl.r2dbc$abstractresultsubscriber.complete(r2dbc.java:303)        at org.jooq.impl.r2dbc$forwarding.complete(r2dbc.java:265)      at org.jooq.impl.r2dbc$forwarding.onerror(r2dbc.java:252)       at reactor.core.publisher.strictsubscriber.onerror(strictsubscriber.java:106)       at org.springframework.security.test.context.support.reactorcontexttestexecutionlistener$delegatetestexecutionlistener$securitysubcontext.onerror caused by: io.r2dbc.postgresql.exceptionfactory$postgresqlbadgrammarexception: [42883] operator does not exist: unknown <<% unknown   at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:96)   suppressed: the stacktrace has been enhanced by reactor, refer to additional information below:  assembly trace from producer [reactor.core.publisher.fluxhandlefuseable] :     reactor.core.publisher.flux.handle(flux.java:5913)  io.r2dbc.postgresql.postgresqlresult.map(postgresqlresult.java:107) error has been observed at the following site(s):   *__flux.handle ⇢ at io.r2dbc.postgresql.postgresqlresult.map(postgresqlresult.java:107) original stack trace:       at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:96)       at io.r2dbc.postgresql.exceptionfactory.createexception(exceptionfactory.java:65)       at io.r2dbc.postgresql.exceptionfactory.handleerrorresponse(exceptionfactory.java:132)      at io.r2dbc.postgresql.postgresqlresult.lambda$map$2(postgresqlresult.java:111)         at reactor.core.publisher.fluxhandlefuseable$handlefuseablesubscriber.onnext(fluxhandlefuseable.java:176)       at reactor.core.publisher.fluxwindowpredicate$windowflux.drainregular(fluxwindowpredicate.java:668)         at reactor.core.publisher.fluxwindowpredicate$windowflux.drain(fluxwindowpredicate.java:746)        at reactor.core.publisher.fluxwindowpredicate$windowflux.onnext(fluxwindowpredicate.java:788)       at reactor.core.publisher.fluxwindowpredicate$windowpredicatemain.onnext(fluxwindowpredicate.java:239)      at io.r2dbc.postgresql.util.fluxdiscardoncancel$fluxdiscardoncancelsubscriber.onnext(fluxdiscardoncancel.java:91)
登录后复制

请注意,如果直接在数据库上运行,以下纯 sql 查询将有效:

select id from example where 'something' <<% text_field;
登录后复制

另请注意:这不是类型转换问题。如果我将 searchkeyword 转换为文本,或者将其内联,则会产生类似的错误

另一个注意事项:如果我更改代码以使用 strict_word_similarity 函数而不是运算符,它就可以工作。问题仅出在运营商

解决方法

您可以将绑定变量显式转换为文本:

dsl.condition("{0}::text <<% {1}", ...)
登录后复制

或者内联它而不是绑定它:

DSL.condition("{0} <<% {1}", DSL.inline(searchKeyword), Tables.EXAMPLE.TEXT_FIELD)
登录后复制

以上是Jooq 和 postgres:纯 sql 中的 pg_trgm 运算符产生错误'运算符不存在”的详细内容。更多信息请关注PHP中文网其他相关文章!

来源:stackoverflow.com
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板