Jooq 和 postgres：纯 sql 中的 pg_trgm 运算符产生错误'运算符不存在”-Java-PHP中文网

首页

Java

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

王林

Feb 22, 2024 pm 01:13 PM

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)

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

声明

本文转载于：stackoverflow。如有侵权，请联系admin@php.cn删除

热AI工具

热工具

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序，非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具，帮助Web开发人员更好地理解保护Web应用程序的过程，并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞，难度各不相同。请注意，该软件中

SublimeText3汉化版

中文版，非常好用

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具，用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

SublimeText3 英文版

推荐：为Win版本，支持代码提示！

mPDF

mPDF是一个PHP库，可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件，并处理不同的语言。与原始脚本如HTML2FPDF相比，它的速度较慢，并且在使用Unicode字体时生成的文件较大，但支持CSS样式等，并进行了大量增强。支持几乎所有语言，包括RTL（阿拉伯语和希伯来语）和CJK（中日韩）。支持嵌套的块级元素（如P、DIV），

显示更多

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

问题内容

解决方法

热AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

热门文章

热工具

DVWA

SublimeText3汉化版

螳螂BT

SublimeText3 英文版

mPDF

热门话题