mysql分页从页面到sql从头到尾详解

1.ResponseVo 定义分页属性

public class ResponseVo {
	private String resultNote;
	private Integer result;
	private Integer statu;
	private Integer totalCount;
	private Integer pageIndex;
	private Integer pageSize;
	private Integer currPageCount;
	private Object detail;
	private List<Object> detailList;
}

2.PageHelp 定义同样相关属性

public class PageHelp {
	
	private Integer pageIndex;//当前第几页 下标从0开始
    private Integer pageSize;//每页展示多少条数据
	private Integer totalCount;//数据总数
	private Integer currPageCount;//当前页数据个数
	private Integer totalIndex;//总页数
}

/**
	 * 判断json传过来的数据有无分页需求，格式化，默认值等设置
	 * @param jsonString
	 * @return
	 */
	public PageHelp isHasPageHelp(String jsonString){
		this.pageIndex = 0;
		this.pageSize =  10;
		JSONObject json = JSONObject.parseObject(jsonString);
		if(json.getString("pageSize") !=null){
			if(json.getString("pageSize").trim().matches("^[0-9]*$")){
				this.pageSize = Integer.parseInt(json.getString("pageSize").trim());
			}
			if(this.pageSize>50){//分页如果一页显示大于50条数据，则只能以50条每页分页
				this.pageSize = 50;
			}
		}
		if(json.getString("pageIndex") !=null){
			if(json.getString("pageIndex").trim().matches("^[0-9]*$")){
				this.pageIndex = Integer.parseInt(json.getString("pageIndex").trim());
			}
		}
		return selectPageCriteria(this.pageIndex, this.pageSize);
		
	}

/**
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public PageHelp selectPageCriteria(Integer pageIndex,Integer pageSize){
		PageHelp pageHelp = new PageHelp();
		if(pageIndex!=0){
			pageIndex = (pageIndex)*pageSize;
		}else{
			pageIndex = 0;
		}
		
		pageHelp.setPageIndex(pageIndex);
		pageHelp.setPageSize(pageSize);
		return pageHelp;
	}
	
	

上面两个方法对

pageIndex 和PageSize进行验证，然后map.put("pageHelp", pageHelp.isHasPageHelp(param));

List<UserLog> userLogsLis = userLogService.listUserLog(map);

int total = userLogService.totalUserLog(map);

<select id="listUserLog" parameterType="map" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
     from cp_user_log
     <where>
     <if test="loginName != null">
       and login_name = #{loginName,jdbcType=VARCHAR}
     </if>
     <if test="level != null">
       and level = #{level,jdbcType=VARCHAR}
     </if>
     <if test="keyWord != null">
       and key_word = #{keyWord,jdbcType=VARCHAR}
     </if>
     <if test="otherParamVo.addTimeBegin != null">
      <![CDATA[ and create_time >=#{otherParamVo.addTimeBegin,jdbcType=TIMESTAMP}]]>
      </if>
      <if test="otherParamVo.addTimeEnd != null">
     <![CDATA[ and create_time <= #{otherParamVo.addTimeEnd,jdbcType=TIMESTAMP}]]>
      </if>
     <if test="loginNameType != null">
     and login_name_type = #{loginNameType,jdbcType=INTEGER}
     </if>
   </where>
    <if test="pageHelp.pageIndex != null and pageHelp.pageSize != null">
       limit #{pageHelp.pageIndex},#{pageHelp.pageSize}
    </if>
  </select>

以上是从java到sql的分页内容

--------------------------------------------------

接下来看从java到前段：

resVo.setPageIndex(pageHelp.getPageIndex());
		resVo.setPageSize(pageHelp.getPageSize());
		pageHelp.setCurrPageCount(userLogsLis.size());
		resVo.setCurrPageCount(userLogsLis.size());
		resVo.setDetail(userLogsLis);
		resVo.setTotalCount(total);
		
		return JSONObject.toJSONString(resVo, SerializerFeature.WriteMapNullValue,
				SerializerFeature.WriteNullListAsEmpty, SerializerFeature.WriteNullStringAsEmpty,
				SerializerFeature.WriteDateUseDateFormat);

返回了Json  resVo到前台;

JS:定义参数：

var vmm=new Vue({
	 el:'#searchbox',
	 data:{ 
		 //用户类型（0为普通用户、1为管理员）
		 loginNameType_datas: [{num:'-1',value:'全部'},{num:'0',value:'普通用户'},{num:'1',value:'管理员'}],
 		 loginNameType_selected:'-1',
 		 
		//日志级别
		 level_datas: [{num:'-1',value:'全部'},{num:'0',value:'正常'},{num:'1',value:'异常'}],
		 level_selected:'-1',
		 
		//日志内容
		 keyWordName_datas:[{name:'全部'}],
		 keyWordName_selected:"全部",
		 
		 loginName:"",
		 orderId:"",
		 userLogList:[],
		 pageNum:1,
		 pageIndex:1,
		 pageChoice:1,
		 orderId:"",
		 userId:"",
		 dateTime:'',
		 currentPage:1,
		 totalPage:0,
		 pageSize:10,
	 },

submit: function(pageIndex,searchId){
			 
			 this.userLogList=[];
			 var data= {};//此为提交到后台data
			 //var id = this.orderId;
			 var loginNameType = this.loginNameType_selected;
			 var loginName = this.loginName;
			 var level = this.level_selected;
			 var keyWord = this.keyWordName_selected;
			 
			 if(loginNameType != -1){
				 data.loginNameType = loginNameType;
			 }
			 if(loginName != ""){
				 data.loginName = loginName;
			 }
			 if(this.dateTime != ''){
				 data.createTimeBegin = this.dateTime[0];
				 data.createTimeEnd = this.dateTime[1];
			 }
			 if(level != -1){
				 data.level = level;
			 }
			 if(keyWord != "全部"){
				 data.keyWord = keyWord;
			 }
			 data.pageIndex = pageIndex-1;
			 data.pageSize = this.pageSize;
			 this.pageIndex = pageIndex;
			
			 this.$http.post(
				 '/cpmgr/listUserLog',
				 data
				 
			 ).then(function(data){//此为后台传来的data
				 
				 var json = data.body;
				 var userLogList = data.body.detail;
					
					var obj={};
					for(var i =0;i<userLogList.length;i++){
						obj = userLogList[i];
						
						if(obj.loginNameType==0){
							obj.loginNameType="用户";
						}
						if(obj.loginNameType==1){
							obj.loginNameType="管理员";
						}
						if(obj.level==0){
							obj.level="正常";
						}
						if(obj.level==1){
							obj.level="异常";
						}
						vmm.userLogList.push(obj);
					}
					var pageNum = Math.ceil(json.totalCount/json.pageSize);
					vmm.pageNum = pageNum;
					this.totalPage = json.totalCount;
                   console.log(userLogList);
			 });
			 
		 },

jsp:

<!--page start-->
				<ul class="page">
				    	 <el-pagination background 
				    	 @size-change="handleSizeChange" 
				    	 @current-change="handleCurrentChange" 
				    	 :current-page="currentPage"
                         :page-sizes="[10, 20, 50, 100]" 
                         :page-size="10"
                         layout="total, sizes, prev, pager, next, jumper"
                         :total="totalPage">
                          </el-pagination>
				</ul>
				<!--page end-->