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中文网其他相关文章!