沃梦达 / 编程技术 / 编程语言 / Java开发 / 正文

jsp+mysql实现网页的分页查询

Java开发
2023-07-30

本文实例为大家分享了jsp+mysql实现网页的分页查询的具体代码，供大家参考，具体内容如下一、实现分页查询的核心sql语句（1）查询数据库的记录总数的sql语句：select count(*) from +（表名）;（2）每次查询的记录数的sql语句：...

本文实例为大家分享了jsp+mysql实现网页的分页查询的具体代码，供大家参考，具体内容如下

一、实现分页查询的核心sql语句

（1）查询数据库的记录总数的sql语句：

select count(*) from +（表名）;

（2）每次查询的记录数的sql语句：

其中：0是搜索的索引，2是每次查找的条数。

select * from 表名 limit 0,2;

二、代码实现

*上篇写过这两个类 ， DBconnection类：用于获取数据库连接，Author对象类。这两个类的代码点击连接查看。点击链接查看 DBconnection类和Author对象类

（1）登录页面：index.jsp。

<%@ page language="java" contentType="text/html; charset=utf-8"
  pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
  <a href="AuthorListPageServlet">用户列表分页查询</a>
</body>
</html>

（2）显示页面：userlistpage.jsp。

<%@ page language="java" contentType="text/html; charset=utf-8"
  pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>查询页面</title>
</head>
<body>
<table border="1">
 <tr>
  <td>编号</td>
  <td>名称</td>
  <td>价格</td>
  <td>数量</td>
  <td>日期</td>
  <td>风格</td>
 </tr>
 <c:forEach items="${pageBean.list}" var="author">
 <tr>
  <td>${author.id}</td>
  <td>${author.name }</td>
  <td>${author.price }</td>
  <td>${author.num }</td>
  <td>${author.dates}</td>
  <td>${author.style}</td>
 </tr>
 </c:forEach>
</table>
<c:if test="${ pageBean.record>0}">
<div>
  
   <c:if test="${pageBean.currentPage <= 1}">
   <span>首页</span>
   <span>上一页</span>
   <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage + 1 }">下一页</a>
   <a href ="AuthorListPageServlet?currPage=${pageBean.totalPage }">尾页</a>
   </c:if>
  
   <c:if test="${pageBean.currentPage > 1 && pageBean.currentPage < pageBean.totalPage }">
   <a href ="AuthorListPageServlet?currPage=1">首页</a>
   <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage - 1 }">上一页</a>
   <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage + 1 }">下一页</a>
   <a href ="AuthorListPageServlet?currPage=${pageBean.totalPage }">尾页</a>
   </c:if>
  
  <c:if test="${ pageBean.currentPage >= pageBean.totalPage}">
   <a href ="AuthorListPageServlet?currPage=1">首页</a>
   <a href ="AuthorListPageServlet?currPage=${pageBean.currentPage - 1 }">上一页</a>
  <span>下一页</span>
  <span>尾页</span>
  </c:if>
</div>
</c:if>
</body>
</html>

（3）功能实现：AuthorDao.java。

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.entity.Author;

public class AuthorDao {
 
  public Author check(String username ,int password ) {
  
   Author obj = null ;
   try {
   DBConnection db = new DBConnection();
   //获取数据库连接
   Connection conn = db.getConn();
   
   String sql="select *from furnitures where name = ? and id = ?";
   
   PreparedStatement ps=conn.prepareStatement(sql);
   //设置用户名和密码作为参数放入sql语句
   ps.setString(1,username);
   ps.setInt(2,password);
   //执行查询语句
   ResultSet rs = ps.executeQuery();
   //用户名和密码正确，查到数据 欧式风格 茶几
   if(rs.next()) {
    obj = new Author();
    obj.setId(rs.getInt(1));
    obj.setName(rs.getString(2));
    obj.setPrice(rs.getInt(3));
    obj.setNum(rs.getInt(4));
    obj.setDates(rs.getString(5));
    obj.setStyle(rs.getString(6));
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
   return obj;
  }
  /**
  * 用户列表信息查询
  * @return
  */
  public List<Author> queryAuthorList(){
   Author obj = null ;
   List<Author> list = new ArrayList<Author>();
   try {
   DBConnection db = new DBConnection();
   //获取数据库连接
   Connection conn = db.getConn();
   
   String sql="select *from furnitures";
   
   PreparedStatement ps=conn.prepareStatement(sql);
 
   //执行查询语句
   ResultSet rs = ps.executeQuery();
   //用户名和密码正确，查到数据 欧式风格 茶几
   //循环遍历获取用户信息
   while(rs.next()) {
    
    obj = new Author();
    obj.setId(rs.getInt(1));
    obj.setName(rs.getString(2));
    obj.setPrice(rs.getInt(3));
    obj.setNum(rs.getInt(4));
    obj.setDates(rs.getString(5));
    obj.setStyle(rs.getString(6));
    //将对象加入list里边
    list.add(obj);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
   return list;
  }
 
 
  /**
  * 查询用户表总记录数
  * @return
  */
  public int queryUserListCount() {
   DBConnection db;
  try {
     db = new DBConnection();
    Connection conn = db.getConn();
    String sql = "select count(*) from furnitures";
   
    PreparedStatement ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
   
   
    if(rs.next()) {
     return rs.getInt(1);
    }
   
   
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
   return 0;
  }
  /**
  * 查询用户分页数据
  * @param pageIndex数据起始索引
  * @param pageSize每页显示条数
  * @return
  */
  public List<Author>queryUserListPage(int pageIndex,int pageSize){
  
   Author obj = null;
   List<Author> list = new ArrayList<Author>();
  
   try {
   Connection conn = new DBConnection().getConn();
   String sql = "select * from furnitures limit ?,?;";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setObject(1, pageIndex);
   ps.setObject(2,pageSize);
   
   ResultSet rs = ps.executeQuery();
   //遍历结果集获取用户列表数据
   
   while(rs.next()) {
    obj = new Author();
    
    obj.setId(rs.getInt(1));
    obj.setName(rs.getString(2));
    obj.setPrice(rs.getInt(3));
    obj.setNum(rs.getInt(4));
    obj.setDates(rs.getString(5));
    obj.setStyle(rs.getString(6));
    
    list.add(obj);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
   return list;
  }
  /**
  * 用户新增
  * @param obj
  */
  public void add(Author obj) {
  
  try {
   
   DBConnection db = new DBConnection();
   //获取数据库连接
   Connection conn = db.getConn();
   
   String sql="insert into furnitures values(id,?,?,?,?,?)";
   
   PreparedStatement ps=conn.prepareStatement(sql);
   ps.setObject(1, obj.getName());
   ps.setObject(2, obj.getPrice());
   ps.setObject(3, obj.getNum());
   ps.setObject(4,obj.getDates());
   ps.setObject(5, obj.getStyle());
   
   //执行sql语句
    ps.execute();
   
   
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
   
  }
  //删除用户
  public void del(int id) {
   try {
    
    DBConnection db = new DBConnection();
    //获取数据库连接
    Connection conn = db.getConn();
    
    String sql="delete from furnitures where id = ?";
    
    PreparedStatement ps=conn.prepareStatement(sql);
    
    ps.setObject(1, id);
    
    //执行sql语句
     ps.execute();
    
    
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
    
  }
 
}

（4）交互层：AuthorListPageServlet.java。

package com.servlet;

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.AuthorDao;
import com.entity.Author;
import com.util.PageBean;

/**
* Servlet implementation class AuthorListPageServlet
*/
@WebServlet("/AuthorListPageServlet")
public class AuthorListPageServlet extends HttpServlet {
 private static final long serialVersionUID = 1L;
   
  /**
  * @see HttpServlet#HttpServlet()
  */
  public AuthorListPageServlet() {
    super();
    // TODO Auto-generated constructor stub
  }

 /**
  * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
  */
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  int pageSize = 2;
  AuthorDao ad = new AuthorDao();
  //总记录数
  int record = ad.queryUserListCount();
  //接收页面传入的页码
  String strPage = request.getParameter("currPage");
  int currPage = 1;//默认第一页
  if(strPage != null) {
   currPage = Integer.parseInt(strPage);
 
  }
  
  PageBean<Author> pb = new PageBean<Author>(currPage,pageSize,record);
  //查询某一页的结果集
  List<Author> list = ad.queryUserListPage(pb.getPageIndex(), pageSize);
  pb.setList(list);
  request.setAttribute("pageBean", pb);
  request.getRequestDispatcher("userlistpage.jsp").forward(request, response);
 }

 /**
  * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
  */
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  doGet(request, response);
 }

}

（5）工具类：PageBean.java。作用是：获取结果集。

package com.util;

import java.util.List;

public class PageBean<T>{
 private int currentPage;//当前页码
 private int pageIndex;//数据起始索引
 private int pageSize;//每页条数
 
 
 private int record;//总记录数
 private int totalPage;//总页数
 
 private List<T>list;//每页显示的结果集
 /**
  * 构造方法初始化pageIndex和totalPage
  * @param currentPage
  * @param pageIndex
  * @param pageSize
  */
 public PageBean(int currentPage,int pageSize,int record) {
  
  this.currentPage = currentPage;
  this.pageSize = pageSize;
  this.record = record;
  
  //总页数
  if(record % pageSize == 0) {
   //整除，没有多余的页
   this.totalPage = record / pageSize;
   
  }
  else {
   //有多余的数据，在增加一页
   this.totalPage = record / pageSize + 1;
  }
  
  //计算数据起始索引pageIndex
  if(currentPage < 1) {
   this.currentPage = 1;
  }
  else if(currentPage > this.totalPage) {
   this.currentPage = this.totalPage;
  }
  this.pageIndex = (this.currentPage -1)*this.pageSize;
 }
 
 public int getCurrentPage() {
  return currentPage;
 }

 public void setCurrentPage(int currentPage) {
  this.currentPage = currentPage;
 }

 public int getPageIndex() {
  return pageIndex;
 }

 public void setPageIndex(int pageIndex) {
  this.pageIndex = pageIndex;
 }

 public int getPageSize() {
  return pageSize;
 }

 public void setPageSize(int pageSize) {
  this.pageSize = pageSize;
 }

 public int getRecord() {
  return record;
 }

 public void setRecord(int record) {
  this.record = record;
 }

 public int getTotalPage() {
  return totalPage;
 }

 public void setTotalPage(int totalPage) {
  this.totalPage = totalPage;
 }

 public List<T> getList() {
  return list;
 }

 public void setList(List<T> list) {
  this.list = list;
 }
 
}

三、运行结果

（1）首页：

（2）中间页：

（3）尾页：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

本文标题为：jsp+mysql实现网页的分页查询

上一篇： jsp实现登录界面
下一篇： jsp实现简单图片验证码功能

基础教程推荐

学习HTML
学习jQuery
学习Laravel
学习CSS3
学习Vue.js
学习Bootstrap5
学习ThinkPHP
学习AJAX
猜你喜欢
最新文章
热门文章
编程基础
热门标签
网站首页 HTML/CSS 菜单 Layui Yii2