I'm trying to execute an sql function using SimpleJdbcCall and BeanPropertyRowMapper classes but can not obtain a proper List of objects. Looks like there is a pair key-value in the list. Also I have an error:
Inconvertible types; cannot cast 'com.example.demo.model.MyCalendarDto' to 'org.springframework.util.LinkedCaseInsensitiveMap'
Need to be mentioned that I'm obligated to use this exactly function and can't change it. I use PostgreSql 13. Can someone show me the error?
- The table:
-- 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
- The function:
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$
;
- The entity class:
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);
}
}
- The repository
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");
}
}
select * from f_get_year()instead? Then you are "just" running a SELECT in Spring JDBC