文章详情页
jsp+mysql实现网页的分页查询
本文实例为大家分享了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
排行榜
网公网安备