spring-data-jpa 如何使用Spring Data JPA查询更新PostgreSQL中的JSONB列

xn1cxnb4  于 2022-11-10  发布在  Spring
关注(0)|答案(2)|浏览(311)

我有以下控制器

@RequestMapping(value = "/update/{tableId}", method = RequestMethod.PUT, produces = MediaType.APPLICATION_JSON_VALUE,
            consumes = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity updateItemQty (@PathVariable Long tableId, @RequestBody AddItemsRequestBody requestBody,
                                              HttpServletRequest request){
        try {
            String addedBy = getUserName(request);
            Float ItemQuantity = requestBody.getItemQuantity();
            LocalDateTime dateTimeAdded = LocalDateTime.now();
            String subId = requestBody.getItemSubId();
            ArrayList<ItemAdditionDetails> updatedAdditionDetails = new ArrayList<>();

        ItemAdditionDetails newItemAdditionDetails = new ItemAdditionDetails();
        newItemAdditionDetails.setIncreamentCount(ItemQuantity);
        newItemAdditionDetails.setAddedDateTime(dateTimeAdded);
        newItemAdditionDetails.setAddedBy(addedBy);

        updatedAdditionDetails.add(newItemAdditionDetails);

// Here i am fetching a JSON-B column that returns an array of json objects from Postgress DB  
     ItemInventoryService.findByItemSubId(subId).getItemsInventoryAdditionDetails().forEach((el) -> {
                updatedAdditionDetails.add(el);
            });

        itemInventoryService.updateItemsAdditionDetails(subId,updatedAdditionDetails);

        return new ResponseEntity("Updated", HttpStatus.CREATED);
    }catch (Exception ex){
        return new ResponseEntity(ex, HttpStatus.INTERNAL_SERVER_ERROR);
    }

}

如果我删除/注解

ItemInventoryService.findByItemSubId(subId).getItemsInventoryAdditionDetails().forEach((el) -> {
                updatedAdditionDetails.add(el);
            });

也就是说,我没有将提取的结果添加到新的updatedAdditionDetails(类型为ArrayList)中,数据库更新成功,否则我会得到以下错误:

错误:列\“itemsinventory_addtion_details\”的类型为jsonb,但表达式的类型为记录\n提示:您将需要重写或转换表达式。\n位置

在我的存储库中,我有以下查询

@Transactional
    @Modifying
    @Query(value = "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId", nativeQuery = true)
    Integer updateItemsAdditionDetails(@Param("subId") String subId, @Param("updatedAdditionDetails") List<ItemAdditionDetails> updatedAdditionDetails);

我已经尝试了如下JPA方式:

@Query(value = "UPDATE ItemsInventory items  SET items.itemsInventoryAdditionDetails = :updatedAdditionDetails WHERE items.itemSubId = :subId")

不幸的是,这也带来了同样的错误。对此的任何帮助都非常感谢。提前感谢。

70gysomp

70gysomp1#

错误原因

PostgreSQL错误如下所示:
错误:列“itemsinventory_addtion_details”的类型为jsonb,但表达式的类型为记录\n提示:您将需要重写或转换表达式。\n位置
导致此错误的原因是List的传递方式如下所示:

UPDATE
    ItemsInventory items
SET
    items.itemsInventoryAdditionDetails = (?, ?, ?, .., ?)
WHERE
    items.itemSubId = ?

默认情况下,使用Hibernate的Spring Data JPA不知道如何处理JSON类型,因此,您需要使用一个自定义的Hibernate类型,如Hibernate Types项目提供的JsonBinaryType

Maven依赖项

首先,您需要添加Hibernate类型依赖项:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

hibernate-types.version Maven属性设置为最新版本后,将从Maven Central下载依赖关系。

显式设置Hibernate类型

您将无法使用Spring @Query注解,因为您无法显式设置Hibernate类型。
因此,您需要使用以下方法添加一个自定义SpringDataJPARepository实现:

public int updateItemsAdditionDetails(
    String subId, 
    List<ItemAdditionDetails> updatedAdditionDetails) {
    return entityManager.createNativeQuery(
        "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId")
    .unwrap(NativeQuery.class)
    .setParameter("updatedAdditionDetails", updatedAdditionDetails, JsonBinaryType.INSTANCE)
    .setParameter("subId", subId)
    .executeUpdate();
}

就是这样!

flvtvl50

flvtvl502#

我发现的另一个选项是在将集合输入到查询之前将其序列化为字符串:

@Transactional
@Modifying
@Query(value = "UPDATE items_inventory  SET itemsinventory_addtion_details = :updatedAdditionDetails WHERE item_subid = :subId", nativeQuery = true)
Integer updateItemsAdditionDetails(
    @Param("subId") String subId,
    @Param("updatedAdditionDetails") String updatedAdditionDetails
                                  /* ^^^^^^ here */
);

default Integer updateItemsAdditionDetails(
    @Param("subId") String subId,
    @Param("updatedAdditionDetails") List<ItemAdditionDetails> updatedAdditionDetails
) {
    String updatedAdditionDetailsStr = new com.fasterxml.jackson.databind
        .ObjectMapper().writeValueAsString(updatedAdditionDetails);

    return updateItemsAdditionDetails(subId, updatedAdditionDetailsStr);
}

Postgres似乎隐式地将其转换为jsonb没有问题。

相关问题