Hibernate - How to do @Query update with reference to @JoinColumn attribute

Question

@Entity
public class TransactionInfo {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @OneToOne
    @JoinColumn(name="txnRequestId")
    private TransactionRequest txnRequest;

    ..
}

I would like to do a bulk update on this table using the txnRequestId id field (which is mapped with id field of TransactionRequest table)

With my limited knowledge on HQL so far, I tried this query with tr.txnRequest.id field to compare id of TransactionRequest table -

@Repository
public interface TransactionInfoRepository extends JpaRepository<TransactionInfo, Long>, JpaSpecificationExecutor<TransactionInfo> {
    

@Transactional
@Modifying
@Query("UPDATE TransactionInfo as tr SET status=?2 WHERE tr.status=?1 and tr.txnRequest.id in (?3)")
int updateStatusToRequestedByTxnReqId(Enum.TransactionStatus oldStatus, Enum.TransactionStatus newStatus, List<Long> trIds);

Exception -

Unexpected errororg.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.project.model.TransactionInfo as tr SET status=?2 WHERE tr.status=?1 and tr.txnRequest.id in (?3)]; nested exception is java.lang.IllegalStateException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for 
DML operations [UPDATE com.project.model.TransactionInfo as tr SET status=?2 WHERE tr.status=?1 and tr.txnRequest.id in (?3)]
org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.project.model.TransactionInfo as tr SET status=?2 WHERE tr.stat
us=?1 and tr.txnRequest.id in (?3)]; nested exception is java.lang.IllegalStateException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.project.model.TransactionInfo as 
tr SET status=?2 WHERE tr.status=?1 and tr.txnRequest.id in (?3)]

I also tried with tr.txnRequestId but got -

 org.hibernate.QueryException: could not resolve property: txnRequestId of: com.project.model.TransactionInfo

I would appreciate a documentation link or link to this chapter in Hibernate, as I could not reach upto studying these things yet and hence do not know what to search for.

score 0 · Answer 1 · answered Jul 09 '20 at 19:15

0

You can use Native query then you can use txnRequestId

@Query("UPDATE TransactionInfo SET status=?2 WHERE status=?1 and txnRequestId in (?3)", nativeQuery = true)
int updateStatusToRequestedByTxnReqId(Enum.TransactionStatus oldStatus, Enum.TransactionStatus newStatus, List<Long> trIds);

Assuming your database table name is TransactionInfo

answered Jul 09 '20 at 19:15

Eklavya

17,618
4
28
57

Well, if I do a native query, then auditing of the table doesn't happen automatically. – Sandeepan Nath Jul 10 '20 at 03:42
For JPQL you need to do subquery with join. Are you using auditor info or only auditing time ? – Eklavya Jul 10 '20 at 03:48

Hibernate - How to do @Query update with reference to @JoinColumn attribute

1 Answers1