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;
   }