springJDBC实现mysql简单分页
效果图:
前台jsp代码如下:
<div class="listNav"> <div class="instruction"> 您正在查看${result }个结果中的第${start+1 }-${limit }项结果。 </div> <fieldset><legend>首页</legend> <input type="button" name="eventSubmit_doList_first" onclick="javascript:window.location.href='${pageContext.request.contextPath}/cw/allSiteCourse.do?start=0&limit=${limit}'" value="|<"> </fieldset> <fieldset><legend>前一页</legend> <input type="button" name="eventSubmit_doList_prev" onclick="javascript:window.location.href='${pageContext.request.contextPath}/cw/allSiteCourse.do?start=${start-limit }&limit=${limit}'" value="<"> </fieldset> <select name="selectPageSize" id="selectPageSize" onchange="changePager(this.value)"> <option value="5">显示5项…</option> <option value="10">显示10项…</option> <option value="20" selected>显示20项…</option> <option value="50">显示50项…</option> <option value="100">显示100项…</option> <option value="200">显示200项…</option> <fieldset><legend>后一页</legend> <input type="button" name="eventSubmit_doList_next" onclick="javascript:window.location.href='${pageContext.request.contextPath}/cw/allSiteCourse.do?start=${start+limit }&limit=${limit}'" value=">"> </fieldset> <fieldset><legend>尾页</legend> <input type="button" name="eventSubmit_doList_last" onclick="javascript:window.location.href='${pageContext.request.contextPath}/cw/allSiteCourse.do?start=${result }&limit=${limit}'" value=">|"> </fieldset> </div>
controller如下:
@RequestMapping(value="/allSiteCourse",method=RequestMethod.GET) public String allSiteCourse(HttpServletRequest request,Pager pager){ //查询数据库中所有的条数 int result = cwInfoService.queryAllSitesCount(); //设置分页 pager = cwInfoService.setPage(pager, result); List<Sites> siteList = cwInfoService.queryAllSites(pager); request.setAttribute("siteList", siteList); request.setAttribute("start", pager.getStart()); request.setAttribute("limit", pager.getLimit()); request.setAttribute("result", result); return "/jsp/coursewareSelectAllSites"; }
service,以及实现类如下:
/** * 描述:查询出所有的站点总共有多少个 * @return */ int queryAllSitesCount(); /** * 描述:设置分页的起始条数 * @param pager * @return */ Pager setPage(Pager pager,int result);
@Override public int queryAllSitesCount() { return cwInfoDao.queryAllSitesCount(); } @Override public Pager setPage(Pager pager,int result) { //总共多少页 int pageCount = 0; //最后一页的起始条数 int lastStart = 0; if(pager.getLimit()!=null){ //根据余数判断最后一页是否是满 if(result%pager.getLimit()==0){ //计算出当前数据总共有多少页 pageCount = result/pager.getLimit(); //当前页数小于一页 if(pageCount<=1){ lastStart = 0; }else{ lastStart = result-pager.getLimit(); } }else{ pageCount = (result/pager.getLimit())+1; if(pageCount<=1){ lastStart = 0; }else{ lastStart = (pageCount-1)*pager.getLimit(); } } } //设置分页 if(!(pager.getStart()!=null&&pager.getLimit()!=null)){ pager.setStart(0); pager.setLimit(20); //前一页出现负值情况 }else if(pager.getStart()<0){ pager.setStart(0); //当前页的数据不够一页 }else if(result-pager.getLimit()<0){ pager.setStart(0); //下一页出现超出数据情况 }else if(pager.getStart()>=result){ pager.setStart(lastStart); } return pager; }
dao以及底层springJDBC:
/** * 描述:查询所有的站点信息 * @return */ List<Sites> queryAllSites(Pager pager); /** * 描述:查询出所有的站点总共有多少个 * @return */ int queryAllSitesCount();
@Override public List<Sites> queryAllSites(Pager pager) { String sql = "select * from sakai_site order by SITE_ID limit ?,?"; Object[] obj = new Object[]{pager.getStart(),pager.getLimit()}; List<Sites> list = jdbcTemplate.query(sql,obj, new siteMapper()); return list; } @Override public int queryAllSitesCount() { String sql = "select count(*) from sakai_site"; int result = jdbcTemplate.queryForObject(sql,Integer.class); return result; }
class siteMapper implements RowMapper<Sites>{ @Override public Sites mapRow(ResultSet rs, int rowNum) throws SQLException { Sites site = new Sites(); site.setCreatedby(rs.getString("CREATEDBY")); site.setCreatedon(rs.getDate("CREATEDON")); site.setCustomPageOrdered(rs.getString("CUSTOM_PAGE_ORDERED")); site.setDescription(rs.getString("DESCRIPTION")); site.setIconUrl(rs.getString("ICON_URL")); site.setInfoUrl(rs.getString("INFO_URL")); site.setIsSoftlyDeleted(rs.getString("IS_SOFTLY_DELETED")); site.setIsSpecial(rs.getString("IS_SPECIAL")); site.setIsUser(rs.getString("IS_USER")); site.setJoinable(rs.getString("JOINABLE")); site.setJoinRole(rs.getString("JOIN_ROLE")); site.setModifiedby(rs.getString("MODIFIEDBY")); site.setModifiedon(rs.getDate("MODIFIEDON")); site.setPublished(rs.getInt("PUBLISHED")); site.setPubview(rs.getString("PUBVIEW")); site.setShortDesc(rs.getString("SHORT_DESC")); site.setSiteId(rs.getString("SITE_ID")); site.setSkin(rs.getString("SKIN")); site.setSoftlyDeletedDate(rs.getDate("SOFTLY_DELETED_DATE")); site.setTitle(rs.getString("TITLE")); site.setType(rs.getString("TYPE")); return site; } }
还有一个简短的js:
//分页 function changePager(obj){ window.location.href=ctx + "/cw/allSiteCourse.do?start=0&limit="+obj; } $(document).ready(function(){ if(limit!=''){ $('option[value='+limit+']').attr('selected',true); } });