Hibernate統(tǒng)計查詢手記
TAG:Hibernate,group by,order by ，order by if
前段時間在做考試系統(tǒng)的時候，遇到了這樣一個統(tǒng)計功能，學(xué)員進行考試之后，所有的答案都存儲在數(shù)據(jù)庫當中，項目需求是要得到正確率最高的前十名，并顯示在網(wǎng)頁上。這就需要使用Hibernate對學(xué)員的答案進行統(tǒng)計，在完成了這個功能的時候遇到了很多的問題，所幸都得到了解決，在此記錄下來，與大家共勉。
所涉及到的類結(jié)構(gòu)如下
public class Answer {
private Boolean corrected = false;
private ScoreInfo scoreInfo;
//其它無關(guān)屬性省略….
}
public class ScoreInfo {
private Student student;
//其它無關(guān)屬性省略….
}
項目采用的數(shù)據(jù)庫是MySQL數(shù)據(jù)庫，首先我就根據(jù)數(shù)據(jù)庫寫出了查詢語句
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中正常運行，并顯示出正確率和學(xué)員所有的信息，在我嘗試將這個SQL語句轉(zhuǎn)換成HQL語句，得到了查詢一個學(xué)生答案正確率的語句，語句如下：
select count(if(answer.corrected=true))/count(*) from Answer a where a.scoreInfo.student=?
運行之后，出錯了，查詢API之后，發(fā)現(xiàn)Hibernate不支持count中加上if條件，別的聚集函數(shù)也不行
......郁悶，換子查詢吧
改成以下語句
select ((select count(*) from Answer where corrected=true) /count(*))from Answer a where a.scoreInfo.student=?
得到了一個語法錯誤，猜測是不是count(*)子句執(zhí)行之后是集合，于是加了一個[0]
select ((select count(*) from Answer where corrected=true)[0] /count(*))from Answer a where a.scoreInfo.student=?
得到的結(jié)果是不能進行強制轉(zhuǎn)換(LiteralNode不能轉(zhuǎn)成FromReferenceNode)
只能另想其他辦法了，找了N多資料之后，看到了一種特殊的用法
hibernate的sum函數(shù)支持 case when 條件 then 成立執(zhí)行的語句 else 不成立執(zhí)行的語句 end
換到查詢里面就變成了：
select sum(case when a.corrected=true then 1 else 0 end)/count(*)from Answer a where a.scoreInfo.student=?
查詢了一下，終于能運行了，不過結(jié)果都是0，于是，我把/換成了,看看兩個的值，都取出來了，正常，估計就是整形相除的問題，小數(shù)都忽略了，再改：
select sum(case when a.corrected=true then 1 else 0 end)*1.0/count(*)from Answer a where a.scoreInfo.student=?
終于OK了，把不能count的這個問題搞定了，可以看到一個學(xué)員的正確率了，接下來的問題就是進行分組查詢了，調(diào)整一下語句，如下
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
嗯，很好，沒有出問題，正確率和學(xué)員都被查詢出來了，下面再進行排序和截取就好了。
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
給準確率起了一個別名，最后根據(jù)這個進行逆向排序就應(yīng)該可以了，跑了一下，又報錯了，提示是Unknown column 'accuracy' in 'order clause'，在排序的時候找不到'accuracy'列，不對啊，我明明起了別名啊，觀察了一下Hibernate產(chǎn)生的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
發(fā)現(xiàn)確實沒有我起的別名，hibernate會為每一列都起別名，自己起的就無效了，而hibernate中的order by 不支持算術(shù)運算，沒法了，這不排序就無法取出前10名，于是又開始找資料中......發(fā)現(xiàn)了一份資料上說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
最后設(shè)置maxResults為10,結(jié)果終于出來了，真是困難啊。
通過一下午的努力，結(jié)果終于出來了，想想遇到的問題
count不支持條件
無法起別名
hibernate在項目中做做CRUD操作確實比較簡單，如果做復(fù)雜的查詢，可能就不那么好用了，也難怪也有很多項目在數(shù)據(jù)庫確定的情況下，使用了Mybatis，由此可見Hibernate作為一種通用的數(shù)據(jù)操作方式是可以的，但是仍然有其局限性，在具體的項目中應(yīng)該根據(jù)項目需求進行選擇。
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點擊舉報。
