JpaRepository Not supported for DML operations [delete query]

📝 Title: JpaRepository Not supported for DML operations [delete query]

👋 Hey there, tech enthusiasts! Have you ever encountered an exception when trying to delete objects using a delete query in an interface that extends JpaRepository? 🤔 If so, I'm here to help you understand the issue and find a solution!

❓The Problem: The user had written a query to delete objects in an interface extending JpaRepository, but when executing the query, an exception was thrown. Let's take a look at the example query provided:

public interface LimitRepository extends JpaRepository<CLimit, Long> {

  @Query("delete from CLimit l where l.trader.id =:#{#trader.id}")
  void deleteLimitsByTrader(@Param("trader") CTrader trader);

}

And here's the exception thrown:

org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [delete from com.query.domain.CLimit l where l.trader.id =:__$synthetic$__1]
...

🤔 The Explanation: The exception message indicates that the usage of a delete query with JpaRepository is not supported for Data Manipulation Language (DML) operations, such as delete or update. JpaRepository is designed to perform CRUD (Create, Read, Update, Delete) operations using the default methods provided by the interface.

⚙️ The Solution: To overcome this issue, we can make use of the @Modifying annotation in combination with the @Query annotation. The @Modifying annotation indicates that the query will perform a DML operation and modify data in the database.

Here's how we can modify the code to resolve the issue:

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

}

By adding the @Modifying annotation before the @Query annotation, we inform Spring Data JPA that the query is expected to perform a modification operation.

🌟 Conclusion: Now that you understand why the exception was occurring and how to fix it, you can confidently delete objects using a delete query in your interface extending JpaRepository.

Feel free to reach out if you have any questions or need further assistance. Happy coding! 💻

📢 Call-to-Action: Have you ever encountered any exceptions or issues while working with JpaRepository? Share your experiences and solutions in the comments below! Let's learn from each other and make our coding journey smoother. 🚀