1.Monitor对象
private String name;
private String max=”0″;
private String min=”0″;
private Double avage;
/** 0-1 **/
private Long section1;
/** 1-2 **/
private Long section2;
/** 2-3 **/
private Long section3;
/** 3-4 **/
private Long section4;
/** 4-5 **/
private Long section5;
/** 5-6 **/
private Long section6;
/** 6-7 **/
private Long section7;
/** 7-8 **/
private Long section8;
/** 8-9 **/
private Long section9;
/** 9-10 **/
private Long section10;
private Set<BeanMItem> items=new HashSet<BeanMItem>();
2.BeanMItem对象
private String companyName;
private String failureRate;
private BeanMonitor monitor;
3.获取当前最新的Monitor对象并获取其中的观测条目的最大值,最小值和平均值,以及对应的区间数量
a.left join
b.group by
c.MAX,MIN,AVG
d.sum(case when boolean then 1 else 0 end)
QueryResult qr = new QueryResult<BeanMonitor>();
String entityname = getEntityName(this.entityClass);
Query query = em.createQuery("select new BeanMonitor(o.id,o.name,MAX(items.failureRate),MIN(items.failureRate),avg(items.failureRate),"
+ "sum(case when items.failureRate < 10 then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 10 and items.failureRate < 20) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 20 and items.failureRate < 30) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 30 and items.failureRate < 40) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 40 and items.failureRate < 50) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 50 and items.failureRate < 60) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 60 and items.failureRate < 70) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 70 and items.failureRate < 80) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 80 and items.failureRate < 90) then 1 else 0 end),"
+ "sum(case when (items.failureRate >= 90) then 1 else 0 end)) from " + entityname + " o left join o.items as items " + (wherejpql == null || "".equals(wherejpql.trim()) ? "" : "where " + wherejpql) +" group by o.id " +buildOrderby(orderby));
setQueryParams(query, queryParams);
if (firstResult != -1 && maxresult != -1)
query.setFirstResult(firstResult).setMaxResults(maxresult);
qr.setResultlist(query.getResultList());