Could not extract ResultSet when performing customized native query

Question

I am using Spring Data JPA and I want to encapsulate a method which performs specific SQL. I do it in the following matter:

@Component
public interface UserRepository extends CrudRepository<User, String> {
  @Query(
      value = "delete from User u where u.alias = :alias",
      nativeQuery = true
      )
  void deleteUserByAlias(@Param("alias") String alias);
}

However, I got the following message as the result:

{
  "timestamp": "2018-12-11T15:54:54.627+0000",
  "status": 500,
  "error": "Internal Server Error",
  "message": "could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet",
  "path": "/user/delete"
}

So where is the problem?

Answer 1

If your method is already Transactional , then please use transactional on repository method also

@Component
    public interface UserRepository extends CrudRepository<User, String> {

      @Query(
          value = "delete from User u where u.alias = :alias",
          nativeQuery = true
          )
      @Modifying
      @Transactional
      void deleteUserByAlias(@Param("alias") String alias);
    }

Answer 2

your class should be like this:

@Component
public interface UserRepository extends CrudRepository<User, String> {

  @Query(
      value = "delete from User u where u.alias = :alias",
      nativeQuery = true
      )
  @Modifying
  void deleteUserByAlias(@Param("alias") String alias);
}

As you can see I am using @Modifying, for more information take a look to this https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.modifying-queries