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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>