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;
?}
