|
Hibernate统计查询手记
TAG:Hibernate,group by,order by ，order by if
前段时间在做考试系统的时候，遇到了这样一个统计功能，学员进行考试之后，所有的答案都存储在数据库当中，项目需求是要得到正确率最高的前十名，并显示在网页上。这就需要使用Hibernate对学员的答案进行统计，在完成了这个功能的时候遇到了很多的问题，所幸都得到了解决，在此记录下来，与大家共勉。 所涉及到的类结构如下 public class Answer { private Boolean corrected = false; private ScoreInfo scoreInfo; //其它无关属性省略…. } public class ScoreInfo { private Student student; //其它无关属性省略…. } 项目采用的数据库是MySQL数据库，首先我就根据数据库写出了查询语句 SELECT COUNT(IF(answer_student.corrected=1,TRUE,NULL))/COUNT(*) AS accuracy, answer_student.stu_id FROM (SELECT answer.corrected AS corrected,student.id AS stu_id,student.* FROM student,answer,score_info WHERE answer.score_info_id=score_info.id AND score_info.student_id=student.id) answer_student GROUP BY stu_id ORDER BY accuracy DESC LIMIT 0,10 这个可以在MySQL中正常运行，并显示出正确率和学员所有的信息，在我尝试将这个SQL语句转换成HQL语句，得到了查询一个学生答案正确率的语句，语句如下：
select count(if(answer.corrected=true))/count(*) from Answer a where a.scoreInfo.student=? 运行之后，出错了，查询API之后，发现Hibernate不支持count中加上if条件，别的聚集函数也不行 ......郁闷，换子查询吧 改成以下语句 select ((select count(*) from Answer where corrected=true) /count(*))from Answer a where a.scoreInfo.student=? 得到了一个语法错误，猜测是不是count(*)子句执行之后是集合，于是加了一个[0] select ((select count(*) from Answer where corrected=true)[0] /count(*))from Answer a where a.scoreInfo.student=? 得到的结果是不能进行强制转换(LiteralNode不能转成FromReferenceNode) 只能另想其他办法了，找了N多资料之后，看到了一种特殊的用法 hibernate的sum函数支持 case when 条件 then 成立执行的语句 else 不成立执行的语句 end 换到查询里面就变成了： select sum(case when a.corrected=true then 1 else 0 end)/count(*)from Answer a where a.scoreInfo.student=? 查询了一下，终于能运行了，不过结果都是0，于是，我把/换成了,看看两个的值，都取出来了，正常，估计就是整形相除的问题，小数都忽略了，再改： select sum(case when a.corrected=true then 1 else 0 end)*1.0/count(*)from Answer a where a.scoreInfo.student=? 终于OK了，把不能count的这个问题搞定了，可以看到一个学员的正确率了，接下来的问题就是进行分组查询了，调整一下语句，如下 select sum(case when a.corrected=true then 1 else 0 end)*1.0 / count(*),a.scoreInfo.student from Answer a group by a.scoreInfo.student 嗯，很好，没有出问题，正确率和学员都被查询出来了，下面再进行排序和截取就好了。 select sum(case when a.corrected=true then 1 else 0 end)*1.0 / count(*) as accuracy,a.scoreInfo.student from Answer a group by a.scoreInfo.student order by accuracy desc 给准确率起了一个别名，最后根据这个进行逆向排序就应该可以了，跑了一下，又报错了，提示是Unknown column 'accuracy' in 'order clause'，在排序的时候找不到'accuracy'列，不对啊，我明明起了别名啊，观察了一下Hibernate产生的SQL语句 select sum(case when answer0_.corrected=1 then 1 else 0 end)*1.0/count(*) as col_0_0_, scoreinfo1_.student_id as col_1_0_, student2_.id as id0_, student2_.version as version0_, student2_.passwd as passwd0_, student2_.enabled as enabled0_, student2_.username as username0_, student2_.description as descript6_0_, student2_.name as name0_ from answer answer0_, score_info scoreinfo1_ inner join student student2_ on scoreinfo1_.student_id=student2_.id where answer0_ .score_info_id=scoreinfo1_.id group by scoreinfo1_.student_id order by accuracy desc 发现确实没有我起的别名，hibernate会为每一列都起别名，自己起的就无效了，而hibernate中的order by 不支持算术运算，没法了，这不排序就无法取出前10名，于是又开始找资料中......发现了一份资料上说order by 后面不一定要写列名，写列的顺序也是一样的，可以写成order by 1 desc，于是又开始修改。 select sum(case when a.corrected=true then 1 else 0 end)*1.0 / count(*) ,a.scoreInfo.student from Answer a group by a.scoreInfo.student order by 1 desc 最后设置maxResults为10,结果终于出来了，真是困难啊。 通过一下午的努力，结果终于出来了，想想遇到的问题
count不支持条件 无法起别名 hibernate在项目中做做CRUD操作确实比较简单，如果做复杂的查询，可能就不那么好用了，也难怪也有很多项目在数据库确定的情况下，使用了Mybatis，由此可见Hibernate作为一种通用的数据操作方式是可以的，但是仍然有其局限性，在具体的项目中应该根据项目需求进行选择。 |
|
|
