【MySQL】（4）数据库分页技术

来源：互联网发布：从零开始学seo 编辑：程序博客网时间：2024/06/06 03:54

一、数据库分页所需技术

1、所需Jar包：

这里写图片描述

2、c3p0-config.xml配置文件：

<c3p0-config>    <default-config>        <property name="driverClass">com.mysql.jdbc.Driver</property>        <property name="jdbcUrl">            <![CDATA[jdbc:mysql://127.0.0.1:3306/abc?useUnicode=true&characterEncoding=UTF-8]]>        </property>        <property name="user">root</property>        <property name="password">1234</property>        <!-- 初始化池大小 -->        <property name="initialPoolSize">2</property>        <!-- 最大空闲时间 -->        <property name="maxIdleTime">30</property>        <!-- 最多有多少个连接 -->        <property name="maxPoolSize">10</property>        <!-- 最少几个连接 -->        <property name="minPoolSize">2</property>        <!-- 每次最多可以执行多少个批处理语句 -->        <property name="maxStatements">50</property>    </default-config> </c3p0-config>

3、项目框架：

这里写图片描述

二、各框架的实现类

1、主页：：：index.jsp：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>数据库分页技术演示</title>      </head>  <body>    <c:redirect url="/PageServlet"></c:redirect>      </body></html>

2、web.xml：

<?xml version="1.0" encoding="UTF-8"?><web-app version="3.0"     xmlns="http://java.sun.com/xml/ns/javaee"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee     http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">  <display-name></display-name>  <servlet>    <servlet-name>PageServlet</servlet-name>    <servlet-class>cn.hncu.servlets.PageServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>PageServlet</servlet-name>    <url-pattern>/PageServlet</url-pattern>  </servlet-mapping>      <welcome-file-list>    <welcome-file>index.jsp</welcome-file>  </welcome-file-list></web-app>

2、utils层：：：c3p0Pool数据库连接池：

package cn.hncu.pubs;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;/////※※※以后我们开发可以做一个这样的池来使用public class C3p0Pool {    private static DataSource ds; //单例的池    private static ThreadLocal<Connection> t = new ThreadLocal<Connection>();    static{        try {            ds = new ComboPooledDataSource();        } catch (Exception e) {            throw new RuntimeException("数据库连接池创建失败!", e);        }    }    //以后会用到这个功能     public static DataSource getDataSource(){        return ds;    }    /**     * 获得数据库连接池中的对象     * @return     * @throws SQLException     */    public static Connection getConn() throws SQLException{        Connection con = t.get();        if(con==null){            con = ds.getConnection();            t.set(con);        }        return con;    }    public static void clearConFromThreadLocal(){        t.set(null);    }}

3、servlet层：：：PageServlet.java：

package cn.hncu.servlets;import java.io.IOException;import java.io.PrintWriter;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.service.IPageService;import cn.hncu.service.PageServiceImpl;public class PageServlet extends HttpServlet {    private IPageService service = new PageServiceImpl();    public void doGet(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        doPost(request, response);    }    public void doPost(HttpServletRequest request, HttpServletResponse response)            throws ServletException, IOException {        String spageNo = request.getParameter("page");//当前要查的页序号        if(spageNo==null || spageNo.trim().length()==0){            spageNo="1";        }        Integer pageNo = Integer.parseInt(spageNo);        try {            Map<String, Object> result = service.query(pageNo);            request.setAttribute("result", result);            request.getRequestDispatcher("/jsps/show.jsp").forward(request, response);        } catch (Exception e) {        }    }}

4、service层：：：IPageService.java：

package cn.hncu.service;import java.util.Map;public interface IPageService {    public Map<String, Object> query(Integer pageNo) throws Exception;}

4、service层：：：PageServiceImpl.java：

package cn.hncu.service;import java.util.Map;import cn.hncu.dao.PageDAO;import cn.hncu.dao.PageDaoJdbc;public class PageServiceImpl implements IPageService{    private PageDAO dao = new PageDaoJdbc();    @Override    public Map<String, Object> query(Integer pageNo) throws Exception {        return dao.query(pageNo);    }}

5、dao层：：：PageDAO：

package cn.hncu.dao;import java.util.Map;/* * （1）必须返回总页数：（int）+查询的表数据（list<map<String,Object>>） * （2）可以封装成：Map<String,Object> map = new HashMap<String,Object>(); */public interface PageDAO {    public Map<String, Object> query(Integer pageNo) throws Exception;}

5、dao层：：：PageDaoJdbc：

package cn.hncu.dao;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import cn.hncu.pubs.C3p0Pool;public class PageDaoJdbc implements PageDAO{    @Override    public Map<String, Object> query(Integer pageNo) throws Exception {        int pageSize = 10;//每页显示的行数        Map<String, Object> result = new HashMap<String, Object>();        result.put("currentPage", pageNo);        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());        //查询页数        String sql = "select count(1) from stud";        int rows = Integer.parseInt(run.query(sql, new ScalarHandler())+"");//计算总行数        int pageCount = rows/pageSize + (rows%pageSize==0? 0:1 );//计算总页数        result.put("pageCount",pageCount);        //查询当前页的所有表数据        int startN = (pageNo-1)*pageSize;//起始页        sql = "select *from stud limit "+startN+","+pageSize;        List<Map<String, Object>> datas = run.query(sql, new MapListHandler());        result.put("datas", datas);        return result;    }}

MySQL数据库：：：

这里写图片描述

6、前端页面：：：show.jsp：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>  </head>  <body>    <!-- 当前页的表数据 -->    <h2>学生信息</h2>    <c:forEach items="${result.datas}" var="map">        ${map.id},${map.name},${map.pwd}<br/>     </c:forEach>    <hr>    <!--显示上一页  -->          <c:if test="${result.currentPage!=1}">        <a href="<c:url value='/PageServlet?page=${result.currentPage-1}'/>">上一页</a> &nbsp;&nbsp;      </c:if>    <!-- 所有的页数 -->    <c:forEach begin="1" end="${result.pageCount}" var="page">        <c:if test="${result.currentPage!=page}">            <a href="<c:url value='/PageServlet?page=${page}'/>">${page }</a>&nbsp;&nbsp;           </c:if>        <c:if test="${result.currentPage==page}">             <font size="12px" color="red">${page}</font>&nbsp;&nbsp;        </c:if>    </c:forEach>    <!-- 显示下一页 -->    <c:if test="${result.pageCount!=result.currentPage}">        <a href="<c:url value='/PageServlet?page=${result.currentPage+1}'/>">下一页</a> &nbsp;&nbsp;      </c:if>    <hr>    <!--用下拉框切换页面  -->    <select onchange="sub(this);">        <c:forEach begin="1" end="${result.pageCount}" var="page">            <option value="${page }" <c:if test="${page==result.currentPage }">selected="selected"</c:if> >                第${page}页            </option>        </c:forEach>    </select>    <script type="text/javascript">        function sub(obj){              window.location.href="<c:url value='/PageServlet?page='/>"+obj.value;        }    </script>    <hr>    <!--显示上一页  -->          <c:if test="${result.currentPage!=1}">        <a href="<c:url value='/PageServlet?page=${result.currentPage-1}'/>">上一页</a> &nbsp;&nbsp;      </c:if>    <!--计算有哪些页面  -->    <c:set var="currentPage" value="${result.currentPage}"></c:set>    <c:set var="count" value="${result.pageCount}"></c:set>    <c:if test="${currentPage<=5}">        <c:set var="start" value="1"></c:set>        <c:if test="${count/10>0}"><c:set var="end" value="10"></c:set></c:if>         <c:if test="${count/10==0}"><c:set var="end" value="${count}"></c:set></c:if>       </c:if>     <c:if test="${currentPage>5}">        <c:set var="start" value="${currentPage-5}"></c:set>        <c:if test="${currentPage+4>count}"><c:set var="end" value="${count}"></c:set></c:if>           <c:if test="${currentPage+4<=count}"><c:set var="end" value="${currentPage+4}"></c:set></c:if>       </c:if>     <!-- 显示出页面 -->      <c:forEach begin="${start}" end="${end}" var="i">        <c:if test="${i!=currentPage}">            <a href="<c:url value='/PageServlet?page=${i}'/>">${i}</a>&nbsp;&nbsp;        </c:if>        <c:if test="${i==currentPage}">            <font size="12px" color="red">${i}</font>&nbsp;&nbsp;        </c:if>    </c:forEach>    <!-- 显示下一页 -->    <c:if test="${result.pageCount!=result.currentPage}">        <a href="<c:url value='/PageServlet?page=${result.currentPage+1}'/>">下一页</a> &nbsp;&nbsp;      </c:if>  </body></html>