-->
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
  [ 12 posts ] 
  Previous topic | Next topic 
Author Message
mbucknam
 Post subject: is ordering a pageable query possible?
PostPosted: Fri Jan 30, 2004 7:15 pm 
Newbie

Joined: Tue Oct 14, 2003 9:18 am
Posts: 10
Location: Charleston, SC USA
I have a pageable query set up and would like to order by a column in the underlying table but it doesn't appear to be possible. I want to pull rows out 15 at a time but order the entire table of 1000+ rows by different columns depending on what the user selects.

I looked at the sql generated by hibernate and he is doing:

select * from (select columns from table where ...) where rownum < whatever

This works great but when I put an order by in the HQL I get an error.
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jan 30, 2004 7:17 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Quote:
I get an error.


Great description. What is the error? Generated SQL? Executed HQL? Please give a proper description of the problem.
Top
 Profile  
 
mbucknam
 Post subject:
PostPosted: Fri Jan 30, 2004 7:21 pm 
Newbie

Joined: Tue Oct 14, 2003 9:18 am
Posts: 10
Location: Charleston, SC USA
Sorry. The error is from oracle and it says unmatched right parentheses but that is not really the problem. I read that you cannot order by a subselect so given that Hibernate creates the sql for a pageable query as shown above, there isn't really a solution using that approach. So the question really is does anyone know of a way to create a pageable query ordered by the entire contents of the underlying table on some arbitrary column?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Jan 30, 2004 7:33 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
How about you give us something to work with here!

What is the actual generated SQL? What is the full text of the exception?


TIA.
Top
 Profile  
 
mbucknam
 Post subject:
PostPosted: Mon Feb 02, 2004 1:18 pm 
Newbie

Joined: Tue Oct 14, 2003 9:18 am
Posts: 10
Location: Charleston, SC USA
OK. back at work so I can connect to target Oracle database:

My code snippet:
--------------------
Query query = session.createQuery(queryBuffer.toString());

/*--------------------------------------------------------------------*
* Set query parameters. *
*--------------------------------------------------------------------*/
query.setProperties(seatInfo);
if (history != null) {
query.setProperties(history);
}

/*--------------------------------------------------------------------*
* Set paging variables if passed in *
*--------------------------------------------------------------------*/
if (pageSize > 0) {
query.setMaxResults(pageSize);
query.setFirstResult(pageSize * pageNumber);
}
List finds = query.list();

+++++++++++++++++++++++++++++++++++++++++++++++

HQL I pass in queryBuffer:
-------------------------------
select seatInfo from SeatInfo as seatInfo where seatInfo.seatStatus like :seatStatus and seatInfo.seatOrgCode like :seatOrgCode order by seatInfo.seatName

+++++++++++++++++++++++++++++++++++++++++++++++

Stack Trace including generated SQL:
-------------------------------------------

Hibernate: select * from ( select seatinfo0_.ID as ID, seatinfo0_.SEAT_ID as SEAT_ID, seatinfo0_.SEAT_NBR as SEAT_NBR, seatinfo0_.SEAT_STATUS as SEAT_STA4_, seatinfo0_.SEAT_ORGCODE as SEAT_ORG5_, seatinfo0_.SEAT_NAME as SEAT_NAME, seatinfo0_.SEAT_CLIN as SEAT_CLIN, seatinfo0_.SEAT_TYPE as SEAT_TYPE, seatinfo0_.SEAT_FEE as SEAT_FEE, seatinfo0_.SUPPORT_FEE as SUPPORT10_, seatinfo0_.SEAT_CHARGE_JON as SEAT_CH11_, seatinfo0_.SEAT_ERROR_JON as SEAT_ER12_, seatinfo0_.AUDIT_USER_ID as AUDIT_U13_, seatinfo0_.AUDIT_DATE as AUDIT_DATE from nmcibsa.SEAT_INFO seatinfo0_ where (seatinfo0_.SEAT_STATUS like ? )and(seatinfo0_.SEAT_ORGCODE like ? ) order by seatinfo0_.SEAT_NAME ) where rownum <= ?
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 907, SQLState: 42000
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-00907: missing right parenthesis

Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 907, SQLState: 42000
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-00907: missing right parenthesis

Feb 2, 2004 12:10:19 PM net.sf.hibernate.JDBCException <init>
SEVERE: Could not execute query
java.sql.SQLException: ORA-00907: missing right parenthesis

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at mil.spawar.bsa.dataaccess.hibernate.SeatInfoDAO.findBy(SeatInfoDAO.java:161)
at mil.spawar.bsa.nmciservicecenter.actions.SeatInfoSearchAction.search(SeatInfoSearchAction.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at mil.spawar.bsa.system.HibernateStrutsFilter.doFilter(HibernateStrutsFilter.java:56)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:233)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:594)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:209)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:589)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:666)
at java.lang.Thread.run(Thread.java:536)


+++++++++++++++++++++++++++++++++++++++++++++++

I think Oracle is barking at the order by in the subselect. I get the same error when trying to execute the generated SQL from my SQL query tool. Maybe there is a better approach to what I am trying to do?
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Feb 02, 2004 4:45 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
This kind of query works just perfectly on my Oracle install. Also I notice that noone else seems to be having any problem with this functionality (and we do have thousands of Oracle users). So I don't know what yr problem is...
Top
 Profile  
 
mbucknam
 Post subject:
PostPosted: Mon Feb 02, 2004 5:52 pm 
Newbie

Joined: Tue Oct 14, 2003 9:18 am
Posts: 10
Location: Charleston, SC USA
Further research indicates this does NOT work in early versions of Oracle 8.x.x but may work in later versions or Oracle 9.x.x -- I don't know as I am stuck with an 8.x.x version.

Gavin, are you using a later version of Oracle than 8.0.6.0.0 which is our production version? If so that would explain why it works for you and not for me.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Mon Feb 02, 2004 6:00 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I am on Oracle 9, but I'm guessing that I would have heard screams of protest a few months ago if this did not work for current versions of Oracle 8.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Tue Feb 03, 2004 4:46 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Oracle 8.0 is a really bad and quite different to 8.1.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.

Top
 Profile  
 
mbucknam
 Post subject:
PostPosted: Tue Feb 03, 2004 10:01 am 
Newbie

Joined: Tue Oct 14, 2003 9:18 am
Posts: 10
Location: Charleston, SC USA
Confirmed that order by in a subselect/subquery is allowed in Oracle 8.1.7.0.0 and Gavin confirmed it works in 9.x -- for what it's worth if it ever comes up again.


Thanks for your attention to this Gavin. Your help is truly appreciated.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Feb 03, 2004 2:06 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
No problem, its good that we are now aware of this. Note that if you really need to run on Oracle 8.0, you could easily customize the dialect.
Top
 Profile  
 
chughes
 Post subject:
PostPosted: Fri Jun 18, 2004 2:03 am 
Newbie

Joined: Fri Jun 18, 2004 1:59 am
Posts: 1
Location: Sydney, Australia
Just to add that using Hibernate 2.1.4 (using either Oracle Dialect), this issue causes the standard "build eg" to fail if attempting to run against Oracle 8.0.

Not an issue if using a later Oracle version.
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
  [ 12 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.