如何使用 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 表.

You can find current definition for operator you are interested in by querying pg_catalog.pg_operator table.

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');

操作符系列也一样(我们将从 operator类表 代替)，我们正在寻找 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

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

Then we get strategy used by operator we duplicated:

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)

而且你应该能够使用索引:

And you should be able to use index:

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

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