Subquery in hibernate criteria API

Question

I am attempting to generate this query in hibernate Criteria:

 select count(*) from res_mapping where mop_id = ? and role_name = ? and 
mop_id not in(select mop_id from res_mapping_mod where mop_id = ? and role_name = ?);

Here is My Method:

public static boolean roleHasMenu(String roleName, String mopId) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        long count = 0;
        try {
            tx = session.beginTransaction();
            DetachedCriteria subquery = DetachedCriteria.forClass(ResMappingMod.class);
            subquery.add(Restrictions.eq("roleName", roleName));
            subquery.add(Restrictions.eq("mopId", mopId));
            subquery.setProjection(Projections.property("mopId"));
            Criteria cr = session.createCriteria(ResMapping.class);
            cr.add(Restrictions.eq("roleName", roleName));
            cr.add(Restrictions.eq("mopId", mopId));
            cr.add(Subqueries.notIn("mopId", subquery));
            count = (Long) cr.setProjection(Projections.rowCount()).uniqueResult();
            tx.commit();
        } catch (Exception asd) {
            log.debug(asd.getMessage());
            if (tx != null) {
                tx.rollback();
            }
        } finally {
            session.close();
        }
        return count > 0;
    }

What I am getting is:

    select count(*) as y0_ from GLS.RES_MAPPING this_ where this_.ROLE_NAME=? 
and this_.MOP_ID=? and ? not in (select this_.MOP_ID as y0_ from 
GLS.RES_MAPPING_MOD this_ where this_.ROLE_NAME=? and this_.MOP_ID=?)

Before not in I have a parameter instead of a field. What could be the issue?

Answer 1

try this one

cr.add(Subqueries.propertyNotIn("id", subCriteria));

Thanks, Amit Kumar