软件项目开发,特别是web项目,页面的分页是经常使用的功能,几乎每个项目都要用到,开发人员需要很熟悉它的开发原理,能够把它做得组件化包装,得到更简化的包装和重用,就能大大提高开发效率。下面介绍一下。
我们先来看一下一个页面 分页的画面,如下:
我们通过前端页面看到,分页有几个要素:pageSize页面大小、currentPage当前页、totalPages总页数、totalCount总记录数。其中pageSize页面大小可以指定默认值,比如每页默认10条;currentPage当前页是首次显示为第一页,以后操作分页时值为当前所处的页数;totalCount总记录数是根据后台查询取得的总记录数;totalPages总页数可以根据上述参数计算得到。如下是计算逻辑:
int totalPages; // 总页数 int totalCount; // 总数据数
if ((totalCount % pageSize) == 0) {//求余 totalPages = totalCount / pageSize; } else { totalPages = totalCount / pageSize + 1; }
分页机制一般是1)前端页面传递分页参数(pageSize页面大小、currentPage当前页)请求给后端服务器;2)后端查询数据库取得结果集返回给前端页面展示数据记录,查询完后要更新参数currentPage当前页,以便下次查询时传递正确参数。这里第1)步包装请求参数,传递分页请求实现上主要是使用js或ajax请求;第2)步后端查询数据库取得结果集,谈下这一点,在架构层次上设计有许多方案,一般都使用MVC架构,View视图层做前端页面展现,Control控制层前端与后端请求响应传递,调用实际业务层逻辑处理,这些没什么分别,所以主要是在Biz业务层、Dao资源层(或叫持久化层,负责从数据库等资源提取数据给业务层)。主要程序流程应该是这样的:View视图层传递分页参数给Control控制层,分页参数可以包装成对象如Page,传递给Biz业务层,再继续传递给Dao资源层,Dao层使用sql查询数据库取得结果集。这里推荐一个比较好的设计思路,Dao层方法包含分页Page参数、sql语句参数,还可以做得通用些,传递的Page参数可以用AOP拦截处理,将Page参数与sql语句经过合理的拼装,成一个分页的sql语句。这样我们就可以只关心逻辑处理的sql语句,不用关心分页处理了。例子代码如下
Control控制层代码:
public class SmsEntryCtrl{
/** * 查询短信信息列表 */ public ModelAndView searchSmsEntryList(HttpServletRequest request, HttpServletResponse response) throws Exception { Map<String, Object> content = new HashMap<String, Object>(); Integer pageSize = CustomerConstant.PAGE_SIZE; String limit = request.getParameter("limit"); // 每页显示条数 String start = request.getParameter("start"); Integer currentPage = 1; if (StringUtils.isNotBlank(limit)) { pageSize = Integer.parseInt(limit); } if (StringUtils.isNotBlank(start)) { currentPage = Integer.parseInt(start) / pageSize + 1; } Pagination page = new Pagination();//这里使用1个实体对象包装分页功能的参数,下面会介绍 page.setPageSize(pageSize); page.setCurrentPage(currentPage); // 取得查询条件 SmsEntryCondition smsEntryCond = fillSmsEntryCondition(request); List<SmsEntry> smsEntryList = smsEntryBiz.searchSmsEntryPage(page, smsEntryCond); content.put("smsEntryList", smsEntryList); content.put("page", page); return new ModelAndView(showSmsEntryListView, content); }
}
分页功能实体对象:
public class Pagination implements java.io.Serializable { private static final long serialVersionUID = 1L; private int DEFAULT_PAGE_SIZE = 10; private int DEFAULT_CURRENTPAGE = 1; private int pageSize; // 每页默认10条数据 private int currentPage; // 当前页 private int totalPages; // 总页数 private int totalCount; // 总数据数
public Pagination(int totalCount, int pageSize) { this.init(totalCount, pageSize); }
public Pagination() { this.pageSize = DEFAULT_PAGE_SIZE; this.currentPage = DEFAULT_CURRENTPAGE; }
//省略get、set方法
}
Biz业务层代码:
public class SmsEntryBizImpl{
。。。
/** * 分页查询短信记录 */ @Override public List<SmsEntry> searchSmsEntryPage(Pagination page, SmsEntryCondition smsEntryCond) throws BizException { List<SmsEntry> smsEntryList = smsEntryDao.findSmsEntryPage(page, smsEntryCond); return smsEntryList; }
Dao持久层(本例采用Mybatis3.0)
/** * 短信实体Dao接口 */ public interface SmsEntryDao{
/** * 查询短信记录条目 */ public List<SmsEntry> findSmsEntryPage(Pagination page, @Param(value = "smsEntryCond") SmsEntryCondition smsEntryCond);}
}
mybatis的mapper文件的sql语句定义
<!-- 分页查询短信记录条目yandk --> <select id="findSmsEntryPage" parameterType="cn.zwork.crm.sms.model.SmsInfoCondition" resultMap="sms.smsEntryResultMap"><!-- ,sr.RECEIVE --> select distinct s.ID,sr.SEND,s.SEND_DATE,s.CONTENT,s.STATUS from SMS s,SMS_RELATION sr LEFT JOIN UC_EMPLOYEE AS ucEmployee ON sr.SEND = ucEmployee.ID <include refid="SmsEntryCondition"/> order by s.SEND_DATE desc </select> <!-- 查询条件 --> <sql id="SmsEntryCondition"> <where> s.ID=sr.SMS_ID <if test="smsEntryCond.content!= null and smsEntryCond.content!= '' "> AND s.CONTENT LIKE CONCAT('%',#{smsEntryCond.content},'%' ) </if> <if test="smsEntryCond.status == 'NOT_SEND'"> AND s.STATUS ='NOT_SEND' </if> <if test="smsEntryCond.status == 'SEND'"> AND s.STATUS ='SEND' </if> <if test="smsEntryCond.startSendDate != null"> AND s.SEND_DATE >= #{smsEntryCond.startSendDate} </if> <if test="smsEntryCond.endSendDate != null"> <![CDATA[ AND s.SEND_DATE <= #{smsEntryCond.endSendDate} ]]> </if> <if test="smsEntryCond.userIds != null and smsEntryCond.userIds.size()>0"> <![CDATA[ AND sr.SEND IN ]]> <foreach collection="smsEntryCond.userIds" item="userId" open="(" separator="," close=")"> '${userId}' </foreach> </if> </where> </sql>
分页拦截器
它的作用是采用AOP方式,对DAO层的查询方法拦截,需要分页时,使产生的对数据库查询的sql语句,动态加上分页相关参数,产生分页结果相关数据信息。传递到前端表现层,进行数据展现。代码如下:
public class PaginationInterceptor implements Interceptor { public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = invocation.getArgs()[1]; BoundSql boundSql = mappedStatement.getBoundSql(parameter); if (boundSql == null || StringUtils.isEmpty(boundSql.getSql())) return null;
Object parameterObject = boundSql.getParameterObject();
Pagination pagination = null; // 通过参数传递Pagination if (parameterObject != null) { pagination = (Pagination) getPage(parameterObject); }
if (pagination != null) { String originalSql = boundSql.getSql().trim(); int totalCount = pagination.getTotalCount(); // 得到总记录数 if (totalCount <= 0) {
StringBuffer countSql = new StringBuffer(); countSql.append("select count(1) from (").append(originalSql).append(") t"); Connection connection = mappedStatement.getConfiguration().getEnvironment().getDataSource() .getConnection(); PreparedStatement countStmt = connection.prepareStatement(countSql.toString()); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql.toString(), boundSql .getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBS, parameterObject); ResultSet rs = countStmt.executeQuery(); if (rs.next()) { totalCount = rs.getInt(1); } rs.close(); countStmt.close(); connection.close(); } // 分页计算 pagination.init(totalCount, pagination.getPageSize(), pagination.getCurrentPage()); RowBounds rowBounds = (RowBounds) invocation.getArgs()[2]; if (rowBounds == null || rowBounds == RowBounds.DEFAULT) { rowBounds = new RowBounds(pagination.getPageSize() * (pagination.getCurrentPage() - 1), pagination.getPageSize()); }
// 分页查询 本地化对象 修改数据库注意修改实现 String pagesql = getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()); invocation.getArgs()[2] = new RowBounds(RowBounds.NO_ROW_OFFSET, RowBounds.NO_ROW_LIMIT); BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), pagesql, boundSql .getParameterMappings(), boundSql.getParameterObject()); MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql)); invocation.getArgs()[0] = newMs; } return invocation.proceed();
}
public static class BoundSqlSqlSource implements SqlSource { BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; }
public BoundSql getBoundSql(Object parameterObject) { return boundSql; } }
public Object plugin(Object arg0) { return Plugin.wrap(arg0, this); }
.....
}
可以将上述拦截类配置在spring配置文件中,就可以进行拦截处理了。
参考:http://www.cnblogs.com/jcli/archive/2011/08/09/2132222.html mybatis高级应用系列一:分页功能