CODEWITHSUNDEEP
mysqlhibernatespring-bootspring-data-jpajpql
Abhijeet Behare
Abhijeet Behare

Reputation: 607

Delete in spring data jpa with multiple where clause

I want to delete record from MySQL table.

Query which i have written in the interface which extends JpaRepositoy is. 

@Modifying
@Transactional
@Query(value="DELETE FROM tablename WHERE end_date>=?1 and username=?2 and start_date <=?3)
void deleteByStart_dateAndUsernameAndEnd_date(Date start_date,String username,Date end_date);

I want to pass start_date which is less than or equal to end_date and end_date is greater than or equal to start_date .

I am getting below exception:

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [delete from com.poc.pa.model.tablename where end_date>=?1 and username=?2 and start_date <=?3]; nested exception is java.lang.IllegalStateException: org.hibernate.hql.internal.QueryExecutionRequestException: Not supported for DML operations [delete from com.poc.pa.model.tablename where end_date>=?1 and username=?2 and start_date <=?3]

Any help would be appreciated.

Upvotes: 3

Views: 8840

Answers (1)

Simon Martinelli
Simon Martinelli

Reputation: 36223

I assume the delete statement is a SQL Query. Therefor you have to declare this in the Query annotation. Simply add nativeQuery = true

@Transactional    
@Modifying
@Query(value="DELETE FROM tablename WHERE end_date>=?1 and username=?2 and start_date <=?3, 
    nativeQuery = true)
void deleteByStart_dateAndUsernameAndEnd_date(Date start_date,String username,Date end_date);

Upvotes: 4

Related Questions