动态查询和分页

一、xml配置

<!-- 获取动态获取设备总数 -->
    <select id="getAllCcListCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(1) from routerinfo
        where 1=1
        <isNotNull prepend="and" property="sn">
            SN like concat('%', #sn#, '%')
        </isNotNull>
        <isNotNull prepend="and" property="version">
            version like concat('%', #version#, '%')
        </isNotNull>
    </select>
    
    <!-- 后台动态查询 获取设备信息分页列表 -->
    <select id="getCcInfoList" resultClass="CCinfo" parameterClass="java.util.Map">
        select ri.SN,ri.version,ri.remoteip,ri.wanip,ri.updateTime 
        from routerinfo ri
        where 1=1
        <isNotNull prepend="and" property="sn">
            ri.SN like concat('%', #sn#, '%')
        </isNotNull>
        <isNotNull prepend="and" property="version">
            ri.version like concat('%', #version#, '%')
        </isNotNull>
        limit #rowStart#, #pageSize#
    </select>

二、Controller

public ModelAndView toRouterList(@RequestParam(required = false) String sn,
            @RequestParam(required = false) String version,
            @RequestParam(required = false) String currentPage) {
        
        ModelAndView mav = new ModelAndView("router/routerList");
        
        //System.out.println("sn: "+sn+" version: "+version+" currentPage:"+currentPage);        
        
        int total = ccService.getAllCcListCount(sn, version);    //总记录数
        //System.out.println("总记录数:"+total);
        
        if(0 == total){        //查询没有该数据时返回null
            return null;
        }
        
        int pageSize = 10;    //每页显示记录数(默认10)
        
        if(total < pageSize){    //总记录数小于每页记录数时
            pageSize = total;    
        }
        
        /*int totalPage = (total % pageSize) == 0 ? (total / pageSize) : (total / pageSize + 1) ;    //总页数
        System.out.println("总页数:"+totalPage);*/
        
        if(null == currentPage || "".equals(currentPage) ){
            currentPage = "1";    //默认第一页
        }
        /*int rowStart = (Integer.valueOf(currentPage) -1) * pageSize;    //起始记录行数
        System.out.println("起始记录行数:"+rowStart);
        System.out.println("每页记录数："+ pageSize);*/
        
        PageUtil page = null;
        page = new PageUtil(pageSize, total, Integer.valueOf(currentPage));    // 每页显示的条数,总记录数,当前页
        
        List<CCinfo> cclist = null;
        cclist = ccService.getCcInfoList(sn, version, page.getStart(), page.getPageRecord());    //路由数据列表
        
        mav.addObject("page", page);    // 显示的页数
        mav.addObject("cclist", cclist);
        mav.addObject("sn", sn);
        mav.addObject("version",version);
        
        return mav;
    }

三、分页工具类PageUtil

/**
 * 分页工具包
 */
@SuppressWarnings("unused")
public class PageUtil {
    
    private int pageRecord;        // 每页的记录数
    private int totalRecord;    // 总记录数
    private int totalPage;        // 总页数
    private int currentPage = 1;    // 当前页（默认第一页）
  
    private int prePage;    // 上一页
    private int nextPage;    // 下一页
  
    private int pageNumStart;    // 页码显示开始listbegin;
    private int pageNumEnd;        // 页码显示结束listend;
    
    // 传给mybatis，limit start,size
    private int start;    //起始记录数
    private int size;    //每页显示的记录数
    
    private String pageurl;
      
    public PageUtil() {
          
    }
    
    /**
     * @parms pageRecord 每页面的记录数,totalRecord 总记录数,currentPage 当前页
     * @return
     */
    public PageUtil(int pageRecord, int totalRecord, int currentPage) {
        this.pageRecord = pageRecord;
        this.currentPage = currentPage;
        setTotalRecord(totalRecord);
        setPageNumEnd(pageNumEnd);
        setPageNumStart(pageNumStart);
    }
  
    public int getPageRecord() {
        return pageRecord;
    }
  
    public void setPageRecord(int pageRecord) {
        this.pageRecord = pageRecord;
    }
  
    public int getTotalRecord() {
        return totalRecord;
    }
  
    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
        //设置总页数
        setTotalPage(this.totalRecord % this.pageRecord == 0 ? this.totalRecord
                / this.pageRecord : this.totalRecord / this.pageRecord + 1);
    }
  
    public int getTotalPage() {
        return totalPage;
    }
  
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
  
    public String getPageurl() {
        return pageurl;
    }

    public void setPageurl(String pageurl) {
        this.pageurl = pageurl;
    }

    // 获取当前页
    public int getCurrentPage() {
        return currentPage;
    }
  
    // 设置当前页面
    public void setCurrentPage(int currentPage) {
        // 如果当前页数大于总页数，即当前页等于总页面数
        if (currentPage > getTotalPage()) {
            this.currentPage = getTotalPage();
        } else {
            if (currentPage < 1) {
                this.currentPage = 1;// 如果当前页小于1，默认是1
            } else {
                this.currentPage = currentPage;
            }
        }
    }
  
    // 获取下一页
    public int getNextPage() {
        return this.getCurrentPage() + 1;
    }
  
    // 获取上一页
    public int getPrePage() {
        return this.getCurrentPage() - 1;
    }
  
    public int getPageNumStart() {
        return pageNumStart;
    }
  
    // 设置起始页
    public void setPageNumStart(int pageNumStart) {
        this.pageNumStart = 1;
    }
  
    public int getPageNumEnd() {
        return pageNumEnd;
    }
  
    // 设置末页
    public void setPageNumEnd(int pageNumEnd) {
        this.pageNumEnd = totalPage;
    }
  
    public int getStart() {
        return (this.currentPage - 1) * pageRecord ;//+ 1;
    }
  
    public int getSize() {
        return this.currentPage * pageRecord;
    }
  
}

四、前端核心代码

<body>
    <div class="form-inline definewidth m20" id='tj'>
        <div align="center">
            序列号(S/N号):<input type="text" name="sn" id="sn"
            class="abc input-default" placeholder="" value="${sn}"
            maxlength="19" style="ime-mode:disabled" />&nbsp;&nbsp;
            
            版本号:<input type="text" name="version" id="version"
            class="abc input-default" placeholder="" value="${version}"
            maxlength="12" style="ime-mode:disabled" />&nbsp;&nbsp;
            <button class="btn btn-primary" onclick="page()">查询</button>&nbsp;&nbsp;&nbsp;&nbsp;
            <br/>
        </div>
        
        <table id="table1" class="table table-bordered table-hover definewidth m10">
            <thead>
                <tr>
                    <th id="order">序列号（S/N）</th>
                    <th>当前版本</th>
                    <th>所在地</th>
                    <th>WAN口IP</th>
                    <th>更新时间</th>
                    <!-- <th><span onclick="sort('sn');">最后登录时间(点击排序)</span></th> -->
                    <th>操作</th>
                </tr>
            </thead>
            <c:forEach items="${cclist}" var="cc" varStatus="status">
                <tr>
                    <td>${cc.SN }</td>
                    <td>${cc.version}</td>
                    <td>${cc.remoteip}</td>
                    <td>${cc.wanip}</td>
                    <td><fmt:formatDate value="${cc.updateTime}" pattern="yyyy-MM-dd HH:mm:ss"/></td>
                    <td>
                        <a href="javascript:void(0);" onclick="regularlyUpgraded('${cc.SN }')">定时升级</a>&nbsp;&nbsp;
                        <a href="javascript:void(0);" onclick="regularReporting('${cc.SN }')">间隔上报</a>&nbsp;&nbsp;
                        <a href="javascript:void(0);" onclick="sendUpgrade2device('${cc.SN }')">立即升级</a>                        
                    </td>
                </tr>
            </c:forEach>
        </table>
    </div>
    
    <div id="pagelist" class="inline pull-right page">
        <span>总记录 ${page.totalRecord} 条 / 共 ${page.totalPage} 页  第 ${page.currentPage} 页</span>
        <input onclick="page(this)" id="first" name="first" value="${page.pageNumStart}" type="image" src="${basePath}/static/Images/shouye.png">-
        <input onclick="page(this)" id="upPage" name="upPage" value="${page.prePage}" type="image" src="${basePath}/static/Images/shangyiye.png">-
        <input onclick="page(this)" id="nextPage" name="nextPage" value="${page.nextPage}" type="image" src="${basePath}/static/Images/xiayiye.png">-
        <input onclick="page(this)" id="last" name="last" value="${page.pageNumEnd}" type="image" src="${basePath}/static/Images/moye.png">        
    </div>
    
</body>
<script type="text/javascript">
//动态查询和分页
    function page(e) {
        
        var sn = $("#sn").val().trim();    //设备sn号
        //alert(sn);
        
        var version = $("#version").val().trim();    //设备版本号
        //alert(version);
        
        var currentPage = $(e).val();    //获取要显示的页数
        //alert(currentPage);
        
        var totalPage = ${page.totalPage};    //最大页数
        
        if(currentPage < 1){
            alert("已经是第一页了！");
        }else if(currentPage > totalPage){
            alert("已经是最后一页了！");
        }else{
            $.ajax({
                cache : false,
                type : "POST",
                url : "${basePath}/web/toRouterList",
                data : {
                    "sn" : sn,
                    "version" : version,
                    "currentPage":currentPage
                },
                async : true,
                error : function(request) {
                    alert("没有该设备！");
                    
                },
                success : function(data) {
                    
                    if (data) {
                        var html = data+"";
                        $("body").html(html);//要刷新的页面
                    }else{
                        alert("没有该设备！");
                        window.location.reload();
                    }
                }
            });
        }
            
    }
    
</script>