Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Old Style SQL Vs. ANSI SQL...
PostPosted: Wed Aug 31, 2005 12:10 pm 
Newbie

Joined: Tue Nov 30, 2004 2:37 pm
Posts: 16
I am Using Hibernate 3.x and Oracle 9.x.

I have this issue when I convert from native sql to ansi sql, some rows are not returned due to outer join differences. My original query in native SQL contains something like this...

tableAlias.ColumnName (+) = 1

This above table participates in outer join with other tables, when I convert this query to ANSI SQL

something like this;

Left Outer Join Tablename TableAlias on TableAlias.columnName = TableAlias.columnName

The problem is I dont know the equivalent if avilable for

tableAlias.ColumnName (+) = 1 to ANSI standard.

If I use the above condition directly, some rows are not returned, which were originally returned in the old style SQL.

Please help if someone knows a solution for this... Thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 31, 2005 12:23 pm 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
I think it should just be:

Code:
LEFT OUTER JOIN Tablename TableAlias ON (TableAlias.columnName = TableAlias.columnName AND TableAlias.columnName = 1)


This is an additional condition in the ON clause meaning that the candidate row is to be returned, if TableAlias.columnName is NULL or equals 1.

FYI: Using an additional condition within HQL you can use the new WITH clause (Hibernate 3.1 beta 2).

Best regards
Sven

_________________
Please don't forget to give credit, if this posting helped to solve your problem.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 31, 2005 12:27 pm 
Newbie

Joined: Tue Nov 30, 2004 2:37 pm
Posts: 16
Thanks a lot, that worked!.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 31, 2005 12:42 pm 
Newbie

Joined: Tue Nov 30, 2004 2:37 pm
Posts: 16
Sven, this feature is not available in 3.0 is that what you are saying? I was hoping your solution would be availble in 3.0.5 hibernate version.

Please confirm?

Thanks a lot.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 31, 2005 12:50 pm 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
The HQL WITH clause, which adds "extra" restrictions to an association join, is available for Hibernate 3.1. I think you'll have to use at least beta 2.

An alternative way to do it with HQL (e.g. version 3.0):
Code:
select c1 from Clazz1 as c1
left join c1.c2 as joinedClass
where joinedClass.column is null or joinedClass.column = 1


That's not quite elegant, but it works and AFAIK there's no other solution for adding "extra" conditions to an association join with Hibernate prior to 3.1.

Best regards
Sven

_________________
Please don't forget to give credit, if this posting helped to solve your problem.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 31, 2005 3:18 pm 
Newbie

Joined: Tue Nov 30, 2004 2:37 pm
Posts: 16
Yes I tried that solution, but I want the one equivalen to (+), anyway, your first solution was good, I used it to write a ANSI style native SQL.. when we upgrade to hibernate 3.1 etc.. we would implement this (if the client wants).. anyway, this is good information.
thanks...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Sep 01, 2005 4:55 pm 
Beginner
Beginner

Joined: Sat Dec 20, 2003 5:09 pm
Posts: 38
Sorry for butting in here, but I'm curious. With Hibernate 3.1 beta 2 I'm trying to do something like this:

Code:
    <query name="DashConfigQry">
        <![CDATA[
             from DashConfig As C
                left join fetch C.selectBoxItems as ITEMS
                left join fetch C.dashTabData as DATA with DATA.clientId = :CLIENT_ID
                left join fetch C.dashNotes as NOTES with NOTES.clientId = :CLIENT_ID
             order by C.viewId, C.displayOrder, ITEMS.item
        ]]>
    </query>


And what I get in the logs is this:

Code:
Caused by:  with-clause not allowed on fetched associations; use filters


Is there no way to prefetch data using the with clause without having to use filters?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.