通用分页01

通用分页核心思路：

1、 为了提高查询性能及节约网络流量，每次只查询指定的记录数，而不是全部，在数量比较大时很有用
2、当点击下一页或指定页面的数据时，将带着所有的查询条件，再次执行查询
讲简单点就是将上一次查询请求再发一次，只不过页码变了

---

1. PageBean

• 页码，从页面传递过来——page
• 每页行数，从也能传递过来——rows
• 总记录数， 从数据库中统计得到——total
• 是否分页， 如果为false，则查询所有记录——pagination
• 查询参数， 点击上一页或下一页时需要及携带用户输入的所有查询参数——url
• 另外提供上页，下页，总页数等计算

---

2. 后台

        ①entity

        ②dao

              1、查询满足条件的总记录数
              2、查询满足条件的当前页的数据
              3、上两个步骤的查询条件要一致

        ③控制层

        Servlet

---

3、案例

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;
	}

}

需要根据传入的Request初始化分页对象

DBUtil

连接数据库

driver.name = com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/t274?useUnicode=true&characterEncoding=utf-8&useSSL=false
db.user=root
db.password=123

public final 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();
        }
    }

Dao

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 = (Connection) DBUtil.getConection();
				ps = (PreparedStatement) 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 = (Connection) DBUtil.getConection();
				ps = (PreparedStatement) 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 = (PreparedStatement) 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 null;
	}

最后进行测试：

@Test
	public void testStudentDao() {
		StudentDao stu = new StudentDao();
		PageBean pb = new PageBean();
		List<Student> ls = stu.getStudents(null, pb);
		ls.forEach(t->System.out.println(t));
		System.out.println("总行数："+pb.getTotal());
		//每页放两条数据
		pb.setRows(2);
		//查询总页数
		System.out.println("分页后总页数"+pb.getTotalPage());
	}

运行结果：

 这是控制台输出版本的分页，接下来作者会把网页版的分页发布出来，敬请期待！！！