Hibernate criteria API with "in clause"

Question

I have native SQL, that I want to move to the Hibenate's criteria API,

Here it is:

SELECT *
FROM HV_LMA_VI t0
WHERE t0.HV_ID IN
              (SELECT t2.HV_ID
               FROM Z_LSZERG_HV t4,
                    Z_LSZ_ERG t3,
                    HERSTELLUNGSVARIANTE t2
               WHERE ((t3.LSZ_ID = 204)
                      AND ((t4.HV_ID = t2.HV_ID)
                      AND ((t3.ERG_ID = t4.ERG_ID)
                      AND (t3.LSZ_ID = t4.LSZ_ID)))));

All needed entites (HV_LMA_VI, Z_LSZERG_HV, Z_LSZ_ERG,) exist.

Answer 1

From what I can understand of your query, it would be something close to:

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(HERSTELLUNGSVARIANTE.class)
    .createAlias("propertyToT3", "t3")
    .createAlias("t3.propertyToT4", "t4")
    .add(Restrictions.eq("t3.LSZ_ID", 204))
    .setProjection(Projections.property("HV_ID"));
session.createCriteria(HV_LMA_VI.class)
    .add(Property.forName("HV_ID").in(detachedCriteria))
    .list();