Thank your for your responses :)
For an update, here is what I discovered:
If we convert back to XML mappings instead of annotations, the column
name can be specified in the order-by attribute (instead of the java
attribute name), so we can use back ticks as usual.
I forgot to mention that Hibernate generates the correct SQL (regarding
adding quotes to column/table names) everywhere except this one instance
with using @OrderBy annotation.
I stepped through the Hibernate code and I believe there is potentially
a bug/issue here. Hibernate uses a Column object that has all the
information it needs to add the quotes (Booleans as to whether the
column should be quoted, a getQuotedName method, etc) however Hibernate
just never performs any check to see if the column should be quoted;
instead it always does getName when building the Order By clause from
annotations no matter what, which returns the unquoted name of the
column.
I am not 100% positive that this behavior is not being caused by an
error in our app... however I haven't been able to find anything wrong
with our mappings as of yet.
I hope this info can help out somebody else that might run into this
same issue.
Ultimately we decided to remove the @OrderBy annotation and leave
sorting/ordering up to the View level of the app.
Thanks again!
__________________________________
Valerie Cole
Technical Services
Wirestone, LLC
(916) 340 2551
valerie.cole@wirestone.com
-----Original Message-----
From: alvherre@alvh.no-ip.org [mailto:alvherre@alvh.no-ip.org] On Behalf
Of Alvaro Herrera
Sent: Friday, October 19, 2007 5:59 PM
To: Valerie Cole
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Question regarding Hibernate ORDER BY issue
Valerie Cole wrote:
> I have a problem and am pretty sure Hibernate is the culprit but was
> wondering if anybody knew of a fix. We are working on legacy code and
> converted a class from Hibernate 2 xml mappings to Hibernate 3 with
> annotations. On one of the One To Many attributes we have used the
> @OrderBy("displayPosition"). The SQL generated by Hibernate outputs
the
> column name as DisplayPosition with no quoting, and Postgres kicks
back
> an error saying the column does not exist.
Did you try adding extra double quotes? Something like
@OrderBy("\"displayPosition\"").
> Our tables/columns have all been created with quotes and must be
> accessed with quotes (I don't know if that is the norm, I am somewhat
> of a Postgres newb).
This is correct, unless your table/column names (a.k.a. "identifiers")
are lowercase in which case you can leave the quotes out.
--
Alvaro Herrera
http://www.flickr.com/photos/alvherre/
A male gynecologist is like an auto mechanic who never owned a car.
(Carrie Snow)
