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

最新推荐文章于 2023-01-31 11:00:27 发布

一只小小的蚂蚁

最新推荐文章于 2023-01-31 11:00:27 发布

阅读量758

点赞数

分类专栏： JAVA Web

本文链接：https://blog.csdn.net/qq_44863179/article/details/104466379

版权

JAVA Web 专栏收录该内容

34 篇文章 3 订阅

订阅专栏

一、实现分页查询的核心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）尾页：

一只小小的蚂蚁

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
jsp+mysql 实现网页的分页查询

一、实现分页查询的核心sql语句（1）查询数据库的记录总数的sql语句：select count(*) from +（表名）;（2）每次查询的记录数的sql语句：其中：0是搜索的索引，2是每次查找的条数。select * from 表名 limit 0,2;二、代码实现*上篇写过这两个类， DBconnection类：用于获取数据库连接，Author对象类。这两个类的代...
复制链接

扫一扫

专栏目录