JPA date literal

Question

How represent a date in a JPA query, without using (typed) parameters?
If the date is really fixed (for example, 1 mar 1980), the code:

TypedQuery<MyEntity> q = em.createQuery("select myent from db.MyEntity myent where myent.theDate=?1", db.MyEntity.class).setParameter(1, d);

having set:

Date d = new Date(80, Calendar.MARCH, 1);

is quite verbose, isn't it? I would like to embed 1980/1/3 into my query.

UPDATE: I modified the sample date to 1980/1/3, because 1980/1/1 as it was, was ambiguous.

Answer 1

IIRC you can use date literals in JPQL queries just like you do it in JDBC, so something like:

// d at the beginning means 'date'
{d 'yyyy-mm-dd'} i.e. {d '2009-11-05'}

// t at the beginning means 'time'
{t 'hh-mm-ss'} i.e. {t '12-45-52'}

// ts at the beginning means 'timestamp'; the part after dot is optional
{ts 'yyyy-mm-dd hh-mm-ss.f'} i.e. {ts '2009-11-05 12-45-52.325'}

should do the work (the curly braces and apostrophes are required).

Answer 2

I spent a couple days digging around on this. Seems the root of the problem is that the Hibernate generated grammar does not include support for temporal literals.

The JPA specification does include support for temporal literals but does not require persistence providers to translate from from the JPA syntax to the native syntax of the JDBC driver. From the JPA2 Spec 4.6.1:

"The JDBC escape syntax may be used for the specification of date, time, and timestamp literals. For example:

SELECT o
FROM Customer c JOIN c.orders o
WHERE c.name = 'Smith'
AND o.submissionDate < {d '2008-12-31'}

The portability of this syntax for date, time, and timestamp literals is dependent upon the JDBC driver in use. Persistence providers are not required to translate from this syntax into the native syntax of the database or driver."

It would be nice if Hibernate did provide support for date literals, but it seems the implementation for this is a little more involved that I'd suspected.

The functionality lacking here as far as my needs are concerned is that you cannot do a select coalesce(somePath, someDateLiteral) query. You can still do a where somePath=someDate. As long as they're mapped entities you can throw whatever you want in a where clause.

Answer 3

I used criteriaQuery for my querys, its just great. It looks like:

@Override
public List<Member> findAllByDimensionAtTime(Dimension selectedDimension,
        Date selectedDate) {
    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Member> criteriaQuery = criteriaBuilder
            .createQuery(Member.class);
    Root<Member> member = criteriaQuery.from(Member.class);
    criteriaQuery
            .select(member)
            .where(criteriaBuilder.lessThanOrEqualTo(
                    member.get(Member_.validFrom), selectedDate),
                    criteriaBuilder.greaterThanOrEqualTo(
                            member.get(Member_.validTo), selectedDate),
                    criteriaBuilder.equal(
                            member.get(Member_.myDimensionId),
                            selectedDimension.getId())).distinct(true);
    return em.createQuery(criteriaQuery).getResultList();

validFrom and validTo are date fields!

Edit: shorter Example (according to yours):

   CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<MyEntity> criteriaQuery = criteriaBuilder
            .createQuery(MyEntity.class);
    Root<MyEntity> entity= criteriaQuery.from(MyEntity.class);
    criteriaQuery
            .select(member)
            .where(criteriaBuilder.equal(
                            entity.get(MyEntity_.theDate),
                            new Date(80, Calendar.MARCH, 1);)).distinct(true);
    return em.createQuery(criteriaQuery).getResultList();