Executing an update/delete query in the JQPL query

Question

I have the insert query in JPQL like this:

@Modifying
    @Query(value = "insert into Product_Category (product_id, category_id , description , numberOfProduct, image, price ) values (:product_id, :category_id, :description, :numberOfProduct, :image, :price)", nativeQuery = true)
    void insertProduct(@Param("product_id") int product_id, @Param("category_id") int category_id,
            @Param("description") String description, @Param("numberOfProduct") int numberOfProduct,
            @Param("image") String image, @Param("price") int price);

I do not understend why this not work. I have this error:

javax.persistence.TransactionRequiredException: Executing an update/delete query

Answer 1

You need to wrap your statements with @Transactional. To be JPA compliant you should use javax.transaction.Transactional, although in some version combination of spring-data if javax.transaction.Transactional causes issues you can try using org.springframework.transaction.annotation.Transactional.

Keep in mind that in JPA it's all about entity state transitions. When you modify the entities hiberante updates the Persistence Context and flushes it periodically. The order in which this happens is:

• OrphanRemovalAction
• AbstractEntityInsertAction
• EntityUpdateAction
• QueuedOperationCollectionAction
• CollectionRemoveAction
• CollectionUpdateAction
• CollectionRecreateAction
• EntityDeleteAction

So naturally inserts and updates are before deletes. When you have code which does: delete + insert the insert will actually be performed before the delete. In such cases the correct way is the fetch and update.

Answer 2

you must open an session to interrogates with the BD

import org.springframework.transaction.annotation.Transactional.

    @Transactional //try to add this annotation 
    @Modifying
    @Query(value = "insert into Product_Category (product_id, category_id , description , numberOfProduct, image, price ) values (:product_id, :category_id, :description, :numberOfProduct, :image, :price)", nativeQuery = true)
    void insertProduct(@Param("product_id") int product_id, @Param("category_id") int category_id,
            @Param("description") String description, @Param("numberOfProduct") int numberOfProduct,
            @Param("image") String image, @Param("price") int price);