CriteriaQuery returning wrong result

Question

My CriteriaQuery returns only one record and should two for dateFrom = '2019-12-10' and dateTo = '2019-12-20' And i don't know why.

This is my screenshot from database.

And this is my query:

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Reservation> reservationQuery  = criteriaBuilder.createQuery(Reservation.class);
        Root<Reservation> reservationRoot = reservationQuery.from(Reservation.class);

        Predicate dateFrom = criteriaBuilder.greaterThanOrEqualTo(reservationRoot.get("dateFrom"), reservationDto.getDateFrom());
        Predicate dateTo = criteriaBuilder.lessThanOrEqualTo(reservationRoot.get("dateTo"), reservationDto.getDateTo());

        reservationQuery.where(dateFrom, dateTo);
        TypedQuery<Reservation> res = entityManager.createQuery(reservationQuery);
        List<Reservation> result = res.getResultList();

Your code seems to be alright. Did you try to debug it? – Pedro Estevão Jan 11 '20 at 02:19 — Pedro Estevão, Jan 11 '20 at 02:19
Try to enable query logging and check the generated query. – S B Jan 11 '20 at 03:17 — S B, Jan 11 '20 at 03:17

score -1 · Answer 1 · answered Jan 12 '20 at 00:40

I solved my problem with query, it works fine.

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Reservation> reservationQuery  = criteriaBuilder.createQuery(Reservation.class);
        Root<Reservation> reservationRoot = reservationQuery.from(Reservation.class);

        Predicate dateFrom = criteriaBuilder.greaterThanOrEqualTo(reservationRoot.get("dateFrom"), reservationDto.getDateFrom());
        Predicate dateTo = criteriaBuilder.lessThanOrEqualTo(reservationRoot.get("dateTo"), reservationDto.getDateTo());

        criteriaBuilder.and(dateFrom);
        criteriaBuilder.and(dateTo);
//        reservationQuery.where(dateFrom, dateTo);
        TypedQuery<Reservation> res = entityManager.createQuery(reservationQuery);
        List<Reservation> result = res.getResultList();
        return result;

CriteriaQuery returning wrong result

1 Answers1