oracle中分页问题(带有order by)

[CNSDYT]

软件环境：
struts+hibernate
eclipse
oracle
配置文件：
sql = "FROM TVoyage WHERE STATUE between '2' and '4' ORDER BY ARRIVE_DATE DESC";
q = session.createQuery(sql);
q.setFirstResult((np - 1) * newspagecount);
q.setMaxResults(newspagecount);
it = q.iterate();
错误提示信息：
Hibernate: select * from ( select tvoyage0_.VOYAGEKEY as x0_0_, tvoyage0_.VESSEL
ENG as x1_0_, tvoyage0_.VESSELCHN as x2_0_, tvoyage0_.ARRIVE_DATE as x3_0_, tvoy
age0_.REGION as x4_0_, tvoyage0_.I_VOYAGE as x5_0_, tvoyage0_.E_VOYAGE as x6_0_,
tvoyage0_.TO_PORT_CHN as x7_0_, tvoyage0_.I_E as x8_0_, tvoyage0_.CUSTOM_BH as
x9_0_, tvoyage0_.WZH as x10_0_, tvoyage0_.YARDMAN as x11_0_, tvoyage0_.WQ_NAME a
s x12_0_, tvoyage0_.SHIPCODE as x13_0_ from T_VOYAGE tvoyage0_ where (STATUE bet
ween '2' and '4' ) order by ARRIVE_DATE DESC ) where rownum <= ?
WARN (JDBCExceptionReporter.java:57) - SQL Error: 907, SQLState: 42000
ERROR (JDBCExceptionReporter.java:5 - ORA-00907: 缺少右括号

WARN (JDBCExceptionReporter.java:57) - SQL Error: 907, SQLState: 42000
ERROR (JDBCExceptionReporter.java:5 - ORA-00907: 缺少右括号
你的分析：
如果把order by 去掉的话则就不会出错，在sql server中没有遇到类似的问题。看到hbiernate的show sql: from T_VOYAGE tvoyage0_ where (STATUE between '2' and '4' ) order by ARRIVE_DATE DESC ) where rownum <= ?
hibernate中自动把后面的where放到了order by 后面了。请问你们遇到这样的问题如何解决的阿？？？？？？

[sasacat]

SELECT *
FROM (SELECT tvoyage0_.voyagekey AS x0_0_, tvoyage0_.vesseleng AS x1_0_,
tvoyage0_.vesselchn AS x2_0_, tvoyage0_.arrive_date AS x3_0_,
tvoyage0_.region AS x4_0_, tvoyage0_.i_voyage AS x5_0_,
tvoyage0_.e_voyage AS x6_0_, tvoyage0_.to_port_chn AS x7_0_,
tvoyage0_.i_e AS x8_0_, tvoyage0_.custom_bh AS x9_0_,
tvoyage0_.wzh AS x10_0_, tvoyage0_.yardman AS x11_0_,
tvoyage0_.wq_name AS x12_0_, tvoyage0_.shipcode AS x13_0_
FROM t_voyage tvoyage0_
WHERE (statue BETWEEN '2' AND '4')
ORDER BY arrive_date DESC)
WHERE ROWNUM <= ?

你自己看呢，子表里用这个order by 并无意义。所以oracle认为这句ORDER BY arrive_date DESC)是错的，连带后面的括号也失效了，所以会报少了一个右括号

[CNSDYT]

那么在oracle下hibernate里如何分页阿。

[bobfang]

数据库版本？

[CNSDYT]

数据库版本 oracle 8.05

[CNSDYT]

难道是驱动的问题。

[nowait]

8i不支持子查询中的order by语法

[bobfang]

8.0.5不支持子查询中的order by语法

[heyixiang]

select /*+first_rows*/* from (select * from 表名 where rownum<=20*第几页 order by 排序的字段 desc ) where rownum<=20 order by 排序的字段


每页20行记录

[CNSDYT]

纳在这种情况下，如何利用hibernate分页阿，做过的给解答一下阿。

[heyixiang]

上面我写错了。

N为第几页,每页20行记录.

select * from (select * from (select a.*,rownum rn from 表名 order by 排序的字段) b where b.rn<=20*N) c where c.rn>20*(N-1)