• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

outer join in HQL

 
sajan Thankappan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table A and another table B.Both the table has userId as column which is a foreign key of User table.Is it possible to do a outer join of these 2 tables using userIds in HQL/JPQL.
 
sajan Thankappan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is it possible in hibernate.In orinary sql we can write like "table A left outer join B on(A.user_Id=B.user_Id) but hibernate shows error in 'on'.

using 'with' I may be able to add additonal join conditions.but I want to do a left join of 2 tables using userId.If it is inner join in where cluase it can be added.Does any one has any idea on this .A solution or a workaround?
 
sajan Thankappan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It has been given in some of the forums that this is possible only using native SQL.Can anybody confirm this please..
 
Saravanan Thoppae
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can specify the outer join condition between the tables in your mapping file. Is there any reason going for HQL for this?

And yeah. Me too referred in some forum that specifying outer join (as we would do in normal ansi supported db) is not possible in HQL.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have two left outer joins in HQL from the parent to two children, then most likely the resultset would be a cartesian product, meaning way way too many records, and Hibernate doesn't like that.

You can use Native SQL, like you said. You can set your fetching strategy such that you get one of those Collections eagerly, and set the other one to "subselect" so that when you first go into the second Collection, it will go to the database and use one query to load that collection.

Mark
 
Rama Krishna
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isn't left outer join same as left join. If so read ahead.

I had the same question quite a while ago; the solution comes by defining the relationships, mapping all the relationships in your mapping file and allowing you to write your HQL as:


this works when
SomeTableObject defines a set of SomeChildSetObjects
and SomeChildSetObject defines a set of SomeChildSetChildObj

I don't think there is any other way other than defining the relationships in the mapping files and ofcourse defining the sets of child objects or the parent object in the actual object you want to use in the HQL!


Lets say there is a field, a foreign key of another table which is a child table and defined as a column in the database parent table and which you want to access directly as tableNameObj.childForeignKey.

But when you want to left join the parent table with a child table as above, you will be creating a set of childObjects in the parent table Object with getters and setters assuming you have defined it as a set in the parent table mapping file.

This provides access to any of the child object fields by writing the HQL in the above way

<set name="ChildTableObjectSet"
inverse="true"
cascade="all">
<key column="childForeignKey"/>
<one-to-many class="ChildTableObj"/>
</set>

Similarly, this works for any other kind of relationship

I did face problems when I reuse the same column for defining it as a table column and also in a relationship with another table. But I am not sure when this might occur. So don't worry about this limitation of defining field/database column in the mapping file of the object only once.

"left join fetch" here gets all the child table field values at the same time fetching (not lazily) the parent table data here whereas left join does not and there are many other things to consider.

A good place to start would be the "Hibernate in Action" rather than jumping directly into examples which I am doing right now!

-Cheers
Rama
[ May 04, 2008: Message edited by: Rama Krishna ]
 
Rama Krishna
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I forgot to mention that when you re-use the foreign key properties of a table in order to access any other tables you have to change your property from


to



Here if you don't remove the first declaration, you are bound to get an error as you cannot use the same property twice. Let me know if you want to know why you get the error you will have to remove the first declaration in your mapping file.

But the advantage of writing the second way, the correct way of mentioning any foreign key relationships in advance, is you can still access your foreign key without causing any performance issues.

An HQL:


will create a sql statement:




Just don't forget all references of your old object which will update/save the particular foreign key property.

You will be replacing

baseTableObj.setForeignKey(someKey)

with

baseTableObj.setParentObj(someParentDao.find(someKey))


change in getters, as I discussed first above, will be simpler though

baseTableObj.getForeignKey()

to

baseTableObj.getParentObj.getPrimaryKey();

I had to do all of this because of improperly generating and designing my mapping file in the first place!

[ May 06, 2008: Message edited by: Rama Krishna ]

[ May 06, 2008: Message edited by: Rama Krishna ]
[ May 06, 2008: Message edited by: Rama Krishna ]
 
sajan Thankappan
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot for all your suggestions .I will make use of it
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic