Java Web 的分页查询

在开发过程中，我们经常会从数据库中查询数据，然后在客户端显示出来。当数据少时，可以在一个页面显示。当我们查询几百条以上数据，直接显示在一个页面上，不仅浏览不方便，查询效率也会受到影响，这是，我们就可以使用分页查询来解决这个问题。

第一步

dao层

import java.util.List;


public interface IBandDao {
    public boolean insert(Band band);
    public boolean delete(Integer id);
    public boolean update(Band band);
    public Band findById(int id);
    public List<Band> findAll();
    public List<Band> find(Band band);

    /**
     * 分页查询数据
     * @param page 页号
     * @param pageSize 页的大小
     * @param band  查询条件
     * @return
     */
    public Pager paginate(int page, int pageSize, Band band);
}

第二步

工具类

import java.util.List;

public class Pager {
    private int page = 1;
    private int pageSize = 10;
    private int rows;
    private int pages;
    private boolean isFirst = true;
    private boolean isLast = false;
    private List<Object> data;

    public Pager() {
    }

    public Pager(int page, int pageSize) {
        this.page = page;
        this.pageSize = pageSize;
    }

    public int getPage() {
        if(page > pages){
            return pages;
        }else {
            return page;
        }
    }

    public void setPage(int page) {
        if(page < 1){
            page = 1;
        }
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        if(pageSize<1){
            pageSize = 10;
        }
        this.pageSize = pageSize;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
        getPages();
    }

    public int getPages() {
        pages = (rows / pageSize);
        if(pages*pageSize < rows){
            pages = pages + 1;
        }
        return pages;
    }

    public boolean isFirst() {
        if(page == 1){
            return true;
        }else{
            return false;
        }
    }

    public boolean isLast() {
        if(page == pages){
            return true;
        }else {
            return false;
        }
    }

    public List<Object> getData() {
        return data;
    }

    public void setData(List<Object> data) {
        this.data = data;
    }
}

测试

public class TsetPager {
    @Test
    public void testPager() {
        Pager pager = new Pager();
        pager.setPage(10);
        pager.setRows(11);
        System.out.println("page=" + pager.getPage());
        System.out.println("pageSize=" + pager.getPageSize());
        System.out.println("pages=" + pager.getPages());
        System.out.println("first=" + pager.isFirst());
        System.out.println("last=" + pager.isLast());
    }
}

第三步

在功能里面添加分页查询功能

 /**
     * 分页获取满足条件的数据
     *
     * @param page     页号
     * @param pageSize 页的大小
     * @param band     查询条件
     * @return
     */
    @Override
    public Pager paginate(int page, int pageSize, Band band) {
        Pager pager = new Pager(page, pageSize);
        String sql = "SELECT COUNT(*) AS total FROM band ";
        StringBuilder where = new StringBuilder(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        if (band != null) {
            if (band.getName() != null && !"".equals(band.getName())) {
                where.append(" AND name LIKE ? ");
                params.add("%" + band.getName() + "%");
            }
            if (band.getRemarks() != null && !"".equals(band.getRemarks())) {
                where.append(" AND remark LIKE ? ");
                params.add("%" + band.getRemarks() + "%");
            }
        }
        try {
            Connection conn = MySqlDbUtil.getConnect();
            PreparedStatement pst = conn.prepareStatement(sql + where.toString());
            for (int i = 0; i < params.size(); i++) {
                pst.setObject(i + 1, params.get(i));
            }
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                pager.setRows(rs.getInt("total"));
            }
            rs.close();
            pst.close();
            sql = "SELECT * FROM band";
            pst = conn.prepareStatement(sql + where.toString() + " LIMIT ?, ?");
            int index = 0;
            for (int i = 0; i < params.size(); i++) {
                ++index;
                pst.setObject(i + 1, params.get(i));
            }
            pst.setObject(++index, (pager.getPage() - 1) * pager.getPageSize());
            pst.setInt(++index, pager.getPageSize());
            rs = pst.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()) {
                band = new Band();
                band.setId(rs.getInt("id"));
                band.setName(rs.getString("name"));
                band.setRemarks(rs.getString("remark"));
                data.add(band);
            }
            pager.setData(data);
            rs.close();
            pst.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pager;
    }

测试

public class TestPage {
    @Test
    public void testPaginate(){
        IBandDao dao = new BandDao();
        Band band = new Band();
        band.setRemarks("备注");
        Pager pager = dao.paginate(1,10,band);
        System.out.println(JSONObject.toJSONString(pager));
    }
}