Wouter/Tom/Michael, thanks for the excellent suggestions.
It is true that I hadn't really looked to the extent that I could construct
native queries and set parameters. However, I still have a problem that may
be more of a design issue at this point: the whole reason I am doing this
is to allow dynamic generation of queries based on a JSF form that users
fill out. There are several fields for any given table that may or may not
be queried, including the option for each field to search >, <, = , and
between. I had thrown together some code that constructs the various
clauses of an SQL query by concatenating criteria. I have to believe that
such a pattern exists in the enterprise community, but being new to this
area and not having come across this in my searches, I'm shooting from the
hip.
So that leaves me with this issue: If I am constructing my query by
concatenating where-clause critera (e.g., " AND fieldX > <numeric literal>"
or " AND fieldY > '<string literal>'"), how would I do so with dates?
My initial thought is to:
1) have a separate method for concatenating date values (based on what the
three of you suggested)
2) including a "?" in the where-clause corresponding to the date value
3) Have a class array that stores the corresponding date value for each date
as it is added to the query.
4) Once the getQuery method of the class is called (which does some
validation and adds together the from, where and orderby clauses), I would
call "setParameter" on the Query object to add back in the values stored in
the date array.
The initial class is becoming convulted, but I'm not aware of a better way.
Am I on the right track, or am I overlooking a key feature of EJBQL? I wish
not to use named queries, as I would have to have separate queries for
nearly every combination of fields and entities in my app; something for
which I was hoping Native queries would provide an elegant workaround.
Tim
-----Original Message-----
From: Michael.Bouschen_at_Sun.COM [mailto:Michael.Bouschen_at_Sun.COM]
Sent: Tuesday, January 30, 2007 9:26 AM
To: persistence_at_glassfish.dev.java.net
Subject: Re: Using binary operators with Timestamp fields in EJBQL
Hi Tim,
I would like to second what Wouter and Tom said. I think you should use
a query parameter for the date value. You can then use the setParameter
as Tom described to bind the actual date instance to the date query
parameter. The Java Persistence query language does not support date
literals. Comparing a date field with a string as in "... e.startDate >
'Sun Jan 28 19:00:00 EST 2007'" is not standardized.
Regards Michael
> When you use this strategy for temporal types, it may also be useful
> to use the Query.setParameter() methods that take a TemporalType as an
> argument:
>
> public Query setParameter(String name, Date value, TemporalType
> temporalType);
> public Query setParameter(String name, Calendar value, TemporalType
> temporalType);
> public Query setParameter(int position, Date value, TemporalType
> temporalType);
> public Query setParameter(int position, Calendar value, TemporalType
> temporalType);
>
> That way you can use the temporalType argument to specify which type
> you would like to use.
>
> -Tom
>
> Wouter van Reeven wrote:
>
>> Hi,
>>
>> On Mon, Jan 29, 2007 at 06:20:53PM -0500, Romanowski, Tim wrote:
>>
>>
>>> 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'
>>>
>>
>> Have you tried this: let's say you have a variable called
>> oldestValidDate which
>> is a Date object and has a value that represents 'Sun Jan 28 19:00:00
>> EST 2007'.
>> Create a NamedQuery with the query
>>
>> @NamedQuery(name="SelectNewerThanDate" query="SELECT e FROM MyEntity
>> e WHERE e.startDate > :checkDate");
>>
>> and call the query with the parameter oldestValidate. No formattng, no
>> converting to String or whatever, just the date object:
>>
>> @PersistenceContext EntityManager em;
>> Query query = em.createNamedQuery("SelectNewerThanDate");
>> query.setParameter("checkDate", oldestValidDate);
>>
>> By the way, EQL knows of three date functions:
>>
>> CURRENT_DATE
>> CURRENT_TIME
>> CURRENT_TIMESTAMP
>>
>> The datetime functions return the value of current date, time, and
>> timestamp on
>> the database server.
>>
>> So this should also work
>>
>> @NamedQuery(name="SelectNewerThanTwoDaysAgo" query="SELECT e FROM
>> MyEntity e WHERE e.startDate > CURRENT_DATE-2");
>>
>>
>> HTH, Wouter van Reeven
>>
>>
>>>
>>>
>>> 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
>>>
>>>
>>
>>
>>
>>
- application/x-pkcs7-signature attachment: smime.p7s