Create subquery in Criteria Query

Question

I need to create subquery using the Criteria API

the current Criteria query which i have created is

Criteria propCriteria = session.createCriteria(PropertyDetail.class);
            propCriteria.createAlias("property","prop");
            propCriteria.createAlias("country", "country");
propCriteria.add(Restrictions.eq("prop.propertyId",promotionFormBean.getPropertyId()));

The SQL produced by Hibernate is

select * from PROPERTY_DETAILS this_, COUNTRY_MASTER country2_, PROPERTY_MASTER prop1_, USER_MASTER user6_, ROLE_MASTER role7_, USER_MASTER user8_ where this_.country_id=country2_.country_Id and this_.property_id=prop1_.PROPERTY_ID and prop1_.archive_user_id=user6_.id(+) and user6_.role_id=role7_.ROLE_ID(+) and prop1_.create_user_id=user8_.id(+) and prop1_.PROPERTY_ID=?.

What i need to do is to produce the below SQL using criteria

Required:

select * from PROPERTY_DETAILS this_, COUNTRY_MASTER country2_, PROPERTY_MASTER prop1_, USER_MASTER user6_, ROLE_MASTER role7_, USER_MASTER user8_ where (this_.country_id=country2_.country_Id or country2_.controller_order in (select zone_id from property_zone_mapping where property_id=?)) and this_.property_id=prop1_.PROPERTY_ID and prop1_.archive_user_id=user6_.id(+) and user6_.role_id=role7_.ROLE_ID(+) and prop1_.create_user_id=user8_.id(+) and prop1_.PROPERTY_ID=?

The bold part of Required section.Property_zone_mapping is one another table which not associated with PropertyDetail.class.

I want somehow to use this table using subquery .

Thanks in advance.

For `this_.country_id=country2_.country_Id`, this join part is generated by hibernate, changing to the required custom join clause is not easy. Use HQL or SQL query instead. If you still want to use subquery, please add details(schema, association) for the related entity to see if the query can be refactered to use subquery. — samabcde, Nov 04 '19 at 14:57

score 0 · Answer 1 · answered Jul 29 '15 at 15:31

I find criteria queries so much harder to read than HQL. Have you considered a NamedQuery?

@NamedQueries({
    @NamedQuery(
        name="PropertyDetail.findByPropertyId", 
        query="from PropertyDetail join property p join country c where p.propertyId = :propertyId"
    )
})
public class PropertyDetail {
    ...
}

public class PropertyDetailDao {
    public List<PropertyDetail> findByPropertyId(long propertyId) {
        Query query = getSession().getNamedQuery("PropertyDetail.findByPropertyId");
        query.setParameter("propertyId", propertyId);
        return query.list();
    }
}

Create subquery in Criteria Query

1 Answers1