CODEWITHSUNDEEP
javahibernatespring-mvcjpahql
Mike3355
Mike3355

Reputation: 12081

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

Upvotes: 65

Views: 61895

Answers (4)

thomasso
thomasso

Reputation: 290

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

Upvotes: 2

Champika Wijesundara
Champika Wijesundara

Reputation: 49

@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.

Upvotes: 3

tk_
tk_

Reputation: 17318

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.

Upvotes: 5

JB Nizet
JB Nizet

Reputation: 691635

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.

Upvotes: 129

Related Questions