yu766588220
花公子丶
采纳率60%
2015-10-20 11:00 阅读 2.6k

用hibernate实现sqlserver分页的问题

15

下面这段伪代码,当显示第一页的时候正常,但是第二页开始就会报错了,错误是:
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();
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    54powerman 54powerman 2015-10-21 02:00

    这个需求有个问题:

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

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

    点赞 1 评论 复制链接分享
  • devmiao devmiao 2015-10-20 23:43
    点赞 1 评论 复制链接分享
  • lizhenlzlz lizhenlzlz 2015-10-23 01:33

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

    点赞 评论 复制链接分享

相关推荐