星空网 > 软件开发 > Java

Spring JDBC Pagination Tutorial

This tutorial uses the use code of listing a number of companies. This might be a public service listing records of public companies.

Using Spring JDBC without Pagination

First of all, let's look at a standard technique using Spring JDBC to return a list of companies in a single result set.

public List<Company> getCompanies() throws SQLException {    return jdbcTemplate.query(            "SELECT id, name FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",            new ParameterizedRowMapper<Company>() {                public Company mapRow(ResultSet rs, int i) throws SQLException {                    return new Company(                            rs.getInt(1),                            rs.getString(2)                    );                }            },            userId    );}

 

Using Spring JDBC with Pagination

Here's a new version of the method which uses a PaginationHelper class, which is shown further on in this tutorial. As you can see, the usage is very similar to standard Spring JDBC. The most obvious difference is that we now have two SQL statements instead of one. This is necessary if we want to be able to show the users how many pages of data there are but it is an extra performance hit so there is room for refinement in this approach by perhaps caching the number of pages. The code uses the standard ParameterizedRowMapper to minimize impact on existing code.

public Page<Company> getCompanies(final int pageNo, final int pageSize) throws SQLException {        PaginationHelper<Company> ph = new PaginationHelper<Company>();        return ph.fetchPage(                jdbcTemplate,                "SELECT count(*) FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",                "SELECT id, name FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",                new Object[]{userId},                pageNo,                pageSize,                new ParameterizedRowMapper<Company>() {                    public Company mapRow(ResultSet rs, int i) throws SQLException {                        return new Company(                            rs.getInt(1),                            rs.getString(2)                        );                    }                }        );    }

The Page class

The Page class is a very simple template class that contains a list of items, the page number, and the number of pages that are available.

 

public class Page<E> {        private int pageNumber;        private int pagesAvailable;        private List<E> pageItems = new ArrayList<E>();        public void setPageNumber(int pageNumber) {            this.pageNumber = pageNumber;        }        public void setPagesAvailable(int pagesAvailable) {            this.pagesAvailable = pagesAvailable;        }        public void setPageItems(List<E> pageItems) {            this.pageItems = pageItems;        }        public int getPageNumber() {            return pageNumber;        }        public int getPagesAvailable() {            return pagesAvailable;        }

     public List<E> getPageItems() {
            return pageItems;        }    }  

Pagination Helper

Here's the source code for the PaginationHelper class. This is actually very simple. The first SQL query is executed to determine how many rows of data are available. This allows the number of pages to be calculated. The second query is then executed using the JdbcTemplate query method that accepts a Spring ResultSetExtractor. The implementation of this ResultSetExtractor processes the result set and delegates to the supplied ParameterizedRowMapper for those rows that should be returned as part of the current page of data.

public class PaginationHelper<E> {    public Page<E> fetchPage(            final JdbcTemplate jt,            final String sqlCountRows,            final String sqlFetchRows,            final Object args[],            final int pageNo,            final int pageSize,            final ParameterizedRowMapper<E> rowMapper) {        // determine how many rows are available        final int rowCount = jt.queryForInt(sqlCountRows, args);        // calculate the number of pages        int pageCount = rowCount / pageSize;        if (rowCount > pageSize * pageCount) {            pageCount++;        }        // create the page object        final Page<E> page = new Page<E>();        page.setPageNumber(pageNo);        page.setPagesAvailable(pageCount);
     // fetch a single page of results        final int startRow = (pageNo - 1) * pageSize;        jt.query(                sqlFetchRows,                args,                new ResultSetExtractor() {                    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {                        final List pageItems = page.getPageItems();                        int currentRow = 0;                        while (rs.next() && currentRow < startRow + pageSize) {                            if (currentRow >= startRow) {                                pageItems.add(rowMapper.mapRow(rs, currentRow));                            }                            currentRow++;                        }                        return page;                    }                });        return page;    }
}
 
 



原标题:Spring JDBC Pagination Tutorial

关键词:Spring

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

揭秘|关于亚马逊A9算法的几大谜团:https://www.ikjzd.com/articles/116522
亚马逊:不上传法国增值税税号或被禁止销售:https://www.ikjzd.com/articles/116523
如何知道自己是绩效问题被封号?:https://www.ikjzd.com/articles/116525
Amazon亚马逊官方绩效团队各类邮箱地址大全汇总:https://www.ikjzd.com/articles/116526
中国呼吁WTO成员国尽快解除不必要的限制措施!:https://www.ikjzd.com/articles/116527
顺丰加快国际航线复航,新增流向,助力跨境企业复产:https://www.ikjzd.com/articles/116528
小米SU7的不甘心全写进了 “周边”的商品链接里 :https://www.kjdsnews.com/a/1836549.html
10天涨粉300+万 “王婆说媒”凭什么一夜爆红? :https://www.kjdsnews.com/a/1836550.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流