No results returned by the Query error in PostgreSQL
Solution 1
Use
executeUpdate
instead of
executeQuery
if no data will be returned (i.e. a non-SELECT
operation).
Solution 2
Please use @Modifying annotion over the @Query annotion.
@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)
Solution 3
Using @Modifying and @Transaction fixed me
Solution 4
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);
}
Solution 5
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);
Related videos on Youtube
Ragesh Kr
Updated on July 09, 2022Comments
-
Ragesh Kr almost 2 years
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 ??
-
Ragesh Kr over 10 yearsBeauty ! works perfectly.. but y is it like executeUpdate works but executeQuery doesnt work ?? in both casesdata is getting inserted properly.
-
Paul Draper over 9 years@RageshKr, it gets inserted correctly, but the Java database connector is afterwards expecting data, and none comes. So the error happens after the insert.
-
Aaban Tariq Murtaza over 3 yearsIts @Transactional
-
need_to_know_now about 3 yearsThanks for this, I wanted a solution for a JPQL query!
-
Usman about 3 years@Transactional @Modifying(clearAutomatically = true) @Query(value = Update/Delete/InstertQuery , nativeQuery = true)