?
Solved

Hibernate. retrieve the size of a query result

Posted on 2007-07-22
6
Medium Priority
?
628 Views
Last Modified: 2012-06-27
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?


0
Comment
Question by:dportabella
  • 3
  • 3
6 Comments
 

Author Comment

by:dportabella
ID: 19542600
Some more points...
0
 
LVL 19

Accepted Solution

by:
Jim Cakalic earned 1500 total points
ID: 19542728
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
 

Author Comment

by:dportabella
ID: 19542770
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 19543156
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
 

Author Comment

by:dportabella
ID: 19543453
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
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 19543739
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question