JPA date literal

Question

How to present a date in a JPA request without using (typed) parameters?
If the date is really corrected (for example, March 1, 1980), the code:

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

by setting:

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

pretty verbose, right? I would like to include 1980/1/3 in my request.

UPDATE: I changed the sampling date to 1980/1/3, because in 1980/1/1 it was mixed.

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'} ie {d '2009-11-05'} // t at the beginning means 'time' {t 'hh-mm-ss'} ie {t '12-45-52'} // ts at the beginning means 'timestamp'; the part after dot is optional {ts 'yyyy-mm-dd hh-mm-ss.f'} ie {ts '2009-11-05 12-45-52.325'} 

must do the job (braces and apostrophes are needed).

Answer 2

I used the Query criteria for my queries, this is just great. It looks like this:

 @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(); 

Answer 3

I spent a couple of days delving into this. The root of the problem seems to be that the hibernate-generated grammar does not include support for temporary literals.

The JPA specification includes support for temporary literals, but does not require persistence providers to translate from JPA syntax to native JDBC driver syntax. From JPA2 Spec 4.6.1:

"The JDBC escape syntax can be used to specify date, time, and time 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 time stamp literature depends on the JDBC driver used. Resiliency providers do not need to translate from this syntax into their own database or driver syntax. "

It would be nice if Hibernate provided support for literary dates, but it seems that the implementation of this is slightly more related to what I suspected.

The functionality that is missing here regarding my needs is that you cannot execute the select coalesce(somePath, someDateLiteral) . You can still do where somePath=someDate . As long as they display objects, you can drop whatever you want into the where clause.