Spring data reactive repository - r2dbc not working

Question

The query is getting executed but not getting any result.

router :- api/v1/service/appt/usr/{usr_id}

private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");

public Mono<ServerResponse> retrieveProjectsByUsr(ServerRequest request) {
        final String userIdStr = request.pathVariable(USER_ID_PARAM);
        final Optional<String> stDateStr = request.queryParam("stDate");
        final Optional<String> endDateStr = request.queryParam("endDate");

        final LocalDateTime stDate = LocalDateTime.parse(stDateStr.get(), DATE_TIME_FORMATTER);
        final LocalDateTime endDate = LocalDateTime.parse(endDateStr.get(), DATE_TIME_FORMATTER);
        long userId = Long.parseLong(userIdStr);

        return secContext.retrieveUser().flatMap(usr -> {
            Flux<Appt> appts = projectRepository.findApptsBetween(stDate, endDate, userId, usr.getOrgId());
            return ServerResponse.ok().contentType(APPLICATION_JSON).body(appts, Project.class);
        });
    }

Repository code,

@Repository
public interface ApptRepository extends ReactiveCrudRepository<Appt, Long> {
    
    @Query("select * from appt where usr_id = :usrId and org_id = :orgId and start_time BETWEEN :stDate and :endDate")
    Flux<Appt> findApptsBetween(LocalDateTime stDate, LocalDateTime endDate, long usrId, int orgId);

}

Query from the log,

Executing SQL statement [select * from appt where usr_id = :usrId and org_id = :orgId and start_time BETWEEN :stDate and :endDate]

Data in project table,

Postman request,

http://localhost:9090/api/v1/service/appt/usr/2?stDate=2021-01-24 03:20&endDate=2021-03-25 05:23

Not sure what is wrong with this. It doesn't return the record.

Answer 1

The following code works. Answer was modified from the above posts.

return secContext.retrieveUser()
                .flatMap(usr -> apptRepository.findApptsBetween(userId, usr.getOrgId(), stDate, endDate).collectList()
                        .flatMap(appts -> ServerResponse.ok().contentType(APPLICATION_JSON).bodyValue(appts)));