How to call a stored procedure(PostgreSQL) with Spring JPA?

Question

PostgreSQL Procedure:

CREATE OR REPLACE PROCEDURE public.create_task_by_device(task_id integer, device_ids text)
 LANGUAGE plpgsql
AS $procedure$
    begin
        -- do something here
    END;
$procedure$
;

Spring JPA:(Kotlin)

interface TaskTemplateRepository : JpaRepository<TaskTemplate, Int> {
    @Procedure("public.create_task_by_device")
    fun createTaskByDevice(@Param("task_id") taskId: Int, @Param("device_ids") device_ids: String)
}

Exception:

org.postgresql.util.PSQLException: ERROR: public.create_task_by_device(integer, character varying) is a procedure. To call a procedure, use CALL.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.20.jar:42.2.20]

Then I got the SQL statement executed {call public.create_task_by_device(?,?)} by spring.jpa.show-sql: true

It seems that the statement actually executed is call using call.

I don't understand why the whole sentence is surrounded by { ... }

Then I tried to single-step debugging, the SQL statement actually executed in the org.postgresql.jdbc.PgStatement#execute method is select * from create_task_by_device(1,'2') as result, unlike the SQL statements output by JPA on the command line, I don't know why

Finally, I try to write my own SQL statement

    @Query("call public.create_task_by_device(?1, ?2)", nativeQuery = true)
    fun createTaskByDevice(@Param("task_id") taskId: Int, @Param("device_ids") device_ids: String)

I got another exception org.postgresql.util.PSQLException: No results were returned by the query.

If I add @Modifying in the method, throws javax.persistence.TransactionRequiredException: Executing an update/delete query

ThivankaW · Accepted Answer · 2021-06-18T07:58:29.440

2

Try changing to @Query with : as place holder -> (:task_id,:device_ids);

@Query(value = "{call public.create_task_by_device(:task_id,:device_ids)}",nativeQuery = true)

If SP performs a DML operation like (Insert,Update) add following annotations along with @Query;

@Transactional 
@Modifying(clearAutomatically = true)

Heres a reference -> https://www.baeldung.com/spring-data-jpa-stored-procedures#query-annotation

edited Jun 18 '21 at 07:58

answered Jun 18 '21 at 07:04

ThivankaW

511
1
8
21

it throws `org.postgresql.util.PSQLException: No results were returned by the query.` – forDream Jun 18 '21 at 07:46
What kind of operation does the SP perform a DQL or DML ? – ThivankaW Jun 18 '21 at 07:51
I updated my answer to point to the exception your referring to. – ThivankaW Jun 18 '21 at 07:59
1

thx, it's works for me. Remind others that if there is a `commit` in a stored procedure, an exception will be thrown `PSQLException: ERROR: invalid transaction termination`. Reference: https://www.postgresql.org/docs/14/sql-call.html – forDream Jun 18 '21 at 11:38

How to call a stored procedure(PostgreSQL) with Spring JPA?

1 Answers1

Linked