按时间查询最近的5条记录: LampStateTestInfo 为实体类 ,对应数据库表 LampStateTestInfo (oracle数据库)[code=JavaString]
sql = "select * from (select * from LampStateTestInfo where lampID=lampID "+
order by stateTime desc) where rownum <=5";
SQLQuery query = session.createSQLQuery(sql);
query.setInteger("lampID",lampID);
query.addEntity("LampStateTestInfo", LampStateTestInfo.class);
List<LampStateTestInfo> list = query.list();
return list;
[/code]打印结果,但是返回5条记录都是一样的 都是最后一条;
拿SQL语句在ORACLE单独测试返回的是最近的5条不同的记录,符合要求。
怎么会不一致,求解·
sql = "select * from (select * from LampStateTestInfo where lampID=lampID "+
order by stateTime desc) where rownum <=5";
SQLQuery query = session.createSQLQuery(sql);
query.setInteger("lampID",lampID);
query.addEntity("LampStateTestInfo", LampStateTestInfo.class);
List<LampStateTestInfo> list = query.list();
return list;
[/code]打印结果,但是返回5条记录都是一样的 都是最后一条;
拿SQL语句在ORACLE单独测试返回的是最近的5条不同的记录,符合要求。
怎么会不一致,求解·
sql = "select * from LampStateTestInfo where lampID=lampID "+
order by stateTime desc";
SQLQuery query = session.createSQLQuery(sql);
query.setInteger("lampID",lampID);
query.addEntity("LampStateTestInfo", LampStateTestInfo.class);
query.setFirstResult(0);
query.setMaxResults(5);
List <LampStateTestInfo> list = query.list();
Hibernate: select * from ( select * from (select * from LampStateTestInfo where lampID=? order by stateTime desc) where rownum <=5 ) where rownum <= ?设lampID=2818 rownum =5 的到的结果是不同的,符合结果
但是在JAVA中
List<LampStateTestInfo> list = dao.getLampTrend(2818);
for (LampStateTestInfo lampStateTestInfo : list) {
System.out.println(lampStateTestInfo.getStateTime());
}
打印的都是一个记录;
query.setFirstResult(0);
query.setMaxResults(5);debug打印出hibernate转化HQL后的sql:select * from (select * from LampStateTestInfo where lampID=? order by stateTime desc) where rownum <=5在oracle测试是不同的记录
query.setMaxResults(5);
后,sql也改了,你改了吗?
sql = "select * from LampStateTestInfo where lampID=lampID "+
order by stateTime desc";
query.setFirstResult(0);
query.setMaxResults(5);hibernate转化HQL后的sql:Hibernate: select * from ( select * from (select * from LampStateTestInfo where lampID=? order by stateTime desc) where rownum <=5 ) where rownum <= ?不加:select * from (select * from LampStateTestInfo where lampID=? order by stateTime desc) where rownum <=5
加 hibernate打印的是
select * from ( select * from LampStateTestInfo where lampID=? order by stateTime desc ) where rownum <= ?不加
select * from (select * from LampStateTestInfo where lampID=? order by stateTime desc) where rownum <=5
query.setFirstResult(0);
query.setMaxResults(5);
sql 也改成了 select * from LampStateTestInfo where lampID=? order by stateTime desc这就不应该再出错了。
楼主
sql 改成 select * from LampStateTestInfo where lampID=? order by stateTime desc
然后不加下面这两行
query.setFirstResult(0);
query.setMaxResults(5);看是不是按时间排序,且都查询出了。
sql 改成 select * from LampStateTestInfo where lampID=? order by stateTime desc
然后不加下面这两行
query.setFirstResult(0);
query.setMaxResults(5);--------------------------------
在 oracle测试没问题 ,符合想要的结果 按时间排序 都是不同的记录
但在JAVA中运行得到相同的记录,都是时间最近的一条
public List<LampStateTestInfo> getLampTrend(int lampID) {
。
String sql = "select * from LampStateTestInfo where lampID=:lampID order by stateTime desc";
SQLQuery query = session.createSQLQuery(sql);
query.setInteger("lampID",lampID);
query.addEntity("LampStateTestInfo", LampStateTestInfo.class);
List<LampStateTestInfo> list = query.list();
System.out.println(list.get(0).getStateTime()); //这里打印
System.out.println(list.get(1).getStateTime()); //这里打印
。。
return list;
sql = "select * from LampStateTestInfo where lampID=lampID and rownum <=5 "+
"order by stateTime desc)";
试试看
应采用联合主键解决
低级错误 还以为SQL语句错了