项目笔记：统计页面功能实现

　　页面跳转：

//正版化统计列表
public String listUI() {
    List<Software> softwares = softwareService.queryAll();//注意我要获取的是software表的数据
    List<Software> softwareList = new ArrayList<Software>();
    if(softwares != null){
        for(Software gsnm : softwares){
            Software gm = new Software();
            gm.setSoftName(gsnm.getSoftName());
            gm.setSoftId(gsnm.getSoftId());
            softwareList.add(gm);//获取software表的数据存储到softwareList中，然后传给前台
        }
    }
    getRequest().setAttribute("softwareList", softwareList);
    return "listUI";
}

　　前台页面如此获取：

<select id="querys_softName" name="genuineManagementStatic.softName" style="208px;">
<s:if test="#request.softwareList != null">
<s:iterator  value="#request.softwareList">
    <option softId='<s:property value="#request.softId" />' value='<s:property value="#request.softName" />'><s:property value="#request.softName" /></option>
</s:iterator>
</s:if>
</select>

　　后台逻辑处理：

public void list() {
    try {
        HQLBuilderUtil hql = new HQLBuilderUtil(Software.class);//注意要是获取到Software的page数据，此处需是Software.class
        if(software!=null && software.getSoftId()!=null && !"".equals(software.getSoftId())){
            Integer softId = software.getSoftId();
            hql.addWhereClause(" this.softId=? ", softId);
        }
        Integer pcCount = pcInfoService.queryAll().size();//PC总数
        GridData<Software> reportlogs = softwareService.getPageView(hql, getPageNum(), getPageSize());//获取到Software的page数据
        List<Software> list = reportlogs.getRows();
        for (int i = 0; i < list.size(); i++) {
            //取安装数
            Integer installCount = genuineManagementStaticService.queryInstallNum(list.get(i).getSoftId());
            NumberFormat numberFormat = NumberFormat.getInstance();
            // 设置精确到小数点后2位
            numberFormat.setMaximumFractionDigits(2);
            String result = numberFormat.format((float) installCount / (float) pcCount * 100) + "%";
            list.get(i).setInstallNum(installCount);
            list.get(i).setInstallPersent(result);
            //取正版数
            Integer genuine = 1;
            Integer genuineCount = genuineManagementStaticService.queryGenuineNum(genuine, list.get(i).getSoftId());
            Integer softCount = genuineManagementStaticService.querySoftNum(list.get(i).getSoftId());
            NumberFormat numberFormatGenuine = NumberFormat.getInstance();
            // 设置精确到小数点后2位
            numberFormat.setMaximumFractionDigits(2);
            String genuineResult = numberFormatGenuine.format((float) genuineCount / (float) softCount * 100) + "%";
            list.get(i).setGenuineNum(genuineCount);
            list.get(i).setGenuinePersent(genuineResult);
            //取最新版本
            GenuineManagementStatic gms = genuineManagementStaticService.queryNewVersion(list.get(i).getSoftId());
            list.get(i).setVersion(gms.getVersion());
        }
        print(ActionUtil.jsonObj(reportlogs));
    } catch (Exception e) {
        e.printStackTrace();
        GridData<Software> soft = new GridData<Software>();
        soft.setRows(null);
        soft.setTotal(0);
        print(ActionUtil.jsonObj(soft));
    }
}

　　我们再看一下几个dao层的查询方法：

@Override
    public Integer queryInstallNum(Integer softId) {
        return Integer.parseInt(getSession().createSQLQuery(" select count(1) from vrv_paw_genuineManagementStatic a,vrv_paw_pcinfo b where a.pcInfoId=b.id and softId=:softId")
                .setParameter("softId", softId)
                .list().get(0).toString());
    };

@Override
    public Integer queryGenuineNum(Integer matchResult, Integer softId) {
        return Integer.parseInt(getSession().createSQLQuery(" select count(1) from vrv_paw_genuineManagementStatic where matchResult=:matchResult and softId=:softId ")
                .setParameter("matchResult", matchResult)
                .setParameter("softId", softId)
                .list().get(0).toString());
    }

@Override
    public Integer querySoftNum(Integer softId) {
        return Integer.parseInt(getSession().createSQLQuery(" select count(1) from vrv_paw_genuineManagementStatic where softId=:softId ")
                .setParameter("softId", softId)
                .list().get(0).toString());
    }
//获取softId为某个的软件的总数

@SuppressWarnings("unchecked")
    @Override
    public GenuineManagementStatic queryNewVersion(Integer softId) {
        List<GenuineManagementStatic> list = getSession().createQuery(" from " + this.clazz.getName() + " this WHERE this.softId=:softId  order by version desc LIMIT 0,1")
                .setParameter("softId", softId)
                .list();
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

　　上面这个方法，就需要查看下此篇博客深入学习下：unexpected token: * 和 java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to 解决办法

 　　正版数（需要涉及子查询）和最新版本查询修改（最新版本要类型转换cast(version as integer)）

//新的正版数逻辑
    @Override
    public Integer queryGenuineNumNew(Integer matchResult, Integer genuine, Integer softId) {
        return Integer.parseInt(getSession().createSQLQuery(" select count(distinct softId,version,pcInfoId) from vrv_paw_genuineManagementStatic "
                + "where (softId,version,pcInfoId) not in(select softId,version,pcInfoId from vrv_paw_genuineManagementStatic where matchResult=:matchResult and softId=:softId)"
                + "and matchResult=:genuine "
                + "and softId=:softId ")
                .setParameter("matchResult", matchResult)
                .setParameter("genuine", genuine)
                .setParameter("softId", softId)
                .list().get(0).toString());
    }

@Override
    public GenuineManagementStatic queryNewVersion(Integer softId) {
        List<GenuineManagementStatic> list = getSession().createQuery(" from " + this.clazz.getName() + " this WHERE this.softId=:softId  order by cast(version as integer) desc LIMIT 0,1")
                .setParameter("softId", softId)
                .list();
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

　　关于正版数新逻辑查询，请查看这篇文章，说明的比较清楚：从项目上一个子查询扩展学习开来：mysql的查询、子查询及连接查询