JDBC分页管理（MySQL）
分页原理
实现前台页面的分页显示主要依靠MySQL中limit限制，我们可以在select语句中加入limit begin, count设置查询的起始位置和总共需要查询的记录数。由此我们可以定义一个PageBean的类用于记录分页的信息
package com.shop.domain;import java.util.List;public class PageBean<T> { private int page;// 当前页数 private int limit;// 每页显示记录数 private int totalCount;// 总记录数 private int totalPage;// 总页数 private List<T> list;// 查询结果集 public PageBean() { } public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getLimit() { return limit; } public void setLimit(int limit) { this.limit = limit; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } 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; } @Override public String toString() { return "PageBean{" "page=" page ", limit=" limit ", totalCount=" totalCount ", totalPage=" totalPage ", list=" list '}'; }}结果集使用泛型主要是为了能够通用所有的类型
分页实现
package com.shop.service.impl;import com.shop.dao.ProductDao;import com.shop.dao.impl.ProductDaoImpl;import com.shop.domain.PageBean;import com.shop.domain.Product;import com.shop.service.ProductService;import java.util.List;public class ProductServiceImpl implements ProductService { ProductDao productDao = new ProductDaoImpl();@Override public PageBean<Product> findByPage(int page) { PageBean<Product> pageBean = new PageBean<>();// 设置当前页数 pageBean.setPage(page);// 设置每页显示记录数 int limit = 6; pageBean.setLimit(limit);// 调用Dao层的方法查询总记录数 int totalCount = productDao.findCount(); pageBean.setTotalCount(totalCount);// 设置总页数 int totalPage; if (totalCount % limit == 0) totalPage = totalCount / limit; else totalPage = totalCount / limit 1; pageBean.setTotalPage(totalPage);// 查询结果集的起始位置可以推倒公式为(page - 1) * limit int begin = (page - 1) * limit; List<Product> list = productDao.findByPage(begin, limit); pageBean.setList(list); return pageBean; }}package com.shop.dao.impl;import com.shop.dao.ProductDao;import com.shop.domain.Category;import com.shop.domain.Product;import com.shop.utils.JDBCUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class ProductDaoImpl implements ProductDao { @Override public int findCount() { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; Long count = 0L; try { connection = JDBCUtils.getConnection(); String sql = "select count(*) count from product"; statement = connection.prepareStatement(sql); resultSet = statement.executeQuery(); if (resultSet.next()) { count = resultSet.getLong("count"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.release(resultSet, statement, connection); } return count.intValue(); } @Override public List<Product> findByPage(int begin, int limit) { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; List<Product> list = new ArrayList<>(); try { connection = JDBCUtils.getConnection(); String sql = "select * from product limit ?, ?"; statement = connection.prepareStatement(sql); statement.setInt(1, begin); statement.setInt(2, limit); resultSet = statement.executeQuery(); while (resultSet.next()) { Product product = new Product(); product.setPid(resultSet.getInt("pid")); product.setPname(resultSet.getString("pname")); product.setAuthor(resultSet.getString("author")); product.setPrice(resultSet.getDouble("price")); product.setDescription(resultSet.getString("description")); product.setFilename(resultSet.getString("filename")); product.setPath(resultSet.getString("path")); list.add(product); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.release(resultSet, statement, connection); } return list; }} 赞 (0)