JSF多表查询渲染问题(统计查询)
程序员文章站
2022-03-09 16:41:31
...
开发环境:
JSF + Hibernate + MySQL
问题发现:
先来看看sql语句:
select biomarker_type,count(geneid) from biomarkers group by biomarker_type;
由于工程使用的是Hibernate,一般有关数据的CRUD都是通过HQL执行的,即HQL操作的是个实体对象(bean),该对象映射到数据中的一张表中,而上面的查询涉及到sql的聚集函数,查询出来的数据关联不了已经存在的实体对象属性中,自然在渲染到页面的时候也是显得力不从心。。。
解决想法:
把查询结果保存在一个临时的bean对象中。。。
具体实现:
1、新建一个bean对象StatisticsQuery,定义两个属性param1,param2,要有其对应的存取器。
2、将查询结果保存到对象StatisticsQuery中:
public List<StatisticsQuery> queryStatistics(){ List<StatisticsQuery> sqList = new ArrayList<StatisticsQuery>(); ResultSet rs = null; PreparedStatement pst = null; Connection conn = null; try { Class.forName(driver); conn = DriverManager.getConnection(url, user, password); StringBuffer sqlStr = new StringBuffer(); sqlStr.append(" select biomarker_type param1, count(geneid) param2 "); sqlStr.append(" from biomarkers "); sqlStr.append(" group by biomarker_type "); pst = conn.prepareStatement(sqlStr.toString()); pst.execute(); rs = pst.getResultSet(); StatisticsQuery sq = null; while(rs.next()){ sq = new StatisticsQuery(); sq.setParam1(rs.getString(1)); sq.setParam2(rs.getString(2)); } } catch (Exception e) { logger.error(e); } finally { try { if(rs != null) rs.close(); if(pst != null) pst.close(); if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return sqList; }
此处使用的是jdbc连接的数据库,不过这么做还得去读取jdbc配置文件。若用Hibernate连接数据库,执行操作的代码如下:
public List<StatisticsQuery> queryStatistics(){ Session session = getGDSessionFactory().openSession(); List<StatisticsQuery> sqList = new ArrayList<StatisticsQuery>(); StringBuffer sqlStr = new StringBuffer(); sqlStr.append(" select biomarker_type param1, count(geneid) param2 "); sqlStr.append(" from biomarkers "); sqlStr.append(" group by biomarker_type "); SQLQuery query = session.createSQLQuery(sqlStr.toString()); List list = query.list(); StatisticsQuery sq = null; for (Object object : list) { if(object == null) continue; Object[] obj = (Object[])object; sq = new StatisticsQuery(); sq.setParam1(obj[0] == null ? "":obj[0].toString()); sq.setParam2(obj[1].toString()); } return sqList; }
3、渲染到页面,如下:
<rich:dataTable id="searchResultBiomarker" onRowMouseOver="this.style.backgroundColor='#F1F1F1'" onRowMouseOut="this.style.backgroundColor='#{a4jSkin.tableBackgroundColor}'" border="1" value="#{statisticsQueryController.sqList}" var="record" styleClass="table_result" rowKeyVar="i" rowClass='#{i%2 == 1?"spec":"specalt"}'> <rich:column> <f:facet name="header"> <h:outputText value="#{msg.statistics_biomarker_type}" /> </f:facet> <h:outputText value="#{record.param1}" /> </rich:column> <rich:column> <f:facet name="header"> <h:outputText value="#{msg.statistics_no_of_biomarkers}" /> </f:facet> <h:outputText value="#{record.param2}" /> </rich:column> </rich:dataTable>