Hibernate Criteria: Left Outer Join with restrictions on both tables

hibernate join criteria hibernate-criteria
Markos Fragkakis picture Markos Fragkakis · Feb 5, 2010 · Viewed 37.3k times · Source

I am doing a LEFT OUTER JOIN, but I am only able to apply Restrictions on the first table. Is there a way ti apply on the second table as well?

Here is my code:

Criteria criteria = this.crudService
        .initializeCriteria(Applicant.class).setFetchMode("products",
              FetchMode.JOIN);.

This works (applicant has an applicantName property):

criteria.add(Restrictions.eq("applicantName", "Markos")

Neither of these works (product has a productName property)

criteria.add(Restrictions.eq("productName", "product1")

criteria.add(Restrictions.eq("products.productName", "product1") // products: the name of the property criteria.add(Restrictions.eq("Product.productName", "product1") // Product: the name of the DB table

And this is the exception I am receiving saying (if I understand correctly) that the productName property does not exist in Applicant:

EJB Exception: ; nested exception is: org.hibernate.QueryException: could not resolve property: products.inventedName of: org.myCompany.applicant.entity.Applicant; nested exception is: org.hibernate.QueryException: could not resolve property: products.inventedName of: org.myCompany.applicant.entity.Applicant

I tried to use an alias, but this generated an INNER JOIN, instead of the LEFT OUTER JOIN I want.

How can I apply restrictions on both tables?

UPDATE:

Issue is probably the same as this: https://forum.hibernate.org/viewtopic.php?p=2393694

Answer

dotjoe picture dotjoe · Feb 5, 2010

You can specify left outer join in the createalias...

.CreateAlias("products", "p", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Add(Restrictions.Eq("p.inventedName", inventedName));

Be aware that you are doing a left outer join with a restriction on that column...essentially making it an inner join. If you also want applicants without products then you'll have to check for null product too.

Related questions

Hibernate Criteria Join with 3 Tables

I am looking for a hibernate criteria to get following: Dokument.class is mapped to Role roleId Role.class has a ContactPerson contactId Contact.class FirstName LastName I want to search for First or LastName on the Contact class and …

Read More
Hibernate Criteria and multiple join

is possible with Hibernate criteria do it? select A.something, B.something, C.something, D.something from A JOIN B on A.id = B.id_fk JOIN C ON B.id = C.id_fk JOIN D ON C.id = D.…

Read More
Hibernate Criteria: Perform JOIN in Subquery/DetachedCriteria

I'm running into an issue with adding JOIN's to a subquery using DetachedCriteria. The code looks roughly like this: Criteria criteria = createCacheableCriteria(ProductLine.class, "productLine"); criteria.add(Expression.eq("productLine.active", "Y")); DetachedCriteria subCriteria = DetachedCriteria.forClass(Model.class, "model"); subCriteria.…

Read More