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,nmysql> 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层

@Servicepublic 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层

@Controllerpublic 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>