为了实现当数据库统计在某个值为null或者返回值为null是显示此值 ,并且统计数为0
cid count
a1 11
b1(数据库中不存在的值) 0
c2 0
显然此语句不能满足,但cid 在表
EVENTINFOALL中不存在时不能满足的.
后来经过改进实现方法如下
避免创建一个dual映射,直接采用了
createSQLQuery直接用sql语句来完成.
具体实现的SQL语句:
cid count
a1 11
b1(数据库中不存在的值) 0
c2 0
- select cid, count(cid) as t
- from EVENTINFOALL
- where EOCCURTIME >?
- and cid in (?, ?,?)
- group by cid) b
后来经过改进实现方法如下
- List cidlist=new ArrrayList();
- int idsize=cidlist.size();
- StringBuffer hql=new StringBuffer("select a.cid as kcid , nvl(b.t, 0) as kcount from (select ? cid from dual");
- for(int i=1;i<idsize;i++){
- hql.append(" union select ? cid from dual");
- }
- hql.append(") a , (select cid,count(cid) as t from Eventinfoall where eoccurtime>(sysdate-2) and eoccurtime<sysdate and cid in (:setcid");
- for(int i=1;i<idsize;i++){
- hql.append(",:setcid");
- }
- hql.append(") group by cid ) b where a.cid = b.cid(+)");
- Query q=super.getSession().createSQLQuery(hql.toString()).addScalar("kcid",Hibernate.STRING).addScalar("kcount",Hibernate.INTEGER);
- for(int i=0;i<idsize;i++){
- PqmDfindIndex pdi=(PqmDfindIndex)cidlist.get(i);
- q.setString(i, pdi.getId().getCid());
- q.setString("setcid",pdi.getId().getCid());
- }
- return q.list();
- }else if(cidlist.size()==1){
- 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(+)");
- Query q=super.getSession().createSQLQuery(hql.toString()).addScalar("kcid",Hibernate.STRING).addScalar("kcount",Hibernate.INTEGER);
- PqmDfindIndex pdi=(PqmDfindIndex)cidlist.get(0);
- q.setString(0, pdi.getId().getCid());
- q.setString("setcid",pdi.getId().getCid());
- return q.list();
具体实现的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(
+
);