Not supported for DML operations with JPA update query

This has been asked once before but the solution did not solve the issue. I am creating a JUnit test:

 @Test
    @Transactional
    @Modifying
    public void updateMaterialInventory() throws Exception{

        // Initialize the database
        materialRepository.saveAndFlush(material);

        long id =  material.getId();
        materialRepository.updateMaterialInventory(id,UPDATED_INVENTORY_COUNT);

        assertEquals(material.getInventory_count(), UPDATED_INVENTORY_COUNT, 0);
    }

The query the above test is calling is:

 @Query("UPDATE Material m SET m.inventory_count = ?2 WHERE m.id = ?1")
    void updateMaterialInventory(Long id,int newInventoryAmount);

Error:

Caused by: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [UPDATE com.htd.domain.Material m SET m.inventory_count = ?2 WHERE m.id = ?1]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.errorIfDML(QueryTranslatorImpl.java:318)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:369)
	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)
	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
	at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
	at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
	... 55 more

The @Modifying annotation must be placed on the updateMaterialInventory method, along to the @Query annotation, to let Spring-data know that the query is not a query used to select values, but to update values.

As far as I understand, you should not use @Transactional annotation in the repository class. Find the below answer.

> Service Impl class

import org.springframework.transaction.annotation.Transactional;
...
@Test
@Transactional
public void updateMaterialInventory() throws Exception{

    // Initialize the database
    materialRepository.saveAndFlush(material);

    long id =  material.getId();
    materialRepository.updateMaterialInventory(id,UPDATED_INVENTORY_COUNT);

    assertEquals(material.getInventory_count(), UPDATED_INVENTORY_COUNT, 0);
}

> Repository class

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
...

  @Modifying
  @Query("UPDATE Material m SET m.inventory_count = ?2 WHERE m.id = ?1")
  void updateMaterialInventory(Long id,int newInventoryAmount);

make sure to use the correct imports. hope this is helpful.

@Transactional
@Modifying
@Query(DELETE_INVOICE_BY_NUMBER)
public void deleteInvoiceByNumber(@Param("number") int number);

This worked for me, But without @Transactional in the repository class it give an error.

When you added the @Modifying and it's still not working then make sure your query is of type void, so nothing is returned.

Hibernate in its internals tries then to fetch the date which causes then that exception (not the update statement itselfs).