Thursday, September 23, 2010

hibernate criteria API exists subquery

SELECT *
FROM service_type
WHERE EXISTS (SELECT 1
FROM service
WHERE service.active = 1
AND service.is_57 = service_type.id)


Criteria criteria = session.createCriteria(ServiceType.class, "servicetype");
DetachedCriteria serviceCriteria = DetachedCriteria.forClass(Services.class, "service");
serviceCriteria.add(Restrictions.eq("ACTIVE", new Integer(1)));
serviceCriteria.add(Property.forName("servicetype.ID").eqProperty("service.IS_57"));
criteria.add(Subqueries.exists(serviceCriteria.setProjection(Projections.property("service.ID"))));
return criteria.list();

1 comment:

  1. Please give me HQl for below Query.

    select * from tariff a where exists (
    select 1 from (select max(VERSION) VERSION , UTILITYID from TARIFF group by UTILITYID ) b
    where a.VERSION = b.VERSION and a.UTILITYID = b.UTILITYID )

    ReplyDelete