我正在遷移資料庫。在舊資料庫中，我們使用一些存盤程序 -我們希望在新資料庫中擺脫的SP 。只是我們想使用普通的 java 查詢而不是SP。我們將從我們的 java spring boot 應用程式中呼叫查詢。
這是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;
我的資料庫表詳細資訊：
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)
這是我從 owner_details 獲取詳細資訊的 java 存盤庫方法
@Query(nativeQuery = true,
value = "I need the proper equivalent query from the SP here")
OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
@Param("ownerNum") Integer owner,
@Param("typeNum") Integer type );
我無法從我需要在 repo 方法中使用的 SP 形成查詢。有人可以在查詢形成部分幫助我嗎？簡單的選擇正在作業，但我需要在帶有連接的 SP 中框架的選擇查詢，然后如何在我的 repo 方法中使用它。
uj5u.com熱心網友回復：
在為查詢（@Query）指定引數時，您基本上有 2 個選項
選項 1（使用命名引數）
@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 );
或選項 2（使用以 ? 開頭的序數）
@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 );
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/527497.html