Hi Michael,
Michael Horwitz wrote:
On Dec 7, 2007 5:37 PM, Rob Hills <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Hi All,
This is really a Hibernate column, but hopefully someone here will
know
more about it than I do, which wouldn't be hard.
I have a model class that includes an attribute "departureTime" of
type
java.util.Date and the data contains both date and time
information. In
my Dao class, I obtain a list of this object using Hibernate and I
need
to sort it by the date component of the data (ie excluding the time).
I've read the Hibernate documentation on HQL and AFAICT, HQL supports
expressions, but only in the Select part of the HQL. In the
section on
"Order By", the documentation says that HQL will order a list "by any
property of a returned class or component". So, I created an
@Transient
attribute "departureDate" + getter, that returned just the date
part of
my data (in fact a string formatted to "yyyymmdd"), but that
caused an
exception to be raised "could not resolve property departureDate".
Perhaps a small ray of light: the bit on Hibernate supporting
functions in the order by clause is in the next section of the
document 14.12:
SQL functions and aggregate functions are allowed in the having and
order by clauses, if supported by the underlying database (eg. not in
MySQL).
So could be done as long as you are not using MySql?
Well, MySql is one of the databases I'm not using for this project, so
that part's OK. Thanks for pointing out that little bit of the docco,
I'd stopped reading at the end of the previous "Order By" paragraph :-(
Because I'm trying to keep this "generic" HQL, I tried to incorporate an
earlier section of that document (14.10) on expressions with something
else I'd found in my searching that looked promising, the @Formula
annotation. Unfortunately that hasn't worked. I tried defining a
"departureDate" in my pojo with the following:
@Formula("year(departureTime) || month(departureTime) ||
day(departureTime)")
public String getDepartureDate() {...
But that gives me an error along the lines of "no such function
year(Timestamp without timezone)". Having seen some examples of use of
the Hibernate @Formula annotation, it looks like they want raw SQL there
rather than HQL. Unfortunately, it is in handling and formatting of
dates that different databases seem to be most divergent and I need to
make this project as DB-agnostic as I can :-(
I may have to create a function to give me a sortable date string from a
timestamp field. Now it would be nice if there were some way of
defining that within Hibernate!
Cheers,
Rob Hills
Waikiki, Western Australia
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
