Select first 100 objects

Zloof · **Joined:** Wed May 24, 2006 9:52 am **Posts:** 5

I have a database table (approx 100000 objects)
i need to obtain the first hundred.
Is it possible to do it using HQL. without using native queries like (select top 100 from myTbl)

batmat · **Posted:** Tue Dec 19, 2006 5:14 am

You should use a ScrollableResults, as described here : http://blog.hibernate.org/cgi-bin/blosx ... 2004/08/26

Code:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

ScrollableResults customers = session.getNamedQuery("GetCustomers")
   .scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
   Customer customer = (Customer) customers.get(0);
   
   //do what you want here...

   if ( ++count % 20 == 0 ) {
      //flush a batch of updates and release memory:
      session.flush();
      session.clear();
   }
}

tx.commit();
session.close();

I guess that even if you're using NHibernate, you can use a close way for doing this.

Zloof · **Joined:** Wed May 24, 2006 9:52 am **Posts:** 5

i found a better solution

NHibernate.IQuery query = DBManager.Session.CreateQuery("from Race as r ");

query.SetFirstResult(offset);
query.SetMaxResults(numberOfRecords);
System.Collections.IList list = query.List();