Hibernate criteria aliases

If you create an alias, then create a criteria and join to another table, etc., it is only going to resolve {alias} to the root table that the alias was created for.

hibernateCriteria.createCriteria("orderItems", "i", Criteria.LEFT_JOIN ).add(
    Restrictions.or(Restrictions.sqlRestriction("orderNo=?", orderNo, Hibernate.Long),
    Restrictions.sqlRestriction("{alias}.item_id in (SELECT item_id FROM items WHERE item_id = ?)", itemId, Hibernate.LONG)
                            )
);    

Lessons Learnt Blog:

Criteria c = session.createCriteria(BaseTable.class, "base");
Criteria secondCriteria = c.createCriteria("secondTable","second", CriteriaSpecification.LEFT_JOIN);
secondCriteria.add(Restrictions.sqlRestriction("{alias}.id = someCoolCondition");

From the Hibernate docs:

List cats = sess.createCriteria(Cat.class)
.createAlias("kittens", "kt")
.createAlias("mate", "mt")
.add( Restrictions.eqProperty("kt.name", "mt.name") )
.list();

Multiple joins:

The following is a native sql template which has the replaced with a generated alias.

     "EXISTS " + //
     "(SELECT 1 " + //
     " FROM" + //
     "  orderItems <aliasname>" + //
     " WHERE" + //
     "  <aliasname>.orderId = {alias}.id" + //
     "  AND <aliasname>.name='<propertyname>'" + //
     "  AND <otherclause>" + //
     ")";