Spring JDBC Pagination Tutorial
来源:互联网 发布:npm i node sass d 编辑:程序博客网 时间:2024/06/15 12:39
原文链接:http://www.codefutures.com/tutorials/spring-pagination/
Example
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
- Mybatis plugin pagination + Spring MVC
- Spring Tutorial
- Spring Tutorial
- Spring Tutorial
- Spring Tutorial
- Java JDBC Tutorial
- JAVA JDBC Tutorial(1)
- Java Tutorial JDBC
- Pagination
- PAGINATION
- Pagination
- Spring Data JPA: Pagination And Sort
- easyui pagination spring boot 分页程序实现
- 【jdbc】oracle java tutorial—JDBC Basics
- Spring MVC Framework Tutorial
- Spring Batch Tutorial
- Spring mvc框架tutorial
- xcode设置字体大小
- Hadoop1.0与Hadop2.0架构比较
- 算法之旅,直奔<algorithm>之十九 includes
- 读取沙河文件
- 九阳豆浆机各型号字母数字代表的意思
- Spring JDBC Pagination Tutorial
- 三十个应该牢牢记住的CSS选择器(上)/The 30 CSS Selectors you Must Memorize
- 黑马程序员—字符串的一些应用
- Linux常用命令大全(CentOS,Ret Hat等等)
- Hadoop 新 MapReduce 框架 Yarn 详解
- wince 5.0 Platform Builder 5.0 下载地址
- 手机铃声背后,有没有手机社交APP商机?【草稿】
- We Recommend a Singular Value Decomposition
- Java 初始化与清理