时间戳

首页 > 解决方案 > Spring boot jpa查询无法更新字段

java - Spring boot jpa查询无法更新字段

问题描述

我在这张表上有一个关于休眠的更新查询

class PackEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String status;
    private String oldStatus;
    @ManyToOne
    @JoinColumn(name = "order_id")
    private OrderEntity order;
    ...
}

在 OrderEntity 上，当我有机器名称时，我与另一个表有另一种关系。

在 JPA 存储库上，我有查询。基本上首先我按机器和状态搜索，然后我想更新旧状态以放置状态字段的当前值并在状态中放置新状态。就是这个：

@Transactional
@Modifying(clearAutomatically = true)
@Query("UPDATE PackEntity p " +
        "SET p.oldStatus= p.status, p.status = ?3 " +
        "WHERE p.id IN " +
        "     ( SELECT p2" +
        "       FROM PackEntity p2" +
        "       JOIN p2.order " +
        "       JOIN p2.order.machine" +
        "       WHERE p2.order.machine.name = ?1 AND p2.status = ?2)")
List<PackEntity > updateAllWithStatusByMachineNameAndStatus(String machineName, String status, String newStatus);

现在我遇到了这个错误 .QueryExecutionRequestException: Not supported for DML operations [UPDATE com.pxprox.entities.PackEntity with root cause ...

标签： javasql-serverspring-boothibernatespring-data-jpa

解决方案

该方法的返回类型是错误的，查询也应该稍作调整。使用以下内容：

@Transactional
@Modifying(clearAutomatically = true)
@Query("UPDATE PackEntity p " +
        "SET p.oldStatus = p.status, p.status = ?3 " +
        "WHERE EXISTS " +
        "     ( SELECT 1" +
        "       FROM PackEntity p2" +
        "       JOIN p2.order o " +
        "       JOIN o.machine m" +
        "       WHERE m.name = ?1 AND p2.status = ?2 AND p2.id = p.id)")
void updateAllWithStatusByMachineNameAndStatus(String machineName, String status, String newStatus);

甚至更好

@Transactional
@Modifying(clearAutomatically = true)
@Query("UPDATE PackEntity p " +
        "SET p.oldStatus = p.status, p.status = ?3 " +
        "WHERE p.status = ?2 AND EXISTS " +
        "     ( SELECT 1" +
        "       FROM p.order o " +
        "       JOIN o.machine m" +
        "       WHERE m.name = ?1)")
void updateAllWithStatusByMachineNameAndStatus(String machineName, String status, String newStatus);

推荐阅读