In the Hibernate we can count the number of query results without returning them:
( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()
To order a result by the size of a collection, use the following query:
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name
order by count(msg)
If the used database supports subselects, than we can place a condition upon selection size in the where clause of your query:
from User usr where size(usr.messages) >= 1
If the used database does not support subselects, use the following query:
select usr.id, usr.name from User usr.name join usr.messages msg group by usr.id, usr.name
having count(msg) >= 1
For these type of solution cannot return a User with zero messages because of the inner join, the following form is also useful:
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name
having count(msg) = 0
Here Properties of a JavaBean can be bound to named query parameters:
Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();
Collections are pageable by using the Query interface with a filter:
Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();
Collection elements can be ordered or grouped using a query filter:
Collection orderedCollection = s.filter( collection, "order by this.amount" );
Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );
Then we can find the size of a collection without initializing it:
( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue();