hibernate hql子查询问题

[wangyuzx1985]

String hql = "select a.alarmDataDate,a.alarmMessageContent  from (select "
                 + "a.alarmDataDate, " + "a.alarmMessageContent "  + "from "
	        + "BiAlarmMessageInfo a, BiAlarmRuleInfo b " + "where "
	        + "a.alarmRuleId = b.alarmRuleId and "
	        + "b.alarmBusinessTypeId = ? "
	        + "order by a.alarmBuildTime desc) "+"where "
                 + "rownum<60";

求解 ， 为什么错了，
A表和B表关联后,取A表的两个字段，然后根据ROWNUM取前60条记录。

[thislovebyyou]

试试
String sql = "..........."
Session session = getSession();
SQLQuery query = session.CreatSQLQuery(sql);

[thislovebyyou]

query.list()执行·

[wangyuzx1985]

NO 人在？!~#@~

[wrxangeline]

"select a.alarmDataDate,a.alarmMessageContent from (select "
+ "a.alarmDataDate, " + "a.alarmMessageContent " + "from "
+ "BiAlarmMessageInfo a left join BiAlarmRuleInfo b " + "where "
+ "on a.alarmRuleId = b.alarmRuleId where"
+ "b.alarmBusinessTypeId = ? "
+ "order by a.alarmBuildTime desc) "+"where "
+ "rownum<60";

[wrxangeline]

把第三行 最后的+ "where "去掉 不好意思

[qingyuan18]

你这是sql还是HQL?

楼主你要始终明白一点：HQL是面向对象的查询，不是所有sql放到HQL里都可以直接用的

[licip]

你这是sql语言吧。不是hql
你用了oracle中的伪列rownum你要搞清楚，它是在查询出数据后再编号上去的，所以你这种用法是错误的。
可以这样，我指的是sql:
String hql = "select a.alarmDataDate,a.alarmMessageContent from (select rownum rn"
+ "a.alarmDataDate, " + "a.alarmMessageContent " + "from "
+ "BiAlarmMessageInfo a, BiAlarmRuleInfo b " + "where "
+ "a.alarmRuleId = b.alarmRuleId and "
+ "b.alarmBusinessTypeId = ? "
+ "order by a.alarmBuildTime desc) "+"where "
+ "rn<60";

[wangyuzx1985]

这个是hql hibernate里面写的。

select "+ "a.alarmRuleId  "  + "from "
                 + "BiAlarmMessageInfo a, BiAlarmRuleInfo b " + "where "
                 + "a.alarmRuleId = b.alarmRuleId and "
                 + "b.alarmBusinessTypeId = ? "
                 + "order by a.alarmBuildTime desc

这样可以。 加了子查询不行了

[wangyuzx1985]

[Quote=引用 5 楼 licip 的回复:]
你这是sql语言吧。不是hql
你用了oracle中的伪列rownum你要搞清楚，它是在查询出数据后再编号上去的，所以你这种用法是错误的。
可以这样，我指的是sql:
String hql = "select a.alarmDataDate,a.alarmMessageContent from (select rownum rn"
+ "a.alarmDataDate, " + "a.……
[/Quote]
sql的话是没问题的。但是hql就不对， 那个大虾能给个正确的写法？
就是从关联排序后的表里取前60条记录。

[thislovebyyou]

hql语句貌似不支持select ******* in(select ******)!

[luman2002]

hql语句的from不支持子查询，而where支持
楼主可以改为这样的
select a from A a where a.id in (select a1.id from A a1,B b where b.id=a1.id and b.tyeid=? order by a.time desc limit 60);

[luman2002]

String hql = "select a.alarmDataDate,a.alarmMessageContent  from "
             + " BiAlarmMessageInfo  a where a.alarmRuleId in (select "
             + "a1.alarmRuleId"  + "from "
            + "BiAlarmMessageInfo a1, BiAlarmRuleInfo b " + "where "
            + "a1.alarmRuleId = b.alarmRuleId and "
            + "b.alarmBusinessTypeId = ? "
            + "order by a1.alarmBuildTime desc limit 60)"

[wangyuzx1985]

hibernate 不支持伪列 上面的方法 貌似不可行。

[wangyuzx1985]

limit 也不支持