No results returned by the Query error in PostgreSQL

Question

I am trying to insert a data into a table. After executing the query i am getting an exception stating

org.postgresql.util.PSQLException: No results were returned by the query.
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:284)

The data is getting inserted successfully, but i have no idea why i am getting this exception ??

score 122 · Accepted Answer · answered Jan 22 '14 at 06:53

122

Use

executeUpdate

instead of

executeQuery

if no data will be returned (i.e. a non-SELECT operation).

answered Jan 22 '14 at 06:53

Paul Draper

78,542
46
206
285

Beauty ! works perfectly.. but y is it like executeUpdate works but executeQuery doesnt work ?? in both casesdata is getting inserted properly. – Ragesh Kr Jan 22 '14 at 07:15
1

@RageshKr, it gets inserted correctly, but the Java database connector is afterwards expecting data, and none comes. So the error happens after the insert. – Paul Draper Dec 26 '14 at 17:09

score 35 · Answer 2 · edited Jul 21 '22 at 03:07

35

Please use @Modifying annotation over the @Query annotation.

@Modifying
@Query(value = "UPDATE Users set coins_balance = coins_balance + :coinsToAddOrRemove where user_id = :user_id", nativeQuery = true)
    int updateCoinsBalance(@Param("user_id") Long userId, @Param("coinsToAddOrRemove") Integer coinsToAddOrRemove);

The same is true for any DML query (i.e. DELETE, UPDATE or INSERT)

edited Jul 21 '22 at 03:07

Ricardo

3,696
5
36
50

answered Sep 21 '19 at 06:04

Dharmender Rawat

645
8
5

Thanks for this, I wanted a solution for a JPQL query! – need_to_know_now Feb 24 '21 at 08:39

score 12 · Answer 3 · answered Feb 06 '20 at 13:40

12

Using @Modifying and @Transaction fixed me

answered Feb 06 '20 at 13:40

Shahid Hussain Abbasi

2,508
16
10

1

Its @Transactional – Aaban Tariq Murtaza Oct 15 '20 at 15:49
@Transactional @Modifying(clearAutomatically = true) @Query(value = Update/Delete/InstertQuery , nativeQuery = true) – Usman Mar 24 '21 at 06:54

score 5 · Answer 4 · answered Sep 07 '20 at 16:39

The problem that brought me to this question was a bit different - I was getting this error when deleting rows using an interface-based Spring JPA Repository. The cause was that my method signature was supposed to return some results:

@Modifying
@Query(value = "DELETE FROM table t WHERE t.some_id IN (:someIds)", nativeQuery = true)
List<Long> deleteBySomeIdIn(@Param("someIds") Collection<Long> someIds);

Changing the return type to void resolved the issue:

@Modifying
@Query(value = "DELETE FROM table t WHERE t.some_id IN (:someIds)", nativeQuery = true)
void deleteBySomeIdIn(@Param("someIds") Collection<Long> someIds);

score 2 · Answer 5 · answered Dec 18 '17 at 02:03

2

If you want last generated id, you can use this code after using executeUpdate() method

 int update = statement.executeUpdate()
 ResultSet rs = statement.getGeneratedKeys();
 if (rs != null && rs.next()) {
  key = rs.getLong(1);
 }

answered Dec 18 '17 at 02:03

Popeye

253
4
13

score 0 · Answer 6 · edited Apr 30 '21 at 20:48

0

I have solved this Problem using addBatch and executeBatch as following:

statement.addBatch("DELETE FROM public.session_event WHERE id = " + entry.getKey());
statement.executeBatch();

edited Apr 30 '21 at 20:48

Tomerikoo

18,379
16
47
61

answered Apr 30 '21 at 16:17

Ahmed Ahmed

57
2

No results returned by the Query error in PostgreSQL

6 Answers6

Linked

Related