Date criteria not working in Spring boot criteria builder

I am applying a date criteria in my Java Springboot project through criteria builder but it’s not working. All the records are being fetched and the conditioning on date is getting ignored.

Session session = sessionFactory.getCurrentSession();
        CriteriaBuilder builder = session.getCriteriaBuilder();
        CriteriaQuery<FilesInfoModel> query = builder.createQuery(FilesInfoModel.class);
        Root<FilesInfoModel> root = query.from(FilesInfoModel.class);

        Timestamp sqlTimestamp = Timestamp.valueOf(restrictProfileDateTime);

        query.where(builder.greaterThanOrEqualTo(root.get("createdDate"), sqlTimestamp));
        query.where(builder.lessThanOrEqualTo(root.get("createdDate"), Timestamp.valueOf(restrictProfileDateTime.plusDays(1))));
        query.where(builder.equal(root.get("fileType"), deltaScreeningFileType));
        query.select(root);
        List<FilesInfoModel> filesInfoList = session.createQuery(query).getResultList();

I tried putting greaterThanOrEqualTo & lessThanOrEqualTo to the date and date+1 values. The variable on which I am applying the condition is datetime2 data type in SQL Server DB table. So it should fetch only records with that date but instead it’s giving all the records and the condition is getting ignored.

>Solution :

Calling CriteriaQuery.wherer() multiple times does not add the predicates. The javadoc states: Replaces the previously added restriction(s), if any.

So the code should be changed something like this (untested):

    query.where(builder.and(
        builder.greaterThanOrEqualTo(root.get("createdDate"), sqlTimestamp),
        builder.lessThanOrEqualTo(root.get("createdDate"), Timestamp.valueOf(restrictProfileDateTime.plusDays(1))),
        builder.equal(root.get("fileType"), deltaScreeningFileType)
    );