程序员之家 服务器常用软件
快捷导航
您的位置：首页 → 网络编程 → JSP编程 → jsp?mysql网页分页查询

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

 更新时间：2022年02月09日 14:30:44   作者：一只小小的蚂蚁  
这篇文章主要为大家详细介绍了jsp+mysql实现网页的分页查询，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
（福利推荐：【腾讯云】服务器最新限时优惠活动，云服务器1核2G仅99元/年、2核4G仅768元/3年，立即抢购>>>：9i0i.cn/qcloud）

（福利推荐：你还在原价购买阿里云服务器？现在阿里云0.8折限时抢购活动来啦！4核8G企业云服务器仅2998元/3年，立即抢购>>>：9i0i.cn/aliyun）

本文实例为大家分享了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）尾页：

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

您可能感兴趣的文章:

相关文章

最新评论

?


http://www.vxiaotou.com