-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: LIMIT keyword is not supported in HQL or JPQL
PostPosted: Thu Nov 17, 2016 12:25 pm 
Newbie

Joined: Mon Nov 14, 2016 4:16 pm
Posts: 5
Is this syntactically correct?

Code:
Query query3 = session.createSQLQuery("SELECT scheduledprocedure.id FROM   carecube.scheduledprocedure ORDER BY scheduledprocedure.id LIMIT 1 OFFSET :q1");
query3.setParameterList("q1", q1 );


I'm passing in the number 58, which is the number returned from a list called q1. I've written the parameter into the query as ':q1' but unfortunately hibernate cannot extract the resultset.

What am I missing? thank you.


Top
 Profile  
 
 Post subject: Re: Using query.setParameterList
PostPosted: Thu Nov 17, 2016 1:06 pm 
Newbie

Joined: Mon Nov 14, 2016 4:16 pm
Posts: 5
I've found out that Hibernate does not recognise offsetting.

I used setFirstResult(int) and setMaxResults(int) to achieve this :-)


Top
 Profile  
 
 Post subject: Re: Using query.setParameterList
PostPosted: Fri Nov 18, 2016 3:23 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Hibernate does not support the LIMIT keyword because that's only recognized by MySQL and PostgreSQL.

By using the:

- setFirstResult
- setMaxResults

Your query is guaranteed to work on all databases supported by Hibernate. For instance, on Oracle 11g, it will use a derived table to limit the result set by ROWNUM. On Oracle 12c, it can use the SQL 2008 syntax: OFFSET N ROWS FETCH NEXT M. But you don't have to worry about the database-specific syntax since Hibernate will take care of that.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: LIMIT keyword is not supported in HQL or JPQL
PostPosted: Fri Dec 16, 2016 11:10 am 
Newbie

Joined: Fri Dec 09, 2016 5:27 pm
Posts: 8
I am not seeing that behavior in 5.2.5. Looking at the source code, in every LimitHandler (except for SQLServer2005LimitHandler) I see the following code in processSql():
Code:
      if (LimitHelper.hasFirstRow( selection )) {
         throw new UnsupportedOperationException( "query result offset is not supported" );
      }


I am using both setMaxResults() and setFirstResult() and I see that exception every time.

Update - I tried using SQLServer2005LimitHandler and my queries are now working as they did with previous Hibernate versions. I was previously using the SQLServerDialect and its default LimitHandler seemed to support MaxResults and FirstResult. Now I will specifically set my (SQL) dialect to use SQLServer2005LimitHandler.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

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.