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

最新推荐文章于 2024-01-03 16:05:27 发布

YI_DIAN_DIAN

最新推荐文章于 2024-01-03 16:05:27 发布

阅读量365

点赞数

本文链接：https://blog.csdn.net/qq_35415600/article/details/76686834

版权

---- ----⑦WEB开发技术同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

❻项目案例演示

7 篇文章 0 订阅

订阅专栏

❹MySql数据库

6 篇文章 0 订阅

订阅专栏

一、数据库分页所需技术

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>

7、执行效果：：：

这里写图片描述

YI_DIAN_DIAN

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】（4）数据库分页技术

一、数据库分页所需技术1、所需Jar包：2、c3p0-config.xml配置文件：<c3p0-config> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl"> <![CDATA
复制链接

扫一扫