Understanding and Resolving DML Operation Exceptions in JpaRepository: The Role of @Modifying Annotation

Problem Background

In Spring Data JPA, developers often use JpaRepository to simplify data access layers. However, when attempting to execute custom delete or update queries, they may encounter a "Not supported for DML operations" exception. This article bases on a specific code example to deeply analyze the root cause of this issue and provide solutions.

Exception Analysis

The exception stack trace indicates that the error occurs during Hibernate's query translation phase, showing that the query is not supported for DML operations. Spring Data JPA defaults to treating methods with @Query annotation as read-only queries, even if the query statement is DELETE or UPDATE. This is because, in the JPA specification, EntityManager's query methods are typically used for data retrieval, not modification. An example of erroneous code is as follows:

public interface LimitRepository extends JpaRepository<CLimit, Long> {

    @Query("delete from CLimit l where l.trader.id =:#{#trader.id}")

    void deleteLimitsByTrader(@Param("trader") CTrader trader);

}

When executing this method, Spring Data JPA attempts to call getSingleResult(), but DML queries do not support this operation, thus throwing an exception.

Solution: Using the @Modifying Annotation

To execute modification operations, the @Modifying annotation must be added on top of the @Query annotation. This annotation informs Spring Data JPA that the method is a modifying query, adjusting the execution strategy to avoid calling read-only query methods. Additionally, the method's return type must be void or int/Integer to indicate no result or the number of affected rows. The corrected code is as follows:

public interface LimitRepository extends JpaRepository<CLimit, Long> {

    @Modifying

    @Query("delete from CLimit l where l.trader.id =:#{#trader.id}")

    void deleteLimitsByTrader(@Param("trader") CTrader trader);

}

With @Modifying added, Spring Data JPA uses the executeUpdate() method to execute the query, properly handling DML operations.

Additional Considerations

Beyond the @Modifying annotation, transaction management should be noted. Modification operations typically need to be executed within a transactional context to ensure data consistency. Spring Data JPA automatically opens transactions for @Modifying methods, but developers can also customize transaction attributes using the @Transactional annotation, such as isolation levels and propagation behaviors. Furthermore, ensure proper query parameter binding to avoid SQL injection risks.

Conclusion

By adding the @Modifying annotation and ensuring correct return types, DML operations in JpaRepository can be executed smoothly. This mechanism reflects Spring Data JPA's extension of the JPA specification, offering more flexible data access methods. Developers should carefully read documentation and follow best practices when using custom queries to avoid common errors.