How to write left join in hibernate?

Question

I have following as table structure

**BPV table** 
  id, vid, bid

**vt table** 
   vid, name, gender

**uv table**
   uvid, vid, cast,...

i want to write left join between BPV ,uv based on vid in hibernate (mysql)

bpv entity have vt as one to one as well as uv have vt with one to one but there is no bpv &uv in realation

You could at least paste the query you are currently using... — fge, Jun 11 '13 at 08:30
`select * from BPV left join uv on BPV.vid = uv.vid` you need something like that? — user1516873, Jun 11 '13 at 08:43
[i reliazed this is the solution(not actually)](http://stackoverflow.com/questions/9892008/hql-left-join-of-un-related-entities) — AnilHoney, Jun 11 '13 at 12:36

Juan Luis · Answer 1 · 2013-06-11T10:03:27.967

2

Hibernate has Criteria API, which is a good way to execute queries in a OO approach. If you have your persistent entities, then you can do this:

Criteria criteria = session.createCriteria(BPV.class, "bpv");
criteria.createCriteria("bpv.vt", "vt", CriteriaSpecification.LEFT_JOIN);
criteria.createCriteria("vt.uv", "uv", CriteriaSpecification.LEFT_JOIN);
// add restrictions
return criteria.list();

edited Jun 11 '13 at 10:03

answered Jun 11 '13 at 09:37

Juan Luis

3,317
4
17
23

criteria never meets above requirement – AnilHoney Jun 11 '13 at 09:54

score 1 · Answer 2 · answered Jun 11 '13 at 08:49

1

Hibernate SQL:

from BPV where BPV.uv.cast = "your_condition"

It requires properly described entities and dependency;

or pure SQL:

select * from BPV left join uv on BPV.vid = uv.vid where uv.cast = "your_condition"

answered Jun 11 '13 at 08:49

n00bot

229
1
11

you could just do a hibernate native query, you seem to be using sql/hql anyway – NimChimpsky Jun 11 '13 at 09:14
here is no realtion between bpv and uv these are both have commonly with vt table so above query not possible – AnilHoney Jun 11 '13 at 09:48
bpv entity have vt as one to one as well as uv have vt with one to one but there is no bpv &uv in realation – AnilHoney Jun 11 '13 at 09:49
if you described entity with @OneToOne field and FetchType.EAGER, you may simple use HQL "from bpv". It gives you a List, and each BPV will have vt field which have uv field. If you want HQL with condition, use "from bpv where bpv.vt.uv.cast=..." just like an objects in Java – n00bot Jun 13 '13 at 06:25

score 0 · Answer 3 · answered Jun 11 '13 at 15:09

What you're asking for is not posible, there is a Theta Join solution but it only supports inner join, so you have to give up on that approach and do one of the following solutions:

Map the proper relationship:

If this query is a unusual escenario, you dont necesary have to change your actual mapping , you can make a new dto Class with the new mapping, and use that one for this query.

Use native SQL:

This is prettly simple too, use .createSQLQuery(), to run the native query and you can use .addScalar() + .setResultTransformer(Transformers.aliasToBean(someDto.class)) to get a list of your entity.

How to write left join in hibernate?

3 Answers3