Optimized way to determine if query returns any rows?

fashoom · **Joined:** Fri Sep 04, 2009 4:12 pm **Posts:** 4

In SQL I can type:
select exists(select * from customers) as have_customers

I have a DetachedCriteria in hand but I can't figure out how to do this in Hibernate.

Thanks for any help.

Cameron McKenzie · **Posted:** Fri Sep 04, 2009 7:19 pm

What does your object model look like? Hibernate is ORM, so you need to do object-relational mapping. Where are your objects?

fashoom · **Joined:** Fri Sep 04, 2009 4:12 pm **Posts:** 4

yes i know, i am solid in ORM. this is a generic question, you want to know if there are zero, or more than zero instances of an object type w/ certain properties, i.e. whether a query will return any rows. as i said, i have a DetachedCriteria in hand. let me modify the equivalent SQL, it would be more like this:

select exists(select * from customer where customer.zip = '02145') as any_customers_in_boston

so how would this be coded in Hibernate?

fashoom · **Joined:** Fri Sep 04, 2009 4:12 pm **Posts:** 4

in case it's not clear:

customer is a java class and table name, zip is a java data member and column name.

lopezvit · **Joined:** Mon Jan 26, 2015 7:29 am **Posts:** 1

I know this is a fairly old question, but I hit this result in my search and I though that, for the record, I could help once I found the answer myself.

You'll need to create a DetachedCriteria for your class:

Code:

final DetachedCriteria userWithZipCode = forClass(Costumer.class, "det_cost");

Ten add the restriction for the zip code

Code:

userWithZipCode.add(eq("det_cost.zip", "02145"));

And then the restriction from the "parent" query

Code:

userWithZipCode.add(forName("det_cost.costumerId").eqProperty("item.costumerId"));

And finally add a projection, which is necessary to constraint the result to one column (I don't know if, because it's so simple, and only involves one table, this projection could be omitted).

Code:

userWithZipCode.setProjection(property("det_cost.zip"));

This detached criteria can be used now to, for example, check all the items that have to be sent to the given zip code:

Code:

Criteria criteria=getSession().createCriteria(Item.class, "item");
criteria.add(exists(userWithZipCode));

Hope it helps,

Alejandro