I believe you can also see the paging effect on the website! So now let's take a look at his ideas, codes and renderings.
General pagination based on MySql database The core idea of general paging: send the last query request again, but the page number has changed Implementation steps: 1) Query all data first Junit test baseDao<T>,CallBack<K> 2) Universal paging implementation pagebean
1. PageBean Three elements of paging page page number passed by the view layer rows page size passed by the view layer total Total number of records Checked out in the background pagination Whether to paginate Passed by the view layer getStartIndex() Get the pagination start mark based on MySql database paging ------------------------- url request path passed by the view layer map Parameter set Passed by the view layer setRequest(HttpServletRequest req) Set request parameters getMaxPager() Get the maximum page number getProviousPager() Get the previous page getNextPager() Get the next page
2. Background 2.1 entity 2.2 dao BaseDao<T> 1) Anonymous internal interface 2) Paging query method, interface method passing parameters (return: total number of records + specified page number and record set that meets the conditions) 3) The conditions of the second query must be consistent getCountSql()/getPagerSql() 2.3 Control layer Servlet req.getContextPath();//Get the root directory req.getServletPath();//Get the request path
3. junit (a method of code testing) java unit testing/white box testing setUp tearDown test case
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 life cycle: 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("Journey to the West"+(i+1)+"chapter");
book.setBook_name_pinyin(PinYinUtil.toPinyin("Journey to the West"+(i+1)+"chapter").toLowerCase());
book.setBook_price(99f);
book.setBook_type("myth");
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("total:"+pageBean.getTotal());
books.forEach(System.out::println);
}
}The init and destroy methods in the Servlet will only run once The setUp and tearDown methods in Junit are determined according to the number of methods
First of all, I followed the files in my own eclipse to post the code!
The required jar packages are as follows:
Not much to say about the code for general pagination!
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 {
//Get request query parameters
String bookname = req.getParameter("bookname");
//Instantiate BookDao
BookDao bookDao=new BookDao();
//Instantiate book
Book book=new Book();
book.setBook_name(bookname);
//Create PageBean
PageBean pageBean=new PageBean();
pageBean.setRequest(req);
//Realize book query
List<Book> books=bookDao.queryBookPager(book,pageBean);
//Save the query result books in the request scope
req.setAttribute("books", books);
req.setAttribute("pageBean", pageBean);
//Forward to the specified page and display the query results
req.getRequestDispatcher("/bookList.jsp").forward(req, resp);
}
}//Using the paging method (both support paging and may not support paging) general method
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>{
//Only used to traverse the ResultSet result set
public List<K> foreachRs(ResultSet rs) throws SQLException;
}
/**
* By pagination method (both support pagination and may not support paging)
* @param sql Ordinary SQl
* @param pageBean pagination object
* @return query result set
*/
public List<T> executQuery(String sql,
PageBean pageBean,CallBack<T> callBack){
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
conn=DBHelper.getConnection();
//Judging PageBean paging object to judge whether paging
if(null!=pageBean&&pageBean.isPagination()) {
//1) Query the total number of records according to the condition
String countSQL = this.getCountSQL(sql);
//create execution object
stmt=conn.prepareStatement(countSQL);
//Execute the SQL statement and return the total number of records
rs=stmt.executeQuery();
//Get the total number of records
if(rs.next()) {
pageBean.setTotal(rs.getInt(1));;
}
//2) Query the paging result set according to the condition
sql= this.getPagerSQL(sql, pageBean);
}
//create execution object
stmt=conn.prepareStatement(sql);
rs=stmt.executeQuery();
return callBack.foreachRs(rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(conn, stmt, rs);
}
return null;
}
/**
* Convert ordinary SQl statements into SQL statements for the total number of records
* 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 Ordinary SQl
* @return Query the SQl of the total records
*/
private String getCountSQL(String sql) {
return "select count(0) from ("+sql+") temp";
}
/**
* * Convert ordinary SQl statements into SQL statements for the total number of records
* 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...
* ----->
* Convert ordinary SQL statements into SQL statements for querying paged result sets
* @param sql plain SQL
* @param pageBean Paging object (contains the current page number and the number of entries per page, used to calculate the key data of paging)
* @return
*/
private String getPagerSQL(String sql,PageBean pageBean) {
return sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows();
}
}//Code to connect to 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;
/**
* Provides a set of methods to get or close database objects
*
*/
public class DBHelper {
private static String driver;
private static String url;
private static String user;
private static String password;
static {// The static block is executed once, and the driver is loaded once
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);
}
}
/**
* get data connection object
*
* @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("Database linkage(closure)success");
}
}// Book pagination query (method code):
/**
* 2.Book page query 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";
//Splicing query conditions, fuzzy query according to book name
if(StringUtils.isNotBlank(book.getBook_name()))
sql+=" and book_name like '%"+book.getBook_name()+"%'";
//Sort by book number in descending order
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<>();
//Define the Book object
Book b=null;
//Loop through the result set
while(rs.next()) {
//Create Book object
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. View layer 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() {
//Determine whether to page
if(null==pageBean||!pageBean.isPagination())
return "";
else {
StringBuilder sb=new StringBuilder();
//TODO
sb.append("<div style=\"float:right\">");
//Splicing Form form
sb.append("<form id=\"pageBeanForm\" action=\""+pageBean.getUrl()+"\" method=\"post\">");
//Set page hidden field
sb.append("<input type=\"hidden\" name=\"page\"/>");
//Splicing request parameter collection
Map<String, String[]> map = pageBean.getParams();
//Get request parameter set key-value pair
Set<Entry<String,String[]>> entrySet = map.entrySet();
//Traversing request parameter key-value pairs
for (Entry<String, String[]> entry : entrySet) {
//Get the request parameter name, that is, the name attribute name from the form
String name=entry.getKey();
//If the parameter is page, continue skips
if(name.equals("page"))
continue;
//Get the value corresponding to the request parameter, String[]
String[] values=entry.getValue();
//traverse the value value
for (String value : values) {
//Splicing request parameters
sb.append("<input type='hidden' name='"+name+"' value='"+value+"'/>");
}
}
sb.append("</form>");
//How many pages/number of pages to splice
sb.append("common"+pageBean.getMaxPager()+"Page/No."+pageBean.getPage()+"Page,");
//Splicing first page, previous page, next page, last page
if(pageBean.getPage()==1)
sb.append("front page previous page ");
else {
sb.append("<a href=\"javascript:gotoPage(1)\">front page</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getProviousPager()+")\">previous page</a> ");
}
if(pageBean.getPage()==pageBean.getMaxPager())
sb.append("next page last page ");
else {
sb.append("<a href=\"javascript:gotoPage("+pageBean.getNextPager()+")\">next page</a> ");
sb.append("<a href=\"javascript:gotoPage("+pageBean.getMaxPager()+")\">last page</a> ");
}
//Splicing Jump Page Numbers
sb.append("<input type=\"text\" id=\"p\" style=\"width:20px;\"/>");
sb.append("<input type=\"button\" value=\"GO\" onclick=\"javascript:skipPage();\"/>");
//Splicing javascript jump method
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('Please enter 1~"+pageBean.getMaxPager()+"numbers between!');\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;
}
}Click the paging button to send (request) the last request once
The renderings are as follows:
