Hibernate Subqueries.exists

Question

I am having a problem with a Subqueries.exists criteria.

I have the following:

DetachedCriteria criteria = DetachedCriteria.forClass(Expense.class, "mainExpense")
        .add(Restrictions.eq("group", group))
        .addOrder(Order.desc("created"));

if (all) {
    criteria.add(
            Restrictions.or(
                    Restrictions.eq("status", ExpenseStatus.PENDING),
                    Restrictions.eq("status", ExpenseStatus.COMPLETE)
            ));
} else {
    criteria.add(Restrictions.eq("status", ExpenseStatus.PENDING));
}

if (user != null) {
    DetachedCriteria userSubCriteria = DetachedCriteria.forClass(UserExpense.class, "userExpense")
            .add(Restrictions.eq("userExpense.primaryKey.user", user))
            .add(Restrictions.eq("userExpense.primaryKey.expense", "mainExpense"))
            .setProjection(Projections.property("userExpense.primaryKey"));
    criteria.add(Subqueries.exists(userSubCriteria));
}

return getHibernateTemplate().findByCriteria(criteria);

If I pass a null user (not using the Subqueries.exists) it works okay and returns me the valid expense items. If I give a user if given me the following:

org.springframework.orm.hibernate3.HibernateSystemException: could not get a field value by reflection getter of Expense.id; nested exception is org.hibernate.PropertyAccessException: could not get a field value by reflection getter of Expense.id

and lower in the logs:

Can not set java.lang.Integer field Expense.id to java.lang.String

Does anyone have any idea of what I am doing wrong?

Solution

I think there are two problems in the query:

1. The exists subquery should have a projection returning a single column. You're using a projection that returns an embedded object.
2. You compare "userExpense.primaryKey.expense" and "mainExpense" with Restrictions.eq(). I don't think it's valid. You should use Restrictions.eqProperty("userExpense.primaryKey.expense.id", "mainExpense.id")