jsp mysql网页分页查询
一只小小的蚂蚁 人气:0一、实现分页查询的核心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)尾页:
加载全部内容