CODEWITHSUNDEEP
springpostgresqlspring-data-jpaspring-data
forDream
forDream

Reputation: 486

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

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

debug picture

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

Upvotes: 2

Views: 10005

Answers (2)

BichriTech
BichriTech

Reputation: 69

Adding this annotation will work without issue @Transactional(propagation = Propagation.NOT_SUPPORTED). It solved my problem, thanks Guys :-)

Upvotes: 0

ThivankaW
ThivankaW

Reputation: 540

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

Upvotes: 3

Related Questions