SpringMVC mybatis分页取得数据

引言

参考 SpringMVC分页查询和显示

在开发中，经常会遇到分页显示的问题，一个表中存在很多行数据，如果全部取过来恐怕会撑破系统资源。所以我们在前端显示时并不是显示全部数据，而是只显示当前页的数据，并通过下一页，前一页等操作切换数据。这就用到了分页。

前提

SpringMvc跟mybatis的ssm框架已经设置好。数据库我使用的时postgreSQL。

1. 创建表跟数据

create table m_user (
  username character varying(8) not null
  , password character varying(16)
  , auth character varying(16)
  , constraint m_user_pkey primary key (username)
);

插入11条数据

username	password	auth
0001	        0001	        admin
0002	        0002	        admin
0003	        0003	        user
0004	        0004	        user
0005	        0005	        user
0006	        0006	        user
0007	        0007	        user
0008	        0008	        user
0009	        0009	        user
0010	        0010	        user
0011	        0011	        user

在这里生成两个select语句，第一个用来获取表中数据的行数。第二个用来指定开始行跟行数获取。

• limit指定行数
• offset指定开始行。需要注意的是postgre中0代表第一行。假如从第5行开始取，则指定4。

<select id="selectUserCount" resultType="java.lang.Integer">
select count(*) from m_user
</select>

<select id="selectUsersByPage" resultMap="BaseResultMap">
select * from m_user order by username limit #{perPageUsers} offset #{start}
</select>

2. 创建Dao层

对应两个sql的接口文件。生成以下两个方法。

public interface MUserMapper {
    int selectUserCount();
    List<MUser> selectUsersByPage(@Param("start")int start, @Param("perPageUsers")int perPageUsers);
}

3. 创建service层

分别调用上述mapper接口。

@Service
public class TestPageableService {
	@Autowired
	MUserMapper mapper;
	
	public int selectUserCount() {
		return mapper.selectUserCount();
	}
	
	public List<MUser> selectUsersByPage(int start, int perPageUsers){
		return mapper.selectUsersByPage(start, perPageUsers);
	}
}

4.创建controller层

三个参数的作用如下

• page
  用于接收显示的某一页的请求，如果不指定，默认显示第一页。
• per_page
  用于接收显示一页中有多少行数据。如果不指定这里默认为5行。
• model
  把取到的数据数据返回前端。

@Controller
public class TestPageableController {
	@Autowired
	TestPageableService service;
	@Autowired
	PageAdapter p;
	
	@RequestMapping("page")
	public String findUsersByPage(@RequestParam(required = false, defaultValue = "1")Integer page, 
			@RequestParam(required = false, defaultValue = "5")Integer per_page,
			Model model) {
		
		p.setPageSize(per_page);
		p.setTotalUsers(service.selectUserCount());
		p.setCurrentPage(page);
		List<MUser> list = service.selectUsersByPage((page - 1) * p.getPageSize(), p.getPageSize());
		model.addAttribute("list", list);
		model.addAttribute("page", p);
		model.addAttribute("per_page",per_page);
		return "pageable";
    }

}

5.创建封装类

该类根据取到的数据行数，计算总页数（getTotalPages）。以及得到上一页，下一页。

@Component
public class PageAdapter {
    private int currentPage = 1;    
    private int totalPages;       
    private int totalUsers;          
    private int pageSize;    
    private int nextPage;
    private int prefPage;

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getTotalPages() {
        totalPages = totalUsers % pageSize == 0 ? totalUsers / pageSize : totalUsers / pageSize + 1;
        return totalPages;
    }

    public int getTotalUsers() {
        return totalUsers;
    }

    public void setTotalUsers(int totalUsers) {
        this.totalUsers = totalUsers;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getNextPage() {
        if (currentPage < totalPages) {
            nextPage = currentPage + 1;
        } else {
            nextPage = currentPage;
        }
        return nextPage;
    }

    public int getPrefPage() {
        if (currentPage > 1) {
            prefPage = currentPage - 1;
        } else {
            prefPage = currentPage;
        }
        return prefPage;
    }
}

6. 创建前端

<%@page contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt" %>
<html>
<head>
    <title>Test paging</title>
</head>
<body>
<h2>All users：</h2>

<table frame="box" rules="all">
    <tr>
        <td>name</td>
        <td>auth</td>
    </tr>
    <c:if test="${list!= null || fn:length(list) != 0}">
        <c:forEach items="${list}" var="user" begin="0" end="${fn:length(list) }">
            <tr>
                <td>${user.username}</td>
                <td>${user.auth}</td>
            </tr>
        </c:forEach>
    </c:if>
</table>
<br>

<table>
    <tr items="${page}">
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="1"></td>
            <td><input type="submit" value="首页"></td>
        </form>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.prefPage}"></td>
            <td><input type="submit" value="上一页"></td>
        </form>
        <td>当前：第${page.currentPage}页<--></td>
        <td>共：${page.totalPages}页</td>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.nextPage}"></td>
            <td><input type="submit" value="下一页"></td>
        </form>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.totalPages}"></td>
            <td><input type="submit" value="尾页"></td>
        </form>
    </tr>
</table>
<form method="GET" action="#">
    <table>
        <tr>
            <td>跳转到第:<input type="text" name="page" size="5"/>页</td>
            <td><input type="submit" value="确定"></td>
        </tr>
    </table>
</form>
</body>
</html>

7.测试效果

可以看到11条数据默认每页有5行，分出了3页。

如果想动态的设定显示行数，可以通过参数传给controller的per_page参数，把前端稍微改一下。改成可以选择3行，5行，10行。

<%@page contentType="text/html; charset=utf-8" pageEncoding="utf-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt" %>

<html>
<head>
    <title>Test paging</title>
</head>
<body>
<h2>All users：</h2>

<table frame="box" rules="all">
    <tr>
        <td>name</td>
        <td>auth</td>
    </tr>
    <c:if test="${list!= null || fn:length(list) != 0}">
        <c:forEach items="${list}" var="user" begin="0" end="${fn:length(list) }">
            <tr>
                <td>${user.username}</td>
                <td>${user.auth}</td>
            </tr>
        </c:forEach>
    </c:if>
</table>

<br>
每页显示的行数:${per_page}
<br>
<form name="form1" action="#">
	<select onchange="submit(this.form)" name="per_page">
		<c:choose>
			<c:when test="${per_page == 3 }">
				<option value="3" selected="selected">3</option>
				<option value="5">5</option>
				<option value="10">10</option>
			</c:when>
			<c:when test="${per_page == 5 }">
				<option value="3">3</option>
				<option value="5" selected="selected">5</option>
				<option value="10">10</option>
			</c:when>
			<c:when test="${per_page == 10 }">
				<option value="3">3</option>
				<option value="5">5</option>
				<option value="10" selected="selected">10</option>
			</c:when>
			<c:otherwise>
				<option value="3">3</option>
				<option value="5" selected="selected">5</option>
				<option value="10">10</option>
			</c:otherwise>

		</c:choose>
		
	</select>
</form>

<table>
    <tr items="${page}">
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="1"></td>
            <td><input type="hidden" name="per_page" value="${per_page}"></td>
            <td><input type="submit" value="首页"></td>
        </form>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.prefPage}"></td>
            <td><input type="hidden" name="per_page" value="${per_page}"></td>
            <td><input type="submit" value="上一页"></td>
        </form>
        <td>当前：第${page.currentPage}页<--></td>
        <td>共：${page.totalPages}页</td>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.nextPage}"></td>
            <td><input type="hidden" name="per_page" value="${per_page}"></td>
            <td><input type="submit" value="下一页"></td>
        </form>
        <form method="GET" action="#">
            <td><input type="hidden" name="page" value="${page.totalPages}"></td>
            <td><input type="hidden" name="per_page" value="${per_page}"></td>
            <td><input type="submit" value="尾页"></td>
        </form>
    </tr>
</table>
<form method="GET" action="#">
    <table>
        <tr>
            <td>跳转到第:<input type="text" name="page" size="5"/>页</td>
            <td><input type="submit" value="确定"></td>
        </tr>
    </table>
</form>
</body>
</html>

效果。选择为3行。