使用Spring JDBC处理PostgreSQL中的游标

Question

我试图使用SimpleJdbcCall和BeanPropertyRowMapper类执行sql函数，但无法获得正确的对象列表。列表中似乎有一个配对键值。我也有一个错误：

Inconvertible types; cannot cast 'com.example.demo.model.MyCalendarDto' to 'org.springframework.util.LinkedCaseInsensitiveMap'

需要说明的是，我有义务使用这个函数，并且不能更改它。我使用PostgreSql 13。有人能给我看看错误吗？

1. 表格：

-- cntr_m2.calendar definition
-- Drop table
-- DROP TABLE cntr_m2.calendar;
CREATE TABLE cntr_m2.calendar (
id_calendar int4 NOT NULL,
period_name varchar(255) NOT NULL,
calendar_date date NULL,
calendar_level int4 NOT NULL,
calendar_level_name varchar NULL,
year_number int4 NULL,
month_number int4 NULL
);

-- cntr_m2.calendar foreign keys

2. 函数：

CREATE OR REPLACE FUNCTION cntr_m2.f_get_year(p_id_year_in integer DEFAULT NULL::integer)
RETURNS refcursor
LANGUAGE plpgsql
AS $function$
declare
ref refcursor;
begin
open ref for
select c.id_calendar as id_year,
c.year_number
from cntr_m2.calendar c
where c.id_calendar = coalesce(p_id_year_in, c.id_calendar)
and c.calendar_level_name = 'year';
return ref;
end;
$function$
;

3. 实体类：

package com.example.demo.model;
import java.util.Objects;
public class MyCalendarDto {
private Integer idYear;
private Integer yearNumber;
public MyCalendarDto() {
}
public Integer getIdYear() {
return idYear;
}
public void setIdYear(Integer idYear) {
this.idYear = idYear;
}
public Integer getYearNumber() {
return yearNumber;
}
public void setYearNumber(Integer yearNumber) {
this.yearNumber = yearNumber;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
MyCalendarDto that = (MyCalendarDto) o;
return Objects.equals(idYear, that.idYear) && Objects.equals(yearNumber, that.yearNumber);
}
@Override
public int hashCode() {
return Objects.hash(idYear, yearNumber);
}
}

4. 存储库

package com.example.demo.repository;
import com.example.demo.model.MyCalendarDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.util.*;
@Repository
public class JdbcMyCalendarRepository implements MyCalendarRepository{
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall simpleJdbcCall;
@PostConstruct
private void postConstruct(){
this.jdbcTemplate = new JdbcTemplate(dataSource);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("cntr_m2")
.withProcedureName("f_get_year")
.returningResultSet("#result-set-1", 
BeanPropertyRowMapper.newInstance(MyCalendarDto.class));
}
@Override
public List<MyCalendarDto> findMyCalendars(Integer id) {
SqlParameterSource parameters = new MapSqlParameterSource()
.addValue("p_id_year_in", id);
Map out = simpleJdbcCall.execute(parameters);
if (out == null){
return Collections.emptyList();
}
return  (List) out.get("#result-set-1");
}
}

Answer 1

不幸的是，Spring无法猜测过程列和POJO属性之间的映射，除非它们的名称100%相同，所以您必须明确告诉它如何使用RowMapper<Your_POJO>

在这里你可以找到一个如何做到这一点的例子。(参见1.1自定义行映射器(

https://mkyong.com/spring/spring-jdbctemplate-querying-examples/

Answer 2

由于某些原因，它是这样工作的：

@Repository
public class JdbcMyCalendarRepository implements MyCalendarRepository{
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall simpleJdbcCall;
@PostConstruct
private void postConstruct(){
jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName("cntr_m2")
.withProcedureName("f_get_year")
.declareParameters(
new SqlParameter("p_id_year_in", Types.INTEGER))
.withoutProcedureColumnMetaDataAccess()
.returningResultSet("calendars",
BeanPropertyRowMapper.newInstance(MyCalendarDto.class));
}
@Override
public List<MyCalendarDto> findMyCalendars(Integer id) {

Map out = simpleJdbcCall.execute(new MapSqlParameterSource().addValue("p_id_year_in", id));
if (out == null){
return Collections.emptyList();
}
return  (List) out.get("calendars");
}
}