Java – Hibernate Criteria Left Joining Two Tables

criteriahibernatejavaleft-joinsql

I have two entities, say Business and Area.

Relevant properties:
Business – area, area2, code
Area – areaId, areaName

area and area2 of Business map to the id in Area

I'm trying to write a Hibernate criteria that returns all the areas with businesses only.

SQL looks like:
FROM area a LEFT OUTER JOIN business b on a.areaId = b.area or a.areaId = b.area2
WHERE b.code != null
GROUP BY a.areaName

This is what I have:

DetachedCriteria criteria = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area"))
.setProjection(Property.forName("area2"))
.add(Restrictions.ne("code", null));

Criteria criteriaArea = fullTextSession.createCriteria(Area.class)
.createAlias("areaId", "areaId", CriteriaSpecification.LEFT_JOIN)
.add(Property.forName("areaId").in(criteria));

But this doesn't work, I get a "not an association: areaId" query exception.

Any ideas why this is happening? Thanks.

Best Answer

createAlias() joins another entity using provided property. Hibernate calculates what table to join using mapping of provided property. But areaId isn't mapped as a @ManyToOne or @ManyToMany reference to Business entity. So Hibernate doesn't understand to what table you want to join using Area.areaId.

Your criteria will be translated to SQL like:

select a.* from Area a
left join <here should be table referenced by areaId> b on a.areaId = b.id
where a.areaId in (
  select area, area2 from Business where code <> null
)

You may rewrite query without unused join:

DetachedCriteria criteria1 = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area"));

DetachedCriteria criteria2 = DetachedCriteria.forClass(Business.class)
.setProjection(Property.forName("area2"));

Criteria criteriaArea = fullTextSession.createCriteria(Area.class)
.add(Restrictions.or(
  Property.forName("areaId").in(criteria1),
  Property.forName("areaId").in(criteria2)
);
Related Topic