Parametrized query request postgresql-CodePudding

I want to get data from my DB where is LocalDateTime equals to LocalDateTime in get request.

@Override
    public List<Timeslot> getAllAvailable(LocalDateTime localDateTime) {
        return jdbcTemplate.query("select * from timeslot where day = ?", TIMESLOT_ROW_MAPPER);
    }

Timeslot table code:

CREATE TABLE "timeslot" (
    "timeslot_id" serial,
    "day" date NOT NULL,
    "start_time" TIME NOT NULL,
    "end_time" TIME NOT NULL,
    "user_id" serial NOT NULL,
    "is_recorded" boolean,
    CONSTRAINT "timeslot_pk" PRIMARY KEY ("timeslot_id")
);

Controller code:

@GetMapping("/allAvailable")
    public List<Timeslot> getAllAvailable(@RequestParam("day") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime day) {
        return userService.allAvailable(day);
    }

But when I did this request result in console is: org.postgresql.util.PSQLException: ERROR: syntax error at end of input. How do i change sql request code to fix this error? Should I use PrepareStatement or something else?

CodePudding user response：

As @AndrewS mentioned, you didn't pass localDateTime value as parameter. Therefore jdbcTemplate doesn't bind ? to localDateTime.

You should use overloaded method of query and pass localDateTime as the last parameter:

jdbcTemplate.query("select * from timeslot where day = ?", TIMESLOT_ROW_MAPPER, localDateTime);

CodePudding user response：

I think you are storing day as Date format in the database. and in the query you are comparing day whose type is Date with LocalDateTime type which might be wrong. first take Date from LocalDateTime then pass as method argument. For example

jdbcTemplate.query("select * from timeslot where day = ?", TIMESLOT_ROW_MAPPER, localDateTime.toLocalDate());