[SOLVED] Is this query possible using Criteria or DetachedCriteria Hibernate

Is this query possible using Criteria or DetachedCriteria Hibernate

The question is simple can this query be done in Hibernate using Criteria or DetachedCriteria? i guess not but i wanted to do this question maybe exist a workaround.

SELECT
  COLUMNS 
FROM table
WHERE id not in (
    SELECT * FROM (
        SELECT id 
        FROM table
        WHERE
        SOMECONDITIONS   
        ORDER BY timestamp desc limit 0, 15
  ) 
  as t);

I will mark the answer by @Dean Clark as correct but another question arises is the following

where can i find the findByCriteria from SessionFactory we are not using Spring

Solution

To exactly match you query, you really need to do it with 2 steps, but I'd avoid this if possible:

final Criteria innerCriteria = getSession().createCriteria(YourEntity.class);
// SOME CONDITIONS
innerCriteria.add(Restrictions.eq("someColumn", "someValue"));
innerCriteria.addOrder(Order.desc("timestamp"));
innerCriteria.setMaxResults(15);
innerCriteria.setProjection(Projections.id());
List<YourIdClass> ids = innerCriteria.list();


final Criteria criteria = getSession().createCriteria(YourEntity.class);
criteria.add(Restrictions.not(Restrictions.in("id", ids)));
List<YourEntity> results = criteria.list();

Would the objects you're trying to identify have the same "SOMECONDITIONS"? If so, this would functionally accomplish what you're looking for:

final DetachedCriteria criteria = DetachedCriteria.forClass(YourEntity.class);
// SOME CONDITIONS
criteria.add(Restrictions.eq("someColumn", "someValue"));
criteria.addOrder(Order.desc("timestamp"));
getHibernateTemplate().findByCriteria(criteria, 16, 9999999);