具有多行插入的java Quarkus Hibernate/Panache NativeQuery
我试图创建某种类型的更新端点,当您点击它时,它将从一个表插入到另一个表。基本上,它来自一个名为funds
的表,它是fundId
、reportingfrequency
和country
。它保留fundIds
,并为该值获取一个日期列表,这些日期是与Postgres存储过程/函数联接的结果。下面是一个完整的SQL查询示例。因此,理想情况下,如果表中有100行,它会将所有100行以及日期数组插入到due_dates
表中
INSERT INTO due_dates (fund_id, listdate)
SELECT fundid,
(
SELECT array_agg(weekdays::date)
FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
WHERE f.datestamp IS NULL
AND extract(dow from weekdays) BETWEEN 1 AND 5
)
FROM funds
WHERE reportingfrequency = 'Daily';
现在我的问题是我不知道如何。。。以编程方式执行此操作,以便根据funds
表中的行。。。我不知道如何从行中获取country
字段来执行特定的存储过程调用。例如,如果一行在它的country
字段中有Poland
,那么理想情况下它会调用holidays.poland(2021, 2021)
。。。如果这个国家是美国,它会称之为holidays.usa(2021, 2021)
。下面是我当前的NativeQuery的运行方式
entityManager.createNativeQuery("INSERT INTO due_dates (fund_id, listdate)" +
"SELECT fundid," +
"(" +
"SELECT array_agg(weekdays::date)" +
"FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)" +
"LEFT JOIN holidays." + country + "(" + year + "," + year + ") f ON (weekdays = f.datestamp)" +
"WHERE f.datestamp IS NULL" +
"AND extract(dow from weekdays) BETWEEN 1 AND 5" +
")" +
"FROM funds" +
"WHERE reportingfrequency = 'Daily'; ")
.executeUpdate();
在实现我想要做的事情之前,我需要做些什么来调整原始SQL语句吗
共 (0) 个答案