Hibernate and Oracle DATE column using HQL query

Question

I have an Oracle database with a DATE column. A sample value from this column is 15/12/2014 15:20:15 in the format DD/MM/YYYY HH:MM:SS.

I have this column defined as follows in my java Entity class:

@Column(name = "SVC_EVENT_END_DTTM", columnDefinition = "timestamp")
private Date dateTime;

The following HQL query works:

public List<ServiceEventLogEntity> getServiceEventLogEntries(DateTime from,
        DateTime to) {
    Query query = getSession().createQuery("FROM ServiceEventLogEntity e "
            + "WHERE e.dateTime between :start and :end "
            + "ORDER BY e.dateTime");
    query.setDate("start", from.toDate());
    query.setDate("end", to.toDate());
    query.setMaxResults(100);
    if(to.isBefore(new DateTime())) {
        // Only caching results for queries in the past.
        // Otherwise we could cache incomplete results when 
        // the end of the query range is in the future
        query.setCacheable(true);
    }
    try {
        return (List<ServiceEventLogEntity>) query.list();
    } catch (HibernateException hex) {
        hex.printStackTrace();
    }
    return null;
}

However, this only seems to work when the start and end times span a day transition, see the trace messages below:

[http-8080-1] TRACE org.hibernate.type.descriptor.sql.BasicBinder  - binding parameter [1] as [DATE] - Sat Dec 13 23:35:00 GMT 2014
[http-8080-1] TRACE org.hibernate.type.descriptor.sql.BasicBinder  - binding parameter [2] as [DATE] - Sun Dec 14 00:35:00 GMT 2014

I'm guessing that this is due to the fact that my DATE objects only represent a day and don't have a time element. I want to be able to query on minutes/hours not days. I've tried various approaches such as using setTimestamp instead of setDate but this just ends up resulting in the query never coming back with results, even in a much narrower window of time (5 minutes).

How can I modify either my entity or my query so I can go to a more granular level to enable me to query by minute or hour instead of day?

przemek hertel · Accepted Answer · 2014-12-16 20:59:37Z

3

Try to use:

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "SVC_EVENT_END_DTTM")
private Date dateTime;

Some javadoc from Temporal source code says:

[...] This annotation must be specified for persistent fields or properties of type java.util.Date and java.util.Calendar [...]

If you don't need milliseconds, than you can use Oracle's DATE type in column (which is up to seconds). When you need millis than you must use Oracle's TIMESTAMP type.

== EDIT ==

Additionaly you should use query.setTimestamp() instead of query.setDate(). I have checked org.hibernate.Query sources and is says:

/**
 * Bind the date (time is truncated) of a given Date object 
 * to a named query parameter.
 *
 * @param name The name of the parameter
 * @param date The date object
 */
public Query setDate(String name, Date date);

but

/**
 * Bind the date and the time of a given Date object to a named query parameter.
 *
 * @param name The name of the parameter
 * @param date The date object
 */
public Query setTimestamp(String name, Date date);

edited Dec 16, 2014 at 20:59

answered Dec 16, 2014 at 20:40

przemek hertel

4,0541 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

conorgriffin Over a year ago

Yeah I originally had that annotation too. It seems to make no difference whether it's there or not.

przemek hertel Over a year ago

I have added some more info. It seems that @Temporal type is important for Hibernate when you use generic query.setParameter(String name, Object val). But you can use query.setTimestamp to tell that you want to use temporal=timestamp.

conorgriffin Over a year ago

I have tried numerous combinations and I'm quite certain I've used setTimestamp in combination with the @Temporal(TemporalType.TIMESTAMP) annotation to no avail

przemek hertel Over a year ago

I suggest to try one more time with setTimestamp. I have checked my old project, where I found several places with hqlquery.setTimestamp and it worked. Sometimes we are sure but accidentally checked other setting that we thought, as I have very often :)

conorgriffin Over a year ago

OK I have tried it, I put @Temporal(TemporalType.TIMESTAMP) on the dateTime field in my Entity and I altered the query to use setTimestamp("start", from.toDate()) and the same for end but I have the same issue. The query executes but never seems to return any results and never throws an error.

|

Collectives™ on Stack Overflow

Hibernate and Oracle DATE column using HQL query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related