您现在的位置是：网站首页> 编程资料编程资料
jsp+mysql实现网页的分页查询_JSP编程_
2023-05-25
429人已围观
简介 jsp+mysql实现网页的分页查询_JSP编程_
本文实例为大家分享了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"%>Insert title here 用户列表分页查询
（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" %>查询页面
| 编号 | 名称 | 价格 | 数量 | 日期 | 风格 |
| ${author.id} | ${author.name } | ${author.price } | ${author.num } | ${author.dates} | ${author.style} |
（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 queryAuthorList(){ Author obj = null ; List list = new ArrayList(); 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 ListqueryUserListPage(int pageIndex,int pageSize){ Author obj = null; List list = new ArrayList(); 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 c
点击排行
本栏推荐
