JPA: Timestamp comparison in NamedQuery: time data lost

Question

I have a column destined to hold a timestamp which is defined with a java.util.Date data type and stored in a Oracle DATE type in the database. I've verified that the full date and time information is persisted and retrieved correctly from the database, but when I try to compare it in a NamedQuery, the time information appears not to taken into account in the comparison.

The column is defined as follows (analyzeDate is of type java.util.Date):

@Basic(optional = false)
@Column(name = "ANALYZE_DATE")
@Temporal(TemporalType.TIMESTAMP)
private Date analyzeDate;

And my NamedQuery:

@NamedQuery(name = "BOADocument.findByBeforeAnalyzeDate", 
    query = "SELECT b FROM BOADocument b WHERE b.companyId = :companyId AND b.analyzeDate < :analyzeDate")

Which I then execute, passing analyzeDate which is of type java.util.Date:

List<BOADocument> docs = em.createNamedQuery("BOADocument.findByBeforeAnalyzeDate") 
            .setParameter("companyId", clientRecord)
            .setParameter("analyzeDate", analyzeDate, TemporalType.TIMESTAMP)
            .getResultList()

I get back a list of all of the lines in the Document table, although they all have exactly the same value for analyzeDate as the value I've set as a parameter to the NamedQuery.

If I inverse the comparison, I get no lines, further convincing me that the time portion of the timestamp is being lost on the b.analyzeDate side of things, not the :analyzeDate side of things.

I'm using JPA 1.0 with Hibernate 3.5.

This is stumping me, and I'm close to giving up and using a native query, but that feels like cheating!

Answer 1

It turns out that the underlying Oracle type must be TIMESTAMP.

DATE holds hour, minute and second information. This is enough precision for my purposes, so I was planning on using it instead of TIMESTAMP, which also holds milliseconds. As I mentioned previously, storage and retrieval of the hour, minute, and second information to and from DATE was working perfectly, but not comparisons based on the time data in named queries.

I changed the database column type and voila, the code as previously written works. I also get the milliseconds saved, of course, but this doesn't present any side effects in my code.

Answer 2

Modify the query as following one

SELECT b FROM BOADocument b WHERE b.companyId = :companyId AND CAST(b.analyzeDate AS TIMESTAMP) < :analyzeDate".