Mysql分页查询

创建Page对象

class Page<T>{
    private int currentPage; // 当前页

    private int pageSize; // 每页条数

    private int totalCount; // 总条数

    private int totalPage; // 总页数

    private List<T> result; // 结果集

    public Page(int currentPage, int pageSize) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    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> getResult() {
        return result;
    }

    public void setResult(List<T> result) {
        this.result = result;
    }
}

实现分页查询

public class TestPage {

    /**
     * 分页查询
     * @param page
     */
    public void selectPage(Page page){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            connection = TestJdbcUtiles.getConnection();
            //查询当前页面数据
            ps = connection.prepareStatement("select * from users limit ?,?");
            ps.setInt(1,(page.getCurrentPage()-1)*page.getPageSize());
            ps.setInt(2,page.getPageSize());
            rs= ps.executeQuery();
            while (rs.next()){
                User user = new User(rs.getInt("userid"),rs.getString("username"),rs.getInt("userage"));
                list.add(user);
            }
            page.setResult(list);
            //查询总数居
            ps = connection.prepareStatement("select count(*) from users");
            ResultSet resultSet = ps.executeQuery();
            while(resultSet.next()){
                //此处只有一个数据，就是总条数
                int totalCount = resultSet.getInt(1);
                page.setTotalCount(totalCount);
                //此处需要时double类型才能向上取整
                int totalPage = (int)Math.ceil(1.0 * totalCount / page.getPageSize());
                page.setTotalPage(totalPage);
            }
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            TestJdbcUtiles.closeResource(rs,ps,connection);
        }
    }
}

测试分页查询

class Test5{
    /**
     * 测试分页查询
     * @param args
     */
    public static void main(String[] args) {
        //获取数据
        TestPage testPage = new TestPage();
        Page<User> page = new Page<>(2,2);
        testPage.selectPage(page);
        List<User> result = page.getResult();
        //显示分页数据信息
        System.out.println("总条数："+page.getTotalCount());
        System.out.println("总页数："+page.getTotalPage());
        System.out.println("当前页："+page.getCurrentPage());
        System.out.println("当前页数据数："+page.getPageSize());
        //查询数据
        for(User u : result){
            System.out.println(u.toString());
        }
    }
}