相信大家也在网站上看到的分页效果的吧！那么现在来一起看看他的思路以及代码还有效果图吧！
基于MySql数据库的通用分页
通用分页核心思路：将上一次查询请求再发一次，只不过页码变了
实现步骤：
1）先查询全部数据
Junit测试
baseDao<T>、CallBack<K>
2）通用分页实现
pagebean
分页三要素
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来pagination 是否分页 视图层传递过来
getStartIndex() 基于MySql数据库分页，获取分页开始标记
-------------------------
url 请求路径 视图层传递过来
map 参数集合 视图层传递过来
setRequest(HttpServletRequest req) 设置请求参数getMaxPager() 获取最大页码
getProviousPager() 获取上一页
getNextPager() 获取下一页
2.1 entity
2.2 dao
BaseDao<T>
1）匿名内部接口
2）分页查询方法，接口方法传参
（返回：总记录数+指定页码并满足条件的记录集）
3）二次查询的条件要一致
getCountSql()/getPagerSql()
2.3 控制层
Servlet
req.getContextPath();//获取根目录
req.getServletPath();//获取请求路径
java单元测试/白盒测试
setUp
tearDown
测试用例package com.zking.pagination.dao; import static org.junit.jupiter.api.Assertions.*; import java.util.List; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.BeforeEach; import org.junit.jupiter.api.Test; import com.zking.pagination.entity.Book; import com.zking.pagination.util.PageBean; import com.zking.pagination.util.PinYinUtil; class BookDaoTest { //Servlet生命周期：init/service/destory Book book=null; BookDao bookDao=new BookDao(); @BeforeEach void setUp() throws Exception { book=new Book(); } @AfterEach void tearDown() throws Exception { } @Test void testAddBook() { for(int i=0;i<81; i++) { book=new Book(); book.setBook_name("西游记第"+(i+1)+"章"); book.setBook_name_pinyin(PinYinUtil.toPinyin("西游记第"+(i+1)+"章").toLowerCase()); book.setBook_price(99f); book.setBook_type("神话"); bookDao.addBook(book); } } @Test void testQueryBookPager() { book.setBook_name("2"); PageBean pageBean=new PageBean(); pageBean.setPage(2); List<Book> books=bookDao.queryBookPager(book,pageBean); System.out.println("总记录数："+pageBean.getTotal()); books.forEach(System.out::println); } }Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的
首先我是跟着我自己eclipse中的文件来发代码的！
所需要的jar包如下：
话不多说上通用分页的代码啦！
package com.zking.pagination.action;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zking.pagination.dao.BookDao;
import com.zking.pagination.entity.Book;
import com.zking.pagination.util.PageBean;
public class BookAction extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取请求查询参数
String bookname = req.getParameter("bookname");
//实例化BookDao
BookDao bookDao=new BookDao();
//实例化book
Book book=new Book();
book.setBook_name(bookname);
//创建PageBean
PageBean pageBean=new PageBean();
pageBean.setRequest(req);
//实现书本查询
List<Book> books=bookDao.queryBookPager(book,pageBean);
//将查询结果books保存request作用域中
req.setAttribute("books", books);
req.setAttribute("pageBean", pageBean);
//转发到指定页面并显示查询结果
req.getRequestDispatcher("/bookList.jsp").forward(req, resp);
}
}
//通过分页方法（既支持分页，也可以不支持分页）通用方法
package com.zking.pagination.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.sun.glass.ui.MenuItem.Callback;
import com.zking.pagination.util.DBHelper;
import com.zking.pagination.util.PageBean;
public class BaseDao<T> {
public static interface CallBack<K>{
//只用于遍历ResultSet结果集
public List<K> foreachRs(ResultSet rs) throws SQLException;
}
/**
* 通过分页方法（既支持分页，也可以不支持分页）
* @param sql 普通的SQl
* @param pageBean 分页对象
* @return 查询结果集
*/
public List<T> executQuery(String sql,
PageBean pageBean,CallBack<T> callBack){
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
conn=DBHelper.getConnection();
//判断PageBean分页对象判断是否分页
if(null!=pageBean&&pageBean.isPagination()) {
//1)根据满足条件查询总记录数
String countSQL = this.getCountSQL(sql);
//创建执行对象
stmt=conn.prepareStatement(countSQL);
//执行SQL语句并返回总记录数
rs=stmt.executeQuery();
//获取总记录数
if(rs.next()) {
pageBean.setTotal(rs.getInt(1));;
}
//2)根据满足条件查询分页结果集
sql= this.getPagerSQL(sql, pageBean);
}
//创建执行对象
stmt=conn.prepareStatement(sql);
rs=stmt.executeQuery();
return callBack.foreachRs(rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(conn, stmt, rs);
}
return null;
}
/**
* 将普通的SQl语句转换成总记录数的SQL语句
* select * from t_book
* select * from t_book...
* select book_id,book_name from t_book
* select book_id,book_name from t_book where...
* ----->
* select count(0) from t_book where...
*
* @param sql 普通的SQl
* @return 查询总记录的SQl
*/
private String getCountSQL(String sql) {
return "select count(0) from ("+sql+") temp";
}
/**
* * 将普通的SQl语句转换成总记录数的SQL语句
* select * from t_book
* select * from t_book...
* select book_id,book_name from t_book
* select book_id,book_name from t_book where...
* ----->
* 将普通的SQL语句转换成查询分页结果集的SQL语句
* @param sql 普通的SQL
* @param pageBean 分页对象（包含当前页码和每页条数，用于计算分页的关键数据）
* @return
*/
private String getPagerSQL(String sql,PageBean pageBean) {
return sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows();
}
}
//连接Mysql的代码：
package com.zking.pagination.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 提供了一组获得或关闭数据库对象的方法
*
*/
public class DBHelper {
private static String driver;
private static String url;
private static String user;
private static String password;
static {// 静态块执行一次，加载 驱动一次
try {
InputStream is = DBHelper.class
.getResourceAsStream("config.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("pwd");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 获得数据连接对象
*
* @return
*/
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static void close(ResultSet rs) {
if (null != rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Statement stmt) {
if (null != stmt) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Connection conn) {
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
close(rs);
close(stmt);
close(conn);
}
public static boolean isOracle() {
return "oracle.jdbc.driver.OracleDriver".equals(driver);
}
public static boolean isSQLServer() {
return "com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driver);
}
public static boolean isMysql() {
return "com.mysql.jdbc.Driver".equals(driver);
}
public static void main(String[] args) {
Connection conn = DBHelper.getConnection();
DBHelper.close(conn);
System.out.println("isOracle：" + isOracle());
System.out.println("isSQLServer：" + isSQLServer());
System.out.println("isMysql：" + isMysql());
System.out.println("数据库连接(关闭)成功");
}
}
//书本分页查询（方法代码）：
/**
* 2.书本分页查询 query/find/select/get
* @param book
* @return
*/
@SuppressWarnings("unchecked")
public List<Book> queryBookPager(Book book,PageBean pageBean){
String sql="select book_id,book_name,book_name_pinyin,book_price,"
+ "book_type from t_book where 1=1";
//拼接查询条件，按照书本名称模糊查询
if(StringUtils.isNotBlank(book.getBook_name()))
sql+=" and book_name like '%"+book.getBook_name()+"%'";
//按照书本编号降序排序
sql+=" order by book_id desc";
System.out.println(sql);
Collections.sort(new ArrayList<>(),new Comparator() {
@Override
public int compare(Object o1, Object o2) {
// TODO Auto-generated method stub
return 0;
}
});
return super.executQuery(sql, pageBean, new CallBack<Book>() {
@Override
public List<Book> foreachRs(ResultSet rs) throws SQLException {
List<Book> lst=new ArrayList<>();
//定义Book对象
Book b=null;
//循环遍历结果集
while(rs.next()) {
//创建Book对象
b=new Book();
b.setBook_id(rs.getInt("book_id"));
b.setBook_name(rs.getString("book_name"));
b.setBook_name_pinyin(rs.getString("book_name_pinyin"));
b.setBook_price(rs.getFloat("book_price"));
b.setBook_type(rs.getString("book_type"));
lst.add(b);
}
return lst;
}
});
4. 视图层
PageTag
package com.zking.pagination.tag;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import javax.servlet.jsp.JspException;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.tagext.BodyTagSupport;
import com.zking.pagination.util.PageBean;
public class PaginationTag extends BodyTagSupport {
private PageBean pageBean;
@Override
public int doEndTag() throws JspException {
return EVAL_PAGE;
}
@Override
public int doStartTag() throws JspException {
JspWriter out = pageContext.getOut();
try {
out.write(toHtml());
} catch (Exception e) {
e.printStackTrace();
}
return SKIP_BODY;
}
private String toHtml() {
//判断是否分页
if(null==pageBean||!pageBean.isPagination())
return "";
else {
StringBuilder sb=new StringBuilder();
//TODO
sb.append("<div style=\"float:right\">");
//拼接Form表单
sb.append("<form id=\"pageBeanForm\" action=\""+pageBean.getUrl()+"\" method=\"post\">");
//设置page隐藏域
sb.append("<input type=\"hidden\" name=\"page\"/>");
//拼接请求参数集合
Map<String, String[]> map = pageBean.getParams();
//获取请求参数集合键值对
Set<Entry<String,String[]>> entrySet = map.entrySet();
//遍历请求参数键值对
for (Entry<String, String[]> entry : entrySet) {
//获取请求参数名，也就是来自于表单中的name属性名称
String name=entry.getKey();
//如果参数为page，则continue跳过
if(name.equals("page"))
continue;
//获取请求参数对应的值，String[]
String[] values=entry.getValue();
//遍历value值
for (String value : values) {
//拼接请求参数
sb.append("<input type='hidden' name='"+name+"' value='"+value+"'/>");
}
}
sb.append("</form>");
//拼接共几页/第几页
sb.append("共"+pageBean.getMaxPager()+"页/第"+pageBean.getPage()+"页,");
//拼接首页、上一页、下一页、末页
if(pageBean.getPage()==1)
sb.append("首页 上一页 ");
else {
sb.append("<a href=\"javascript:gotoPage(1)\">首页</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getProviousPager()+")\">上一页</a> ");
}
if(pageBean.getPage()==pageBean.getMaxPager())
sb.append("下一页 末页 ");
else {
sb.append("<a href=\"javascript:gotoPage("+pageBean.getNextPager()+")\">下一页</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getMaxPager()+")\">末页</a> ");
}
//拼接跳转页码
sb.append("<input type=\"text\" id=\"p\" style=\"width:20px;\"/>");
sb.append("<input type=\"button\" value=\"GO\" onclick=\"javascript:skipPage();\"/>");
//拼接javascript跳转方法
sb.append("<script type=\"text/javascript\">\r\n" +
"function gotoPage(page){\r\n" +
" document.getElementById(\"pageBeanForm\").page.value=page;\r\n" +
" document.getElementById(\"pageBeanForm\").submit();\r\n" +
"}");
sb.append("function skipPage(){\r\n" +
" var page=document.getElementById(\"p\").value;\r\n" +
" if(isNaN(page)||page<1||page>="+pageBean.getMaxPager()+"){\r\n" +
" alert('请输入1~"+pageBean.getMaxPager()+"之间数字！');\r\n" +
" return false;\r\n" +
" }\r\n" +
" gotoPage(page);\r\n" +
"}\r\n" +
"</script>");
sb.append("</div>");
return sb.toString();
}
}
public PageBean getPageBean() {
return pageBean;
}
public void setPageBean(PageBean pageBean) {
this.pageBean = pageBean;
}
}
275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
