分页查询思路与实现

1. controller层

@Controller
public class AttendanceEquipmentCmdController {
	@Resource(name = "attendanceEquipmentCmdService")
	private AttendanceEquipmentCmdService attendanceEquipmentCmdService;
	
	//获得分页列表
	@RequestMapping(value="/getDeviceCmdChildrenList",method = RequestMethod.POST)
	public void getDeviceCmdChildrenList(HttpServletRequest request,HttpServletResponse response,String cmd_devid,
	String pageno) {
	    // 第一步：将当前页，每页记录条数，记录的总条数封装到实体类page中
		Page page = new Page(Integer.parseInt(pageno), 12, attendanceEquipmentCmdService.getDeviceCmdTotal(cmd_devid));
		//第二步 ：获取当前页的数据集合
		List<AttendanceEquipmentCmd> list = attendanceEquipmentCmdService.getDeviceCmdChildrenList(cmd_devid, page.getPageno(), page.getPagesize());
		//第三步：将数据集合封装到实体类page中
		page.setPagelist(list);
		//第四步：获取json工具类，将封装好的实体类对象转成json格式返回到当前页面
		JsonUtil out = new JsonUtil(request,response);
		out.outObjString(page);
	}
}

2. Page实体类对象

public class Page {
	// 总页数
	@SuppressWarnings("unused")
	private int totalpage;
	// 总记录数
    private int totalcount; 
    // 当前页
    private int pageno;
	// 每页的数量
    private int pagesize = 12; 
    //当前页list
    private List<Object> pagelist;
    
    //重构构造方法
    /**
     * 
     * @param pageNo 当前页
     * @param pageSize 每页记录条数
     * @param totalCount 记录的总条数
     */
     
    public Page(int pageNo,int pageSize,int totalCount) {
    	setTotalcount(totalCount);
		setPagesize(pageSize);
		getTotalpage();
		setPageno(pageNo);
    }
    
	@SuppressWarnings("rawtypes")
	public List getPagelist() {
		return pagelist;
	}
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void setPagelist(List pagelist) {
		this.pagelist = pagelist;
	}
	public int getPageno() {
		return pageno;
	}
	public void setPageno(int pageno) {
		if(pageno>getTotalpage()) {
			pageno = getTotalpage();
		}
		this.pageno = pageno;
	}
	
	public int getTotalpage() {
		int item = this.totalcount%this.pagesize;
		if(item>0) {
			this.totalpage = this.totalcount/this.pagesize+1;
			return this.totalcount/this.pagesize+1;
		}else {
			this.totalpage = this.totalcount/this.pagesize;
			return this.totalcount/this.pagesize;
		}
	}
	
	public void setTotalpage(int totalpage) {
		this.totalpage = totalpage;
	}
	public int getTotalcount() {
		return totalcount;
	}
	public void setTotalcount(int totalcount) {
		this.totalcount = totalcount;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}

}

3. mappin.xml中的sql语句

    <!-- 通过设备id获得设备分页总数 -->
	<select id="getDeviceCmdTotal" resultType="java.lang.Integer">
		SELECT count( * ) FROM fx_cmd  WHERE cmd_devid = #{cmd_devid}
	</select>
	
	<!-- 通过设备id获得设备分页列表 -->
	<select id="getDeviceCmdChildrenList" resultType="com.zhiruan.attendance.bean.AttendanceEquipmentCmd">
		SELECT
			* 
		FROM
			( SELECT ROW_NUMBER ( ) OVER ( ORDER BY fc.cmd_submittime ) AS ROWNUMBER, fc.* FROM fx_cmd fc WHERE cmd_devid = #{cmd_devid}  ) AS MODULE 
		WHERE
	     	ROWNUMBER BETWEEN (#{pageno}-1)*#{pagesize}+1 AND #{pagesize}*#{pageno}
	    ORDER BY cmd_submittime DESC
	</select>

4. jsp页面

<form id="cfom">
         //相当于 缓存调用此页面时传来的参数
		<input type="hidden" id="ledid" name="ledid" value="<%=ledid %>">
</form>

<div>
		 <table>
		        <thead>
		        <tr class="thead_tr">
		            <th>选择</th>
		            <th>条屏id</th>
		            <th>窗口号</th>
		            <th>x轴开始位置</th>
		            <th>y轴开始位置</th>
		            <th>窗口宽度</th>
		            <th>窗口高度</th>
		            <th>窗口位置</th>
		            <th>是否启用</th>
		            <th>欢迎词</th>
		            <th>大厅分区</th>
		        </tr>
		        </thead>
		        <tbody id="orglist">
		
		        </tbody>
	 </table>
	 <div id="turnpagediv" class="page_div">
	 
	 </div>
</div>

5. js页面

/*
   1：表明初始化页面时默认访问第一页的数据
   getDeviceWindowChildrenList.action：请求后端的url
   cfom :是前端form表单标签，缓存着参数
   orglist：table表中tbody标签的id值
   turnpagediv：页面上分页的<div>标签id值


*/
$(function() {
	setlisthtml(1, "getDeviceWindowChildrenList.action", "cfom", "orglist", "turnpagediv");
})

// 窗口分页列表
function setlisthtml(pageno, urltemp, cfomid, devicelistid, turnpagedivid) {
	var status = {
			'0':'否',	
			'1':'是',	
		}
	var statusqu = {
			'A':'A区',	
			'B':'B区',	
			'C':'C区',	
			'D':'D区',	
			'E':'E区',	
			'F':'F区',	
			'J':'G区',	
			'H':'H区',	
			'I':'I区',	
			'J':'J区',	
		}
	
	// 获取page
	var page = getpagelist2(pageno, urltemp, cfomid);
	// 翻页html
	setturnpagehtml(page, urltemp, turnpagedivid, cfomid, devicelistid);
	var list = page.pagelist;
	var htm = "";
	for (var i = 0; i < list.length; i++) {
		var obj = list[i];
		htm = htm + "<tr class='kind_tr'>"+ 
						"<td><input type='checkbox' name='ledwinid' value='" + obj.ledwinid + 
						"'ledid='" + obj.ledid + "' winno='" + obj.winno + 
						"'x='" + obj.x + "' y='" + obj.y + 
						"'widthw='" + obj.width + "' heighth='" + obj.height + 
						"'pos='" + obj.pos + "' enable='" + obj.enable + "' /></td>" + 
						"<td>" + obj.ledid + "</td>" + 
						"<td>" + obj.winno + "</td>" + 
						"<td>" + obj.x + "</td>" + 
						"<td>" + obj.y + "</td>" + 						
						"<td>" + obj.width + "</td>" + 
						"<td>" + obj.height + "</td>" + 
						"<td>" + obj.pos + "</td>" + 						
						"<td>" +  ( status[obj.enable] == undefined ? "" : status[obj.enable] ) + "</td>" +
						"<td>" + obj.welcome + "</td>" + 
						"<td>" +  ( statusqu[obj.hallarea] == undefined ? "" : statusqu[obj.hallarea] ) + "</td>" +
					+ "</tr>";
	}
	$("#" + devicelistid).html("");
	$("#" + devicelistid).html(htm);
}

//获取特定页的list
function getpagelist2(pageno,urltemp,cfomid){
	 urltemp = urltemp+"";
	 if(urltemp.indexOf("?")>0){
		 urltemp = urltemp+"&pageno="+pageno;
	 }else{
		 urltemp = urltemp+"?pageno="+pageno;
	 }
	 var page;
	 $.ajax({
	    	type:"post",
	    	url:getRootPath_web()+"/"+urltemp,
	    	data: $('#'+cfomid).serialize(),   // 取出form表单中的值作为入参
	    	async:false,
	    	success:function(result){
	 			page = result.data;
	    	}
	    });	
	 return page;
}

/**
 * @param page 存放list
 * @param url  
 * @param cfomid 查询条件form
 * @param turnpagedivid 翻页div id
 * @returns 设置翻页div html
 */
function setturnpagehtml(page,url,turnpagedivid,cfomid,listid){
	//设置翻页div为空
	$("#"+turnpagedivid).html("");
	//当前页
	var pageno = page.pageno;
	//总的记录条数
	var totalcount = page.totalcount;
	//总的页数
	var totalpage = page.totalpage;
	var htm = "共<span>"+totalcount+"</span>条记录&nbsp;&nbsp;当前页<span>"+pageno+"/"+totalpage+"</span>页"
	htm = htm+"<ul class='am-pagination'>"
	htm = htm+ "<li><a onclick=\"setlisthtml(1,'"+url+"','"+cfomid+"', '"+listid+"','"+turnpagedivid+"')\">«</a></li>";
	/*for(var i = 0; i<totalpage;i++){
		htm = htm+"<li><a href='#' onclick=\"setlisthtml("+(i+1)+",'"+url+"','"+cfomid+"', '"+listid+"','"+turnpagedivid+"')\" >"+(i+1)+"</a></li>";
	}*/
	//上一页
	htm += "<li>"+
				"<a onclick=\"setlisthtml("+((pageno-1)<=0?1:(pageno-1))+",'"+url+"','"+cfomid+"', '"+listid+"','"+turnpagedivid+"')\" >上一页</a>"+
			"</li>";
	//下一页
	htm += "<li>"+
				"<a onclick=\"setlisthtml("+((pageno+1)>totalpage?totalpage:(pageno+1))+",'"+url+"','"+cfomid+"', '"+listid+"','"+turnpagedivid+"')\" >下一页</a>"+
			"</li>";
	
	htm = htm +"<li><a onclick=\"setlisthtml('"+totalpage+"','"+url+"','"+cfomid+"', '"+listid+"','"+turnpagedivid+"')\">»</a></li>";
	htm =htm +"</ul>";
	$("#"+turnpagedivid).html(htm);
}