hibernate 原生sql 聚合函数问题

[specturn]

code=Java]

public List<LampInfo> findLampsBoxID(int boxID, int boxgruop) {

List<LampInfo> list = null;
Session session = this.getSession();
try {
String sql = "FROM LampInfo "
+ " WHERE LampID IN(SELECT max(LampID) FROM LampInfo"
+ " WHERE IsDoubleLamp='Y' and boxID= " + boxID + " and boxgruop= " + boxgruop + " "
+ " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1))"
+ " UNION ALL FROM LampInfo WHERE NVL(IsDoubleLamp,'')<>'Y' and "
+ " and boxID= " + boxID + " and boxgruop= " + boxgruop + " order by groupnum ";

Query query = session.createQuery(sql);
list = query.list();
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally {
session.close();
}

}
[/code]

LampID boxID boxgruop groupnum 都为LampInfo的字段

程序报错No data type for node .. AggregateNode: 'max' （SSH框架，SQL语句单独测试过没错，关键在SSH框架下MAX MIN等聚合函数会报错

[TKD03072010]

不懂
友情帮顶
继续关注学习

[菜继续]

接点分

[Inhibitory]

String sql = "FROM LampInfo "
这个明显不是SQL语句，这个还是HQL的写法。

SQL的要如select * from tableName where............

[小绵羊]

貌似hibernate不支持子查询？？
http://loomz.blog.163.com/blog/static/5211155920079170451872/
用原生sql吧
session.createSQLQuery(sql);

[BUG胡汉三]

SELECT max(LampID) FROM LampInfo"
  + " WHERE IsDoubleLamp='Y' and boxID= " + boxID + " and boxgruop= " + boxgruop + " "
  + " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1)"

LZ都max了为什么还要去GROUP BY呢？我记得好像在Hibernate中使用聚合函数后、就不能在用Order By跟GROUP By这些东西了！！！

[BUG胡汉三]

[Quote=引用 7 楼 x19881216 的回复:]
貌似hibernate不支持子查询？？
http://loomz.blog.163.com/blog/static/5211155920079170451872/
用原生sql吧
session.createSQLQuery(sql);
[/Quote]

LS Hibernate是绝对支持子查询的！！！

[zn85600301]

你这是HQL 不是SQL

改成SQL 然后用createSQLQuery 将返回的数据项封装成对象就行了

[Fly_m]

hql应该是支持的，将详细错误打出来看？是否是返回数据时出错了？

[specturn]

问题解决
一 GROUP BY 要跟聚合函数配合用
二 UNION ALL （UNION）跟 ORDER BY 一起用会报错 要再加一层用括号括起来

原生 SQL 要用 SQLQuery query = session.createSQLQuery(sql)
我开始贴错了HQL的
因为HQL不支持 UNION ALL （UNION）故用SQL

Session session = this.getSession();
		try {
	
		 String sql =  " select * FROM   (SELECT * from  LampInfo WHERE LampID IN(SELECT MIN(LampID) FROM LampInfo " 
         + " WHERE IsDoubleLamp='Y'  and boxID = " + boxID + " and boxgruop = " +boxgruop + "  "
         + " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1)) ORDER BY LampID) t1 " 
         + " UNION ALL select * FROM  (SELECT * from LampInfo WHERE IsDoubleLamp<>'Y' "
         + " and boxID = " + boxID + " and boxgruop = " + boxgruop + " ORDER BY LampID )  t2 ";
							
		 SQLQuery query  = session.createSQLQuery(sql).addEntity("LampInfo",LampInfo.class);
		 List<LampInfo>  list = query.list();

		 return list;
		 
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}finally {
			session.close();
		}

	}