前言
1) 通用分页功能总体思路:
为了提高查询性能及节约网络流量,每次只查询指定的记录数,而不是全部,在数量比较大时很有用
2)当点击下一页或指定页面的数据时,将带着所有的查询条件,再次执行查询
提示:以下是本篇文章正文内容,下面案例可供参考
一、分页信息实体(PageBean)
用于存储和传递分页参数,主要内容如下:
- 页码,从页面传递过来
- 每页行数,从也能传递过来
- 总记录数, 从数据库中统计得到
- 是否分页, 如果为false,则查询所有记录
- 查询参数, 点击上一页或下一页时需要及携带用户输入的所有查询参数
- 另外提供上页,下页,总页数等计算
码如下(示例):
-
import java.util.Map; import javax.servlet.http.HttpServletRequest; import com.mysql.jdbc.StringUtils; public class PageBean { /** * 页码 */ private int page = 1; /** * 每页显示的记录数 */ private int rows = 10; /** * 总记录数 */ private int total = 0; /** * 是否分页 */ private boolean pagination = true; /** * 记录查询的url,以便于点击分页时再次使用 */ private String url; /** * 存放请求参数,用于生成隐藏域中的元素 */ private Map<String,String[]> parameterMap; /** * 根据传入的Request初始化分页对象 * @param request */ public void setRequest(HttpServletRequest request) { if(!StringUtils.isNullOrEmpty(request.getParameter("page"))) { this.page = Integer.valueOf(request.getParameter("page")); } if(!StringUtils.isNullOrEmpty(request.getParameter("rows"))) { this.rows = Integer.valueOf(request.getParameter("rows")); } if(!StringUtils.isNullOrEmpty(request.getParameter("pagination"))) { this.pagination = Boolean.valueOf(request.getParameter("pagination")); } this.url = request.getRequestURI(); this.parameterMap = request.getParameterMap(); request.setAttribute("pageBean", this); } public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getRows() { return rows; } public void setRows(int rows) { this.rows = rows; } public int getTotal() { return total; } public void setTotal(int total) { this.total = total; } public boolean isPagination() { return pagination; } public void setPagination(boolean pagination) { this.pagination = pagination; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public Map<String, String[]> getParameterMap() { return parameterMap; } public void setParameterMap(Map<String, String[]> parameterMap) { this.parameterMap = parameterMap; } //计算起始页码 public int getStartIndex() { return (this.page - 1) * this.rows; } //获取总页数 public int getTotalPage() { if (this.getTotal() % this.rows == 0) { return this.getTotal() / this.rows; } else { return this.getTotal() / this.rows + 1; } } //上一页 public int getPreviousPage() { return this.page - 1 > 0 ? this.page - 1 : 1; } //下一页 public int getNextPage() { return this.page + 1 > getTotalPage() ? getTotalPage() : this.page + 1; }
二、后台分页数据查询
1.处理流程
1) 查询满足条件的总记录数
2) 查询满足条件的当前页的数据
3) 上两个步骤的查询条件要一致
流程图:
2.实现
1) 为简化数据库操作,需要一个DBUtil工具类
代码如下(示例):
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author Administrator
* @create 2020-05-0811:43
*/
public class DBUtil {
private static String DRIVER_NAME;
private static String DB_URL;
private static String DB_USER;
private static String DB_PASSWORD;
private DBUtil() {
}
static {
try {
InputStream in = DBUtil.class.getResourceAsStream("/jdbc.properties");
Properties properties = new Properties();
properties.load(in);
DRIVER_NAME = properties.getProperty("driver.name");
DB_URL = properties.getProperty("db.url");
DB_USER = properties.getProperty("db.user");
DB_PASSWORD = properties.getProperty("db.password");
Class.forName(DRIVER_NAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConection() throws SQLException {
Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
return connection;
}
public static void closeDB(ResultSet rs, Statement ps, Connection con) {
try {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (ps != null && !ps.isClosed()) {
ps.close();
}
if(con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeDB(ResultSet rs, Statement ps) {
try {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (ps != null && !ps.isClosed()) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
Connection conection = DBUtil.getConection();
DBUtil.closeDB(null, null, conection);
System.out.println("------- 成功 -------------");
}
2) 分页查询
代码如下(示例):
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.zking.mymvc.model.Student;
import com.zking.mymvc.util.DBUtil;
import com.zking.mymvc.util.PageBean;
import org.junit.Test;
public class StudentDao {
public List<Student> getStudents(String sname, PageBean pageBean) {
String sql = "select * from t_student t ";
if(sname != null && !"".equals(sname)) {
sql += " where t.sname like ?";
}
//查询之前的准备
List<Student> students = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
//如果不需要分页,直接查询
if(pageBean == null || !pageBean.isPagination()) {
try {
con = DBUtil.getConection();
ps = con.prepareStatement(sql);
if(sname != null && !"".equals(sname)) {
ps.setObject(1, sname+"%");
}
rs = ps.executeQuery();
while(rs.next()) {
Student stu = new Student();
stu.setSid(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
stu.setAge(rs.getInt("age"));
stu.setRemark(rs.getString("remark"));
students.add(stu);
}
return students;
} catch(SQLException e) {
e.printStackTrace();
} finally {
DBUtil.closeDB(rs, ps, con);
}
} else {
//如果需要分页
//1. 总记录数
String countSql = "select COUNT(*) from ("+sql+") t";
try {
con = DBUtil.getConection();
ps = con.prepareStatement(countSql);
//设置查询参数
if(sname != null) {
ps.setObject(1, sname+"%");
}
rs = ps.executeQuery();
//为总记录数赋值
while(rs.next()) {
pageBean.setTotal(rs.getInt(1));
}
//如果总记录为0, 则直接返回一个空的结果集
if(pageBean.getTotal() == 0) {
return students;
}
//查询当前页数据的sql
String pagingSql = sql + " limit " + pageBean.getStartIndex() + ", " + pageBean.getRows();
ps = con.prepareStatement(pagingSql);
//设置查询参数
if(sname != null) {
ps.setObject(1, sname+"%");
}
rs = ps.executeQuery();
while(rs.next()) {
Student stu = new Student();
stu.setSid(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
stu.setAge(rs.getInt("age"));
stu.setRemark(rs.getString("remark"));
students.add(stu);
}
return students;
} catch(SQLException e) {
e.printStackTrace();
} finally {
DBUtil.closeDB(rs, ps, con);
}
}
return students;
}
}
Student实体,及对应的数据库表可自行准备。
public class Student {
private Integer sid;
private String sname;
private Integer age;
private String remark;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", age=" + age + ", remark=" + remark + "]";
}