JavaWeb入门教程之分页查询功能的简单实现-APISpace

JavaWeb入门教程之分页查询功能的简单实现

一、mysql实现分页查询的SQL语句

1、分页需求：

客户端通过传递pageNo(页码)，counter(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：

比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,20; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,30; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

2、总结

通过上面的分析，可以得出符合我们自己需求的分页sql格式是：select * from table limit (pageNo-1)*counter,counter; 其中pageNo是页码，counter是每页显示的条数。

二、javaWeb程序

1、创建PageBeanUtils.java工具类

package com.ambow.utils;

import java.util.List;

public class PageBeanUtils {

private int prePage;//上一页

private int nextPage;//下一页

private int firstPage=1;//首页

private int lastPage;//尾页

private int currentPage = 1;//当前

private int totalPage;//总页数

private int pageSize;//每页显示条数，默认显示10条

private int totalData;//数据总条数

private List pageData;//数据

public PageBeanUtils(int currentPage,int pageSize, int totalData) {

this.currentPage = currentPage;

this.pageSize = pageSize;

this.totalData = totalData;

//计算获得总页数（尾页）

// this.totalPage = this.lastPage = (totalData+pageSize-1)/pageSize;

this.totalPage = this.lastPage = (int)Math.ceil((double)totalData/pageSize);

//防止当前页小于1

this.currentPage = Math.max(this.currentPage, 1);

//防止当前页大于总的页数

this.currentPage = Math.min(this.totalPage, this.currentPage);

this.prePage = Math.max(this.currentPage-1, 1);

this.nextPage = Math.min(this.currentPage+1, this.totalPage);

/**

* ceil

public static double ceil(double a)

返回最小的（最接近负无穷大） double 值，该值大于等于参数，并等于某个整数。特殊情况如下：

如果参数值已经等于某个整数，那么结果与该参数相同。

如果参数为 NaN、无穷大、正 0 或负 0，那么结果与参数相同。

如果参数值小于 0，但是大于 -1.0，那么结果为负 0。

注意， Math.ceil(x) 的值与 -Math.floor(-x) 的值完全相同。

参数：

a - 一个值。

最小（最接近负无穷大）浮点值，该值大于等于该参数，并等于某个整数。

}

public PageBeanUtils(int prePage, int nextPage, int firstPage, int lastPage, int currentPage, int totalPage,

int pageSize, int totalData, List pageData) {

super();

this.prePage = prePage;

this.nextPage = nextPage;

this.firstPage = firstPage;

this.lastPage = lastPage;

this.currentPage = currentPage;

this.totalPage = totalPage;

this.pageSize = pageSize;

this.totalData = totalData;

this.pageData = pageData;

}

public int getPrePage() {

return prePage;

}

public void setPrePage(int prePage) {

this.prePage = prePage;

}

public int getNextPage() {

return nextPage;

}

public void setNextPage(int nextPage) {

this.nextPage = nextPage;

}

public int getFirstPage() {

return firstPage;

}

public void setFirstPage(int firstPage) {

this.firstPage = firstPage;

}

public int getLastPage() {

return lastPage;

}

public void setLastPage(int lastPage) {

this.lastPage = lastPage;

}

public int getCurrentPage() {

return currentPage;

}

public void setCurrentPage(int currentPage) {

this.currentPage = currentPage;

}

public int getTotalPage() {

return totalPage;

}

public void setTotalPage(int totalPage) {

this.totalPage = totalPage;

}

public int getPageSize() {

return pageSize;

}

public void setPageSize(int pageSize) {

this.pageSize = pageSize;

}

public int getTotalData() {

return totalData;

}

public void setTotalData(int totalData) {

this.totalData = totalData;

}

public List getPageData() {

return pageData;

}

public void setPageData(List pageData) {

this.pageData = pageData;

}

* totalPage = (totalData+pageSize-1)/pageSize;

* */

}

2、在接口里面定义分页查询的方法

package com.ambow.dao;

import java.util.List;

import com.ambow.pojo.Good;

public interface IGoodDao {

//增删改查

public void add(Good good);

public void delete(Good good);

public void update(Good good);

public void query(Good good);

public Good queryOne(Good good);

public List queryMore(Good good);

public List queryByName(String name);//根据商家名称进行模糊查询

//条件分页查询

public List queryByName(String name,int currentPage,int pageSize);

//获取满足某个条件的总记录数

public int getTotalNum(String name);

}

3、在接口的实现类里面实现方法

package com.ambow.dao.impl;

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.ambow.dao.IGoodDao;

import com.ambow.pojo.Good;

import com.ambow.utils.DBUtils;

public class GoodDaoImpl implements IGoodDao {

DBUtils db = new DBUtils();

@Override

public void add(Good good) {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = "insert into good (name,address,tel,dishes) values (?,?,?,?)";

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, good.getName());

pstmt.setString(2, good.getAddress());

pstmt.setString(3, good.getTel());

pstmt.setString(4, good.getDishes());

int isOk = pstmt.executeUpdate();

//System.out.println("add-----"+isOk);

} catch (SQLException e) {

e.printStackTrace();

}

@Override

public void delete(Good good) {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = "delete from good where id = ?";

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, good.getId());

int isOk = pstmt.executeUpdate();

System.out.println("delete-----"+isOk);

} catch (SQLException e) {

e.printStackTrace();

}

@Override

public void update(Good good) {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = "update good set name=?,address=?,tel=?,dishes=? where id=?";

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, good.getName());

pstmt.setString(2, good.getAddress());

pstmt.setString(3, good.getTel());

pstmt.setString(4, good.getDishes());

pstmt.setInt(5,good.getId());

int isOk = pstmt.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

@Override

public void query(Good good) {

// TODO Auto-generated method stub

}

@Override

public Good queryOne(Good good) {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "select * from good where id = ?";

Good gd = null;

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, good.getId());

rs = pstmt.executeQuery();

while(rs.next()){

gd = new Good(rs.getInt(1),rs.getString(2),rs.getString(3),

rs.getString(4),rs.getString(5));

}

} catch (SQLException e) {

e.printStackTrace();

}

return gd;

}

@Override

public List queryMore(Good good) {

// TODO Auto-generated method stub

return null;

}

@Override

public List queryByName(String name) {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "select * from good where name like ?";

List goodList = new ArrayList();

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "%"+name+"%");

rs = pstmt.executeQuery();

while(rs.next()){

goodList.add(new Good(rs.getInt(1),rs.getString(2),rs.getString(3),

rs.getString(4),rs.getString(5)));

}

} catch (SQLException e) {

e.printStackTrace();

}

return goodList;

}

@Override

public List queryByName(String name, int currentPage, int pageSize) {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "select * from good where name like ? limit ?,?";

List goodList = new ArrayList();

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "%"+name+"%");

pstmt.setInt(2,(currentPage-1)*pageSize);

pstmt.setInt(3,pageSize);

rs = pstmt.executeQuery();

while(rs.next()){

goodList.add(new Good(rs.getInt(1),rs.getString(2),rs.getString(3),

rs.getString(4),rs.getString(5)));

}

} catch (SQLException e) {

e.printStackTrace();

}

return goodList;

}

@Override

public int getTotalNum(String name) {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "select count(id) from good where name like ?";

int total = 0;

try {

conn = DBUtils.getConnection();

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "%"+name+"%");

rs = pstmt.executeQuery();

while(rs.next()){

total = rs.getInt(1);

}

} catch (SQLException e) {

e.printStackTrace();

}

return total;

}

4.在Servlet里面调用实现类里面的分页查询方法

package com.ambow.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.ambow.dao.IGoodDao;

import com.ambow.dao.impl.GoodDaoImpl;

import com.ambow.pojo.Good;

import com.ambow.utils.PageBeanUtils;

@WebServlet("/QueryServlet")

public class QueryServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

private IGoodDao goodDao = new GoodDaoImpl();

public QueryServlet() {

super();

}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doPost(request,response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

request.setCharacterEncoding("utf-8");

response.setCharacterEncoding("utf-8");

String keywords = request.getParameter("kw");

String method = request.getParameter("method");

if("add".equals(method)){

String name = request.getParameter("name");

String address = request.getParameter("address");

String tel = request.getParameter("tel");

String dishes = request.getParameter("dishes");

Good good = new Good(0,name,address,tel,dishes);

goodDao.add(good);

//调用dao的查询方法，返回一个List

List goods = goodDao.queryByName(keywords);

request.setAttribute("list", goods);

request.getRequestDispatcher("goods.jsp").forward(request, response);

}else if("search".equals(method)){

if(null==keywords) {

keywords="";

}

int currentPage = 1;

try {

currentPage=Integer.parseInt(request.getParameter("curPage"));

if(currentPage<=0) {

currentPage = 1;

}

}catch(Exception e) {

currentPage = 1;

}

int pageSize=10;

int totalData = goodDao.getTotalNum(keywords);

int totalPage = (int)Math.ceil((double)totalData/pageSize);

if(currentPage>totalPage){

currentPage = totalPage;

}

List goods = goodDao.queryByName(keywords,currentPage,pageSize);

PageBeanUtils pg = new PageBeanUtils(currentPage,pageSize,totalData);

pg.setPageData(goods);

request.setAttribute("pg", pg);

request.getRequestDispatcher("good2.jsp").forward(request, response);

}else if("delete".equals(method)){

System.out.println(keywords);

//实现删除

String id = request.getParameter("id");

Good good = new Good();

good.setId(Integer.valueOf(id));

goodDao.delete(good);

//调用dao的查询方法，返回一个List

List goods = goodDao.queryByName(keywords);

request.setAttribute("list", goods);

request.getRequestDispatcher("goods.jsp").forward(request, response);

}else if("queryById".equals(method)){

//查询一个

String id = request.getParameter("id");

Good good = new Good();

good.setId(Integer.valueOf(id));

good = goodDao.queryOne(good);

//调用dao的查询方法，返回一个good

request.setAttribute("good", good);

request.getRequestDispatcher("update.jsp").forward(request, response);

}else if("update".equals(method)){

String id = request.getParameter("id");

String name = request.getParameter("name");

String address = request.getParameter("address");

String tel = request.getParameter("tel");

String dishes = request.getParameter("dishes");

Good good = new Good(Integer.valueOf(id),name,address,tel,dishes);

goodDao.update(good);

//调用dao的查询方法，返回一个List

List goods = goodDao.queryByName(keywords);

request.setAttribute("list", goods);

request.getRequestDispatcher("goods.jsp").forward(request, response);

}else{

//调用dao的查询方法，返回一个List

List goods = goodDao.queryByName(keywords);

request.setAttribute("list", goods);

request.getRequestDispatcher("goods.jsp").forward(request, response);

}

}http://

5.在JSP页面获取Servlet里面传过来的数据

<%@ page language="java" contentType="text/html; charset=utf-8"

pageEncoding="utf-8" import="java.util.ArrayList,com.ambow.pojo.Good,

com.ambow.pojo.User,com.ambow.utils.PageBeanUtils"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

添加商家

首页

尾页

当前第${pg.currentPage}页/共${pg.totalPage}页

每页显示${pg.pageSize}条

总结

c语言sscanf函数的用法是什么

245 2023-02-20

JavaWeb入门教程之分页查询功能的简单实现

c语言sscanf函数的用法是什么

r语言清空数组的方法是什么

linux cpu占用率如何看

推荐文章

api接口有哪几种分类及功能

什么是API接口?API接口简单介绍

短信API接口概述，短信API接口的优势

7款快递物流的物流查询API工具，物流快递查询API接口怎么对接？

企业四要素: 了解企业经营成功的关键

什么是语音验证码?,语音验证码平台有哪些

全国工商查询系统怎么查企业名录

哪些平台提供实名认证的接口？

PHP如何调用API接口?

如何使用百度天气预报API接口?

最近发表

热评文章

数据接口api（数据接口API开发平台）

数据开放接口api（数据服务api开发）

Python爬虫教程：爬取酷狗音乐（python爬取

hbuilder怎么更改字体大小和颜色

直播平台api接口 - 构建卓越的直播平台

实时股票数据api接口（股票实时行情api接口）