CODEWITHSUNDEEP
javahibernate
Jakob
Jakob

Reputation: 556

How can I left join a subquery in hibernate with criteria?

I want to be able to left join a subquery in hibernate with criteria.

Here is sample query:

Select * From Order o
Left Join (Select * From Product p Where p.accountId = 3) p
On p.id = o.productId
Where (p.category is not null and p.category = 'clothes') or 
(p.category is null and o.category = 'clothes')

Note this is a sample query, mine is more complex and I need to be able to left join a subquery.

How could you generate this query with the hibernate criteria syntax?

I would like to do something similar to this:

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(Order.class);

DetachedCriteria productCriteria = DetachedCriteria.forClass(Product.class);
productCriteria.add(Restrictions.eq("accountId", 3));

criteria.createAlias("productCriteria", "p", JoinType.LEFT_OUTER_JOIN, productCriteria);

criteria.add(Restrictions.or(
        Restrictions.and(Restrictions.isNotNull("p.category"), Restrictions.eq("p.category", "clothes")),
   Restrictions.and(Restrictions.isNull("p.category"), Restrictions.eq("category", "clothes"))
));

List list = criteria.list();

Edit

In my real problem the subquery consists of two tables.

Upvotes: 4

Views: 2554

Answers (2)

saver
saver

Reputation: 2684

You can use the following Criteria method to use subquery in joins.

public Criteria createAlias(String associationPath, String alias, JoinType joinType, Criterion withClause) throws HibernateException;

and a Criterion parameter, which you can create with the Subqueries factory class, will generate a subquery in the join condition.

You will end up with something like this:

Criteria criteria = session.createCriteria(Order.class);

DetachedCriteria productCriteria = DetachedCriteria.forClass(Product.class)
                .setProjection(property("id"))
                .add(Restrictions.eq("accountId", 3));

criteria.createAlias("product", "p", JoinType.LEFT_OUTER_JOIN, Subqueries.propertyEq("id", productCriteria));
...

Upvotes: 1

Atul
Atul

Reputation: 1566

You can use createAlias API of Criteria class and specify join type. Refer this documentation for more details.

    Criteria createAlias(String associationPath,
                 String alias,
                 int joinType)
                 throws HibernateException
Join an association using the specified join-type, assigning an alias to the joined association.
The joinType is expected to be one of CriteriaSpecification.INNER_JOIN (the default), CriteriaSpecification.FULL_JOIN, or CriteriaSpecification.LEFT_JOIN.

Upvotes: 1

Related Questions