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 不存在：字符变化 。
堆栈跟踪：
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)
