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

Question

我有以下 Controller

@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 类型)中，数据库更新成功
否则我会收到以下错误:

ERROR: column \"itemsinventory_addtion_details\" is of type jsonb but expression is of type record\n Hint: You will need to rewrite or cast the expression.\n Position

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

@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")

不幸的是，这出现了同样的错误。
非常感谢您对此的任何帮助。
提前致谢。

Answer 1

错误原因
PostgreSQL 错误如下所示:

ERROR: column "itemsinventory_addtion_details" is of type jsonb but expression is of type record\n Hint: You will need to rewrite or cast the expression.\n Position

这个错误是因为 List是这样传递的:

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

默认情况下，使用 Hibernate 的 Spring Data JPA 不知道如何处理 JSON 类型。因此，您需要使用自定义的 Hibernate Type，例如 JsonBinaryType 由 Hibernate Types 提供项目。
Maven 依赖
首先，您需要添加 Hibernate Types 依赖项:

<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 类型。
所以，你需要添加一个 custom Spring Data JPA Repository implementation使用以下方法:

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();
}

就是这样!