這裡是按照條件模糊查詢要查詢的內容
在jsp頁面:
<table align="center" class="table-showboat" cellpadding="0" cellspacing="0"> <tr> <td align="right" colspan="14" height="30"> <input type="text" name="bchaxun" id="bchaxun" οnfοcus="bfocuscha()" value="請輸入船舶名"> <input type="button" value="查詢" οnclick="bclickcha()"><br> <input type="radio" value="中文名" name="ming" id="zh" checked="checked">中文名 <input type="radio" value="英文名" name="ming" id="yi">英文名 </td> </tr> <tr> <th class="boat-th" width="60">船員名</th> <th class="boat-th" width="60">船舶名</th> <th class="boat-th" width="70">違章程式碼</th> <th class="boat-th">違法行為</th> <th class="boat-th">違法依據</th> <th class="boat-th" width="35">記分</th> <th class="boat-th" width="70">罰款(元)</th> <th class="boat-th">行政處罰</th> <th class="boat-th" width="50">經辦人</th> <th class="boat-th" width="70">事發時間</th> <th class="boat-th" width="60">事發地址</th> <th class="boat-th" width="60">事發原由</th> <th class="boat-th" width="60" style="border-right:1px solid #99BBE8;">操作</th> </tr> <s:if test="#request.mapbreak.num!=0"> <s:iterator value="#request.mapbreak.list" var="break"> <tr align="center"> <td class="boat-td">${break.driveTable.sailorTable.sname }</td> <td class="boat-td">${break.driveTable.boatTable.bchinesename }</td> <td class="boat-td">${break.informationTable.incode }</td> <td class="boat-td" align="left">${break.informationTable.inillegal }</td> <td class="boat-td" align="left">${break.informationTable.inbasis }</td> <td class="boat-td">${break.informationTable.ingrade }</td> <td class="boat-td">${break.informationTable.inmoney }</td> <td class="boat-td" align="left">${break.informationTable.inpunish }</td> <td class="boat-td">${break.operatorTable.oname }</td> <td class="boat-td">${break.vitime }</td> <td class="boat-td">${break.viaddress }</td> <td class="boat-td">${break.vireason }</td> <td class="boat-td" style="border-right:#C5C5C5 1px solid;"> <a href="javascript:ubreak(${break.viid},'${break.driveTable.sailorTable.sname}','${break.driveTable.boatTable.bchinesename }','${break.driveTable.bfirst}','${break.driveTable.bcheck}','${break.informationTable.incode }','${break.informationTable.inillegal }','${break.informationTable.inbasis }','${break.informationTable.ingrade }',${break.informationTable.inmoney },'${break.informationTable.inpunish }','${break.operatorTable.oname }','${break.vitime }','${break.viaddress }','${break.vireason }',${mapbreak.page })">修改</a> <a href="break!delOneBreak.do?viid=${break.viid }" οnclick="return(confirm('真的要刪除嗎?'))">刪除</a> </td> </tr> </s:iterator> <!-- 分頁 --> <tr align="right"> <td colspan="13"> <font style="font-size:12px; color:black;">當前${mapbreak.page}頁 共${mapbreak.count}頁,${mapbreak.num}條</font> <a href="break!getAllBreak.do?page=1">【首 頁】</a> <a href="break!getAllBreak.do?page=${mapbreak.page-1}">【上一頁】</a> <a href="break!getAllBreak.do?page=${mapbreak.page+1}">【下一頁】</a> <a href="break!getAllBreak.do?page=${mapbreak.count}">【末 頁】</a> <a href="break!getAllBreak.do">【返回初始頁】</a> </td> </tr> </s:if> <s:if test="#request.mapbreak.num==0"> <tr height="100" valign="middle"> <td colspan="14" align="center"><font size="14">對不起,沒有您想要的結果!</font></td> </tr> <tr> <td colspan="14" align="right"><a href="break!getAllBreak.do">【返回初始頁】</a></td> </tr> </s:if> </table>
js程式碼:
var bcha=document.getElementById("bchaxun"); function bfocuscha(){ bcha.value=""; } function bclickcha(){ var bxun=bcha.value; if(document.getElementById("zh").checked){ location="break!getAllBreak.do?bname="+bxun+"&flag=zh"; return; } if(document.getElementById("yi").checked){ location="break!getAllBreak.do?bname="+bxun+"&flag=en"; return; } }
在dao包中,如果模糊查詢的條件不是外來鍵的情況:
/*條件查詢分頁顯示*/ public List checkPageQuery(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); 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 query.list(); }}); }
//求按條件查詢的船舶的總條數 public Integer countcBoat(final String bname, final String flag) { return (Integer) getHibernateTemplate().execute(new HibernateCallback(){ public Object doInHibernate(Session session) throws HibernateException, SQLException { String hql = "select count(*) from BoatTable where 1=1"; StringBuffer sb=new StringBuffer(hql); if(bname!="" && bname!=null && "en".equals(flag)){ sb.append(" and benglishname like '%"+bname+"%'"); } if(bname!="" && bname!=null && "ch".equals(flag)){ sb.append(" and bchinesename like '%"+bname+"%'"); } String sql=sb.toString(); Query query = session.createQuery(sql); Object object = query.uniqueResult(); return Integer.parseInt(object.toString()); }}); } public String getHQL(final String bname, final String flag) { String hql="from BoatTable where 1=1"; StringBuffer sb=new StringBuffer(hql); if(bname!="" && bname!=null && "en".equals(flag)){ sb.append(" and benglishname like '%"+bname+"%'"); } if(bname!="" && bname!=null && "ch".equals(flag)){ sb.append(" and bchinesename like '%"+bname+"%'"); } sb.append(" order by bid desc"); String sql=sb.toString(); return sql; }
在dao包下面,如果模糊查詢的條件是外來鍵的情況:
//分頁顯示所有船舶違章的具體資訊 public List getAllBreakInformation(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); 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<ViolationTable> list = query.list(); SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); String string; Date vitime; for (ViolationTable object : list) { string=sdf.format(object.getVitime()); vitime=java.sql.Date.valueOf(string); object.setVitime(vitime); } return list; }}); } //求船舶違章記錄的總條數 public Integer countBoatBreak(final String bname,final String flag) { 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(bname!="" && bname!=null && "en".equals(flag)){ sb.append(" and driveTable.boatTable.benglishname like '%"+bname+"%'"); } if(bname!="" && bname!=null && "zh".equals(flag)){ sb.append(" and driveTable.boatTable.bchinesename like '%"+bname+"%'"); } String sql=sb.toString(); Query query = session.createQuery(sql); Object object = query.uniqueResult(); return Integer.parseInt(object.toString()); }}); } public String getHQL(final String bname,final String flag) { String hql="from ViolationTable where 1=1"; StringBuffer sb=new StringBuffer(hql); if(bname!="" && bname!=null && "en".equals(flag)){ sb.append(" and driveTable.boatTable.benglishname like '%"+bname+"%'"); } if(bname!="" && bname!=null && "zh".equals(flag)){ sb.append(" and driveTable.boatTable.bchinesename like '%"+bname+"%'"); } sb.append(" order by viid desc"); String sql=sb.toString(); return sql; }
在service下面:
//分頁顯示所有船舶違章資訊 public Map getAllBreak(String bname,String flag,int page,int size){ String hql=violationDao.getHQL(bname, flag); System.out.println("hql="+hql); //求總條數 int num = violationDao.countBoatBreak(bname, flag); 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 ; List list = violationDao.getAllBreakInformation(hql, page, size); 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 getAllBreak(){ 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"); System.out.println("bname="+bname); String flag=request.getParameter("flag"); System.out.println("flag="+flag); Map map = breakService.getAllBreak(bname, flag, page, size); request.setAttribute("mapbreak", map); List operator=breakService.findAllOpe(); request.setAttribute("operator", operator); List infor=breakService.findAllInfor(); request.setAttribute("information", infor); return "ShowBreak"; }