java web实现分页查询

我会带着你远行 2021-09-21 21:50 495阅读 0赞

### 引言 ###

> 思路：本次教大家采用java+jsp+servlet+c3p0技术实现数据的分页查询，在这之前，请创建好数据库表和搭建好基础框架并连接上数据库。分页会用到一个封装的PageTool工具，由它完成分页的核心操作。

> 开发工具：intellij IDEA  
> 核心技术：JSP+Servlet+C3P0  
> 数 据 库：Mysql5.7

> 以下是c3p0所需jar包，没有jar包，关注微信公众号源码客栈后，在右菜单的软件下载里面有网盘地址可以下载  
> ![在这里插入图片描述][20210326144143409.jpg_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]

##### 数据库表字段 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]

##### 项目基础骨架（mvc的设计模式） #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 2]

##### C3P0配置文件 #####

<?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>
    	<default-config>
    	
    		<property name="driverClass">com.mysql.jdbc.Driver</property>
    		<property name="jdbcUrl">jdbc:mysql://localhost:3306/db_xlzx?useUnicode=true&characterEncoding=utf-8</property>
    	
    	
    		<property name="user">root</property>
    		<property name="password">123456</property>
    		<property name="acquireIncrement">5</property>
    		<property name="initialPoolSize">10</property>
    		<property name="minPoolSize">5</property>
    		<property name="maxPoolSize">20</property>
    	</default-config>
    </c3p0-config>

##### C3P0Utils类 #####

package edu.school.utils;
    import java.sql.Connection;
    import java.sql.SQLException;
    import javax.sql.DataSource;
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    //使用C3p0连接池获取连接数据库
    public class C3p0Utils {
    	private static DataSource ds;
    	static{
    		ds=new ComboPooledDataSource();
    	}
    	public static DataSource getDs(){
    		return ds;
    	}
    	public static Connection getConnection() throws SQLException{
    		return ds.getConnection();
    	}
    	public static void main(String args[]) throws SQLException {
    		System.out.println(new C3p0Utils().getConnection()+"连接成功");
    	}
    }

##### web.xml配置文件 #####

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
             version="4.0">
        <servlet>
            <display-name>BaseServlet</display-name>
            <servlet-name>BaseServlet</servlet-name>
            <servlet-class>edu.school.servlet.BaseServlet</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>BaseServlet</servlet-name>
            <url-pattern>/BaseServlet</url-pattern>
        </servlet-mapping>
        
        <servlet>
            <display-name>StudentServlet</display-name>
            <servlet-name>StudentServlet</servlet-name>
            <servlet-class>edu.school.servlet.StudentServlet</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>StudentServlet</servlet-name>
            <url-pattern>/StudentServlet</url-pattern>
        </servlet-mapping>
    </web-app>

##### 1.分页工具PageTool类 #####

package edu.school.entity;
    public class PageTool {
        private int totalCount;//总数据量
        private int currentPage;//当前页面
        private int pageCount;//总页数
        private int lastPage;//上一页页数
        private int nextPage;//下一页页数
        private int pageSize;//每一页数据量
        private int startIndex;//起始下标
        public int getTotalCount() {
            return totalCount;
        }
        public void setTotalCount(int totalCount) {
            this.totalCount = totalCount;
        }
        public int getCurrentPage() {
            return currentPage;
        }
        public void setCurrentPage(int currentPage) {
            this.currentPage = currentPage;
        }
        public int getPageCount() {
            return pageCount;
        }
        public void setPageCount(int pageCount) {
            this.pageCount = pageCount;
        }
        public int getLastPage() {
            return lastPage;
        }
        public void setLastPage(int lastPage) {
            this.lastPage = lastPage;
        }
        public int getNextPage() {
            return nextPage;
        }
        public void setNextPage(int nextPage) {
            this.nextPage = nextPage;
        }
        public int getPageSize() {
            return pageSize;
        }
        public void setPageSize(int pageSize) {
            this.pageSize = pageSize;
        }
        public int getStartIndex() {
            return startIndex;
        }
        public void setStartIndex(int startIndex) {
            this.startIndex = startIndex;
        }
        public PageTool() {
        }
        /*
             为什么只传递总数据量及当前页码呢？
                 每页数据量给定为死值，不需要传
                 我们只需要再知道当前页码及总数据量，其他的值都可以推算出来！
                 所以只传递当前页码及总数据量
         */
        public PageTool(int totalCount, String currentPage) {
            super();
            this.totalCount = totalCount;
            //将每页数据量给定为死值
            pageSize = 5;
            initialCurrentPage(currentPage);
            initialPageCount();
            initialLastPage();
            initialNextPage();
            initialStartIndex();
        }
    
        //初始化当前页码
        private void initialStartIndex() {
            startIndex = (currentPage - 1) * pageSize;
        }
    
        //初始化下一页页码
        private void initialNextPage() {
            if (currentPage == pageCount) {
                nextPage = pageCount;
            } else {
                nextPage = currentPage + 1;
            }
        }
    
        //初始化上一页页码
        private void initialLastPage() {
            if (currentPage == 1) {
                lastPage = 1;
            } else {
                lastPage = currentPage - 1;
            }
        }
    
        //初始化总页数的方法
        private void initialPageCount() {
            pageCount = totalCount / pageSize + (totalCount % pageSize == 0 ? 0 : 1);
        }
    
        //初始化当前页码的方法
        private void initialCurrentPage(String currentPage) {
    		/*
    		 	从页面获取到的当前页码有两种情况：
    		 		1、当前页码为null，代表第一次进行分页展示页面，那么当前页码就是第一页
    		 		2、当前页码不为null，就代表点击了首页、尾页、上一页、下一页的按钮，传递的值
    		 */
            if (currentPage == null) {
                this.currentPage = 1;
            } else {
                this.currentPage = Integer.valueOf(currentPage);
            }
        }
    
        @Override
        public String toString() {
            return "PageTool [totalCount=" + totalCount + ", currentPage=" + currentPage + ", pageCount=" + pageCount
                    + ", lastPage=" + lastPage + ", nextPage=" + nextPage + ", pageSize=" + pageSize + ", startIndex="
                    + startIndex + "]";
        }
    
    }

##### 2.实体类Student #####

package edu.school.entity;
    //学生
    public class Student {
       
    	public Student() {
    		super();
    	}
    	public Student(Integer id, String stuno, String realname, String pwd, String major, String sex, String bj,
    			String createDate) {
    		super();
    		this.id = id;
    		this.stuno = stuno;
    		this.realname = realname;
    		this.pwd = pwd;
    		this.major = major;
    		this.sex = sex;
    		this.bj = bj;
    		this.createDate = createDate;
    	}
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getStuno() {
    		return stuno;
    	}
    	public void setStuno(String stuno) {
    		this.stuno = stuno;
    	}
    	public String getRealname() {
    		return realname;
    	}
    	public void setRealname(String realname) {
    		this.realname = realname;
    	}
    	public String getPwd() {
    		return pwd;
    	}
    	public void setPwd(String pwd) {
    		this.pwd = pwd;
    	}
    	public String getMajor() {
    		return major;
    	}
    	public void setMajor(String major) {
    		this.major = major;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public String getBj() {
    		return bj;
    	}
    	public void setBj(String bj) {
    		this.bj = bj;
    	}
    	public String getCreateDate() {
    		return createDate;
    	}
    	public void setCreateDate(String createDate) {
    		this.createDate = createDate;
    	}
    	private Integer id;
    	private String stuno;
    	private String realname;//学生姓名
        private String pwd;//密码
        private String major;//专业
        private String  sex;//性别
        private String  bj;//班级
        private String  createDate;//录入日期
    
    }

##### 3.StudentDao层 #####

package edu.school.dao;
    
    import java.util.List;
    import edu.school.entity.PageTool;
    import edu.school.entity.Student;
    public interface StudentDao {
        public int queryCount();//统计数量
        public List<Student> findAllByPage(PageTool pageTool);//分页
    
    }

##### 4.StudentDaoImpl层 #####

> 实现StudentDao接口的实现类

package edu.school.dao.impl;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import edu.school.dao.StudentDao;
    import edu.school.entity.PageTool;
    import edu.school.entity.Student;
    import edu.school.utils.C3p0Utils;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.apache.commons.dbutils.handlers.ScalarHandler;
    public class StudentDaoImpl implements StudentDao {
        private QueryRunner runner=new QueryRunner(C3p0Utils.getDs());
          //统计数量
        public int queryCount() {
            String sql="select count(*) from student";
            try {
                Long count =  (Long) runner.query(sql, new ScalarHandler());
                //将long的类型转成int类型
                return count.intValue();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    
        @Override
        public List<Student> findAllByPage(PageTool pageTool) {
            String sql="select * from student limit ?,?";
            List<Student> students=null;
            try {
                students=runner.query(sql,new BeanListHandler<Student>(Student.class),pageTool.getStartIndex(),pageTool.getPageSize());
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return students;
        }
    
        }

##### 5.StudentService层 #####

> //业务层接口,调用StudentDao

package edu.school.service;
    import edu.school.entity.PageTool;
    import edu.school.entity.Student;
    import java.util.List;
    
    public interface StudentService {
        public int queryCount();
        public List<Student> findAllByPage(PageTool pageTool);//分页
    }

##### 6.StudentServiceImpl层 #####

> 实现StudentService类，并调用dao里的持久层方法

package edu.school.service.impl;
    import edu.school.dao.StudentDao;
    import edu.school.dao.impl.StudentDaoImpl;
    import edu.school.entity.PageTool;
    import edu.school.entity.Student;
    import edu.school.service.StudentService;
    import java.util.List;
    public class StudentServiceImpl implements StudentService {
        private StudentDao dao=new StudentDaoImpl();
        public int queryCount() {
            return dao.queryCount();
        }
        @Override
        public List<Student> findAllByPage(PageTool pageTool) {
            return dao.findAllByPage(pageTool);
        }
    
       }

##### 7.BaseServlet #####

> 创建一个模板BaseServlet,使其它的Servlet继承BaseServlet即可，通过过反射来调用业务，优化ifelse，减少大量的判断，可直接触发每一个Servlet里的方法

package edu.school.servlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.lang.reflect.Method;
    public class BaseServlet extends HttpServlet {
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException {
            String action=request.getParameter("action");
            try {
                Method method=this.getClass().getDeclaredMethod(action,HttpServletRequest.class,HttpServletResponse.class);//通过反射来调用业务，优化ifelse
                method.invoke(this,request,response);//调用目标业务，方法
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException {
            doPost(request,response);
        }
    }

##### 8.StudentServlet #####

package edu.school.servlet;
    import edu.school.entity.PageTool;
    import edu.school.entity.Student;
    import edu.school.service.StudentService;
    import edu.school.service.impl.StudentServiceImpl;
    import edu.school.utils.WebUtils;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    public class StudentServlet extends BaseServlet {//继承BaseServlet
        private StudentService service=new StudentServiceImpl();
        //分页查询学生信息
        protected void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            //1.统计查询到的总数
            int totalCount=service.queryCount();
            //2.获取的当前页码,这个是从页面获取的
            String currentPage = request.getParameter("currentPage");
            //3.进行封装
            PageTool pageTool=new PageTool(totalCount, currentPage);
            List<Student> studentList=service.findAllByPage(pageTool);
            //4.存储到域对象中
            request.setAttribute("studentList", studentList);
            //5.将分页信息存储
            request.setAttribute("pageTool", pageTool);
            //6.通过请求转发跳转到学生信息列表
            request.getRequestDispatcher("/WEB-INF/student/student_list.jsp").forward(request, response);
        }

##### 9.学生分页信息展示界面 #####

<%@ page language="java" contentType="text/html; charset=utf-8"
        pageEncoding="utf-8"%>
     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    //头文件省略
    <body>
    <table class="table table-border table-bordered table-bg table-hover table-sort table-responsive">
    			<thead>
    				<tr class="text-c">	
    					<th width="80">学号</th>
    					<th width="80">学生姓名</th>
    					<th width="50">性别</th>		
    					<th width="80">登录密码</th>
    					<th width="120">专业</th>
    					<th width="75">班级</th>			
    					<th width="120">录入日期</th>
    					<th width="50">基础操作</th>
    				</tr>
    			</thead>
    			<tbody>
    			<c:forEach items="${studentList}" var="u" >
    				<tr class="text-c">				
    				<td>${u.stuno }</td>
    				<td>${u.realname }</td>
    				<td>${u.sex }</td>	
    				<td>${u.pwd }</td>
    				<td>${u.major }</td>
    				<td>${u.bj }</td>			
    				<td>${u.createDate }</td>
    				<td class="f-14 td-manage">
    					<a  href="StudentServlet?action=query&id=${u.id}"   title="编辑" ><i class="Hui-iconfont"></i></a>
    					<a style="text-decoration:none" class="ml-5" onClick="del_student(this,'${u.id }')" href="javascript:;" title="删除"><i class="Hui-iconfont"></i></a>	
    				</td>
    
    				</tr>
    
    			</c:forEach>
    
    			</tbody>
    		</table>
    		<br>
    		<div style="text-align: center">
    			 <div class="inline pull-right page">
    						<a class="btn btn-secondary-outline  size-MINI radius" href='StudentServlet?action=list&currentPage=1' >首页</a>
    
    						<a class="btn btn-secondary-outline  size-MINI radius"href='StudentServlet?action=list&currentPage=${pageTool.lastPage}'>上一页</a>
    
    						<a class="btn btn-secondary-outline  size-MINI radius"href='StudentServlet?action=list&currentPage=${pageTool.nextPage}'>下一页</a>
    
    						<a class="btn btn-secondary-outline  size-MINI radius" href='StudentServlet?action=list&currentPage=${pageTool.pageCount}'>尾页</a>
    
    						   共<span class='current'> ${pageTool.totalCount } </span>条记录
    						<span class='current'> ${pageTool.currentPage }/${pageTool.pageCount } </span>页
    					</div>
                    </div>
      <script type="text/javascript" src="lib/layer/2.4/layer.js"></script>
    <script type="text/javascript">
    function del_student(obj,id){
    	 //用户安全提示
        if(confirm("您确定要删除该条记录吗？")){
            //访问路径
            location.href="${pageContext.request.contextPath}/StudentServlet?action=delete&id="+id;
        }
    }
    
    </script>
    </body>

##### 10.效果图 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 3]

> 若需要定制Java web项目，根据您的需求开发项目，可以加3531912911咨询，或者关注公众号回复数字6联系我们

[20210326144143409.jpg_pic_center]: /images/20210920/96629d243aa843eba568c8d2a90ca5a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]: /images/20210920/5307a23c0ce44caa88777591586bb9c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210920/a9390a192a9c4acaa7ff37a283ddf0dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210920/1b7bd63285c349d49b636f5e80b4e83b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210920/3fefeb27c8f74c0091876af6ea912cf2.png