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);
    }
});

springJDBC实现mysql简单分页

推荐阅读