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.
Joined: Dec 10, 2003
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?
Joined: Dec 10, 2003
It has been given in some of the forums that this is possible only using native SQL.Can anybody confirm this please..
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.
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
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!
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
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.
will create a sql statement:
Just don't forget all references of your old object which will update/save the particular foreign key property.