Hibernate Disabled Queries as Part of a Criteria Request

Question

java specialists you can help me write separate queries as part of the criteria query for the next SQL statement.

select A.* FROM AETABLE A where not exists ( select entryid FROM AETABLE B where B.classpk = A.classpk and B.userid = A.userid and B.modifiedDate > A.modifiedDate ) and userid = 10146 

Answer 1

You need to write a correlated subquery . Assuming the property / class names match the column / table names above:

 DetachedCriteria subquery = DetachedCriteria.forClass(AETable.class, "b") .add(Property.forName("b.classpk").eqProperty("a.classpk")) .add(Property.forName("b.userid").eqProperty("a.userid")) .add(Property.forName("b.modifiedDate").gtProperty("a.modifiedDate")); Criteria criteria = session.createCriteria(AETable.class, "a") .add(Property.forName("userid").eq(new Integer(10146))) .add(Subqueries.notExists(subquery); 

Answer 2

Only one addition to the above request. If entryid is not the main key, you need to add a projection.

 DetachedCriteria subquery = DetachedCriteria.forClass(AETable.class, "b") .add(Property.forName("b.classpk").eqProperty("a.classpk")) .add(Property.forName("b.userid").eqProperty("a.userid")) .add(Property.forName("b.modifiedDate").gtProperty("a.modifiedDate")) .add(setProjection(Projections.property("entryId")); // Additional projection property Criteria criteria = session.createCriteria(AETable.class, "a") .add(Property.forName("userid").eq(new Integer(10146))) .add(Subqueries.notExists(subquery);
DetachedCriteria subquery = DetachedCriteria.forClass(AETable.class, "b") .add(Property.forName("b.classpk").eqProperty("a.classpk")) .add(Property.forName("b.userid").eqProperty("a.userid")) .add(Property.forName("b.modifiedDate").gtProperty("a.modifiedDate")) .add(setProjection(Projections.property("entryId")); // Additional projection property Criteria criteria = session.createCriteria(AETable.class, "a") .add(Property.forName("userid").eq(new Integer(10146))) .add(Subqueries.notExists(subquery);