Javaweb-分页查询
发布时间
阅读量：
阅读量
使用Mysql的LIMIT方法实现分页查询
LIMIT查询方法
SELECT TABLE_COLUMN1,TABLE_COLUMN2...FROM TABLE_NAME LIMIT OFFSET rowNum;分页的详细过程，以及分页所要涉及到的变量
在每次操作时都需要查询数据总量，并避免在此次操作前数据总量出现变动
一、Entity层
Student.java
package com.zbt.entity;
public class Student {
private int id;
private String firstName;
private String lastName;
private String address;
private String phone;
public Student(){}
public Student(int id,String firstName,String lastName,String address,String phone){
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.address = address;
this.phone = phone;
}
//省略了getters方法和setters方法
@Override
public String toString() {
return "Student{" +
"id=" + id +
", firstName='" + firstName + '\'' +
", lastName='" + lastName + '\'' +
", address='" + address + '\'' +
", phone='" + phone + '\'' +
'}';
}
}Page对象 :将一个页面信息全部封装到Page对象中
package com.zbt.entity;
import java.util.List;
public class Page {
public final static int NUM = 5;
private int start;//起始页
private int next;//下一页
private int last;//上一页
private int currentPage;//当前页
private int totalPage;//总页数
private List<Student> studentList;//用户信息
//构造方法
public Page(){}
public Page(int start,int num,List<Student> studentList){
this.start = start;
this.studentList = studentList;
//caculate the last
last = start == 0? start:start-NUM;
// calculate the next
next = start + NUM > num ? start: start+NUM;
// calculate the currentPage
currentPage = start/NUM +1;//start从零开始因此要加1
//calculate the totalPage
totalPage = num % NUM == 0 ? num/NUM : num/NUM+1;//如果总记录数不是NUM的倍数，那么加1；例如21，那么总共有5页
}
//gettes、setters方法省略
}二、DAO层：实现与数据库的交互
DBUtils工具包
package com.zbt.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtils {
private final static String DRIVER = "com.mysql.jdbc.Driver";
private final static String URL ="jdbc:mysql://localhost:3306/pagination";//使用pagination这个数据库
private final static String USERNAME="****";//输入自己的Mysql的username
private final static String PASSWORD="*****";//输入自己Mysql相应的password
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
return conn;
}
}StudentDao.java ：是一个接口类
package com.zbt.dao;
import com.zbt.entity.Page;
import com.zbt.entity.Student;
import java.sql.SQLException;
import java.util.List;
public interface StudentDao {
public List<Student> getStudents(int start) throws SQLException, ClassNotFoundException;
public int getStudentsNum() throws SQLException, ClassNotFoundException;
public Page getPage(Page page)throws SQLException, ClassNotFoundException;
}StudentDaoImpl.java ：实现StudentDao接口
package com.zbt.dao.impl;
import com.zbt.dao.StudentDao;
import com.zbt.db.DBUtils;
import com.zbt.entity.Page;
import com.zbt.entity.Student;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements StudentDao{
private Connection conn;
private PreparedStatement pstmt ;
private ResultSet rs;
public List<Student> getStudents(int start) throws SQLException, ClassNotFoundException {
List<Student> stuList = new ArrayList<Student>();
conn = DBUtils.getConnection();
String sql = "select * from student limit ?,?";
pstmt = conn.prepareStatement(sql);
int firstParameter = 1;//在Intellij中直接输入占位符会报错,所以就使用变量代替
int secondParameter = 2;//
pstmt.setInt(firstParameter, start);
pstmt.setInt(secondParameter,Page.NUM);
rs = pstmt.executeQuery();
Student stu = null;
while(rs.next()){
int id = rs.getInt("id");
String firstName = rs.getString("firstName");
String lastName = rs.getString("lastName");
String address = rs.getString("address");
String phone = rs.getString("phone");
stu = new Student(id,firstName,lastName,address,phone);
stuList.add(stu);
}
if(rs != null) {
rs.close();
}
if(pstmt != null){
pstmt.close();
}
if(conn != null){
conn.close();
}
return stuList;
}
@Override
public int getStudentsNum() throws SQLException, ClassNotFoundException {
int num = 0;
String sql = "select count(id) as count from student";
conn = DBUtils.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
num = rs.getInt("count");
}
return num;
}
@Override
public Page getPage(Page page) throws SQLException, ClassNotFoundException{
int start = page.getStart();
int num = getStudentsNum();
List<Student> studentList = getStudents(start);
Page p = new Page(start,num,studentList);
return p;
}
}三、Service层
StudentService.java ：连接Controller层和DAO层
package com.zbt.service;
import com.zbt.dao.StudentDao;
import com.zbt.entity.Page;
import java.sql.SQLException;
public class StudentService{
public StudentDao stuDao;
public Page getPage(Page page) throws SQLException, ClassNotFoundException {
return stuDao.getPage(page);
}
}四、Controller层
package com.zbt.controller;
import com.zbt.entity.Page;
import com.zbt.service.StudentService;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
public class StudentsServlet extends HttpServlet {
private StudentService stuService = new StudentService();
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
doPost(req,res);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String startStr = request.getParameter("start");
int start = 0;
if(startStr != null){
start = Integer.parseInt(startStr);//一开始查询的时候，没有start这个请求参数，所以会为null
}
Page page = new Page();
page.setStart(start);
try {
page = stuService.getPage(page);
request.setAttribute("stus",page);//属性设置
request.getRequestDispatcher("student.jsp").forward(request,response);//转发实现
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}web.xml配置文件
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd" >
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>Archetype Created Web Application</display-name>
<servlet>
<servlet-name>StudentServlet</servlet-name>
<servlet-class>com.zbt.controller.StudentsServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentServlet</servlet-name>
<url-pattern>/student</url-pattern>
</servlet-mapping>
</web-app>五、前端实现
<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" import="java.util.*,com.zbt.entity.Student"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<body>
<table align='center' border='1' cellspacing='0'>
<tr>
<td>id</td>
<td>firstName</td>
<td>lastName</td>
<td>address</td>
<td>phone</td>
</tr>
<c:forEach items="${requestScope.stus.studentList}" var = "stu">
<tr>
<td>${stu.id}</td>
<td>${stu.firstName}</td>
<td>${stu.lastName}</td>
<td>${stu.address}</td>
<td>${stu.phone}</td>
</tr>
</c:forEach>
<tr>
<td colspan="6" align="center">
总页数${requestScope.stus.totalPage}/当前页${requestScope.stus.currentPage}
<a href="?start=0">[首页]</a>
<c:choose>
<c:when test="${requestScope.stus.currentPage==1}">
[上一页]
</c:when>
<c:otherwise>
<a href="?start=${stus.last}">[上一页]</a>
</c:otherwise>
</c:choose>
<c:choose>
<c:when test="${requestScope.stus.currentPage==requestScope.stus.totalPage}">
[下一页]
</c:when>
<c:otherwise>
<a href="?start=${requestScope.stus.next}">[下一页]</a>
</c:otherwise>
</c:choose>
</td>
</tr>
</table>
</body>
</html>六、 GitHub地址
全部评论 (0)
还没有任何评论哟~