在做项目时,好多地方都会用到分页,以前只是把别人封装号的分页拿过来用,从来没有自己认真研究过,今天我在项目里要自己开始写了分页了,在网上查了一下资料,再结合自己的项目,谢了一个简单的分页,主要有三块核心内容,page实体类,mybatis拦截器,前台实现分页:
Page实体类代码如下:
package com.hrtel.framework.util;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.codehaus.jackson.map.ObjectMapper;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
public class Page {
private static final Logger logger = Logger.getLogger(Page.class);
private static ObjectMapper mapper = new ObjectMapper();
private static String DEFAULT_PAGESIZE = "10";
private int pageNo; // 当前页码
private int pageSize; // 每页行数
private int totalRecord; // 总记录数
private int totalPage; // 总页数
private Map<String, String> params; // 查询条件
private Map<String, List<String>> paramLists; // 数组查询条件
private String searchUrl; // Url地址
public Page() {
pageNo = 1;
pageSize = Integer.valueOf(DEFAULT_PAGESIZE);
totalRecord = 0;
totalPage = 0;
params = Maps.newHashMap();
paramLists = Maps.newHashMap();
searchUrl = "";
}
public static Page newBuilder(int pageNo, int pageSize, String url) {
Page page = new Page();
page.setPageNo(pageNo);
page.setPageSize(pageSize);
page.setSearchUrl(url);
return page;
}
/**
* 查询条件转json
*
* @return
*/
public String getParaJson() {
Map<String, Object> map = Maps.newHashMap();
for (String key : params.keySet()) {
if (params.get(key) != null) {
map.put(key, params.get(key));
}
}
String json = "";
try {
json = mapper.writeValueAsString(map);
} catch (Exception e) {
logger.error("转换JSON失败", e);
}
return json;
}
/**
* 数组查询条件转JSON
*/
public String getParaListJson() {
Map<String, Object> map = Maps.newHashMap();
for (String key : paramLists.keySet()) {
List<String> lists = paramLists.get(key);
if (lists != null && lists.size() > 0) {
map.put(key, lists);
}
}
String json = "";
try {
json = mapper.writeValueAsString(map);
} catch (Exception e) {
logger.error("转换JSON失败", e);
}
return json;
}
/**
* 总件数变化时,更新总页数并计算显示样式
*/
public void refreshPage() {
// 总页数计算
totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : (totalRecord / pageSize + 1);
// 防止超出最末页(浏览途中数据被删除的情况)
if (pageNo > totalPage && totalPage != 0) {
pageNo = totalPage;
}
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public Map<String, String> getParams() {
return params;
}
public void setParams(Map<String, String> params) {
this.params = params;
}
public Map<String, List<String>> getParamLists() {
return paramLists;
}
public void setParamLists(Map<String, List<String>> paramLists) {
this.paramLists = paramLists;
}
public String getSearchUrl() {
return searchUrl;
}
public void setSearchUrl(String searchUrl) {
this.searchUrl = searchUrl;
}
}
哪个类需要用到分页,就要在它的实体类里面加上Page属性:
private Page page; //分页
接下来是mybatis的拦截器:
package com.hrtel.framework.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import org.apache.commons.jxpath.JXPathContext;
import org.apache.commons.jxpath.JXPathNotFoundException;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.MappedStatement.Builder;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.log4j.Logger;
@Intercepts({@Signature(type=Executor.class,method="query",args={ MappedStatement.class,
Object.class, RowBounds.class, ResultHandler.class })})
public class PageInterceptor implements Interceptor{
private static final Logger logger = Logger.getLogger(PageInterceptor.class);
@Override
public Object intercept(Invocation invocation) throws Throwable {
logger.info("*****into method intercept for mybatis plugin*****");
//当前环境 MappedStatement,BoundSql,及sql取得
MappedStatement mappedStatement=(MappedStatement)invocation.getArgs()[0];
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
String originalSql = boundSql.getSql().trim();
Object parameterObject = boundSql.getParameterObject();
//Page对象获取,“信使”到达拦截器!
Page page = searchPageWithXpath(boundSql.getParameterObject(),".","page","*/page");
if (null != page){
//Page对象存在的场合,开始分页处理
String countSql = getCountSql(originalSql);
Connection connection=mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
PreparedStatement countStmt = connection.prepareStatement(countSql);
BoundSql countBS = copyFromBoundSql(mappedStatement, boundSql, countSql);
DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBS);
parameterHandler.setParameters(countStmt);
ResultSet rs = countStmt.executeQuery();
int totpage=0;
if (rs.next()) {
totpage = rs.getInt(1);
}
rs.close();
countStmt.close();
connection.close();
//分页计算
page.setTotalRecord(totpage);
page.refreshPage();
//对原始Sql追加limit
int offset = (page.getPageNo() - 1) * page.getPageSize();
StringBuffer sb = new StringBuffer();
sb.append(originalSql).append(" limit ").append(offset).append(",").append(page.getPageSize());
BoundSql newBoundSql = copyFromBoundSql(mappedStatement, boundSql, sb.toString());
MappedStatement newMs = copyFromMappedStatement(mappedStatement,new BoundSqlSqlSource(newBoundSql));
invocation.getArgs()[0]= newMs;
}
logger.info("*******method intercept end**********");
return invocation.proceed();
}
@Override
public Object plugin(Object arg0) {
return Plugin.wrap(arg0, this);
}
@Override
public void setProperties(Properties arg0) {
}
/**
* 根据给定的xpath查询Page对象
*/
private Page searchPageWithXpath(Object o,String... xpaths) {
JXPathContext context = JXPathContext.newContext(o);
Object result;
for(String xpath : xpaths){
try {
result = context.selectSingleNode(xpath);
} catch (JXPathNotFoundException e) {
continue;
}
if ( result instanceof Page ){
return (Page)result;
}
}
return null;
}
/**
* 根据原Sql语句获取对应的查询总记录数的Sql语句
*/
private String getCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ") aliasForPage";
}
/**
* 复制BoundSql对象
*/
private BoundSql copyFromBoundSql(MappedStatement ms, BoundSql boundSql, String sql) {
BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),sql, boundSql.getParameterMappings(), boundSql.getParameterObject());
for (ParameterMapping mapping : boundSql.getParameterMappings()) {
String prop = mapping.getProperty();
if (boundSql.hasAdditionalParameter(prop)) {
newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}
return newBoundSql;
}
public class BoundSqlSqlSource implements SqlSource {
BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
/**
* 复制MappedStatement对象
*/
private MappedStatement copyFromMappedStatement(MappedStatement ms,SqlSource newSqlSource) {
Builder builder = new Builder(ms.getConfiguration(),ms.getId(),newSqlSource,ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
String [] s = ms.getKeyProperties();
if (null == s){
builder.keyProperty(null);
}else{
builder.keyProperty(s[0]);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
}
写了拦截器之后,需要在xml里面注册拦截器,我用的框架是springMVC+mybatis结合的,所以我就在spring-mybatis.xml中注册自己的拦截器:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描mapping.xml文件 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
<!-- 配置Mybatis的插件plugin -->
<property name="plugins">
<array>
<bean class="com.hrtel.framework.util.PageInterceptor">
<property name="properties">
<value>
property-key=property-value
</value>
</property>
</bean>
</array>
</property>
</bean>
写完上面的之后,就需要写前台的js接收了:
function initPage(){
var pageHtml = "";
var pageNo = page.pageNo;
var pageSize = page.pageSize;
var totalPage = page.totalPage;
var totalRecord = page.totalRecord;
if (pageNo != 1){
pageHtml += "<a href='#' οnclick='onclickMenu(1)' style='text-decoration: none'><b style='font-size: 12'>第一页</b></a> ";
pageHtml += "<a href='#' οnclick='onclickMenu("+(pageNo-1)+")' style='text-decoration: none'><b style='font-size: 12'>上一页</b></a> ";
}
if (pageNo != totalPage){
pageHtml += "<a href='#' οnclick='onclickMenu("+(parseInt(pageNo)+1)+")' style='text-decoration: none'><b style='font-size: 12'>下一页</b></a> ";
pageHtml += "<a href='#' οnclick='onclickMenu("+(parseInt(totalPage))+")' style='text-decoration: none'><b style='font-size: 12'>最后一页</b></a> ";
}
pageHtml += "页数:<label id='curpage' style='color: red; font-size: 13'>"+pageNo+"</label>/<label id='wholepages' style='color: red; font-size: 13'>"+totalPage+"</label> ";
pageHtml += "<input id='ppage' type='text' size='5'/><a οnclick='gotoPage()'>跳转</a> ";
pageHtml += "<label>总条数:"+totalRecord+"</label>";
$('#datePage').html("<tr><td>"+pageHtml+"</td></tr>");
}
每次点击分页里面的按钮时,都会调用onclickMenu()方法,这个方法主要封装的是一个ajax,前台向后台发送请求:
function onclickMenu(pageNo){
var startTime = $('#startTime').datebox('getValue');
var endTime = $('#endTime').datebox('getValue');
var citys_name = $('#citys_name').val();
var ci = $('#ci').val();
var consumptionFlow_size = $('#consumptionFlow_size').val();
var IMSI = $('IMSI').val();
var MSISDN = $('MSISDN').val();
var resultConlection = $('#resultConlection').val();
var datas = {"pageNo":pageNo, "treeId":nodeId, "startTime":startTime, "endTime":endTime,
"citys_name":citys_name, "ci":ci, "consumptionFlow_size":consumptionFlow_size,
"resultConlection":resultConlection, "IMSI":IMSI, "MSISDN":MSISDN};
$.ajax({
type:'post',
url:path+'/multipleAnalyseController/selectTempManager.do',
dataType:'json',
async: false,
data: datas,
success:function(result){
myStore.removeAll();
dataSource = result.multipleAnalyseList;
myStore.add(dataSource);
page = result.page;
initPage();
}
});
}
后台controller里面接收参数以及封装参数的方法:
@RequestMapping("selectTempManager")
public ModelAndView selectTempManager(HttpServletRequest request, HttpServletResponse response,
@RequestParam(required = false, defaultValue = "1") int pageNo,
@RequestParam(required = false, defaultValue = "10") int pageSize) throws ParseException{
logger.info("************************Thread into method selectTempManager*********************");
String treeId = request.getParameter("treeId");
Page page = Page.newBuilder(pageNo, pageSize, "selectTempManager");
MultipleAnalyse multipleAnalyse = new MultipleAnalyse();
multipleAnalyse.setNum1(treeId );
multipleAnalyse.setPage(page);
multipleAnalyseList = multipleAnalyseService.selectMultipleAnalyse(multipleAnalyse);
Map<String,Object> attributes = new HashMap<String,Object>();
attributes.put("multipleAnalyseList", multipleAnalyseList);
attributes.put("page", page);
MappingJacksonJsonView view = new MappingJacksonJsonView();
view.setAttributesMap(attributes);
ModelAndView mode = new ModelAndView();
logger.info("************************select Flow analyse:"+multipleAnalyseList+"*********************");
mode.setView(view);
return mode;
}