Wouter,
One comment (of which you may already be privy): A bonus of JSF is that it
takes the conversion of Strings issue largely out of the picture because one
can map form input parameters directly to objects in backing beans, adding a
level of safety to the input parameters. For instance, JSF has a DateTime
converter; custom converters can also be added with ease. The same goes for
JSF validation. Between JSF and the JPA, the mechanism for developing
enterprise level applications is fairly elegant, IMO. I'm just glad I never
had to deal with EJB before the 3.0 spec!
Take care,
Tim
-----Original Message-----
From: Wouter van Reeven [mailto:wouter_at_van.reeven.nl]
Sent: Tuesday, January 30, 2007 11:30 AM
To: persistence_at_glassfish.dev.java.net
Subject: Re: Using binary operators with Timestamp fields in EJBQL
Hi Tim,
Tom has paved the way to your solution. To add to his excellent suggestion,
maybe you can try to reverse your line of thought. Up to now you have been
converting dates to Strings and tried querying with those. This probably has
been fed by the fact that all form parameters in HTML are Strings.
However, with proper validation of the input between the client and the EJB
container, you can always create a Date object from an input String that
represents a date. Once you do that, Toms solution is easy to implement.
HTH, Wouter
On Tue, Jan 30, 2007 at 11:21:54AM -0500, Tom Ware wrote:
> Hi Tim,
>
> I think using parameters should make it vary easy to dynamically
> create queries.
>
> You can use the API designed for dynamic query creation.
>
> e.g. entityManger.createQuery(String jpqlString)
>
> In the above API, jpqlString can be an arbitrarily complex JPQL(EJBQL)
> String. You can simply embed some named parameters in the string. When
> you create your query, include parameter markers in the query you are
> creating.
>
> e.g. query = em.createQuery(select e from Employee e where e.name =
:name)
>
> Parameters can be included in 2 ways.
>
> 1. By Name - the above example shows how to do that
> 2. By Position - use a "?" and then a number. e.g. select e from
> Employee e where e.name = ?1
>
> You can then add your parameters just before executing the query with
> any of the setParameter() methods. (including the ones that use temporal
> types)
>
> e.g. query.setParameter("name", myName)
>
> So... To dynamically construct a query:
>
> // initial string
> jpqlString = "select e from employee e where ";
> // append a string literal comparison
> jpqlString.append("e.firstName = :firstName ");
> // append a numeric literal comparison
> jpqlString.append("AND e.id > :id ");
> // append a date comparison
> jpqlString.append("AND e.startDate > :date");
> // create your query
> query = em.createQuery(jpqlString);
>
> When you are ready to run your query, just add the parameters and run it:
> query.setParameter("firstName", firstName);
> query.setParameter("id", id);
> query.setParameter("date", myDate, TemporarlType.TIMESTAMP);
>
> You should be able to build arbitrarily complex queries this way.
>
> -Tom
>
>
>
> Hope this helps,
> Tom
>
>
> Romanowski, Tim wrote:
>
> >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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
--
People: "If she weighs the same as a Duck, she's made of wood!"
Sir Bedevere: "And therefore...?"
- application/x-pkcs7-signature attachment: smime.p7s