CODEWITHSUNDEEP
javahibernatespringcriteria-api
Razvi
Razvi

Reputation: 2818

Hibernate Subqueries.exists

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?

Upvotes: 2

Views: 11921

Answers (2)

hariprasad
hariprasad

Reputation: 585

There is another simplified example solving the same type of problem. The criteria setup result as disjunct sets. View - RegistryViewable (object representation), Table - DocumentType (object representation), Relation 1:n DocumentType.id : @ManyToOne RegistryViewable.typDokumentu (object representation). In entity class RegistryViewable is attribute typDokumentu DocumentType type. Attribute systemType has boolean type. Very important is clause setProjection, without it the criteria will not work.

            DetachedCriteria criteria = DetachedCriteria.forClass(RegistryViewable.class, "RegistryViewable");
    ...         
    if (EntityType.DOCUMENT.equals(searchDTO.getTypEntity())) {
                DetachedCriteria docTypeCriteria = DetachedCriteria.forClass(DocumentType.class,
                        "DocumentType");
                docTypeCriteria
                        .add(Restrictions.eq("DocumentType" + "." + "systemType",
                                true))
                        .add(Restrictions.eqProperty("DocumentType" + "." + "id",
                                "RegistryViewable" + "." + "typDokumentu" + "."
                                        + "id"));
                docTypeCriteria.setProjection(
                        Projections.property("DocumentType" + "." + "id"));
                if (searchDTO.isSystemType()) {
                    criteria.add(Subqueries.exists(docTypeCriteria));
                } else {
                    criteria.add(Subqueries.notExists(docTypeCriteria));
                }
            }
...
        List<RegistryViewable> list = repository.findByCriteria(criteria);

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 692231

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")

Upvotes: 9

Related Questions