一.通用后台分页
1.简单的查询功能
编码：建立连接--- 预编译对象--- 执行查询--- 结果集处理
不足之处：完全重复的代码和重复的思路
建立连接，预编译对象，执行查询都是重复的
结果集处理思路重复
解决方案：
定义父类处理重复代码
利用反射处理·重复的思路
父类（BeseDao）代码如下：
package com.dao;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.util.DBAccess;
import com.util.PageBean;
public class BaseDao<T> {
public List<T> getAll(String sql,Class<T> cla) throws Exception{
List<T> list=new ArrayList<T>();
Connection con=DBAccess.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
T t = cla.newInstance();
Field[] field = cla.getDeclaredFields();
for (Field f : field) {
f.setAccessible(true);
//f.getName()是每个属性名称
f.set(t, rs.getObject(f.getName()));
}
list.add(t);
}
return list;
}
/**
* 通用分页查询
* @param sql
* @param cla
* @return
* @throws Exception
*/
//1.判断是否需要分页 无需分页：下拉框 必须分页：项目中列表类的需求 订单列表，学生列表.....
public List<T> pages(String sql,Class<T> cla,PageBean pageBean) throws Exception{
List<T> list=new ArrayList<T>();
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(String.valueOf(rs.getObject(1)));
}
String pageSQL=getPageSQL(sql,pageBean);//分页语句
ps = con.prepareStatement(pageSQL);
rs = ps.executeQuery();
}else {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
}
while(rs.next()) {
T t = cla.newInstance();
Field[] field = cla.getDeclaredFields();
for (Field f : field) {
f.setAccessible(true);
//f.getName()是每个属性名称
f.set(t, rs.getObject(f.getName()));
}
list.add(t);
}
return list;
}
/**
* 将原生sql转换成countSQL
* @param sql
* @return
*/
private String getCountSQL(String sql) {
return "select count(1) from ("+sql+") t";
}
/**
* 将原生sql转换成pageSQL
*/
private String getPageSQL(String sql,PageBean pageBean) {
return sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows()+"";
}
}
只需要将改变部分作为参数即可
子类（bookDao）
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.entity.Book;
import com.util.DBAccess;
import com.util.PageBean;
public class BookDao extends BaseDao<Book>{
/**
* 未继承时的方法
* @param book
* @return
* @throws Exception
*/
public List<Book> getAll(Book book) throws Exception{
List<Book> list=new ArrayList<Book>();
String sql="select * from t_mvc_book where 1=1";
//书籍名称
String bname = book.getBname();
if(com.util.StringUtils.isNotBlank(bname)) {
sql+=" and bname like %"+bname+"%";
}
Connection con=DBAccess.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
Book b=new Book();
b.setBid(rs.getInt("bid"));
b.setBname(rs.getString("bname"));
b.setPrice(rs.getDouble("price"));
list.add(b);
}
return list;
}
/**
* 继承父类后的方法
* @param book
* @return
* @throws Exception
*/
public List<Book> getAll2(Book book) throws Exception{
String sql="select * from t_mvc_book where 1=1";
//书籍名称
String bname = book.getBname();
if(com.util.StringUtils.isNotBlank(bname)) {
sql+=" and bname like %"+bname+"%";
}
return super.getAll(sql, Book.class);
}
/**
* 实现分页
* @param book
* @param pageBean
* @return
* @throws Exception
*/
public List<Book> getAll3(Book book,PageBean pageBean) throws Exception{
String sql="select * from t_mvc_book where 1=1";
//书籍名称
String bname = book.getBname();
if(com.util.StringUtils.isNotBlank(bname)) {
sql+=" and bname like %"+bname+"%";
}
return super.pages(sql, Book.class, pageBean);
}
}
2.通用前台分页
mysql的分页：
sql=select * from t_mvc_book where bname like '%aa%'
pageSQL=sql limit (page-1)*rows,row
countSQL=select * from (sql) t
由于在项目中大量出现分页的需求，所以我们也需要写父类来减少代码量
目标：最终当碰到分页需求时，只要写少量代码
这里需要写一个类用来定义分页的基本属性（PageBean）
package com.util;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
/**
* 鍒嗛〉宸ュ叿绫�
*
*/
public class PageBean {
private int page = 1;// 起始页
private int rows = 10;// 每页展示条数
private int total = 0;// 总条数
private boolean pagination = true;// 是否分页
private String url;//保存上一次请求的url
private Map<String, String []> map=new HashMap<>();
public void setRequest(HttpServletRequest req) {
this.setUrl(req.getRequestURI().toString());
this.setMap(req.getParameterMap());
this.setPagination(req.getParameter("pagination"));
this.setRows(req.getParameter("rows"));
this.setPage(req.getParameter("page"));
}
private void setPage(String page) {
if(StringUtils.isNotBlank(page)) {
this.setPage(Integer.parseInt(page));
}
}
private void setRows(String rows) {
if(StringUtils.isNotBlank(rows)) {
this.setRows(Integer.valueOf(rows));
}
}
public void setPagination(String pagination) {
if(StringUtils.isNotBlank(pagination)) {
this.setPagination(!"false".equals(pagination));
}
}
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;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public Map<String, String[]> getMap() {
return map;
}
public void setMap(Map<String, String[]> map) {
this.map = map;
}
/**
* 鑾峰緱璧峰璁板綍鐨勪笅鏍�
*
* @return
*/
public int getStartIndex() {
return (this.page - 1) * this.rows;
}
/**
* 最大页码
* @return
*/
public int maxPage() {
return this.total%this.rows==0?this.total/this.rows:this.total/this.rows+1;
}
/**
* 下一页
* @return
*/
public int nextPage() {
return this.page<maxPage()?this.page+1:this.page;
}
/**
* 上一页
* @return
*/
public int previousPage() {
return this.page<1 ? 1:this.page-1;
}
@Override
public String toString() {
return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination + "]";
}
}
为了便捷，我们定义了一个标签page，属性值为pageBase
用了下面一个模型，也就是说，当出现分页需求时，就会出现下面格式的分页
这是用拼接将代码连接在一起的
package com.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.util.PageBean;
public class PageTag extends BodyTagSupport{
private PageBean pageBean;//包含了所有分页相关的元素
public int doStartTag() throws JspException {
JspWriter out = pageContext.getOut();
try {
out.print(toHTML());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return super.doStartTag();
}
private String toHTML() {
StringBuffer sb=new StringBuffer();
//隐藏的form表单---上一次请求下一次重新发送的关键
sb.append("<form action='"+pageBean.getUrl()+"' id='pageBeanForm' method='post'>");
sb.append("<input type='hidden' name='page'>");
Map<String, String[]> map = pageBean.getMap();//上一次请求的参数
if(map!=null&&map.size()>0) {
Set<Entry<String, String[]>> set = map.entrySet();
for (Entry<String, String[]> s : set) {
//参数名
String key = s.getKey();
//参数值
String[] value = s.getValue();
for (String v : value) {
//上一次请求的参数，再一次拼成了新的form表单
//注意：page参数每次都会提交，我们需避免
if(!"page".equals(key)) {
sb.append(" <input type='hidden' name='"+key+"' value='"+v+"'>");
}
}
}
}
sb.append("</form>");
sb.append("<ul class='pagination justify-content-center'>");
sb.append(" <li class='page-item "+(pageBean.getPage()==1?"disable":"")+"'><a class='page-link' href='javascript:gotoPage(1)'>首页〉</a></li>");
sb.append(" <li class='page-item "+(pageBean.getPage()==1?"disable":"")+"'><a class='page-link' href='javascript:gotoPage("+pageBean.previousPage()+")'><</a></li>");
// sb.append(" <li class='page-item'><a class='page-link' href='#'>1</a></li>");
// sb.append(" <li class='page-item'><a class='page-link' href='#'>2</a></li>");
sb.append(" <li class='page-item active'><a class='page-link' href='javascript:gotoPage("+pageBean.getPage()+")'>"+pageBean.getPage()+"</a></li>");
sb.append(" <li class='page-item "+(pageBean.getPage()==pageBean.maxPage()?"disable":"")+"'><a class='page-link' href='javascript:gotoPage("+pageBean.nextPage()+")'>></a></li>");
sb.append(" <li class='page-item "+(pageBean.getPage()==pageBean.maxPage()?"disable":"")+"'><a class='page-link' href='javascript:gotoPage("+pageBean.maxPage()+")'>尾页〉</a></li>");
sb.append(" <li class='page-item go-input'><b>到第</b><input class='page-link' type='text' id='skipPage' name='' /><b>页/b></li>");
sb.append(" <li class='page-item go'><a class='page-link' href='javascript:skipPage()'>确定</a></li>");
sb.append(" <li class='page-item'><b>共"+pageBean.getTotal()+"条</b></li>");
sb.append("</ul>");
sb.append("<script type='text/javascript'>");
sb.append(" function gotoPage(page) {");
sb.append(" document.getElementById('pageBeanForm').page.value = page;");
sb.append(" document.getElementById('pageBeanForm').submit();");
sb.append(" }");
sb.append("");
sb.append(" function skipPage() {");
sb.append(" var page = document.getElementById('skipPage').value;");
sb.append(" if (!page || isNaN(page) || parseInt(page) < 1 || parseInt(page) > "+pageBean.maxPage()+") {");
sb.append(" alert('请输入1-"+pageBean.maxPage()+"的数字');");
sb.append(" return;");
sb.append(" }");
sb.append(" gotoPage(page);");
sb.append(" }");
sb.append("</script>");
return sb.toString();
}
@Override
public int doEndTag() throws JspException {
return super.doEndTag();
}
}
tld文件中的配置
<tag>
<!-- 标签名 -->
<name>page</name>
<!-- 标签工具类 -->
<tag-class>com.tag.PageTag</tag-class>
<!-- 标签的内容类型：empty表示空标签，jsp表示可以为任何合法的JSP元素 -->
<body-content>jsp</body-content>
<attribute>
<!-- 自定义标签的属性名称 -->
<name>PageBean</name>
<!-- true表示必填 -->
<required>true</required>
<!-- true支持动态值,可以向值里面填jsp表达式、EL表达式,false则不支持 -->
<rtexprvalue>true</rtexprvalue>
</attribute>
</tag>另外，还需要一个servlet与前端连接
package com.web;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dao.BookDao;
import com.entity.Book;
import com.util.PageBean;
@WebServlet("/index.do")
public class BookServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Book b=new Book();
BookDao bd=new BookDao();
b.setBname(req.getParameter("bname"));
PageBean p=new PageBean();
p.setRequest(req);
try {
List<Book> all3 = bd.getAll3(b, p);
req.setAttribute("list3", all3);
req.setAttribute("pageBase", p);
} catch (Exception e) {
e.printStackTrace();
}
req.getRequestDispatcher("index.jsp").forward(req, resp);
}
}
在index界面，一行代码
<z:page PageBean="${ pageBase }"></z:page> 相当于之前的
<c:forEach var="i" items="${ list3 }">
<tr>
<td>${ i.bid }</td>
<td>${ i.bname }</td>
<td>${ i.price }</td>
</tr>
</c:forEach>如果想要改变条件 如每页显示20条，或者不需要分页，直接在表单内添加隐藏的input即可
<form class="form-inline"
action="${pageContext.request.contextPath }/index.do" method="post">
<div class="form-group mb-2">
<input type="text" class="form-control-plaintext" name="bname"
placeholder="请输入书籍名">
<input name="pagination" value="true" type="hidden">
<input name="rows" value="20" type="hidden">
</div>
<button type="submit" class="btn btn-primary mb-2">提交</button>
</form>
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
