代码之家 › 专栏 › 技术社区 › Vishal Gajera

使用带有Spring数据和bind参数的Postgres JSONB查询失败,出现InvalidDataAccessApiUsageException

jsonb spring-data-jpa hibernate postgresql spring

Vishal Gajera · 技术社区 · 7 年前

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

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 来自双引号+双引号+单引号。

我该怎么做?

查询正在使用Postgres JSONB 数据类型。列定义为 ORDER_INFO JSONB

以下本机查询在Postgres客户端中运行良好:

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

4 回复 | 直到 7 年前

Vishal Gajera 7 年前

除了以下几点,以上都不适用于我,

服务层代码:-

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

JPA假设。java代码:-

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

这就是我如何达到这个结果的。我希望这对大家都很有帮助 热情的 !!

谢谢大家的帮助!!!

Jens Schauder 7 年前

TL;博士 :首先使用绑定参数和普通JDBC。然后转到Spring数据,可能会回到自定义实现。

你们在这里面临着许多层面的问题。

现在让我们从忽略弹簧数据开始。您展示的语句与您尝试使用Spring数据构建的语句非常不同,因为它不包含绑定变量。因此
```
SELECT * FROM thgcop_order_placement WHERE "order_info" @> '{"parentOrderNumber":"ORD123"}'
```
我们应该将其与
```
SELECT * FROM thgcop_order_placement WHERE "order_info" @> '{"parentOrderNumber": ? }'
```
请注意,我们正在丢失引号,因为它们表示文字字符串,但我们提供的不是文字字符串,而是绑定参数。
我没有发现任何迹象表明可以在JSON表达式的部分中使用绑定参数。因此,我们需要使用以下语句来代替上述语句:
```
SELECT * FROM thgcop_order_placement WHERE "order_info" @> ?
```
当然,bind参数应该包含完整的JSON表达式
不幸的是,这似乎也不起作用,因为现在Postgres考虑了bind参数a VARCHAR 而不是JSON表达式。看见 https://blog.2ndquadrant.com/processing-json/ . 我认为正确的版本应该是
```
SELECT * FROM thgcop_order_placement WHERE "order_info" @> ?::json
```
但我也没法让它发挥作用。
在任何情况下,都需要将参数转换为JSON结构。通常我建议使用SpEL表达式。但它不会起作用,因为Spring数据阻塞了SpEL表达式中所需的花括号,并将其视为SpEL表达式的结束。

如果您使用简单的JDBC连接或 JdbcTemplate 你可以开始思考 @Query 注释。

@Query(
    value= "SELECT * FROM thgcop_order_placement WHERE \"order_info @> :name::json",
    nativeQuery = true)

这可能会引发更多问题,因为Spring数据会考虑 ::json 参数名称的一部分。如果是这种情况,您将不得不依靠自定义实现。

I ran a couple of experiments, which you can look at and play with here.

Amr Alaa 7 年前

尝试按以下方式绑定参数

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

Monis 5 年前

我也有一段时间被同一个问题困住了。springboot在解析字符串格式的查询时似乎出错了。然而,这是我找到的解决方案,它将直接作为存储库中的本机查询工作:

自从 order_info 属于类型 jsonb ,可以将正在搜索的值转换为jsonb值。
要搜索的值: {"parentOrderNumber":"ORD123"}
让我们转义要由java解析的整个字符串。
String searchString = "{\"parentOrderNumber\":\"ORD123\"}"
现在,让我们以spring能够理解的方式键入postgres查询。

@Query(
value = "SELECT * from thgcop_order_placement where ((?1\\:\\:jsonb) <@ (order_info\\:\\:jsonb))",
nativeQuery=true
)
List<Order> getByParentOrderNumber(String searchString);

哪里

弹簧将更换 ?1 价值为 searchString ,如上所述。
:: 是类型转换运算符,我们使用该运算符显式类型转换传递的参数( 搜索字符串 )进入 jsonb公司 . 因此 {\"parentOrderNumber\":\"ORD123\"} jsonb公司 在尝试搜索之前。
还有列的值 订单信息 显式类型转换为 jsonb公司 .
现在,当两个项目(要搜索的值和列)是相同的数据类型时,我们可以使用 <@ 运算符检查搜索字符串是否包含在列值中。

在服务级别,我们只需做到这一点:

String orderNumber = "-- some order value e.g. ORD123 --"
String searchString = "{\"parentOrderNumber\":\"" + orderNumber + "\"}"
List<Order> list = jpaRepository.getByParentOrderNumber(searchString);

有关Postgres JSON运算符的更多详细信息,请参阅以下官方文档: https://www.postgresql.org/docs/9.5/functions-json.html