From: Romanowski, Tim <tim.romanowski_at_lmco.com>
Date: Mon, 29 Jan 2007 18:20:53 -0500

Hi guys,

 

I'm trying to dynamically construct EJBQL query string based on various
criteria entered via a form in my JSF app. Some of the criteria are dates
and times: in the code below, I have two entity properties called startDate
and stopDate. In my Oracle table, the fields are stored as type TIMESTAMP.
What I'd like to know is: how can I generate the proper format to run a
query like "select * from table t where startDate > [whatever format of
date/time I need here]"? I've done quite a bit of reading today on
java.util.Date, the various java.sql.* time classes, and various examples on
how to format Dates.and am now utterly confused. In fact, the only example
I found on the internet of an EJBQL query using the temporal type Timestamp
was an equality comparison for a named query. Is it even possible to use
other binary operators, such as ">" or "<" with timestamps in EJBQL?

 

Right now, I am simply appending startDate.toString() to my query string,
which I understand is bound to fail without some parsing or formatting, but
I'm wondering what the appropriate way to do this is. Here is a sample
query:

 

SELECT e FROM MyEntity e WHERE e.startDate > 'Sun Jan 28 19:00:00 EST 2007'

 

The Entity code looks like this:

 

    @Column(name = "STARTDATE")

    @Temporal(TemporalType.TIMESTAMP)

    private Date startDate;

 

    @Column(name = "STOPDATE")

    @Temporal(TemporalType.TIMESTAMP)

    private Date stopDate;

 

 

Is there an easy way to store precise timestamps into the database using
Glassfish' EJBQL dialect? An easy way to construct queries using them? If
not easy, I'll settle for _any_ way =) I'm also interested in how to
handle timezones (such as with oracle's TIMESTAMP WITH TIMEZONE column
type), but won't be greedy.

 

 

Tim