查看原文：http://www.ibloger.net/article/291.html
参考文章：http://blog.youkuaiyun.com/isea533/article/details/23831273
大牛中的文章已经说明很清楚了，这里就说一下使用方法吧，我也是刚接触Mybatis不久，记录下来，便于以后调用和复习
第一步：
在包中创建一个PageHelper工具类，实现拦截器Interceptor，下面代码可以直接拷贝（对于Oracle用户可以不用修改任何内容，对于其他数据库，可根据说明进行修改）
我这里用的SpringMVC和MyBatis方式的例子，核心代码如下
package com.founder.utils;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.sql.Connection;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
/**
* Mybatis - 通用分页拦截器 Mybatis拦截器控制分页查询
* 真正生成Statement并执行sql的语句是StatementHandler接口的某个实现，
* 这样就可以写个插件对StatementHandler的行为进行拦截
*/
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }),
@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }) })
public class PageHelper implements Interceptor {
private static final Logger logger = Logger.getLogger(PageHelper.class);
public static final ThreadLocal<Page> localPage = new ThreadLocal<Page>();
/**
* 开始分页
*
* @param pageNum
* @param pageSize
*/
public static void startPage(int pageNum, int pageSize) {
localPage.set(new Page(pageNum, pageSize));
}
/**
* 结束分页并返回结果，该方法必须被调用，否则localPage会一直保存下去，直到下一次startPage
* @return
*/
public static Page endPage() {
Page page = localPage.get();
localPage.remove();
return page;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
if (localPage.get() == null) {
return invocation.proceed();
}
if (invocation.getTarget() instanceof StatementHandler) {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环
// 可以分离出最原始的的目标类)
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = SystemMetaObject.forObject(object);
}
// 分离最后一个代理对象的目标类
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = SystemMetaObject.forObject(object);
}
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
// 分页信息if (localPage.get() != null) {
Page page = localPage.get();
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
// 分页参数作为参数对象parameterObject的一个属性
String sql = boundSql.getSql();
// 重写sql
String pageSql = buildPageSql(sql, page);
// 重写分页sql
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
Connection connection = (Connection) invocation.getArgs()[0];
// 重设分页参数里的总页数等
setPageParameter(sql, connection, mappedStatement, boundSql, page);
// 将执行权交给下一个拦截器
return invocation.proceed();
} else if (invocation.getTarget() instanceof ResultSetHandler) {
Object result = invocation.proceed();
Page page = localPage.get();
page.setResult((List) result);
return result;
}
return null;
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler || target instanceof ResultSetHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties arg0) {
}
/**
* 修改原SQL为分页SQL
* @param sql
* @param page
* @return
*/
private String buildPageSql(String sql, Page page) {
StringBuilder pageSql = new StringBuilder(200);
pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
pageSql.append(sql);
pageSql.append(" ) temp where rownum <= ").append(page.getEndRow());
pageSql.append(") where row_id > ").append(page.getStartRow());
return pageSql.toString();
}
/**
* 获取总记录数
* @param sql
* @param connection
* @param mappedStatement
* @param boundSql
* @param page
*/
private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Page page) {
// 记录总记录数
String countSql = "select count(0) from (" + sql + ")";
PreparedStatement countStmt = null;
ResultSet rs = null;
try {
countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
rs = countStmt.executeQuery();
int totalCount = 0;
if (rs.next()) {
totalCount = rs.getInt(1);
}
page.setTotal(totalCount);
int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);
page.setPages(totalPage);
} catch (SQLException e) {
logger.error("Ignore this exception", e);
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.error("Ignore this exception", e);
}
try {
countStmt.close();
} catch (SQLException e) {
logger.error("Ignore this exception", e);
}
}
}
/**
* 代入参数值
* @param ps
* @param mappedStatement
* @param boundSql
* @param parameterObject
* @throws SQLException
*/
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
/**
* Description: 分页
*/
public static class Page<E> {
private int pageNum;
private int pageSize;
private int startRow;
private int endRow;
private long total;
private int pages;
private List<E> result;
public Page(int pageNum, int pageSize) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.startRow = pageNum > 0 ? (pageNum - 1) * pageSize : 0;
this.endRow = pageNum * pageSize;
}
public List<E> getResult() {
return result;
}
public void setResult(List<E> result) {
this.result = result;
}
public int getPages() {
return pages;
}
public void setPages(int pages) {
this.pages = pages;
}
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getStartRow() {
return startRow;
}
public void setStartRow(int startRow) {
this.startRow = startRow;
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
@Override
public String toString() {
return "Page{" + "pageNum=" + pageNum + ", pageSize=" + pageSize + ", startRow=" + startRow + ", endRow=" + endRow + ", total=" + total + ", pages=" + pages + '}';
}
}
}第二步，配置Mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 拦截，用于mybatis分页处理 -->
<plugins>
<plugin interceptor="com.founder.utils.PageHelper"></plugin>
</plugins>
<mappers>
<mapper resource="com/founder/model/Visitlogs_Mapper.xml" />
</mappers>
</configuration>package com.founder.dao;
import java.util.List;
import com.founder.model.Visitlogs;
import com.founder.utils.PageHelper;
public interface VisitlogsDao {
/**
* 查询所有走访日志
* @return
*/
public List<Visitlogs> findAllVisitlogs();
/**
* 分页查询所有走访日志
* @return
*/
public PageHelper.Page<Visitlogs> findAllVisitlogs(int pageNumber,int pageSize);
}
@Override
public List<Visitlogs> findAllVisitlogs() {
return sqlSession.selectList(Visitlogs.class.getName()+"_Mapper.findAllVisitlogs");
}
@Override
public PageHelper.Page<Visitlogs> findAllVisitlogs(int pageNumber, int pageSize) {
PageHelper.startPage(pageNumber,pageSize); // 开启分页
sqlSession.selectList(Visitlogs.class.getName()+"_Mapper.findAllVisitlogs");
return PageHelper.endPage(); // 必须使用
}/**
* 查找所有走访日志
*
* @param request
* @param response
*/
@RequestMapping("/findAllVisitlogs")
public void findAllVisitlogs(HttpServletRequest request, HttpServletResponse response) {
String obj_page = request.getParameter("page");
String obj_rows = request.getParameter("rows");
int page = obj_page==""?1:Integer.valueOf(obj_page); // 当前位于第几页，基于Jquery easyui 参数提供
int rows = obj_page==""?10:Integer.valueOf(obj_rows); // 每页显示多少条数据，基于Jquery easyui 参数提供
System.out.println("page: "+page+" , rows: "+rows);
<strong>Page<Visitlogs> visitlogs = visitlogsService.findAllVisitlogs(page,rows); // 分页查询结果
List<Visitlogs> listVisitlogs = visitlogs.getResult(); // 取出分页结果</strong>
JsonConfig jsonConfig = new JsonConfig();
jsonConfig.setExcludes(new String []{"startRow","total"}); // 排除json格式化的字段
// 处理日期字段显示
jsonConfig.registerJsonValueProcessor(Date.class, new JsonValueProcessor() {
// 自定义日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
/** 处理单个Date对象 */
@Override
public Object processObjectValue(String propertyName, Object date, JsonConfig config) {
return sdf.format(date);
}
/** 处理数组中的Date对象 */
@Override
public Object processArrayValue(Object date, JsonConfig config) {
return sdf.format(date);
}
});
String json = JSONArray.fromObject(listVisitlogs, jsonConfig).toString();
json = "{\"total\":\""+visitlogs.getTotal()+"\",\"rows\":"+json+"}";
System.out.println("2、" + json);
try {
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
out.print(json);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}打印结果：
{"total":"12","rows":[{"code":"v0001","id":1,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"监督市场，把控风险","workTheme":"监督市场"},{"code":"v0002","id":2,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"审批","workTheme":"审批"},{"code":"v0003","id":3,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"002","visitType":"001","workCont":"aa","workTheme":"a"},{"code":"v0004","id":4,"reportStateCode":"002","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"bb","workTheme":"b"},{"code":"v0005","id":5,"reportStateCode":"002","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"cc","workTheme":"c"},{"code":"v0006","id":6,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"dd","workTheme":"d"},{"code":"v0007","id":7,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"002","visitType":"001","workCont":"ee","workTheme":"e"},{"code":"v0008","id":8,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"ff","workTheme":"f"},{"code":"v0009","id":9,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"001","workCont":"gg","workTheme":"g"},{"code":"v0010","id":10,"reportStateCode":"001","responsibiGridNo":1,"responsibleId":1,"visitDataTime":"2014-11-03","visitObjectType":"001","visitType":"002","workCont":"hh","workTheme":"h"}]}
扫一扫
2231
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
