Search code examples
javajpajpql

JPA query returns wrong result

I have implement my custom CRUD repository, which adds some additional functionality, like getting the first next picture after some date (I am using this to find next picture after some other picture). But for some reason, it does not return result that I would expected. Sometimes the returned picture is the same as used for search (date of this picture). I am 100% sure, that all my pictures has unique dateCreated.

Anyone knows what is wrong with my code?

public class PictureRepositoryImpl implements CustomPictureRepository {

@PersistenceContext
private EntityManager em;

@Override
public Picture next(Date date) {

    TypedQuery<Picture> q = em.createQuery("select p from Picture p where p.dateCreated > :date order by p.dateCreated asc", Picture.class);
    q.setParameter("date",date, TemporalType.DATE);
    Picture picture = q.setFirstResult(0)
            .setMaxResults(1)
            .getSingleResult();


    if(picture == null)
    {
        return null;
    }

    LoggerFactory.getLogger(DemoApplication.class).info("Fetched nextpicture: " + picture.getId() + ")");

    return picture;
}
}

enter image description here

Update: I am expecting to get the first picture after "2016-03-20", instead of it, I get the same picture.

Solution

  • I have resolved this issue, it seems that the main problem was not using Timestamp, see code below.

     TypedQuery<Picture> q = em.createQuery("select p from Picture p where p.dateCreated < :date order by p.dateCreated desc", Picture.class);
    q.setParameter("date", date, TemporalType.TIMESTAMP);

    List<Picture> pictures = q
            .setMaxResults(1)
            .getResultList();