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 ?

score 1 · Answer 1 · edited May 23 '17 at 11:43

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.

edited May 23 '17 at 11:43

Community

1
1

answered Jun 11 '15 at 14:49

Dragan Bozanovic

23,102
5
43
110

I want to use **Associations** specifically !! – Neeraj Jain Jun 11 '15 at 14:52
How your edited answer going to join 2 tables on branch_code ! where have you specified that . – Neeraj Jain Jun 11 '15 at 15:19
So, you don't want to change your tables at all? Because I assumed that you will add foreign key column in one of the tables referencing the primary key of the other table as part of defining the association... Sorry, then my solution won't work. – Dragan Bozanovic Jun 11 '15 at 15:27
I've edited my post again, maybe the solution with the view could be interesting for your use case. – Dragan Bozanovic Jun 11 '15 at 15:48

Joins in Hibernate : HQL equivalent of given SQL

1 Answers1