Spring JpaRepository如何转义问号(?)字符 [英] How to escape question mark (?) character with Spring JpaRepository

问题描述

Postgres定义其他jsonb运算符，例如?|

Postgres defines additional jsonb Operators such as ?|.

但是，使用Spring JpaRepository查询构建器，询问字符始终被视为参数，而且我无法弄清楚如何对其进行转义(除了单引号字符串内，但是查询无效).

However, using Spring JpaRepository query builder, interrogation character is always considered as a parameter, and I can't figure how to escape it (except inside a single quote string, but then the query is invalid).

示例:

@Query(value = "SELECT * FROM public.user u WHERE u.authorities ?| array['ROLE_1', 'ROLE_2']", nativeQuery = true)

错误:

java.lang.IllegalArgumentException: Unable to resolve given parameter name [1] to QueryParameter reference
    at org.hibernate.query.internal.QueryParameterBindingsImpl.resolveQueryParameter(QueryParameterBindingsImpl.java:520)
    at org.hibernate.query.internal.QueryParameterBindingsImpl.getQueryParameterListBinding(QueryParameterBindingsImpl.java:498)
    at org.hibernate.query.internal.AbstractProducedQuery.setParameterList(AbstractProducedQuery.java:560)

是否有一种方法可以逃脱它，或者可以使用其他包含?字符的postgres本机运算符的解决方案.

Is there a way to escape it, or a different solution to be able to use theses postgres native operators containing ? character.

试图用?? |对其进行转义.或\?|目前无法正常工作.

Trying to escape it with ??| or \?| does not work currently.

注意:我也尝试使用自定义的方言功能，但是它以相同的问题结束.

Note: I also tried to use a custom dialect function, but it ends with the same issue.

图书馆:

• 休眠5.2.16
• hibernate-jpa 2.1
• spring-data-jpa 2.0.6.RELEASE
• PostgreSQL 42.2.2

感谢您的回复！

推荐答案

如果无法转义?，则可以创建具有不同名称的重复运算符.

In case escaping ? is not possible, you can create duplicate operator with different name.

在Postgres中创建运算符的语法:

Syntax for creating operators in Postgres:

CREATE OPERATOR name (
    PROCEDURE = function_name
    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
    [, RESTRICT = res_proc ] [, JOIN = join_proc ]
    [, HASHES ] [, MERGES ]
)

如果jsonb中使用的是?|，它将是:

In case of ?| used in jsonb it will be:

CREATE OPERATOR ^|(
  PROCEDURE = jsonb_exists_any,
  LEFTARG = jsonb,
  RIGHTARG = _text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

我以^|为例，为备用名称.可以是此列表中的任何序列:+ - * / < > = ~ ! @ # % ^ & |?`.

I have used ^| as an example, alternative name. It can be any sequence from this list: + - * / < > = ~ ! @ # % ^ & | ?`.

通过查询 pg_catalog.pg_operator 表.

SELECT oid, *
  FROM pg_catalog.pg_operator
 WHERE oprname = '?|'
   AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');

您还可以使用pgAdmin之类的GUI工具并浏览pg_catalog以准备好重用SQL定义.

You can also use GUI tool like pgAdmin and browse pg_catalog to get SQL definition ready for reuse.

如果要为此新"运算符使用索引，则需要创建新的运算符类和(可选)家族.在我们的情况下，我们都需要，因为我们不能将其添加到现有系列中，因为默认运算符已经在使用

If you want to use index for this "new" operator, you will require to create new operator class and optionally family. In our case, we need both, since we can't add it to existing family, because default operator is already taking strategy slot.

与操作员一样，建议使用pgAdmin之类的GUI工具浏览操作员类，然后将其复制并粘贴.

Just like with operators, it is recommended to use GUI tool like pgAdmin to browse operator classes and just copy&paste it.

首先，我们采用与以下项重复的运算符的OID:

First, we take OID of operator we made duplicate of:

SELECT oid, *
  FROM pg_catalog.pg_operator
 WHERE oprname = '?|'
   AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');

与操作员家庭相同(我们将从操作员中获取类表)，我们正在寻找gin类，因为这是支持?|的类.之所以使用opcdefault，是因为存在不支持该运算符的可选类jsonb_path_ops:

Same thing for operator family (we will get it from operator class table instead), we are looking for gin class as this is the one that supports ?|. opcdefault is used, because there is optional class jsonb_path_ops that does not support this operator:

SELECT opcfamily
  FROM pg_opclass
 WHERE opcintype = (SELECT oid FROM pg_type WHERE typname = 'jsonb')
   AND opcmethod = (SELECT oid FROM pg_am WHERE amname = 'gin')
   AND opcdefault

然后我们得到运营商使用的策略复制:

SELECT amopstrategy,
       (SELECT typname FROM pg_type WHERE oid = amoplefttype) AS left_t, 
       (SELECT typname FROM pg_type WHERE oid = amoprighttype) AS right_t,*
FROM pg_amop
WHERE amopfamily = 4036 --family oid
  AND amopopr = 3248 --operator oid

然后类使用的功能:

SELECT amprocnum, amproc::text, pg_get_function_identity_arguments(amproc::oid) AS args,
      (SELECT typname FROM pg_type WHERE oid = amproclefttype) AS left_t,
      (SELECT typname FROM pg_type WHERE oid = amprocrighttype) AS right_t,*
FROM pg_amproc
WHERE amprocfamily = 4036 --op family

这使我们进入了运算符类.如果尚不存在，它将创建操作员系列.

This brings us to this operator class. It will create operator family if it does not exists already.

CREATE OPERATOR CLASS jsonb_ops_custom
   FOR TYPE jsonb USING gin AS
   OPERATOR 10  ^|(jsonb, _text),
   FUNCTION 1  gin_compare_jsonb(text, text),
   FUNCTION 2  gin_extract_jsonb(jsonb, internal, internal),
   FUNCTION 3  gin_extract_jsonb_query(jsonb, internal, smallint, internal, internal, internal, internal),
   FUNCTION 4  gin_consistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal, internal),
   FUNCTION 6  gin_triconsistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal);

现在，您只需要使用创建的操作员名称创建索引，例如:

Now you just need to create index using operator name that was created, something like:

CREATE INDEX ON jsonb_table USING gin(jsonb_column jsonb_ops_custom)

您应该能够使用索引:

SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT * FROM jsonb_table WHERE jsonb_column ^| array['b', 'c'];

这篇关于Spring JpaRepository如何转义问号(?)字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！