Well, to compare against that all depends. Are you comparing to entity
fields, or one entity fields against a Date object set with
setParameter(..) ?
In the former case, you can't compare only the months using pure JPA-QL.
In the latter case, you can get around this limitation by constructing
to Date object, one for the first second of the month, and one for the
last. Then you can do a "BETWEEN", supplying both.
The easiest way depends on which database engines you would be using.
1. If your app will be deployed on only one configuration, you can
create a native query for the database engine used in this
configuration.
2. If your app will be deployed on a single configuration/server, but
POSSIBLY change databases, then (1) can also count, but be sure to
document this limitation, so that when it does change you can
test/update the query. You can even do a check for which persistence
provider is used and issue a warning + mail-flood the administrator
when it changes.
3. Use a event based callback method, that runs on your entity at the
time of persistence. So when your entity will be written to database,
you basically format your date into the way you want it, and store it
in a second column. SO if you have a column "mydate", this method
would run, and using java mangle "mydate" into a Date object that
represents: YYYY/MM/00 00:00:00. Then store this in "mydateMMYYYY".
You can now use this field instead.
4. Similar to (3), except you create your own date/time object, which
has the year/month/day/hour/minute/second split into separate fields.
Then make this embeddable, or an entity of it's own. Then when you
persist, your callback method would split the date object into these
fields. So inside your JPA-QL you would do something like (depending
on how you are storing this entity, ie. embeddable/entity): FROM a
WHERE mydateX.month=xxxxx AND mydateX.year=yyyyy
5. If your app will be deployed on many different configurations, go
over board and write a separate handler for each persistence provider.
This isn't as bad as it sounds, though you might have difficulty
testing all of them. The approach here would be to figure out how you
would do it in each provider, and then implement all the queries. Then
test as much as you can, and make bug-fix releases as you discover
problems with those that don't work. A product I was working on once,
ended up on this route for a specific type of query.
It's not ideal, but we basically created a test table (with known
data), and an history table, recording the results of these tests and
which provider was used. Then whenever the system would startup with a
provider we haven't tested, we would run an extensive sequence of
tests, for every scenario we could think of, using this test table's
data. We would then verify the results, and record the findings. If
any of these tests failed, we would notify the user/administrator, and
prompt them to send us the report + contact details. We would also
disable all the features that would use these queries. When we
received these reports we would hasten to fix them, and then release a
bug fix. If necessary, we would have worked with the affected party to
fix it (as they are running a system we could not get hold of for
testing). I'm lucky to say that our research into these queries were
pretty thorough, and very successful. All-in-all we only had a single
provider fail, with only a handful of tests.
Then, native queries are not as dark a route as made out to be. It
really depends on what you want to achieve, and where your program
would be running. If you are developing in-house software, chances are
that your software will always be running on the same database engine,
and if/when it changes, and the native query still doesn't work, you
would simply have to update the query. If you were to use a single
static method to "build" these queries, you can simplify the update as
well. Still, it's not a good idea to go crazy with native queries.
When you can find a good way using pure JPA-QL, it's would definitely
be a good idea to stick with it, but it's not a sin to use native. It
all depends on the problem and what's available, allowed and possible,
and from all this you make a decision.
And in some cases, like BETWEEN, most databases have this
statement/function, and would work without any changes.
I find it sad that JPA doesn't provide better date/time functions.
These are used very often, and it makes it impossible to compare
different date/time fields in persistent entities without first
retrieving one of them. For instance, you can't do a join on 2 tables,
and join on the month/year pair of their data/time fields.
So. Here I gave you a couple of ideas to get around this limitation.
Hope it helps. I'm sure there are many more, possibly much better than
I gave you. Hopefully they implement proper date/time functions in
future JPA specs.
Q
On Thu, Aug 14, 2008 at 8:52 AM, Quintin Beukes <quintin_at_last.za.net> wrote:
> Can you give an example of the ==, >=, etc. that doesn't work.
>
> Give the JPA-QL, the field definitions in your entity, and the entity
> names (these include any annotations on the fields).
> Then the most important, give the error message.
>
> Q
>
> On Wed, Aug 13, 2008 at 12:59 PM, chflb <labidi.chifa_at_gmail.com> wrote:
>>
>> thank you for your reply,
>> but none of =<,<,>,=>, =, current_date, works with me,
>> i have this message::"A persistence error occurred."
>> (but the query works without this comparison)
>> in fact, the origin of my idea to use to_char, is that i like to select the
>> dates of the same month (by putting in the 'to_char' function :'MM/YYYY'
>> instead of 'MM/DD/YYYY' )
>> if it is not possible with to_char so can i do this?
>> thany you
>> --
>> View this message in context: http://www.nabble.com/how-to-compare-2-dates-in-a-JPA-QL-query-tp18959789p18960901.html
>> Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.
>>
>>
>
>
>
> --
> Quintin Beukes
>
--
Quintin Beukes