Mysql分页－Limit

一．Mysql 的Limit用法

在我们使用查询语句的时候，经常会返回一部分，即按页返回．
mysql已经为我们提供了这样一个功能．

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)．

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1 
// 检索记录行 96-last.
mysql> SELECT * FROM table LIMIT 95,-1; 

//如果只给定一个参数，它表示返回最大的记录行数目
//换句话说，LIMIT n 等价于 LIMIT 0,n
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

二．分页的思想
使用Limit用法实现分页，最关键的就是获得他的两个参数．
我们可以根据选择的页数来获得偏移量（int offset = (currPage - 1) * PageParam.pageSize ）每页所显示的记录数即为显示的最大数目．

三．分页的实现
分页类

public class PageParam {
    private int currPage ; // 当前页
    private int totalPage ; // 总页
    private int rowCount ; // 总记录数
    public static int pageSize = 2; // 页大小
    private List<Article> data ; // 数据
    public int getCurrPage() {return currPage;
    }
    public void setCurrPage(int currPage) {
        this.currPage = currPage;
    }
    public int getTotalPage() {
        return totalPage;
    }
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    public int getRowCount() {
        return rowCount;
    }
    public void setRowCount(int rowCount) {
        //根据总记录数确定总页数
        int totalPage = rowCount / pageSize;
        if (rowCount % pageSize > 0) {
            totalPage += 1;
        }
        setTotalPage(totalPage);
        this.rowCount = rowCount;
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public List<Article> getData() {
        return data;
    }
    public void setData(List<Article> data) {
        this.data = data;
    }
}

Service层

@Service
public class Services {
    @Resource
    Dao1 dao;
    public Configure getConfigured(){
        return dao.getConfigured();
    }
    public void setConfigure(Configure configure){
        dao.setConfigure(configure);
    }
    public int getRowCount() {return dao.getRowCount();}
    public PageParam getArticle(PageParam pageParam) {
        int currPage = pageParam.getCurrPage();
        // limit offset, size
        int offset = (currPage - 1) * PageParam.pageSize ;
        int size = PageParam.pageSize;
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("offset", offset);
        params.put("size", size);

        List<Article> articleList = dao.getArticle(params);
        pageParam.setData(articleList);

        return pageParam;
    }

Controller层

@Controller
public class ArticleController {

    @Resource
    Services service;

    //文章列表
    @RequestMapping(value = "/article",method = RequestMethod.GET)
    public String getArticle(Model map,HttpServletRequest request){
        String currPageStr = request.getParameter("page");
        int currPage = 1;
        try {
            currPage = Integer.parseInt(currPageStr);
        } catch (Exception e) {
        }
        // 获取总记录数
        int rowCount = service.getRowCount();
        PageParam pageParam = new PageParam();
        pageParam.setRowCount(rowCount);
        if (pageParam.getTotalPage() < currPage) {
            currPage = pageParam.getTotalPage();
        }
        pageParam.setCurrPage(currPage);
        pageParam = service.getArticle(pageParam);
        List<String> allcategory = service.getAllCategory();
        map.addAttribute("pageParamData", pageParam.getData());
        map.addAttribute("allcategory",allcategory);
        request.setAttribute("pageParam", pageParam);

        return "article";
    }

Mapper

<mapper namespace="com.dao.Dao">
    <select id="getRowCount" resultType="int">
        select count(*) from article WHERE deleted = 'n'
    </select>

    <select id="getArticle" resultType="Article" parameterType="map">
        select * from article
        where deleted = 'n'
        limit ${offset}, ${size}
    </select>
</mapper>