分页原理

实现前台页面的分页显示主要依靠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;
    }
}