Joins in Hibernate : HQL equivalent of given SQL

Question

I have 2 tables lets say fi and branchMst :

------------------                       ---------------------
|       fi       |                       |    branchMst       |
------------------                        --------------------
|     fi_id      |                       |     branchId       | 
|      uid       |                       |     branchCode     |
|   branch_code  |                       |     branchName     |
------------------                       ----------------------

Now in order to get UID and Branch Name of each fi :

SQL:

select uid,branchName from fi left join branchMst bm 
                             on fi.branch_code=bm.branch_code;

It will provide me the desired Records but i want to do the same via Hibernate Associations .

Some doubts :

1) branchCode is not primary Key in branchMst , so how to define my one to many association.

2) If i define association on 5 tables and i want data from 2 tables only , will All assocation mappings still applies ?

Answer 1

Without introducing the association

select uid, branchName from fi, branchMst bm where fi.branch_code = bm.branch_code;

select uid from fi where branch_code not in (select branch_code from branchMst);

and then combine the results programmatically.

More details here.

With the association

1) You don't need one-to-many association, you need one-to-one. But you will need to change the fi table to reference branchId (PK, I assume). Then use:

select fi.uid, bm.branchName from Fi fi left join BranchMst bm

2) I don't completely understand what you mean, but you can define the associations with as many entities as needed, and you can of course read only what you need in your queries.

With the association without changing either fi or branchMst

You could create a view on fi which will provide branchId column, and then map Fi entity to the view.

With the association without any changes in the database

This is not possible. See this question.