0

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?

  1. 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
  1. 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$
;

  1. 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);
    }

}
  1. 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");

    }
}
2
  • You are returning a result with a fixed number of columns. Why not make that a set-returning function and using select * from f_get_year() instead? Then you are "just" running a SELECT in Spring JDBC Commented Jun 25, 2021 at 14:04
  • I have to follow rules and utiize the functions that alredy exist. Commented Jun 25, 2021 at 14:21

2 Answers 2

1

Unfortunately, there's no way for Spring to guess the mapping between your procedure columns and your POJO attributes, except if their names are 100% identical, so you have to explicitly tell it how to perform the mapping between then using a RowMapper<Your_POJO>

Here you can find an example on how to do that. (see 1.1 Custom RowMapper)

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

Sign up to request clarification or add additional context in comments.

Comments

0

It works like this for some reason:

@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");
    }
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.