目录
思维导图:
1. PageBean
2. 后台
3. junit
4、视图层:(PageTag)
1、准备工作:导入jar包:
2、utils包里的各个类,都有各自不同的作用:
3、标签库描述文件
4、jsp界面—booklist
思维导图:
基于MySql数据库的通用分页
通用分页核心思路:将上一次查询请求再发一次,只不过页码变了
实现步骤:
1)先查询全部数据
Junit测试
baseDao、CallBack
2)通用分页实现
pagebean
1. PageBean
分页三要素
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来
pagination 是否分页 视图层传递过来
getStartIndex() 基于MySql数据库分页,获取分页开始标记
2. 后台
2.1 entity
2.2 dao
BaseDao
1)匿名内部接口
2)分页查询方法,接口方法传参
(返回:总记录数+指定页码并满足条件的记录集)
3)二次查询的条件要一致
getCountSql()/getPagerSql()
3. junit
java单元测试/白盒测试
setUp
tearDown
测试用例
Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的
4、视图层:(PageTag)
1、准备工作:
导入jar包:
分页三要素
page 页码 视图层传递过来
rows 页大小 视图层传递过来
total 总记录数 后台查出来
pagination 是否分页 视图层传递过来
getStartIndex() 基于MySql数据库分页,获取分页开始标记
2. 后台
2.1 entity
2.2 dao
BaseDao
1)匿名内部接口
2)分页查询方法,接口方法传参
(返回:总记录数+指定页码并满足条件的记录集)
3)二次查询的条件要一致
getCountSql()/getPagerSql()
3. junit
java单元测试/白盒测试
setUp
tearDown
测试用例
Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的
4、视图层:(PageTag)
1、准备工作:
导入jar包:
java单元测试/白盒测试
setUp
tearDown
测试用例
Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的
1、准备工作:
导入jar包:
2、建包建类:
2、utils包里的各个类,都有各自不同的作用:
连接数据库—DBHelper
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);//classNotfoundException
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
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("数据库连接(关闭)成功");
}
}
中文乱码处理—EncodingFilter
package com.zking.pagination.util;
import java.io.IOException;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class EncodingFilter implements Filter {
private String encoding = "UTF-8";// 默认字符集
public EncodingFilter() {
super();
}
public void destroy() {
}
public void doFilter(ServletRequest request, ServletResponse response,
FilterChain chain) throws IOException, ServletException {
HttpServletRequest req = (HttpServletRequest) request;
HttpServletResponse res = (HttpServletResponse) response;
// 中文处理必须放到 chain.doFilter(request, response)方法前面
res.setContentType("text/html;charset=" + this.encoding);
if (req.getMethod().equalsIgnoreCase("post")) {
req.setCharacterEncoding(this.encoding);
} else {
Map map = req.getParameterMap();// 保存所有参数名=参数值(数组)的Map集合
Set set = map.keySet();// 取出所有参数名
Iterator it = set.iterator();
while (it.hasNext()) {
String name = (String) it.next();
String[] values = (String[]) map.get(name);// 取出参数值[注:参数值为一个数组]
for (int i = 0; i < values.length; i++) {
values[i] = new String(values[i].getBytes("ISO-8859-1"),
this.encoding);
}
}
}
chain.doFilter(request, response);
}
public void init(FilterConfig filterConfig) throws ServletException {
String s = filterConfig.getInitParameter("encoding");// 读取web.xml文件中配置的字符集
if (null != s && !s.trim().equals("")) {
this.encoding = s.trim();
}
}
}
分页工具类—PageBean
package com.zking.pagination.util;
import java.io.Serializable;
public class PageBean implements Serializable {
//当前页码,默认1
private int page=1;
//页面显示条数,默认10
private int rows=10;
//总记录数,默认0
private int total=0;
//是否分页标记,默认分页
private boolean pagination=true;
public int getStartIndex() {
//page=1 (1-1)*10 limit 0,10
//page=1 (2-1)*10 limit 10.10
//.....
return (this.page-1)*this.rows;
}
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;
}
@Override
public String toString() {
return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
}
}
写实体类—Book
package com.zking.pagination.entity;
import java.io.Serializable;
public class Book implements Serializable {
private Integer book_id;
private String book_name;
private float book_price;
private String book_type;
public Integer getBook_id() {
return book_id;
}
public void setBook_id(Integer book_id) {
this.book_id = book_id;
}
public String getBook_name() {
return book_name;
}
public void setBook_name(String book_name) {
this.book_name = book_name;
}
public float getBook_price() {
return book_price;
}
public void setBook_price(float book_price) {
this.book_price = book_price;
}
public String getBook_type() {
return book_type;
}
public void setBook_type(String book_type) {
this.book_type = book_type;
}
public Book(Integer book_id, String book_name, float book_price, String book_type) {
super();
this.book_id = book_id;
this.book_name = book_name;
this.book_price = book_price;
this.book_type = book_type;
}
public Book() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Book [book_id=" + book_id + ", book_name=" + book_name + ", book_price=" + book_price + ", book_type="
+ book_type + "]";
}
}
dao方法—BookDao
package com.zking.pagination.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.zking.pagination.entity.Book; import com.zking.pagination.util.DBHelper; import com.zking.pagination.util.PageBean; import com.zking.pagination.util.StringUtils; public class BookDao extends BaseDao{ //1.书本新增 public int addBook(Book book) { int i=0; Connection con=null; PreparedStatement ps=null; try { //获取连接对象 con=DBHelper.getConnection(); //定义sql String sql="insert into T_book(book_name," + "book_price,book_type) values(?,?,?)"; //创建执行对象 ps=con.prepareStatement(sql); //赋值 ps.setString(1, book.getBook_name()); ps.setFloat(2, book.getBook_price()); ps.setString(3, book.getBook_type()); i=ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { DBHelper.close(con, ps, null); } return i; } //2.书本修改 //3.书本删除 //4.书本分页查询 public List queryBookPager(Book book,PageBean pageBean){ //定义SQL语句 String sql="select book_id,book_name,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"; return super.executeQuery(sql, pageBean, new CallBack () { @Override public List foreachRs(ResultSet rs) throws SQLException { List lst=new ArrayList<>(); //定义Book对象 Book book=null; //循环遍历结果集 while(rs.next()) { book=new Book(); book.setBook_id(rs.getInt("book_id")); book.setBook_name(rs.getString("book_name")); book.setBook_price(rs.getFloat("book_price")); book.setBook_type(rs.getString("book_type")); lst.add(book); } return lst; } }); } //5.根据书本id查询单个书本对象信息 }
BaseDao
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.zking.pagination.util.DBHelper; import com.zking.pagination.util.PageBean; public class BaseDao{ public static interface CallBack { //只用于遍历ResultSet结果集 public List foreachRs(ResultSet rs) throws SQLException; } public List executeQuery(String sql, PageBean pageBean,CallBack 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; } private String getCountSQL(String sql) { return "select count(0) from ("+sql+") temp"; } private String getPagerSQL(String sql,PageBean PageBean) { return sql+" limit "+PageBean.getStartIndex()+","+PageBean.getRows(); } }
BookAction
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 {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String bookname=request.getParameter("bookname");
//实例化Bookdao
BookDao bookDao=new BookDao();
//实例化Book
Book book=new Book();
book.setBook_name(bookname);
//创建PageBean
PageBean pageBean=new PageBean();
//实现书本查询
List books=bookDao.queryBookPager(book,pageBean);
//将查询结果books保存到request作用域
request.setAttribute("books", books);
request.getRequestDispatcher("/booklist.jsp").forward(request, response);
}
}
3、标签库描述文件
web.xml
pagination encodingFilter com.zking.pagination.util.EncodingFilter encodingFilter /* bookAction com.zking.pagination.action.BookAction bookAction /bookAction.action index.html index.htm index.jsp default.html default.htm default.jsp
4、jsp界面—booklist
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
Insert title here
书本编号
书本名称
书本价格
书本类型
${b.book_id }
${b.book_name }
${b.book_price}
${b.book_type }
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>Insert title here
| 书本编号 | 书本名称 | 书本价格 | 书本类型 |
|---|---|---|---|
| ${b.book_id } | ${b.book_name } | ${b.book_price} | ${b.book_type } |
结果展示:



