jqGrid分页技术学习（七）spring jdbc 分页查询DAO

分页查询的原理，如下图：

 

 

TablePagingController解析查询的代码：

...... String strPage = request.getParameter("page"); int page = Integer.parseInt(strPage); logger.info("Res-Host request page=[" + page + "]"); String strRows = request.getParameter("rows"); int rows = Integer.parseInt(strRows); logger.info("Res-Host request rows=[" + rows + "]"); String sidx = request.getParameter("sidx"); // sidx:amount String sord = request.getParameter("sord"); // sord:asc, sord:desc String strSearch = request.getParameter("_search"); boolean search = Boolean.parseBoolean(strSearch); ...... } else {// _search:false final JQGridPTO<InvHeader> pto = this.getDao().queryByPaging( page, rows, sidx, sord); model.put("JSON_OBJECT", pto); } ......

 

TablePagingDao的分页查询操作：

...... /** * 分页查询. * * @param pageNo * @param pageSize * @param sidx * @param sord * @return */ public JQGridPTO<InvHeader> queryByPaging(final int pageNo, final int pageSize, String sidx, String sord) { JdbcTemplate jdbcTemplate = this.getJdbcTemplate(); PaginationHelper<InvHeader> ph = new PaginationHelper<InvHeader>(); return ph .fetchPage( jdbcTemplate, "SELECT count(*) FROM invheader", "SELECT id, invdate, client_id, amount, tax, total, closed, ship_via, note FROM invheader ORDER BY " + sidx + " " + sord, new Object[] {}, pageNo, pageSize, new ParameterizedRowMapper<InvHeader>() { public InvHeader mapRow(ResultSet rs, int i) throws SQLException { InvHeader data = new InvHeader(); data.setId(rs.getInt(1)); data.setInvdate(rs.getString(2)); data.setClient_id(rs.getInt(3)); data.setAmount(rs.getFloat(4)); data.setTax(rs.getFloat(5)); data.setTotal(rs.getFloat(6)); data.setClosed(rs.getString(7)); data.setShip_via(rs.getString(8)); data.setNote(rs.getString(9)); return data; } }); } ......

 

需要增加一个分页传输对象JQGridPTO：

/** * JQGrid JSON Paging Transport Object * * @author Thomas.H.Zhang * @param <E> */ public class JQGridPTO<E> implements Serializable { private int page; // current page of the query private int total; // total pages for the query private int records; // total number of records for the query // an array that contains the actual data private List<E> rows = new ArrayList<E>(); public int getPage() { return page; } public void setPage(int val) { this.page = val; } public int getTotal() { return total; } public void setTotal(int val) { this.total = val; } public void setRecords(int val) { records = val; } public int getRecords() { return records; } public List<E> getRows() { return rows; } public void setRows(List<E> items) { this.rows = items; } }

 

还有增加一个分页帮助类：

public class PaginationHelper<E> { public JQGridPTO<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 JQGridPTO<E> pto = new JQGridPTO<E>(); pto.setPage(pageNo); pto.setTotal(pageCount); pto.setRecords(rowCount); // fetch a single page of results final int startRow = (pageNo - 1) * pageSize; jt.query(sqlFetchRows, args, new ResultSetExtractor() { @Override public Object extractData(java.sql.ResultSet rs) throws SQLException, DataAccessException { final List pageItems = pto.getRows(); int currentRow = 0; while (rs.next() && currentRow < startRow + pageSize) { if (currentRow >= startRow) { pageItems.add(rowMapper.mapRow(rs, currentRow)); } currentRow++; } return pto; } }); return pto; } }