mysql分页查询

#进阶8：分页查询 ★
/*

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;
	
	size=10
	page  第一条记录编号
	1	  0
	2  	  10
	3	  20
	
*/
#案例1：查询前五条员工信息


SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;


#案例2：查询第11条——第25条
SELECT * FROM  employees LIMIT 10,15;


#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

计算分页查询的页码

//数据库记录数目
int totalsize
//页码数目 
int totalno
//每页记录数
int pagesize
if（totalsize%pagesize==0）{
    totalno=totalsize/pagesize;
}else{
    totalno=totalsize/pagesize+1;
}

 

分页查询java代码

 1.分页对象

package com.atguigu.atcrowdfunding.bean;

import java.util.List;

public class Page<T> {

	private List<T> datas;
	private int pageno;
	private int totalno;
	private int totalsize;
	public List<T> getDatas() {
		return datas;
	}
	public void setDatas(List<T> datas) {
		this.datas = datas;
	}
	public int getPageno() {
		return pageno;
	}
	public void setPageno(int pageno) {
		this.pageno = pageno;
	}
	public int getTotalno() {
		return totalno;
	}
	public void setTotalno(int totalno) {
		this.totalno = totalno;
	}
	public int getTotalsize() {
		return totalsize;
	}
	public void setTotalsize(int totalsize) {
		this.totalsize = totalsize;
	}
	
	
}

2.公共返回数据模型

package com.atguigu.atcrowdfunding.bean;

public class AJAXResult {

	private boolean success;
	private Object data;

	public boolean isSuccess() {
		return success;
	}

	public void setSuccess(boolean success) {
		this.success = success;
	}

	public Object getData() {
		return data;
	}

	public void setData(Object data) {
		this.data = data;
	}
	
}

3.controller方法

	@ResponseBody
	@RequestMapping("/pageQuery")
	public Object pageQuery( String queryText, Integer pageno, Integer pagesize ) {
		
		AJAXResult result = new AJAXResult();
		
		try {
			
			// 分页查询
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("start", (pageno-1)*pagesize);
			map.put("size", pagesize);
			map.put("queryText", queryText);
			
			List<User> users = userService.pageQueryData( map );
			// 当前页码			
			// 总的数据条数
			int totalsize = userService.pageQueryCount( map );
			// 最大页码（总页码）
			int totalno = 0;
			if ( totalsize % pagesize == 0 ) {
				totalno = totalsize / pagesize;
			} else {
				totalno = totalsize / pagesize + 1;
			}
			
			// 分页对象
			Page<User> userPage = new Page<User>();
			userPage.setDatas(users);
			userPage.setTotalno(totalno);
			userPage.setTotalsize(totalsize);
			userPage.setPageno(pageno);
			
			result.setData(userPage);
			result.setSuccess(true);
		} catch ( Exception e ) {
			e.printStackTrace();
			result.setSuccess(false);
		}
		
		return result;
		
	}

4.service，dao

package com.atguigu.atcrowdfunding.service;

import java.util.List;
import java.util.Map;

import com.atguigu.atcrowdfunding.bean.User;

public interface UserService {

	List<User> pageQueryData(Map<String, Object> map);

	int pageQueryCount(Map<String, Object> map);

}




package com.atguigu.atcrowdfunding.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Select;

import com.atguigu.atcrowdfunding.bean.User;

public interface UserDao {
	
	List<User> pageQueryData(Map<String, Object> map);

	int pageQueryCount(Map<String, Object> map);
}

5.mapper

<select id="pageQueryData" resultType="com.atguigu.atcrowdfunding.bean.User">
        select
            *
        from t_user
        <where>
            <if test="queryText != null">
            and loginacct like concat('%', #{queryText}, '%')
            </if>
        </where>
        order by createtime desc
        limit #{start}, #{size}
    </select>
    
    <select id="pageQueryCount" resultType="int">
        select
            count(*)
        from t_user
        <where>
            <if test="queryText != null">
            and loginacct like concat('%', #{queryText}, '%')
            </if>
        </where>
    </select>