执行查询时出现以下异常
org.hibernate.queryexception:未设置所有命名参数:
[:jsonb]
我的问题是:
SELECT * FROM content c,resource r,organization_resource_relationship
org,json_array_elements(c.attributes->'data') obj
WHERE c.resource_id=r.resource_id and r.account_id='633' and
r.resource_id = org.resource_id and r.delete_status = false
and ((obj->>'userId' in(select cast(resource_id as text) from resource
where resource_id=3181) or obj->>'signatureByCmisUsers'
like '%3181%' or obj->>'visibleToCmisUsers' like '%3181%' or
obj->>'editableByCmisUsers' like '%3181%' or
obj->>'obtainSignatureByCmisUsers'
like '%3181%' or obj->>'documentVisibleToCmisUsers' like '%3181%' ) and
(obj->>'signatureStatus' like 'Pending' or obj->>'signatureStatus'
like 'PENDING' or obj->>'signatureStatus' like 'SIGNED' or
obj->>'signatureStatus' like 'Signed') and
(obj->>'isDeleted'='false')
and ((obj->>'parentFolderId'='nil' or obj->>'parentFolderId' in
(select cast(resource_id as text) from
resource re where re.delete_status=false))) and
((obj->>'userType'='owner' or obj->>'userType'='receiver')))
and ((((obj->>'visibleToRoles' like '%215%') and
((obj->>'visibleToRoles') ::jsonb) ?
(select cast(resource_to_id as text) from resource_relationship where
resource_from_id=3181 and resource_to_id=215))))
and json_typeof(c.attributes->'data')='array' ORDER BY r.updated_on
DESC
如何解决这个问题?我需要使用 ::jsonb
. 但它抛出了一个例外。
1条答案
按热度按时间9lowa7mx1#
使用位置参数
?1
,?2
等等。hibernate在遇到:
,也没有“逃避”,因为这是合法的postgres铸造速记。你也可以用
CAST(obj->>'visibleToRoles' AS jsonb)
,但如果您喜欢速记语法,则不能使用命名参数。实际上,你为什么要投?为什么不带上它呢
->
而不是->>
?顺便说一句,这个查询很好(这是讽刺,看起来很可怕,效率很低)。