我已经在stackoverflow和其他网站上搜索了很久,但我无法弄清楚如何在Spring应用程序中使用Hibernate映射从Postgres函数返回的表。
我甚至不确定是否可以将Postgres函数返回的表与MyCustomTable匹配。
我正在尝试的是从Spring应用程序中使用Hibernate调用Postgres函数(存储过程)。
我有这个Postgres函数:
CREATE OR REPLACE FUNCTION func99(type text,start_date TIMESTAMP, end_date TIMESTAMP) RETURNS TABLE(
some_day TIMESTAMP,
tot_requests BIGINT)
AS $$
BEGIN
RETURN QUERY
SELECT t1.first_date, COUNT(*) FROM table1 t1
WHERE t1.request_type = type and t1.first_date > start_date and t1.first_date < end_date;
END;
$$ LANGUAGE PLpgSQL;
控制器
@GetMapping("/users/{username}/func99")
public List<MyCustomTable> getResultsFromFunc99(@PathVariable(value = "username") String username,
@CurrentUser UserPrincipal currentUser,
@RequestParam(value = "service_type") String type,
@RequestParam(value = "start_date") Timestamp startDate,
@RequestParam(value = "end_date") Timestamp endDate){
return queryService.getResultsFromFunc99(username, currentUser, type, startDate, endDate);
}
服务
public List<MyCustomTable> getResultsFromFunc99(String username, UserPrincipal currentUser, String type, Timestamp startDate, Timestamp endDate) {
User user = userRepository.findByUsername(username)
.orElseThrow(() -> new ResourceNotFoundException("User", "username", username));
return return incidentRepository.func99(type, startDate, endDate);
仓库
@Procedure(procedureName = "func99")
List<MyCustomTable> func99(String type, Timestamp startDate, Timestamp endDate);
实体
@Entity
@NamedStoredProcedureQuery(
name = "func99",
procedureName = "func99",
parameters = {
@StoredProcedureParameter(name = "type", mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = "start_date", mode = ParameterMode.IN, type = Timestamp.class),
@StoredProcedureParameter(name = "end_date", mode = ParameterMode.IN, type = Timestamp.class)
}
)
@Table(name = "table1")
public class MyCustomTable {...}
当Postgres函数返回整数时,我可以让它正常工作。我该如何映射来自Postgres函数的表返回值,并将其与Hibernate集成?