前几次已经做好了增删改的操作,这次做一下分页查询,让表格下面的页码能真正使用
先分析一下,既然要分页查询,就是为了不一次性把数据库中的所有数据都查询到(对于很大的数据库,一次查询完数据内存有可能吃不消)。这也意味着一开始的StudentListServlet中的操作逻辑不再适用,需要新写一个Servlet,专门用来分页查询。
接着分析,分页查询需要用到SQL语句中的limit关键字,假定每页显示5条信息,现在在第一页,要查询第二页的内容,SQL语句应如下所示:
select * from student limit 5, 5;
因为数据库中下标由0开始,前5个下标为0~4,而limit关键字的参数为limit n, m,其中n为开始的下标,m为要查询的个数。
于是可以得到通式,我们要查第i页的数据时,limit的关键字应该是(i-1)*5和5。
再分析,页码控件需要知道共有多少页,即总条目数/每页显示数,查询总条目的SQL语句为:
select count(*) from student
总结一下,我们需要知道的内容有:当前页码,每页显示条数用来查询数据库;数据库总条数用来确定总页码。
用一个Page类来记录这些信息,每页还需要记录查询到的学生信息,所以还需要一个List
public class Page {
private int currentPage; //当前页码
private int infoCount; //每页记录数
private int totalCount; //总记录数
private int totalPage; //总页数
private List<Student> students; //当前页查询到的学生数
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getInfoCount() {
return infoCount;
}
public void setInfoCount(int infoCount) {
this.infoCount = infoCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Page{" +
"currentPage=" + currentPage +
", infoCount=" + infoCount +
", totalCount=" + totalCount +
", totalPage=" + totalPage +
", students=" + students +
'}';
}
}
创建FindStudentByPageServlet
@WebServlet("/findStudentByPageServlet")
public class FindStudentByPageServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
StudentService studentService = new StudentServiceImpl();
String currentPage = request.getParameter("currentPage");
String infoCount = request.getParameter("infoCount");
Page page = studentService.findStudentByPage (currentPage, infoCount);
request.setAttribute("page", page);
request.getRequestDispatcher("/studentlist.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
经过查询后得到一个Page对象,保存在request中,请求转发至studentlist.jsp,在studentlist.jsp中用jstl和表达式取出。
为StudentService添加新的方法findStudentByPage (String currentPage, String infoCount);
@Override
public Page findStudentByPage(String currentPage, String infoCount) {
return dao.findStudentByPage (currentPage, infoCount);
}
为StudentDao添加新方法
@Override
public Page findStudentByPage(String currentPage, String infoCount) {
Page page = new Page();
int i_currentPage = Integer.parseInt(currentPage);
int i_infoCount = Integer.parseInt(infoCount);
int totalCount = getTotalCount ();
int totalPage = (totalCount % i_infoCount == 0) ? (totalCount / i_infoCount) : (totalCount / i_infoCount + 1);
String sql = "select * from student limit ?, ?";
List<Student> students = template.query(sql, new BeanPropertyRowMapper<>(Student.class), (i_currentPage - 1) * i_infoCount, i_infoCount);
page.setInfoCount(i_infoCount);
page.setCurrentPage(i_currentPage);
page.setTotalCount(totalCount);
page.setTotalPage(totalPage);
page.setStudents(students);
return page;
}
private int getTotalCount() {
String sql = "select count(*) from student";
Integer count = template.queryForObject(sql, Integer.class);
if (count == null) {
count = 0;
}
return count;
}
接着改造studentlist.jsp,用jstl和EL表达式接收FindStudentByPageServlet传递来的参数
<h3>共${page.totalCount}名学生,共${page.totalPage}页</h3>
<c:forEach items="${page.students}" var="student">
<tr>
<td><input type="checkbox" name="studentcheckbox" value="${student.studentNo}"></td>
<td>${student.studentNo}</td>
<td>${student.name}</td>
<td>${student.classNo}</td>
<td>${student.major}</td>
<td>${student.gender == 1 ? "男" : "女"}</td>
<td>${student.age}</td>
<td>
<button class="btn btn-success" onclick="window.location.href='${pageContext.request.contextPath}/findStudentServlet?studentno=${student.studentNo}'">修改</button>
<button class="btn btn-danger" onclick="window.location.href='${pageContext.request.contextPath}/deleteStudentServlet?studentno=${student.studentNo}'">删除</button>
</td>
</tr>
</c:forEach>
<nav aria-label="Page navigation">
<ul class="pagination">
<li>
<a href="#" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
<c:forEach begin="1" end="${page.totalPage}" var="i">
<li><a href="#">${i}</a></li>
</c:forEach>
<li>
<a href="#" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</nav>
第一阶段完成,给数据库中多添加几条信息
需要注意现在还没有给FindStudentByPageServlet传递数据,现在需要修改loginServlet重定向的链接
response.sendRedirect(request.getContextPath() + "/userListServlet?currentPage=1&infoCount=5");
重启Tomcat,测试!
成了!不过现在还是有一些小小的问题,页码按钮没有用,所在页码也没有特别显示
不过都是小问题,改一下标签属性就OK了
<nav aria-label="Page navigation">
<ul class="pagination">
<li>
<a href="#" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
</li>
<c:forEach begin="1" end="${page.totalPage}" var="i">
<c:if test="${page.currentPage==i}">
<li class="active"><a href="#">${i}</a></li>
</c:if>
<c:if test="${page.currentPage!=i}">
<li><a href="${pageContext.request.contextPath}/findStudentByPageServlet?currentPage=${i}&infoCount=5">${i}</a></li>
</c:if>
</c:forEach>
<li>
<a href="#" aria-label="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</nav>
现在再看一下效果
点击第几页,就能跳转到那一页。
功能基本算是做完了,还不算太简陋,但是也不完善,比如没有登录验证,也没有验证码这种小细节,甚至注册页面也没有写。
这个小项目就到这了,剩下的功能看心情完善吧。