在SSH框架多表查询时聚合函数的使用
对于SSH框架下多表查询聚合函数的问题，还真纠结了我蛮久！做出来之后，发现虽然这个问题没有特别复杂，但是要注意的东西还是有蛮多的！比如：在SSH框架下平时简单的多表查询中，我们可以通过对象来获取他的值，但是在聚合函数查询的时候，我们不能select一个对象，必须是一个具体的值！下面是我做的一个项目中有用到关于这个问题的地方，跟大家一起来分享一下！
在dao包里面：
//分页显示所有统计查询船舶违章的具体信息 public List getAllBreakInformationT(final String hql, final Integer page , final Integer size, final Object ...p){ return getHibernateTemplate().executeFind(new HibernateCallback(){ public Object doInHibernate(Session session) throws HibernateException, SQLException { Query query = session.createQuery(hql); query.setCacheable(true); if (p != null) { for (int i = 0; i < p.length; i++) { query.setParameter(i, p[i]); } } if ( page != null && size != null) { query.setFirstResult((page-1)*size).setMaxResults(size); } List list = query.list(); return list; }}); }
//按时间、船舶船员名，违章代码统计查询的总条数 public Integer countCheckBreak(final String bname, final String sname, final java.sql.Date sDate, final java.sql.Date lDate, final int incode) { return (Integer) getHibernateTemplate().execute(new HibernateCallback(){ public Object doInHibernate(Session session) throws HibernateException, SQLException { String hql = "select count(*) from ViolationTable where 1=1"; StringBuffer sb=new StringBuffer(hql); if(sDate!=null){ sb.append(" and vitime >='"+sDate+"'"); System.out.print("dao sdate:"+sDate); } if(lDate!=null){ sb.append(" and vitime <='"+lDate+"'"); System.out.print("dao ldate:"+lDate); } if(bname!="" && bname!=null){ sb.append(" and driveTable.boatTable.bchinesename like '%"+bname+"%' group by driveTable.boatTable.bchinesename"); System.out.print("dao bname:"+bname); } if(sname!="" && sname!=null){ sb.append(" and driveTable.sailorTable.sname like '%"+sname+"%' group by driveTable.sailorTable.sname"); System.out.print("dao sname:"+sname); } if(incode!=0){ sb.append(" and informationTable.incode ="+incode + "group by informationTable.incode"); System.out.print("dao incode:"+incode); } String sql=sb.toString(); Query query = session.createQuery(sql); //query.setCacheable(true); //Object object = query.uniqueResult(); List list=query.list(); System.out.print("list.size()"+list.size()); return list.size(); }}); }
//根据船舶船员名、时间和违章代码统计查询 public String gethqlB(final String bname, final String sname, java.sql.Date sDate, java.sql.Date lDate, int incode) { String hql="select count(viid),sum(informationTable.ingrade),sum(informationTable.inmoney),informationTable.incode,driveTable.boatTable.bchinesename,driveTable.sailorTable.sname from ViolationTable where 1=1"; StringBuffer sb=new StringBuffer(hql); if(sDate!=null){ sb.append(" and vitime >='"+sDate+"'"); } if(lDate!=null){ sb.append(" and vitime <='"+lDate+"'"); } if(bname!="" && bname!=null){ sb.append(" and driveTable.boatTable.bchinesename like '%"+bname+"%' group by driveTable.boatTable.bchinesename"); } if(sname!="" && sname!=null){ sb.append(" and driveTable.sailorTable.sname like '%"+sname+"%' group by driveTable.sailorTable.sname"); } if(incode!=0){ sb.append(" and informationTable.incode ="+incode + "group by informationTable.incode"); } sb.append(" order by vitime desc"); System.out.println(sb.toString()); String sql=sb.toString(); return sql; }
在service包里面
//根据违章代码，船舶船员名和时间统计查询 public Map getAllChekB(String bname,String sname,Date sDate,Date lDate,int incode,int page,int size){ String hql=violationTableDAO.gethqlB(bname, sname, sDate, lDate,incode); System.out.println("hql="+hql); //求总条数 int num = violationTableDAO.countCheckBreak(bname, sname, sDate, lDate, incode); System.out.println("num="+num); //求总页数 int count = num%size==0 ? num/size : num/size+1 ; //越界查询 if(page<1) page = 1; if(page>count) page = count ; System.out.println(1234564); List vlist = violationTableDAO.getAllBreakInformationT(hql, page, size); System.out.println(vlist.size()+"******"); List list=new ArrayList(); for (int i = 0; i < vlist.size(); i++) { Object ob[]=(Object[]) vlist.get(i); CountTest ct=new CountTest(); ct.setVcount(Integer.parseInt(ob[0].toString())); ct.setVsum(Integer.parseInt(ob[1].toString())); ct.setVmoney(Double.parseDouble(ob[2].toString())); ct.setIncode(Integer.parseInt(ob[3].toString())); ct.setBchinesename(ob[4].toString()); ct.setSname(ob[5].toString()); System.out.println("ct-Vcount:"+ct.getVcount()); list.add(ct); } Map map = new HashMap(); map.put("list", list); map.put("page", page); map.put("size", size); map.put("num", num); map.put("count", count); return map; }
在action包里面
//根据船舶,船员名和日期查找违章记录 public String checkRecord(){ HttpServletRequest request=ServletActionContext.getRequest(); HttpServletResponse response=ServletActionContext.getResponse(); int page =1 ; int size = 5 ; String pageString = request.getParameter("page"); if (pageString != null) page = Integer.parseInt(pageString); String sizeString = request.getParameter("size"); if(sizeString != null) size = Integer.parseInt(sizeString); String bname=request.getParameter("bname"); if(bname!=null){ bname=bname.trim().toString(); } request.setAttribute("bname", bname); System.out.println("bname:"+bname); String sname=request.getParameter("sname"); if(sname!=null){ sname=sname.trim().toString(); } request.setAttribute("sname", sname); System.out.println("sname:"+sname); String icode=request.getParameter("incode"); int incode=0; if(icode!=null){ icode=icode.trim().toString(); } System.out.println(icode); if(icode!="" && icode!=null){ incode=Integer.parseInt(icode); } System.out.println("incode:"+incode); request.setAttribute("incode", incode); String syear=request.getParameter("syear"); String smonth=request.getParameter("smonth"); String sday=request.getParameter("sday"); String lyear=request.getParameter("lyear"); String lmonth=request.getParameter("lmonth"); String lday=request.getParameter("lday"); Date sDate=null; if(!"".equals(syear) && syear!=null){ System.out.println(123456); StringBuffer sb1=new StringBuffer(); sb1.append(syear); sb1.append("-"); sb1.append(smonth); sb1.append("-"); sb1.append(sday); sDate=Date.valueOf(sb1.toString()); } request.setAttribute("sDate", sDate); System.out.println("sDate:"+sDate); Date lDate=null; if(!"".equals(lyear) && lyear!=null){ System.out.println(22222223); StringBuffer sb2=new StringBuffer(); sb2.append(lyear); sb2.append("-"); sb2.append(lmonth); sb2.append("-"); sb2.append(lday); lDate=Date.valueOf(sb2.toString()); } request.setAttribute("lDate", lDate); System.out.println("lDate:"+lDate); if(("".equals(bname) || bname==null) && ("".equals(sname) || sname==null) && incode==0){ Map map=breakRecordService.getAllChek(bname, sname, sDate, lDate, incode, page, size); request.setAttribute("checkmap", map); return "CheckDate"; }else if((!"".equals(bname) || bname!=null) && ("".equals(sname) || sname==null) && incode==0){ System.out.println("bname进来"); Map map=breakRecordService.getAllChekB(bname, sname, sDate, lDate, incode, page, size); request.setAttribute("checkmap", map); return "CheckBname"; }else if(("".equals(bname) || bname==null) && (!"".equals(sname) || sname!=null) && incode==0){ System.out.println("sname进来！"); Map map=breakRecordService.getAllChekB(bname, sname, sDate, lDate, incode, page, size); request.setAttribute("checkmap", map); return "CheckSname"; }else if(("".equals(bname) || bname==null) && ("".equals(sname) || sname==null) && incode!=0){ System.out.println("incode进来！"); Map map=breakRecordService.getAllChekB(bname, sname, sDate, lDate, incode, page, size); request.setAttribute("checkmap", map); return "CheckIncode"; } return "err"; }
