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?

score 12 · Answer 1 · answered Dec 13 '18 at 09:53

12

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

answered Dec 13 '18 at 09:53

Musaddique S

1,539
2
15
36

Thanks for the answer but it doesn't work on my side. – Eugene Dec 16 '18 at 12:38
what is your table name in the database? – Musaddique S Jan 14 '19 at 11:34
Thank you, `@Modifying`, `@Transactional` on method solved my problem – J.J. Beam Jan 06 '22 at 03:55

score 4 · Answer 2 · answered Feb 02 '21 at 06:58

4

@Repository
@Transactional
interface OrderRepository: JpaRepository<Order, OrderIdentity>{

   @Query("SELECT * FROM orders WHERE id=:id",nativeQuery = true)
   fun findBy(@Param("id") id: String): List<OrderEvent>

   @Modifying
   @Query("DELETE FROM orders WHERE id=:id", nativeQuery = true)
   fun deleteFor(@Param("id") id: String)

}

By using @Modifying on method and @Transactional on Repository error will be resolved.

answered Feb 02 '21 at 06:58

Lovish Pahwa

61
1

This is the correct answer: ```@Modifying``` on the method and ```@Transactional``` on the Repo. Do not put both on anywhere. – Magno C Jun 16 '23 at 15:54

score 2 · Answer 3 · answered Dec 11 '18 at 17:12

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

answered Dec 11 '18 at 17:12

Andres Rincon

379
2
6

Thanks for the answer, but the error is still there based on your advice. – Eugene Dec 11 '18 at 17:20
you should combine '@Transactional' on the interface level and '@Modifying' on the method level then it should work – Ahmed Hosny Jan 26 '23 at 08:23

score 0 · Answer 4 · answered Dec 12 '18 at 07:34

Not answer my question directly, but found a workaround to remove record based on other attribute but not ID.
According to answer from this thread,

Derivation of delete queries using given method name is supported starting with version 1.6.0.RC1 of Spring Data JPA. The keywords remove and delete are supported. As return value one can choose between the number or a list of removed entities.

Long removeByLastname(String lastname);

List deleteByLastname(String lastname);

I can write

  @Transactional
  void deleteByAlias(String alias);

at UserRepository to achieve the goal.

I won't accept this answer and open for any further contribution.

score 0 · Answer 5 · answered Sep 07 '21 at 15:36

0

Check the param you are passing is not null. it worked for me.

answered Sep 07 '21 at 15:36

Pravin J.

11
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). – Ivan Barayev Sep 07 '21 at 22:52
This answer actually helped me. I wasn't using Spring, though. Calling `query.setParameter("column", variable)` with `variable` `null` can throw an error with the same message. – Matruskan May 20 '23 at 23:49

Could not extract ResultSet when performing customized native query

5 Answers5