hibernate通过分析函数新增伪列

以前的sql是StringBuffer sqlBuffer = new StringBuffer(" from SwgkZdryckyjXxffb d");

 getHibernateTemplate().setCacheQueries(true);
  return getHibernateTemplate().find(sqlBuffer.toString());

 

如果对分发表的情报表swgk_swryyj_qbxxb按照sfzh号码进行分组，需要在分发列表上显示这个sfzh在swgk_swryyj_qbxxb出现的次数，如何实现？

则需要用到分析函数：

select ffb.*,qbb.gjtj from swryztk.SWGK_SWRYYJ_XXFFB ffb ,(
SELECT id,xt_Zxbz,COUNT(*) OVER (partition  BY sfzh) AS gjtj  FROM swryztk.swgk_swryyj_qbxxb) qbb where ffb.yjxxbzbh=qbb.id(+)

 

具体代码如下：

 /**
  * 返回部省市级预警信息分发列表
  * @param entity
  * @param pager
  * @param userSession
  * @return
  */

public List<BaseEntity> bssjYjxxFfList(SwgkZdryckyjXxffbGj entity,
   PageHelper pager, UserInfo userSession) throws SQLException{
  List<BaseEntity> list = new ArrayList<BaseEntity>();
  StringBuffer sqlBuffer = new StringBuffer("select d.*,qbb.gjtj gjtj from swryztk.SWGK_SWRYYJ_XXFFB d ,("+
"SELECT id,xt_Zxbz,COUNT(*) OVER (partition  BY sfzh) AS gjtj  FROM swryztk.swgk_swryyj_qbxxb) qbb where d.yjxxbzbh=qbb.id(+) and d.xt_Zxbz='0' and qbb.xt_Zxbz='0'");
  
  //部门
  sqlBuffer.append(" and d.jsdwid='"+userSession.getUserOrgCode()+"'");
  
  // 查询条件
  if (!StringUtil.isBlank(entity.getSfzh()))
   sqlBuffer.append(" and d.yjxxbzbh.sfzh='" + entity.getSfzh() + "'");
  //姓名
  if (!StringUtil.isBlank(entity.getXm()))
   sqlBuffer.append(" and d.yjxxbzbh.swryxm like '%"+ entity.getXm() +"%'");
  //人员类别
  if(entity.getZdrybswc()!=null && entity.getZdrybswc().getDm()!=null){
   if (!StringUtil.isBlank(entity.getZdrybswc().getDm())){
    sqlBuffer.append(" and substr(d.yjxxbzbh.zdrylbbj,"+Integer.valueOf(entity.getZdrybswc().getDm())+",1)='1'");
   }
  }
  //涉稳人员类别
  if(entity.getYjxxbzbh()!=null && entity.getYjxxbzbh().getSwrylb()!=null){
   if(!StringUtil.isBlank(entity.getYjxxbzbh().getSwrylb().getDm())){
    sqlBuffer.append(" and d.yjxxbzbh.swrylb.dm='"+entity.getYjxxbzbh().getSwrylb().getDm()+"'");
   }
  }
  //分发
  if(!StringUtil.isBlank(entity.getCzlxzt_1())){
   if ("0".equals(entity.getCzlxzt_1())) {
    sqlBuffer = this.getWff(sqlBuffer);    
   } else {
    sqlBuffer = this.getYff(sqlBuffer);
   }
  }else{
   sqlBuffer = this.getWff(sqlBuffer); 
  }
//  //区域类别
//  if (entity.getQylb()!=null && entity.getQylb().getDm()!=null && !("").equals(entity.getQylb().getDm()))
//   sqlBuffer.append(" and d.yjxxbzbh.qylb.dm = '" + entity.getQylb().getDm() + "'");
  //动态信息类别
  if (entity.getYjxxbzbh()!= null && entity.getYjxxbzbh().getDtxxlb()!=null && !"".equals(entity.getYjxxbzbh().getDtxxlb().getDm())){
   sqlBuffer.append(" and d.yjxxbzbh.dtxxlb.dm = '" + entity.getYjxxbzbh().getDtxxlb().getDm() + "'");
  }
  //预警级别
  if(entity.getYjjb()!=null && entity.getYjjb().getDm()!=null && !entity.getYjjb().getDm().equals("")){
   if (!StringUtil.isBlank(entity.getYjjb().getDm()))
    sqlBuffer.append(" and d.yjxxbzbh.yjjb.dm='" + entity.getYjjb().getDm() + "'");
  }
  //发布单位
  if (!StringUtil.isBlank(entity.getFszbmmc()))
   sqlBuffer.append(" and d.fszbmmc='" + entity.getFszbmmc() + "'");
  //接受对象
  if (!StringUtil.isBlank(entity.getJsdwid()))
   sqlBuffer.append(" and d.jsdwid='" + entity.getJsdwid() + "'");
  //预警发布时间
  if (!StringUtil.isBlank(entity.getFshdsjq()))
   sqlBuffer.append(" and d.yjxxbzbh.yjfbsj>='" + DateTimeHelper.changeTime(entity.getFshdsjq()) + "'" );
  if (!StringUtil.isBlank(entity.getFshdsjz()))
   sqlBuffer.append(" and d.yjxxbzbh.yjfbsj<='" + DateTimeHelper.changeTime(entity.getFshdsjz()) +"'" );
  
  //统计总数sql
  StringBuilder sql_count = new StringBuilder();
  sql_count.append("select count(*) from(");
  sql_count.append(sqlBuffer.toString());
  sql_count.append(")T_count");
//  System.out.println("统计总数sql："+sql_count.toString());
  //加入排序
  String sortStr = ParamUtil.sortStr(pager);
  if (StringUtil.isBlank(sortStr))
   sqlBuffer.append(" order by d.fssj desc ");
  else{
   sqlBuffer.append(" order by " + sortStr + " ");
  }
  pager.setSortStr(sortStr);
  //分页sql
  StringBuilder sql_paging = new StringBuilder();
  sql_paging.append("select *  from (");
  sql_paging.append(" select T_paging.*, rownum rn from (");
  sql_paging.append(sqlBuffer.toString());
  sql_paging.append(")T_paging where rownum < "+(pager.getBegin()+1+pager.getRowPage()));
  sql_paging.append(")where rn >= "+(pager.getBegin()+1));
//  System.out.println("分页sql:"+sql_paging.toString());
  
  //查询总条数
  Session session = this.getSession();
  String strcount = session.createSQLQuery(sql_count.toString()).uniqueResult().toString();
  Integer totalCount =  Integer.parseInt(strcount);
  this.releaseSession(session);
  pager.setTotalCount(totalCount, pager);
  //查询结果集
  session = this.getSession();
  list = session.createSQLQuery(sql_paging.toString()).addEntity(SwgkZdryckyjXxffbGj.class).list(); 

  return list;
 }