How to use jsp+mysql to implement paging query on web pages
1. The core sql statement to implement paging query
(1) The sql statement for querying the total number of records in the database:
select count(*) from +（表名）;
(2) The sql statement for each query of the number of records:
Where: 0 is the search index, 2 is the number of items searched each time.
select * from 表名 limit 0,2;
2. Code Implementation
In the previous article, I have introduced the DBconnection class and the Author object class. The former is used to obtain the database connection, and the latter is used to represent the author object. Click on the link to view the code for these two classes. Click the link to view the DBconnection class and Author object class
(1) Login page: 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) Display page: 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) Function implementation: 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) Interaction layer: 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) Tool class: PageBean.java. The function is: obtain the result set.
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;
}
}3. Running results
(1) Home page:
The above is the detailed content of How to use jsp+mysql to implement paging query on web pages. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1377
52
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
How to view database password in Navicat for MariaDB?
Apr 08, 2025 pm 09:18 PM
Navicat for MariaDB cannot view the database password directly because the password is stored in encrypted form. To ensure the database security, there are three ways to reset your password: reset your password through Navicat and set a complex password. View the configuration file (not recommended, high risk). Use system command line tools (not recommended, you need to be proficient in command line tools).
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
Navicat cannot connect to MySQL/MariaDB/PostgreSQL and other databases
Apr 08, 2025 pm 11:00 PM
Common reasons why Navicat cannot connect to the database and its solutions: 1. Check the server's running status; 2. Check the connection information; 3. Adjust the firewall settings; 4. Configure remote access; 5. Troubleshoot network problems; 6. Check permissions; 7. Ensure version compatibility; 8. Troubleshoot other possibilities.
