Issue in Converting postgres stored procedure to java query-CodePudding

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.

CodePudding user response：

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 );