今天做一个稍微复杂的查询,是用来统计的,但是报错:
Cannot create TypedQuery for query with more than one return using requested result type
代码如下:
1. NamedQuery:
@NamedQuery(name = "Detect.CountByUnitAndResultDx",
query = "select "
+ "a.detectUnitId, "
+ "count(*) as total, "
+ "(select count(*) from Detect a where a.resultDx = 1 and a.detectUnitId =:detectUnitId) as good, "
+ "(select count(*) from Detect a where a.resultDx = 0 and a.detectUnitId =:detectUnitId) as bad "
+ "from Detect a "
+ "where a.detectUnitId = :detectUnitId")
2. 查询代码:
TypedQuery<DetectCountOfUnit> query = em.createNamedQuery("Detect.CountByUnitAndResultDx", DetectCountOfUnit.class);//这句报错
query.setParameter("detectUnitId", id);
List<DetectCountOfUnit> counts = query.getResultList();
if(counts !=null ){
detectCountOfUnit = counts.get(0);
}
网上找原因,找了一圈,在StackOverflow上面找到了答案,原因如下:
在TypedQuery里面,指定了DetectCountOfUnit.class这个类,但是在查询语句中,返回的却是不同的几个字段:
a.detectUnitId,
count(*) as tatal,
(select .....,
所以会报错,也就是返回类型并不匹配,Hibernate无法将分散的字段拼起来填到一个类中。那怎样才能匹配呢?
如果要改查询语句,那至少要改成如下形式:
select a from DetectCountOfUnit a ...
如果要改查询代码,那么改法大致如下:
Query query = em.createNamedQuery("Detect.CountByUnitAndResultDx");
query.setParameter("detectUnitId", id);
object = query.getSingleResult();
这个返回的object,在服务器端没法处理,直接返回给客户端好了,是字符串数组的形式,例如:[160105, 40, 35, 5],也就是和数据库里查出来的结果集形式一致。
如果返回多行记录,那么就用Object[]