基于MySql数据库的通用分页

通用分页核心思路

通用分页核心思路：将上一次查询请求再发一次，只不过页码变了
实现步骤：
1.先查询全部数据
Junit测试
baseDao、CallBack
2.通用分页实现
pagebean

分页的步骤

1.PageBean
分页三要素：
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来

pagination 是否分页 视图层传递过来

getStartIndex() 基于MySql数据库分页，获取分页开始标记

---

url 请求路径 视图层传递过来
map 参数集合 视图层传递过来
setRequest(HttpServletRequest req) 设置请求参数

getMaxPager() 获取最大页码
getProviousPager() 获取上一页
getNextPager() 获取下一页
2. 后台

1. entity
2. dao
   BaseDao
   1）匿名内部接口
   2）分页查询方法，接口方法传参
   （返回：总记录数+指定页码并满足条件的记录集）
   3）二次查询的条件要一致
   getCountSql()/getPagerSql()
3. 控制层
   Servlet
   req.getContextPath();//获取根目录
   req.getServletPath();//获取请求路径

3. junit
java单元测试/白盒测试
setUp
tearDown
测试用例

Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的

4. 视图层
PageTag

点击分页按钮，将上一次的请求在发（请求）一次

注：不能将分页表单嵌套到其它表单中，否则不能提交表单！！！

案例分析

1.封装助手类

package com.entity;

import java.io.Serializable;

public class Book implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -3307957022457529492L;
	private Integer bookId;
	private String bookName;
	private Float price;
	public Book() {
		super();
	}
	public Integer getBookId() {
		return bookId;
	}
	public void setBookId(Integer bookId) {
		this.bookId = bookId;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public Float getPrice() {
		return price;
	}
	public void setPrice(Float price) {
		this.price = price;
	}
	@Override
	public String toString() {
		return "Book [bookId=" + bookId + ", bookName=" + bookName + ", price=" + price + "]";
	}
	
	
	
	
}

2.创建方法接口

package com.dao;

import java.util.List;

import com.entity.Book;
import com.utils.PageBean;

public interface IBookDAO {
	
	public void addBook(Book book);
	
	public List<Book> listBook(Book book);
	
	
	public List<Book> listBook1(Book book,PageBean pageBean);
	
}

3.实现方法

public class BookDAO extends BaseDAO<Book> implements IBookDAO {

	@Override
	public void addBook(Book book) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "INSERT INTO t_book (book_name,price) VALUES(?,?)";
		
		try {
			con = DBHelper.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setFloat(2, book.getPrice());
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.close(con, ps, rs);
		}
		
		

	}

	@Override
	public List<Book> listBook(Book book) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT book_id,book_name,price FROM t_book WHERE 1=1";
		if(StringUtils.isNotBlank(book.getBookName())) {
			sql += " AND book_name LIKE '%"+book.getBookName()+"%'";
		}
		
		List<Book> list = new ArrayList<Book>();
		try {
			con =DBHelper.getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			Book b = null;
			while(rs.next()) {
				b = new Book();
				b.setBookId(rs.getInt("book_id"));
				b.setBookName(rs.getString("book_name"));
				b.setPrice(rs.getFloat("price"));
				list.add(b);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.close(con, ps, rs);
		}
		
		return list;
		
		
		
	}
	
	
	
	public List<Book> listBook1(Book book,PageBean pageBean)  {
		
		String sql = "SELECT book_id,book_name,price FROM t_book WHERE 1=1";
		if(StringUtils.isNotBlank(book.getBookName())) {
			sql += " AND book_name LIKE '%"+book.getBookName()+"%'";
		}
		
		return this.exectueQuery(sql, pageBean, new CallBack<Book>() {
			
			@Override
			public List<Book> forEachRs(ResultSet rs) throws SQLException  {
				
				List<Book> list = new ArrayList<Book>();
				Book b = null;
				
					while(rs.next()) {
						b = new Book();
						b.setBookId(rs.getInt("book_id"));
						b.setBookName(rs.getString("book_name"));
						b.setPrice(rs.getFloat("price"));
						list.add(b);
					}
					return list;
				
				
			}
		});
		
		
		
	}

}

4.创建baseDao方法

public class BaseDAO<K> {
	

	public static interface CallBack<T> {
		public List<T> forEachRs(ResultSet rs) throws SQLException;
	}
	
	
	
	public List<K> exectueQuery(String sql,PageBean pageBean,CallBack callBack)  {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			con = DBHelper.getConnection();
			//查询符合条件的总记录数
			if(null!=pageBean && pageBean.isPagination()) {
				String countSql = this.getCountSql(sql);
				ps = con.prepareStatement(countSql);
				rs = ps.executeQuery();
				if(rs.next()) {
					Object obj = rs.getInt(1);
					pageBean.setTotal(obj.toString());
				}
				DBHelper.close(null, ps, rs);
				
			}
			
		
			if(null!=pageBean && pageBean.isPagination()) {
				sql = this.pageSql(sql, pageBean);
			}
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			
			
			return callBack.forEachRs(rs);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally {
			DBHelper.close(con, ps, rs);
		}
		
		
		
		
		
	}
	

	private String getCountSql(String sql) {
		
		String countSql = "SELECT COUNT(*) FROM ("+ sql +")t1";
		
		return countSql;
	}
	

	private String pageSql(String sql,PageBean pageBean) {
		
		String pageSql = sql+" LIMIT "+pageBean.getStartIndex()+","+pageBean.getRows();
		
		return pageSql;
	}
}

5.创建servlet

public class BookServlet extends HttpServlet {
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		doPost(req, resp);
	}
	
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		//1.设置字符编码
		req.setCharacterEncoding("utf-8");
		
		HttpSession session = req.getSession();
		//2.获取页面传过来的值 
		String bookName = req.getParameter("bookName");
		PageBean pageBean = new PageBean();
		pageBean.setPagination(true);
		pageBean.setPage(2);
		pageBean.setRows(3);
		Book book = new Book();
		book.setBookName(bookName);
		//3.调用biz
		IBookBiz bookBiz = new BookBiz();
		
		List<Book> listBook = bookBiz.listBook(book,pageBean);
//		try {
//			listBook = bookBiz.listBook(book,pageBean);
//		} catch (Exception e) {
//			throw new RuntimeException();
//		}
		
		//4.将查询出来的集合放到session作用域中
		session.setAttribute("listBook", listBook);
		
		req.getRequestDispatcher("listBook.jsp").forward(req, resp);
	}
	
}

6.效果图