Spring boot jpa查询无法更新字段

Question

我在这个表上有一个关于hibernate的更新查询

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

现在我有这个错误。QueryExfigtionRequestException：不支持DML操作[UPDATEcom.pxprox.entities.PackEntity与根原因...

Answer 1

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

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

Answer 2

为什么不创建一个方法来为你做这件事呢？初始化实体并更新所有内容，更改将在事务结束时自动刷新。你可以看看用spill-data-jpa更新实体

基本上应该是这样的：

@Autowired
private PackEntityRepository packEntityRepository;

public void updatePackEntity(PackEntity newPE) {
    PackEntity packEntity = packEntityRepository.findById(newPE.getId());
    packEntity.setOldStatus = packEntity.getStatus();
    packEntity.setStatus = newPE.getStatus();
    packEntityRepository.save(packEntity);
}