-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
boolee
 Post subject: hibernate - limit and offset in select query
PostPosted: Thu Aug 04, 2005 11:40 pm 
Newbie

Joined: Thu May 05, 2005 8:48 am
Posts: 4
Hibernate version: 3



Hi All

is there any way to use limit and offset features of mysql in hibernate queries?
For exmple I have 100 rows in my results. I want to display the results page by page. How can I select rows starting from a particular index (for example from 10 to 20 instead of starting at the first record). The 'setMaxRows()' function can limit the number of results, but how can I specify the offset ? Thanks in advance.
regards
boolee
[/b]
Top
 Profile  
 
jdl
 Post subject:
PostPosted: Thu Aug 04, 2005 11:45 pm 
Regular
Regular

Joined: Thu May 26, 2005 2:08 pm
Posts: 99
This is covered in the documentation and the API.

http://www.hibernate.org/hib_docs/v3/ap ... esults(int)
http://www.hibernate.org/hib_docs/v3/ap ... Result(int)
Top
 Profile  
 
pcasey
 Post subject: Re: hibernate - limit and offset in select query
PostPosted: Fri Aug 05, 2005 12:05 am 
Beginner
Beginner

Joined: Wed Apr 13, 2005 2:03 pm
Posts: 34
boolee wrote:
Hibernate version: 3



Hi All

is there any way to use limit and offset features of mysql in hibernate queries?
For exmple I have 100 rows in my results. I want to display the results page by page. How can I select rows starting from a particular index (for example from 10 to 20 instead of starting at the first record). The 'setMaxRows()' function can limit the number of results, but how can I specify the offset ? Thanks in advance.

regards
boolee
[/b]


You mean something like:

Query q = HibHelper.getSession().createQuery(sql);
q.setFirstResult(10);
q.setMaxResults(10);
List rc = q.list();
Top
 Profile  
 
alskor
 Post subject:
PostPosted: Fri Nov 10, 2006 8:29 am 
Beginner
Beginner

Joined: Mon Jul 03, 2006 5:40 am
Posts: 20
Location: Russia
well, in theory this is right.
but in fact SOMETIMES Hibernate 3.2 generates incorrect SQL query for mysql:

Code:
      // get last N runs for the user
      Criteria criteria = session.createCriteria(RunBean.class)
              .add(Restrictions.eq("user", user))
              .setFirstResult(0)
              .setMaxResults(runsNumber);

      List list = criteria.list();


Code:
org.hibernate.exception.SQLGrammarException: could not execute query
   org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   org.hibernate.loader.Loader.doList(Loader.java:2148)
   org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   org.hibernate.loader.Loader.list(Loader.java:2024)
   org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   org.hibernate.impl.SessionImpl.list(SessionImpl.java:1533)
   org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)


I see the generated SQL statement is not valid for MySQL ("select top ..." is not accepted):

Code:

select top ? this_.id as id9_6_, this_.start_date ...................where this_.icl_user_id=?
Top
 Profile  
 
alskor
 Post subject:
PostPosted: Fri Nov 10, 2006 9:18 am 
Beginner
Beginner

Joined: Mon Jul 03, 2006 5:40 am
Posts: 20
Location: Russia
OOOOOPS! Sorry!! this is my fault
forgot to update hibernate.cfg.xml file.
now it is OK:
Code:
      <!-- SQL dialect -->
      <property name="dialect">
         org.hibernate.dialect.MySQL5Dialect
      </property>

I had default "HSQLDialect" there.
Top
 Profile  
 
anudeepshetty
 Post subject: Re: hibernate - limit and offset in select query
PostPosted: Wed Aug 03, 2011 12:10 pm 
Newbie

Joined: Wed Jul 13, 2011 9:52 am
Posts: 2
Hi pcasey,

how will the order by applied to the search results .?, i.e. say i have list of values 1 2 3 4 5 in database
by using the q.setFirstResult(0); q.setMaxResults(3); (order by asc)

i will get the values 1 2 3, now when i apply the order by(desc to the same query) clause i was expecting 3 2 1, but i am getting 5 4 3.

this means order by is being applied to the whole list first and then the clipping is done .

is there any way i would get the clipped list first then applying the order by to it in a single query.?


Thanks in Advance
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.