JdbcBatchItemWriter - setSql - How to fix timestamp type mismatch

Question

I am using JdbcBatchItemWriter in my Springboot application to import data from a CSV file into an Oracle DB, but I’m not able to import in timestamps. I get the error Failed to convert property value of type 'java.lang.String' to required type 'java.time.LocalDateTime I understand the error – I just don’t understand how to fix it. Is there a way to mark the insert statement so that it reads it as a timestamp?

I did try: INSERT INTO HB357(START_DATE) VALUES (TO_DATE(:START_DATE, YYYY-MM-DD, HH:mm:SS) And I tried TO_TIMESTAMP(:START_DATE, YYYY-MM-DD HH24:mm:SS) but it didn't work

I also have tried changing the entity type to String instead of LocalDataTime, but this results in not a valid month error. So I then tried altering the session before the insert (with the entity type still as string): itemWriter.setSql("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"); And then running the insert state but also got the not a valid month error

ENTITY:

@Entity
@NamedQuery(name ="HB357.getListOfResponseIds", query = "SELECT RESPONSE_ID from HB357")
public class HB357 {

    @Id
    private String RESPONSE_ID;
    @JsonFormat(pattern="yyyy-MM-dd@HH:mm:ss")
    private LocalDateTime START_DATE;
    private String STOVE_OPTION;
    private String FIRST_NAME;
    private String LAST_NAME;
    private String ADDRESS;
    private String CITY;
    private String ZIPCODE;
    private String COUNTY;

//Getters and Setters left out for brevity

Relevant Insert Function

@Bean
    public JdbcBatchItemWriter<HB357> writer() {
        JdbcBatchItemWriter<HB357> itemWriter = new JdbcBatchItemWriter<>();
        itemWriter.setDataSource(appProperties.dataSource());
        itemWriter.setSql("INSERT INTO HB357 (" +
                "START_DATE, RESPONSE_ID, STOVE_OPTION, FIRST_NAME,LAST_NAME,ADDRESS,CITY,ZIPCODE,COUNTY,PHONE_NUMBER,EMAIL," +
                "STOVE_LOCATION,EXEMPTIONS,AGI,INCENTIVE,INCENTIVE_AMT) " +
                "VALUES (:START_DATE, :RESPONSE_ID, :STOVE_OPTION, :FIRST_NAME, :LAST_NAME, :ADDRESS, :CITY," +
                ":ZIPCODE, :COUNTY, :PHONE_NUMBER, :EMAIL, :STOVE_LOCATION, :EXEMPTIONS, :AGI," +
                ":INCENTIVE, :INCENTIVE_AMT)" );
        itemWriter.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
        return itemWriter;
    }

Answer 1

create class eg . public class HB357ParameterSourceProvider implements ItemSqlParameterSourceProvider and implement/do transformation for datatype inside it. eg MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("start time", <>") return param

pojo -> use instant start time conver date into timestamp(in case of sql server) and set

ItemSqlParameterSourceProvider valueSetter = new HB357ParameterSourceProvider () writer.setItemSqlParameterSourceProvider(valueSetter)