一、实现分页查询的核心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 currPage = 1;//默认第一页
if(strPage != null) {
currPage = Integer.parseInt(strPage);
}
PageBean pb = new PageBean(currPage,pageSize,record);
//查询某一页的结果集
List 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。作用是：获取结果集。
伤心森林订单留言系统
下载
功能简介：1.用户留言功能2.用户定货功能3.定制货货功能4.定制网页样式和其实设置(比如主页)5.强大的管理功能(现在的程序都是管理功能大于应用功能:)6.管理功能支持查看订货单，留言，分页，删除等功能管理页面：login.asp管理密码：admin
package com.util; import java.util.List; public class PageBean{ private int currentPage;//当前页码 private int pageIndex;//数据起始索引 private int pageSize;//每页条数 private int record;//总记录数 private int totalPage;//总页数 private List 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 getList() { return list; } public void setList(List list) { this.list = list; } }
三、运行结果
（1）首页：
（2）中间页：
（3）尾页：
