用hibernate实现sqlserver分页的问题

下面这段伪代码,当显示第一页的时候正常,但是第二页开始就会报错了,错误是:
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

 String sql="select sd.sensorInputDataID as 数据ID,e.equipmentName as 设备名,sd.date as 日期  "+  
                "from SensorInputData as sd left join Sensor as s on sd.sensorId=s.sensorId left join EquipmentMessage as e on s.equipmentId=e.equipmentId "+ 
                "where sd.date between '2015/08/13 00:00:00' and '2015/08/13 23:59:59'  and e.equipmentName like'%设备5%' union "+ 

                "select sd.sensorInputDataID as 数据ID,e.equipmentName as 设备名,sd.date as 日期  "+  
                "from SensorInputData as sd left join Sensor as s on sd.sensorId=s.sensorId left join EquipmentMessage as e on s.equipmentId=e.equipmentId "+ 
                "where sd.date between '2015/08/13 00:00:00' and '2015/08/13 23:59:59'  and e.area like'%设备5%' order by sd.date asc";

                session.createSQLQuery(sql).setFirstResult((pageNow-1)*pageSize).setMaxResults(pageSizeInt).list();
0

3个回答

这个需求有个问题:

  1. 先解决你的问题,sql1 union sql2 order by xx没有意义,除非是(sql1 union sql2) order by xx ,你想想

  2. join合并查询,还有Union联合查询,这种sql怎么优化,cost都不会太低,对这个,你还要分页!!建议重构逻辑,生成结果表,在页面里简单分页查询。

1
yu766588220
花公子丶 感谢
接近 4 年之前 回复
1

用存储过程分页试试,换种思路

0
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!