分页查询是我们非常常用的一种查询方式,所以我们这里就以分页作为例子讲解!
对于mybatis来说,当然你可以直接写一个分页sql语句进行查询,将currentPage和pageSize作为参数传入,就可以获得分页数据了!但是呢,分页查询往往伴随的还需要获取”总条数” totalRecord,也就是说,还要对应的再写一条获取总条数的sql语句,再获取一次!比较麻烦,那么问题来了,能不能不那么麻烦,自动的进行分页及总条数的计算获取数据!当然是能的了,这里就需要分页插件了!
下面的例子比较简单,仅以oracle数据库为例子!!
对于mybatis来说,有四大对外插件类,那么可以做成分页的拦截可以是StatementHandler和Executor,StatementHandler简单点,那么我们这边先以StatementHandler来拦截
先一个Page类,用于存储分页的必要数据
public class Page {
//用于存储返回数据
private List<Object> result;
//每页个数
private int pageSize;
//当前页码
private int currentPage;
//总条数
private int totalRecord;
//总页数
private int totalPage;
}
PageUtils : 工具类
public class PageUtils {
//存储page数据,为null则
private static final ThreadLocal<Page> PAGE = new ThreadLocal<Page>();
public static Page getPage() {
return PAGE.get();
}
public static void setPage(Page page) {
PAGE.set(page);
}
public static void clear() {
PAGE.remove();
}
}
@Intercepts({
@Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class, Integer.class }) })
public class PageInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
//获取page null的话不执行分页
Page page = PageUtils.getPage();
if (page != null) {
//获取RoutingStatementHandler
RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
//通过SystemMetaObject工具类,可以很容易的获取其属性变量进行get/set
MetaObject metaobject = SystemMetaObject.forObject(handler);
//获取StatementHandler
StatementHandler delegate = (StatementHandler) metaobject.getValue("delegate");
//获取BoundSql
BoundSql boundSql = delegate.getBoundSql();
MappedStatement mappedStatement = (MappedStatement) metaobject.getValue("delegate.mappedStatement");
//获取原sql
String sql = boundSql.getSql();
//获取总数
getTotal(invocation, mappedStatement, boundSql);
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
//下面是拼接分页sql语句
sqlBuilder.append("select * from ( select t.*, rownum row_id from ( ");
sqlBuilder.append(sql);
sqlBuilder.append(" ) t where rownum <= ").append(page.getCurrentPage() * page.getPageSize());
sqlBuilder.append(") where row_id > ").append((page.getCurrentPage() - 1) * page.getPageSize());
//替换BoundSql的sql
bMetaObject.setValue("sql", sqlBuilder.toString());
}
return invocation.proceed();
} finally {
PageUtils.clear();
}
}
private void getTotal(Invocation invocation, MappedStatement mappedStatement, BoundSql boundSql)
throws SQLException {
//拼接count sql语句
StringBuilder sqlBuilderCount = new StringBuilder(boundSql.getSql().length() + 120);
sqlBuilderCount.append("select count(1) from (").append(boundSql.getSql()).append(")");
//获取数据连接
Connection conn = (Connection) invocation.getArgs()[0];
//生成Statement
PreparedStatement ps = conn.prepareStatement(sqlBuilderCount.toString());
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(),
boundSql);
//注入参数
parameterHandler.setParameters(ps);
//执行返回结果
ResultSet rs = ps.executeQuery();
int totalRecord = 0;
if (rs.next()) {
//获取总数
totalRecord = rs.getInt(1);
}
Page page = PageUtils.getPage();
//赋值给 page类
page.setTotalRecord(totalRecord);
}
private void setTotalRecord() {
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// TODO Auto-generated method stub
}
}
client
//第二页
page.setCurrentPage(2);
//每页5条数据
page.setPageSize(5);
PageUtils.setPage(page);
//其他该怎么调用就怎么调用
好了,分页做好了!
很简单的一个分页插件,没考虑太多因素!
上面这个是拦截StatementHandler做的分页,对于Executor来说,我们拦截query方法,就可以直接返回page类,将返回数据赋值给page类中的result,但是处理起来比StatementHandler复杂,不过最终返回的就直接是page类,对于客户端来说,简单了!