hibernate 使用SQLQuery操作oracle数据库表中值为null的统计

为了实现当数据库统计在某个值为null或者返回值为null是显示此值 ,并且统计数为0
cid                    count
a1                     11
b1(数据库中不存在的值)  0
c2                      0

1.  select cid, count(cid)  as t
2.  from EVENTINFOALL
3.  where EOCCURTIME >?
4. and cid in (?, ?,?)
5. group by cid) b

显然此语句不能满足,但cid 在表EVENTINFOALL中不存在时不能满足的.
后来经过改进实现方法如下

1. List cidlist=new ArrrayList();
2. int idsize=cidlist.size();
3.                     StringBuffer hql=new StringBuffer("select a.cid as kcid , nvl(b.t, 0) as kcount from (select ? cid from dual");
4.                     for(int i=1;i<idsize;i++){
5.                         hql.append(" union select ? cid from dual");
6.                     }
7.                     hql.append(") a , (select cid,count(cid) as t from Eventinfoall  where eoccurtime>(sysdate-2) and eoccurtime<sysdate and cid in (:setcid");
8.                     for(int i=1;i<idsize;i++){
9.                         hql.append(",:setcid");
10.                     }
11.                     hql.append(") group by cid ) b  where a.cid = b.cid(+)");
12.                     Query q=super.getSession().createSQLQuery(hql.toString()).addScalar("kcid",Hibernate.STRING).addScalar("kcount",Hibernate.INTEGER);
13.                     for(int i=0;i<idsize;i++){
14.                         PqmDfindIndex   pdi=(PqmDfindIndex)cidlist.get(i);
15.                         q.setString(i, pdi.getId().getCid());
16.                         q.setString("setcid",pdi.getId().getCid());
17.                     }
18.                     return q.list();
19.                 }else if(cidlist.size()==1){
20.                     StringBuffer hql=new StringBuffer("select a.cid as kcid , nvl(b.t, 0) as kcount from (select ? cid from dual) a , (select cid,count(cid) as t from Eventinfoall  where eoccurtime>(sysdate-2) and eoccurtime<sysdate and cid=:setcid group by cid ) b  where a.cid = b.cid(+)");
21.                     Query q=super.getSession().createSQLQuery(hql.toString()).addScalar("kcid",Hibernate.STRING).addScalar("kcount",Hibernate.INTEGER);
22.                     PqmDfindIndex pdi=(PqmDfindIndex)cidlist.get(0);
23.                     q.setString(0, pdi.getId().getCid());
24.                     q.setString("setcid",pdi.getId().getCid());
25.                     return q.list();

避免创建一个dual映射,直接采用了createSQLQuery直接用sql语句来完成.

具体实现的SQL语句:

select a.cid, nvl(b.t, 0) from (select '01b' cid from dual union select '01f' cid from dual union select '010' cid from dual) a, (select cid, count(cid) as t from EVENTINFOALL where EOCCURTIME > to_date('2008-12-20 10:01:07', 'yyyy-mm-dd hh24:mi:ss') and cid in ('01b', '01f', '010') group by cid) b where a.cid = b.cid(+);