Issue in Converting postgres stored procedure to java query

Question

I am migrating a DB. In old DB we use some stored procedures - SP which we want to get rid off in new DB. Simply we want to use plain java query instead of a SP. We will call the query from our java spring boot app.

Here is the SP :

CREATE OR REPLACE PROCEDURE public.spfetchowner(
    owner integer,
    optype integer,
    INOUT p_refcur refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    OPEN p_refcur FOR
    SELECT
        z.owner_num, 
        COALESCE(op_type_num, optype) AS op_type_num, 
        ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday
        FROM (SELECT owner AS owner_num) AS z
        LEFT OUTER JOIN owner_details AS ad
            ON z.owner_num = ad.owner_num AND op_type_num = optype;
END;
$BODY$;

ALTER PROCEDURE public.spfetchowner(integer, integer, refcursor)
    OWNER TO postgres;

My DB table details :

    owner_num integer NOT NULL,
    op_type_num integer NOT NULL,
    sunday numeric(5,3),
    monday numeric(5,3),
    tuesday numeric(5,3),
    wednesday numeric(5,3),
    thursday numeric(5,3),
    friday numeric(5,3),
    saturday numeric(5,3),
    CONSTRAINT pk_owner_details PRIMARY KEY (owner_num, op_type_num)

This is my java repository method to fetch details from owner_details

@Query(nativeQuery = true,
        value = "I need the proper equivalent query from the SP here")
OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
        @Param("ownerNum") Integer owner, 
        @Param("typeNum") Integer type );

I am unable to form the query from the SP that I need to use in repo method. Can someone help me out here on the query formation part. Simple select is working but I would need the select query as framed in the SP with the joins and then how to use the same in my repo method.

I don't understand the question. Just copy the SELECT from the procedure into your Java code. — user330315
– user330315, Commented Nov 4, 2022 at 10:28
@a_horse_with_no_name : how to pass the parameter values in java method is where I am stuck. — Som
– Som, Commented Nov 4, 2022 at 10:30

dsp_user · Accepted Answer · 2022-11-04 12:39:22Z

When specifying the parameters to your query (@Query) you basically have 2 options

Option 1 (using named parameters)

 @Query(nativeQuery = true,
    value = "SELECT
    z.owner_num, 
    COALESCE(op_type_num, optype) AS op_type_num, 
    ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday
    FROM (SELECT :ownerNum AS owner_num) AS z
    LEFT OUTER JOIN owner_details AS ad
        ON z.owner_num = ad.owner_num AND op_type_num = :typeNum")
   OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
    @Param("ownerNum") Integer owner, 
    @Param("typeNum") Integer type );

or option 2 (using ordinal numbers preceded by a ?)

 @Query(nativeQuery = true,
    value = "SELECT
    z.owner_num, 
    COALESCE(op_type_num, optype) AS op_type_num, 
    ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday
    FROM (SELECT ?1 AS owner_num) AS z
    LEFT OUTER JOIN owner_details AS ad
        ON z.owner_num = ad.owner_num AND op_type_num = ?2")
 OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
    Integer owner, 
    Integer type );

Collectives™ on Stack Overflow

Issue in Converting postgres stored procedure to java query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related