Hibernate. retrieve the size of a query result

Hello,

I need to retrieve the size of a query result ***using Hibernate***.

For instance, I would like to know how many rows exists for this query:
String query = "FROM items WHERE group=1"

I tried with this:
+++++++
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
List list = session.createQuery(query).list();
int size = list.size();
session.getTransaction().commit();
+++++++
However, if there are a lot of items, java throws a java.lang.OutOfMemoryError: Java heap space.

If I use iterator() instead of list(), the Iterator class does not have an equivalent size() method.

What is the easiest way to get the size for a query, using hibernate?


dportabellaAsked:
Who is Participating?
 
Jim CakalicConnect With a Mentor Senior Developer/ArchitectCommented:
The Hibernate user's guide suggests the following technique:
String query = "select count(*) from items where group=1";
int count = ((Integer) session.iterate(query).next() ).intValue();

Remember that HQL ultimately translates to some dialect of SQL. Neither SQL nor the JDBC API have a mechanism for yielding the number of rows that a query will return until the rows have actually been retrieved. HQL therefor has no "magic way of doing that either. You have to explicitly issue a count using the same where clause as the query that returns the objects.

Regards,
Jim Cakalic
0
 
dportabellaAuthor Commented:
Some more points...
0
 
dportabellaAuthor Commented:
Thanks jim_ckalic.

However, what about using filters?

Look at this extract from:
http://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html
++++++
You can use a collection filter to get the size of a collection without initializing it:
( (Integer) s.createFilter( collection, "select count(*)" ).list().get(0) ).intValue()

The createFilter() method is also used to efficiently retrieve subsets of a collection without needing to initialize the whole collection:
s.createFilter( lazyCollection, "").setFirstResult(0).setMaxResults(10).list();
++++++
How I could use a filter to get the number of rows for the query "FROM items WHERE group=1"?


0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jim CakalicSenior Developer/ArchitectCommented:
Ah, NHibernate ...

When it says "get the size of a collection" it isn't an arbitrary group of objects that might be returned by some snippet of HQL. A "collection" is the many side of a mapped one-to-many association. For example, in chapter 17 of the NHibernate reference manual there is an example using Blog with an IList of Items. The mapping uses a bag to define the relationship between the Blog and its Items collection. After loading a Blog object the IList of Items is empty because it is lazy loaded. At this point you could use a Filter to find out how many persisted Items are in the collection without loading them by using a Filter. The "collection" in the snippet you posted would be the IList of items owned by the Blog object. If this is the same as your scenario then you can use a Filter. A Filter is really just a special type of Query that only applies to persisted collections and array.

However, if what you want to do is execute some arbitrary HQL query and determine how many objects will satisfy that query then you want to do what I proposed originally which is to select the count of qualifying objects first as a separate query that returns just an integer result. This is an example from the NHibernate manual:

IEnumerable countEn = session.Enumerable("select count(*) from ....");
countEn.MoveNext();
int count = (int) countEn.Current;

Regards,
Jim
0
 
dportabellaAuthor Commented:
Hello,

I do not understand this last code.
You took it from here, right? http://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html

How is this example supposed to run?
The following code cannot work, because the Session interface does not have the "Enumerable" method, nor I see the IEnumerable class anywhere.
+++++++
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();

IEnumerable countEn = session.Enumerable("SELECT COUNT(*) FROM Items WHERE group=1");
countEn.MoveNext();
int count = (int) countEn.Current;

session.getTransaction().commit();
+++++++

Can you please correct this code using NHibernate?

0
 
Jim CakalicSenior Developer/ArchitectCommented:
Sorry for the confusion. Looks like I went to the 1.0.2 version of the NHibernate reference guide. If you are using the 1.2.0 version then the reference guide suggests:
    int count = (int) session.CreateQuery("select count(*) from ....").UniqueResult();
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.