Thread: 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)

On Friday 19 October 2007 3:03 pm, Valerie Cole wrote:
> Hello
>
>
>
> 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.  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).  I have been
> Googling for about an hour and unable to come up with anything, so I
> thought I would drop a line.
>
>
>
> Thanks,
>
>
>
> V. Cole
You might to look at:
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html
See esp. section 5.4 on SQL quoted identifiers
--
Adrian Klaver
aklaver@comcast.net

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)