通用分页(一)

1.通用查询

分页需要的属性，我把它分到一个类里面，方便调用，再建立一个实体类Book

/**
 * 分页工具类
 *
 */
public class PageBean {

	private int page = 1;// 页码

	private int rows = 10;// 页大小

	private int total = 0;// 总记录数

	private boolean pagination = true;// 是否分页

	public PageBean() {
		super();
	}

	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 void setTotal(String total) {
		this.total = Integer.parseInt(total);
	}

	public boolean isPagination() {
		return pagination;
	}

	public void setPagination(boolean pagination) {
		this.pagination = pagination;
	}

	/**
	 * 获得起始记录的下标
	 * 
	 * @return
	 */
	public int getStartIndex() {
		return (this.page - 1) * this.rows;
	}

	@Override
	public String toString() {
		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
	}

2.分页

1.建一个通用分页的类BaseDao

public class BaseDao<T> {
	/**
	 * 
	 * @param sql决定查询哪张表的数据
	 * @param clz查询出来的数据封装到那个实体类中
	 * @param pagebean决定是否分页
	 * @return
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	
	public List<T> executeQuery(String sql,Class clz,PageBean pagebean) throws InstantiationException, IllegalAccessException, SQLException{
		List<T> list = new ArrayList<>();
		Connection con = DBAccess.getConnection();//获得连接
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		if(pagebean != null && pagebean.isPagination()) {
			//该分页了
			String countSql = getcountSql(sql);//获取分页行数的语句
			ps = con.prepareStatement(countSql);
			rs = ps.executeQuery();
			if(rs.next()) {
				pagebean.setTotal(rs.getLong(1)+"");//分页行数
			}
			String pageSql = getpageSql(sql,pagebean);//拼接
			ps = con.prepareStatement(pageSql);
			rs = ps.executeQuery();
		}
		else {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
		}
		
		try {
			while (rs.next()) {
				//	list.add(new Book(rs.getInt("bid"), rs.getString("bname"), rs.getFloat("price")));
				/**
				 * 1、创建了一个Book对象
				 * 2、从ResultSet结果集中获取值放入Book对象属性中
				 * 2.1、获取到book的属性对象
				 * 2.2、给属性对象赋值
				 * 3、将已有值的book对象放入list集合中
				 */
				T t = (T) clz.newInstance();
				Field[] fields = clz.getDeclaredFields();
				for (Field field : fields) {
					field.setAccessible(true);
					field.set(t, rs.getObject(field.getName()));
				}
				list.add(t);
			} 
		} finally {
			DBAccess.close(con, ps, rs);/
		}
		return list;
		
	}



	/**
	 * 将原生SQL 拼接出符合条件的某一页的数据查询SQL
	 * @param sql	
	 * @param pagebean
	 * @return
	 */
	private String getpageSql(String sql, PageBean pagebean) {
		return sql + " limit " + pagebean.getStartIndex() + "," + pagebean.getRows();
	}
	/**
	 * 用原生sql拼接出查询符合条件的记录数
	 * @param sql
	 * @return
	 */
	private String getcountSql(String sql) {
		return "select count(1) from ("+sql+") t";
	}

2.写一个Dao方法

public class BookDao extends BaseDao<Book>{
	/**
	 * 
	 * @param book从jsp传递过来的参数封装成对象作为参数查询并执行sql语句
	 * @param pagebean决定是否分页
	 * @return
	 * @throws SQLException 
	 */
	
	
	public List<Book> list(Book book,PageBean pagebean) throws SQLException, InstantiationException, IllegalAccessException{
		List<Book> list = new ArrayList<>();
		String sql = "select * from t_mvc_book where 1=1";
		if(StringUtils.isNotBlank(book.getBname())) {//按书籍名称查询相当于封装了这个，null == s || s.trim().equals("")
			sql += " and bname like '%"+book.getBname()+"%'";//封装SQL
		}
		return super.executeQuery(sql, Book.class, pagebean);
	}
	

//重点！！！通过改变Pagebean的属性得到分页的参数
	public static void main(String[] args){
		BookDao bookdao = new BookDao();
		try {
			Book b = new Book();
			PageBean pageBean = new PageBean();
			pageBean.setPagination(false);//设置不分页
//			pageBean.setPage(2);//设置页数
			b.setBname("圣墟");//参数
			List<Book> list = bookdao.list(b, pageBean);
			for (Book book : list) {
				System.out.println(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

3.总结

1、将原有的查询向上抽取
2、让返回值变成泛型
3、使用回调函数处理resultset
4、利用反射处理回调函数
5、获取总记录数（页面展示，计算总页数）
6、拼接分页sql语句，获取对应的结果集