Parametrized query request postgresql

Question

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?

'localDateTime' is not being passed as an argument to jdbcTemplate.query(...). Probably need to use this overloaded version. — Andrew S
– Andrew S, Commented Apr 15, 2022 at 20:39
@AndrewS I changed '?' in the request to '$1'. Output is: ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1. LocalDateTime isn't being passed in SQL requests. — Staaankey
– Staaankey, Commented Apr 15, 2022 at 21:02

Khan9797 · Accepted Answer · 2022-04-16 00:05:37Z

2

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);

answered Apr 16, 2022 at 0:05

Khan9797

6601 gold badge5 silver badges13 bronze badges

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

Comments

rg226965 · Accepted Answer · 2022-04-16 04:19:18Z

1

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());

answered Apr 16, 2022 at 4:19

rg226965

592 bronze badges

Collectives™ on Stack Overflow

Parametrized query request postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related