mysql笔记十——数据库分页技术（再分页，模糊查询）

来源：互联网发布：linux服务启动关闭管理编辑：程序博客网时间：2024/05/22 15:18

1.什么是数据分页：数据分页就是将很多条记录像书本一样分页，每页显示多少行记录；

2.为什么要数据分页：当我们进行sql语句查询时，假如数据有成千上万行记录，如果在同一个页面去显示，那这个页面得有多大，数据就要很多，而我们所需的记录又很少，不使用分页，查看起来那么繁琐，而且一不小心容易看着眼花。使用数据分页，就行书本一样，有页数，一目了然。相当简洁。

3.核心sql语句：SELECT * FROM stud LIMIT m,n ————m表示要显示的页数，n表示显示的记录行数

4.核心思想：

总行数(rows): select count(1) from stud;
每页显示的行数(PAGE_SIZE): 固定值---已知的一个常量
页数: pageSize= num/n + (num%n==0)?0:1
当前页号: currentPage
当前要显示的页面数据的起始行号和终止行号 :startRow: (currentPage-1)*pageSize
如何显示从startN开始的pageSize条记录 select * from stud limit startN, pageSize;
当前显示的开始页号：showStart=currentPage-showSize/2;
当前显示的结束页号：showEnd=showStart+showSize-1;
模糊查询：select count(*) from stud where 1=1 and........

5.成果图：

6.代码实现

需要的包和配置文件：

myConUtil.jar----自己写的c3p0pool工具类commons-dbutils-1.4.jarmysql-connector-java-5.1.34-bin.jarc3p0-0.9.1.2.jarc3p0-config.xml

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>  </head>  <body>    <a href='<c:url value="/PageServlet"></c:url>'>查看分页技术</a>  </body></html>

show,jsp

<span style="font-size:12px;"><%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>演示数据分页</title><link rel="stylesheet" href='<c:url value="/css/table.css"></c:url>'media="screen"><script type="text/javascript">function onsub(obj){window.location.href="<c:url value='/PageServlet?page='></c:url>"+obj.value;}</script></head><body><h3>以下是当前页的内容</h3><form action="<c:url value='/PageServlet'/>" method="post" >请输入要查询的关键字：<br/>学号:<input type="text" name="serachId" value="${stud.id }"><br/>姓名:<input type="text" name="serachName" value="${stud.name }"><br/><input type="submit" value="搜索"></form><table><c:if test="${!empty map.datas}"><tr><th>学号</th><th>姓名</th></tr></c:if><c:forEach items="${map.datas}" var="stud"><tr><td>${stud.id }</td><td>${stud.name }</td></tr></c:forEach></table><c:if test="${map.currentPage!=1}" var="boo"><a href="<c:url value='/PageServlet?page=${map.currentPage-1}'></c:url>" >上一页</a>   </c:if><c:forEach var="idx" begin="${map.showStart }" end="${map.showEnd }"><c:if test="${map.currentPage==idx}" var="boo"><font face="STCAIYUN"><ahref="<c:url value='/PageServlet?page=${idx}'></c:url>">${idx}</a></font>   </c:if><c:if test="${!boo}"><a href="<c:url value='/PageServlet?page=${idx}'></c:url>">${idx}</a>   </c:if></c:forEach><c:if test="${map.currentPage!=map.pageCount}" var="boo"><a href="<c:url value='/PageServlet?page=${map.currentPage+1}'></c:url>">下一页</a>   </c:if><br/><br/><br/><select onchange="onsub(this)"><c:forEach var="i" begin="1" end="${map.pageCount }"><option <c:if test="${i==map.currentPage }" >selected="selected" </c:if>  value="${i}" ><a href="<c:url value='/PageServlet?page=${i}'></c:url>">第 ${i } 页</a></option></c:forEach></select></body></html></span>

table.css

<span style="font-size:12px;">table{color: green;border: 1px solid blue;border-collapse: collapse;width:500px;margin: auto;}td{border: 1px solid blue;}th{border: 1px solid blue;}body{text-align: center;}</span>

PageServlet.java

<span style="font-size:12px;">package cn.hncu.page1.servlet;import java.io.IOException;import java.sql.SQLException;import java.util.Map;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.hncu.page1.domain.Stud;import cn.hncu.page1.service.IPageService;import cn.hncu.page1.service.PageService;public class PageServlet extends HttpServlet {private IPageService service=new PageService();public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doPost(request, response);}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {request.setCharacterEncoding("utf-8");//这里是搜索区域的操作Stud stud=null;if(request.getMethod().equals("POST")){if (stud==null) {stud = new Stud();}String serachId = request.getParameter("serachId");String serachName = request.getParameter("serachName");stud.setId(serachId);stud.setName(serachName);request.getSession().setAttribute("stud", stud);}else{stud=(Stud) request.getSession().getAttribute("stud");if (stud==null) {stud = new Stud();}}//封装studs对象int currentPage=1;try {currentPage = Integer.parseInt(request.getParameter("page"));} catch (NumberFormatException e) {currentPage=1;}Map<String, Object> map=null;try {map=service.query(currentPage,stud);} catch (SQLException e) {e.printStackTrace();}map.put("currentPage", currentPage);//显示滚动页号int showStart=0;//从第几个页号开始显示int showEnd=0;//从第几个页号结束显示int showSize=10;//显示多少页数int pageCount=Integer.parseInt(""+map.get("pageCount"));if(showSize>pageCount){//显示页数大于于总页数showStart=1;showEnd=pageCount;}else{if(currentPage<=showSize/2){showStart=1;showEnd=showSize;}else{showStart=currentPage-showSize/2;showEnd=showStart+showSize-1;}}if(showEnd>pageCount){showEnd=pageCount;showStart=showEnd-showSize;}map.put("showStart", showStart);map.put("showEnd", showEnd);request.setAttribute("map", map);request.getRequestDispatcher("/jsps/show.jsp").forward(request, response);}}</span>

IPageService.java

<span style="font-size:12px;">package cn.hncu.page1.service;import java.sql.SQLException;import java.util.Map;import cn.hncu.page1.domain.Stud;public interface IPageService {public Map<String, Object> query(int currentPage, Stud stud) throws SQLException;}</span>

PageService.java

<span style="font-size:12px;"> package cn.hncu.page1.service;import java.sql.SQLException;import java.util.Map;import cn.hncu.page1.dao.PageDao;import cn.hncu.page1.dao.PageDaoJdbc;import cn.hncu.page1.domain.Stud;public class PageService implements IPageService{    private PageDao dao=new PageDaoJdbc();    @Override    public Map<String, Object> query(int currentPage, Stud stud)            throws SQLException {        return dao.query(currentPage,stud);    }    }</span>

PageDao.java

<span style="font-size:12px;">package cn.hncu.page1.dao;import java.sql.SQLException;import java.util.Map;import cn.hncu.page1.domain.Stud;public interface PageDao {public Map<String, Object> query(int currentPage, Stud stud) throws SQLException;}</span>

PageDaoJdbc.java

<span style="font-size:12px;">package cn.hncu.page1.dao;import java.sql.SQLException;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.junit.Test;import cn.hncu.page1.domain.Stud;import cn.hncu.page1.service.IPageService;import cn.hncu.pool.C3p0Pool;public class PageDaoJdbc implements PageDao{private static final int PAGE_SIZE=10;@Overridepublic Map<String, Object> query(int currentPage, Stud stud) throws SQLException {Map<String, Object> map=new HashMap<String, Object>();DataSource pool=C3p0Pool.getPool();QueryRunner qr=new QueryRunner(pool);String sql="select count(*) from stud where 1=1 ";if(stud.getId()!=null&&stud.getId().trim().length()>0){sql+="and id like '%"+stud.getId()+"%'";}if(stud.getName()!=null&&stud.getName().trim().length()>0){sql+="and name like '%"+stud.getName()+"%'";}int rows=Integer.parseInt(""+ qr.query(sql, new ScalarHandler()));int pageCount=rows/PAGE_SIZE+((rows%PAGE_SIZE==0)?0:1);map.put("pageCount", pageCount);int startRow=(currentPage-1)*PAGE_SIZE;map.put("startRow", startRow);String sql2="select * from stud where 1=1  ";//这种判断方法，很不错if(stud.getId()!=null&&stud.getId().trim().length()>0){sql2+="and id like '%"+stud.getId()+"%'";}if(stud.getName()!=null&&stud.getName().trim().length()>0){sql2+="and name like '%"+stud.getName()+"%' ";}sql2+="limit "+startRow+" , "+PAGE_SIZE;List<Map<String, Object>> datas=qr.query(sql2, new MapListHandler());map.put("datas", datas);return map;}}</span>

Stud.java

<span style="font-size:12px;">package cn.hncu.page1.domain;public class Stud {    private String id;    private String name;    public String getId() {        return id;    }    public void setId(String id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    @Override    public String toString() {        return "Stud [id=" + id + ", name=" + name + "]";    }    }</span>

1 0