英文:
Issue while implementing json_exists using JPA Native query
问题
我的查询如下所示：
@Query(value="SELECT * FROM Retention_Record retention where retention.entity=:entity and retention.category=:category and retention.record_type=:recordType and json_exists(retention.reference_data_id, '$?(@==$referenceDataId)' passing :referenceDataId as \"referenceDataId\")" , nativeQuery=true)
当执行此查询时，出现以下错误：
2020-07-23 16:12:11 [eventsTaskExecutor-5] [] WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 29902, SQLState: 9999
2020-07-23 16:12:11 [eventsTaskExecutor-5] [] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-29902: 在执行 ODCIIndexStart() 例程时出错
ORA-20000: Oracle Text 错误：
DRG-50900: 文本查询解析器在第 1 行，第 10 列出错
DRG-50917: 在文本查询字符串末尾的转义
有人能帮忙吗？
英文:
My Query looks like below,
@Query(value="SELECT * FROM Retention_Record retention where retention.entity=:entity and retention.category=:category and retention.record_type=:recordType and json_exists(retention.reference_data_id, '$?(@==$referenceDataId)' passing :referenceDataId as \"referenceDataId\")" , nativeQuery=true)
When i am getting below error while execution of this query
2020-07-23 16:12:11 [eventsTaskExecutor-5] [] WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 29902, SQLState: 99
999
2020-07-23 16:12:11 [eventsTaskExecutor-5] [] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-29902: error in executing
ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50900: text query parser error on line 1, column 10
DRG-50917: escape on at end of text query string
Can anyone please help.
答案1
得分: 0
我修复了这个问题。问题出在我存储 JSON 对象在数据库中的方式上。我将值存储在数据库中的方式是 ["50000021","50000022"]，事实上这是一个有效的 JSON，然后我试图执行查询以获取结果，但结果却导致错误。当我以这种格式存储数据时，{ "ReferenceDataId" : ["50000021","50000022"] }，下面的查询成功执行。
@Query(value = "SELECT * FROM Retention_Record retention where retention.entity=:entity and retention.category=:category and retention.record_type=:recordType and json_exists(retention.reference_data_id, '$.ReferenceDataId?(@==$referenceDataId)' passing :referenceDataId as \"referenceDataId\")", nativeQuery = true)
英文:
I fixed the issue. Problem was with the way i was storing the Json Object in database. I was storing the value in db as ["50000021","50000022"] which in fact is a valid json, and was trying to execute my query to provide result, which was resulting in error. When i stored data in this format, { "ReferenceDataId" : ["50000021","50000022"] } below query successfully executed. – Ritesh 1 min ago Edit Delete
@Query(value = "SELECT * FROM Retention_Record retention where retention.entity=:entity and retention.category=:category and retention.record_type=:recordType and json_exists(retention.reference_data_id, '$.ReferenceDataId?(@==$referenceDataId)' passing :referenceDataId as \"referenceDataId\")", nativeQuery = true)
专注分享java语言的经验与见解，让所有开发者获益！
评论