java.sql.SQLException: Can not issue data manipulation statements with executeQuery()

Recently while trying to delete specific rows from a table using JPA query and Native query, I have got java.sql.SQLException. The errors state that :

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

Although the error messages is pretty much self explanatory. Let me show you the repository class and share the used tech stack then it would give you the right context.

Tech Stack:

- Spring Boot 1.5.7.RELEASE

- Maven 3.3.9

- spring-data-rest-webmvc

- spring-data-rest-core

- spring-boot-starter-data-jpa

- HikariCP

Note: spring-boot-starter-data-jpa contains "tomcat-jdbc" as it's internal dependency. Wondering how am I using HikariCP then . See [1].

Repository Class: 

package com.anshulsblog.service.employee.datalayer.repository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;
import org.springframework.transaction.annotation.Transactional;

import com.anshulsblog.service.employee.entity.Employee;

@RepositoryRestResource(path="employee")
@Transactional
public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

 @Transactional( readOnly = true )
        public List findByEmpId(String empID);

 @Query( value = "DELETE from Employee e where e.empId = :id " )
 public void deleteById(@Param("id") String id);
}

Now you see my Repository class and the error, it's evident that internally EntityManager is using executeQuery() method to execute the SQL statements. That's why it's working for SELECT statement but not for DELETE statement. I have also tried to INSERT a dummy data using native query just to check but it has given me the same error.

@Query(nativeQuery = true, value = "INSERT INTO `employee` VALUES(1,"Anshul Agrawal","India")" )
public void tryInsert();

We all know that executeQuery() method is used to execute SQL statements which retrieves some data from database whereas executeUpdate() and execute() method is used to execute SQL statements which update or modify the database. But here we are not manually executing these methods therefore I thought there must be some out of box solution provided by spring-data module as a convention.

Solution

Just add the @Modifying annotation to the repository method then one could also execute DML statements like DELETE, INSERT or UPDATE. This annotation would trigger the query annotated to the method as updating query instead of a selecting one. We have other solution also like one could provide custom implementation for their Spring Data repositories. So now our methods becomes:

import org.springframework.data.jpa.repository.Modifying;
 
@Modifying
@Query( value = "DELETE from Employee e where e.empId = :id " )
public void deleteById(@Param("id") String id);

Appendix 

[1] - Excluded tomcat-jdbc from spring-boot-starter-data-jpa, now by default Spring Boot will use HikariCP .

Happy Learning!! :-)