hibernate 数据库查询 实例

java hibernate orcle sql
1、从TaskDown表中计算状态为0,1,2,3,4的任务数，规定taskid为1或者0并且username是传入的参数

StringBuffer hqlBuffer = new StringBuffer();
hqlBuffer.append("select sum(case when state='0' then 1 else 0 end) as notread,sum(case when state='1' then 1 else 0 end)as read,sum(case when state='2' then 1 else 0 end)as excuting,sum(case when sate='3' then 1 else 0 end) as
doneall,sum(case when sate='4' then 1 else 0 end) as delaydone");
hqlBuffer.append(" from TaskDown where taskid in(0,1) and username:=username");
Query query = session.createQuery(hqlBuffer.toString());
query.setString("username", username);//给username参数赋值

2、

StringBuffer hqlBuffer = new StringBuffer();
hqlBuffer.append("select sum(case when state='0' then 1 else 0 end) as notread,sum(case when state='1' then 1 else 0 end) as read,sum(case when state='2' then 1 else 0 end) as excuting,sum(case when state='3' then 1 else 0 end) as doneall,sum(case when state='4' then 1 else 0 end) as delaydone");
hqlBuffer.append(" from TaskDown where totaltaskid in(0,1) and jid like :userjid");
Query query = session.createQuery(hqlBuffer.toString());
String userjid = "%"+username+"%";
query.setString("userjid", userjid);

3、

StringBuffer hqlBuffer = new StringBuffer();
hqlBuffer.append("select distinct(substr(jid,instr(jid,',')+1,length(jid))) as name from TaskDown where userucid=:ucid group by jid) ");
Query query = session.createQuery(hqlBuffer.toString());
query.setString("ucid", ucid);