将Postgres JSONB查询与Spring Data和bind参数一起使用时失败，并显示InvalidDataAccessApiUsageException [英] Using Postgres JSONB query with Spring Data and bind parameter fails with InvalidDataAccessApiUsageException

问题描述

我目前正在寻找该异常的解决方案

I am currently looking for a solution for the exception

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that position [1] did not exist;

我当前的@Query批注是:

@Query(
    nativeQuery = true, 
    value = "SELECT * FROM thgcop_order_placement WHERE \"order_info\" @> '{\"parentOrderNumber\":\" :param \"}'")

我想position [1] did not exist来自双引号加双引号加单引号.

I guess the position [1] did not exist comes from it being in double quotes plus double quote plus single quote.

我该如何进行这项工作?

How can I make this work?

查询使用的是Postgres JSONB数据类型.列定义为ORDER_INFO JSONB

The query is using Postgres JSONB datatype. The column definition is ORDER_INFO JSONB

以下本地查询在Postgres客户端中可以正常工作:

The following native query works just fine in the Postgres client:

SELECT * FROM thgcop_order_placement
WHERE "order_info" @> '{"parentOrderNumber":"ORD123"}'

推荐答案

除以下内容外，以上所有内容都不适合我

None of the above worked for me except the below,

服务层代码:-

OrderInfo orderInfo = new OrderInfo();
orderInfo.setParentOrderNumber("ORD123");
....
String param = objectMapper.writeValueAsString(orderInfo);
List<Order> list = jpaRepository.getByParentOrderNumber(param);

JpaRepository.java代码:-

@Query(nativeQuery = true, value = "select * from thgcop_order_placement where order_info @> CAST(:condition as jsonb)")
List<Order> getByParentOrderNumber(@Param("condition") String parentOrderNumber);

这就是我达到结果的方式.我希望这对所有热情

This is how I achieve the result. I hope this will be very helpful for all enthusiastic!!

谢谢大家的帮助！

Thank you all for your help !!!

这篇关于将Postgres JSONB查询与Spring Data和bind参数一起使用时失败，并显示InvalidDataAccessApiUsageException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！