Spring boot jpa query can't update field

Question

I have an update query on hibernate on this table

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

And on OrderEntity I have there another relationship to another table when I have machine names.

On the JPA repository, I have the query. Basically first I search by machine and status and then I want to update the old status to put the current value of the status field and in status to put the new status. This is it:

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

Now I'm having this error .QueryExecutionRequestException: Not supported for DML operations [UPDATE com.pxprox.entities.PackEntity with root cause ...

The problem may be the return type of the method, see here : https://stackoverflow.com/questions/44022076/jparepository-not-supported-for-dml-operations-delete-query — Arnaud, Aug 16 '21 at 13:53
You are doing an update with a method that expects a `PackEntity` as the result. That isn't going to work. Also your sub select is wrong you are selecting the whole entity whereas you should select `p2.id` — M. Deinum, Aug 16 '21 at 13:56
An example of what? Your method should return `void` or `int` not `List` and, as stated, your subselect is probably also wrong return an id and you don't need the JOINs in there either. — M. Deinum, Aug 16 '21 at 14:16
@M.Deinum I need the joins because of the condition `WHERE p2.order.machine.name ` otherwise it will throw an error. And regarding the subquery, I select `SELECT p2"` which is `List`. My only problem is after I added `p.oldStatus= p.status,` it doesn't work — xarqt, Aug 16 '21 at 14:21
No you don't select, that select is used as the input for the IN clause. It isn't the result of the UPDATE query. Assuming `order` is a field in `PackEntity` hibernate is smart enough to join by itself. — M. Deinum, Aug 16 '21 at 14:22
@M.Deinum ok I will try without the joins again. What about the SET p.oldStatus= p.status, p.status = ?3 ` on an assign the status value to a field and then update it value ? — xarqt, Aug 16 '21 at 14:30
Not sure if that will work. I generally update just the entities instead of a query. — M. Deinum, Aug 16 '21 at 14:32
@M.Deinum so how can I do this, how can I save the current value of the status field to oldStatus and then update the status value? What do you propose, a native query or two queries, any idea, please? — xarqt, Aug 16 '21 at 14:37
As stated I don't know if this works. Fix the query and try it. — M. Deinum, Aug 16 '21 at 14:40
@M.Deinum tried it without the joins it doesn't work. Without `p.oldStatus= p.status` it works and it returns the list of updated packs. So the problem I have is this that you stated that you don't know. — xarqt, Aug 16 '21 at 14:45

score 0 · Answer 1 · answered Aug 16 '21 at 18:24

Why not create a method that does this for you? Initializing the entity and updating everything, changes will be flushed automatically at the end of the transaction. You can have a look at updating entity with spring-data-jpa

It should basically be something like:

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

It will be a bulk update, so I need to make it with query or native query ? can you help please — xarqt, Aug 16 '21 at 18:35

score 0 · Answer 2 · answered Aug 26 '21 at 09:50

The return type of the method is wrong and also the query should be a little adapted. Use the following:

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

or even better

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

Spring boot jpa query can't update field

2 Answers2