mybatis的SqlSession可以直接通过以下接口来进行分页查询:
<E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds);
不过该查询是基于对查询结果去子集的方式。该方法效率较低(如果只查询一条数据服务器需要返回所有满足条件的数据)。
实现思路:用mybatis插件拦截StatementHandler的prepare方法,修改Sql语句,添加分页查询语句。至于分页中的总页数查询,另执行一条select count(*)查询语句。实现较简单。
StatementHandler 的执行过程简介:
在Executor中通过下面代码创建StatementHandler:
StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
configuration是mybatis的全局配置文件,其newStatementHandler创建过程如下:
public StatementHandler newStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
StatementHandler statementHandler = new RoutingStatementHandler(executor, mappedStatement, parameterObject, rowBounds, resultHandler, boundSql);
statementHandler = (StatementHandler) interceptorChain.pluginAll(statementHandler);
return statementHandler;
}
RoutingStatementHandler可以看作是非法语句处理器,具体的操作还是要委托给具体的BaseStatementHandler。interceptorChain.pluginAll用于将所有配置的myabtis插件类应用到这个接口上面。interceptorChain是一个拦截器链(职责链模式)
public Object pluginAll(Object target) {
for (Interceptor interceptor : interceptors) {
target = interceptor.plugin(target);
}
return target;
}
interceptors保存着配置mybatis的所有拦截器,分别调用拦截器对目标进行代理。也就是生成目标类的代理。例如拦截器Incet
package cn.liuyao.mybatis.test;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
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;
@Intercepts({@Signature(type = StatementHandler.class,method = "prepare",args = {java.sql.Connection.class})})
public class Incet implements Interceptor{
@Override
public Object intercept(Invocation invocation) throws Throwable {
return invocation.proceed();
}
//生成目标对象的代理
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
拦截器的plugin方法调用Plugin的wrap对目标对象包装代理。
public static Object wrap(Object target, Interceptor interceptor) {
Map<Class<?>, Set<Method>> signatureMap = getSignatureMap(interceptor);
Class<?> type = target.getClass();
Class<?>[] interfaces = getAllInterfaces(type, signatureMap);
if (interfaces.length > 0) {
return Proxy.newProxyInstance(
type.getClassLoader(),
interfaces,
new Plugin(target, interceptor, signatureMap));
}
return target;
}
wrap返回目标对象的代理,这样在执行特定接口的特定方法时候,能够增加自己的一些代码,类似于spring的aop功能。
Object intercept(Invocation invocation)
intercept编写额外的代码。实现自己的分页逻辑。
由于mybatis的语句执行,最终都由StatementHandler来完成,故只要拦截该接口的prepare方法,并修改boundSql与rowBound既可以达到修改Sql与添加分页信息的效果。
Page类:
package com.ccq.framework.lang;
import java.io.Serializable;
public class Page implements Serializable{
private static final long serialVersionUID = 9000151198919642793L;
private int pages;
private int pageNum;
private int pageSize;
public Page() {
super();
// TODO Auto-generated constructor stub
}
public Page(int pages, int pageNum, int pageSize) {
super();
this.pages = pages;
this.pageNum = pageNum;
this.pageSize = pageSize;
}
public int getPages() {
return pages;
}
public void setPages(int pages) {
this.pages = pages;
}
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;
}
}
page类用于保存分页信息:当前页,页大小,所有页数。
pagePlugin:
package com.ccq.framework.plugin;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
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.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@Intercepts({@Signature(type = StatementHandler.class,method = "prepare",args = {java.sql.Connection.class})})
public class MyPagePlugin implements Interceptor{
private Dialect dialect;
@Override
public Object intercept(Invocation invocation) throws Throwable {
Logger log = LoggerFactory.getLogger(MyPagePlugin.class);
StatementHandler hander = (StatementHandler) invocation.getTarget();
//对handler进行包装可以获取其私有属性
MetaObject meta = MetaObject.forObject(hander, new DefaultObjectFactory(),
new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
RowBounds rowBound = (RowBounds) meta.getValue("delegate.rowBounds");//获取分页信息
if(rowBound.equals(RowBounds.DEFAULT)) {
//未指定分页命令
return invocation.proceed();
}
BoundSql boundSql = (BoundSql) meta.getValue("delegate.boundSql");//获取sql语句
if(dialect == null) {
log.debug("not assigned dialect,use mysql default");
dialect = new MysqlDialect();
}
String buildSql = dialect.builderSql(boundSql.getSql(), rowBound.getOffset(), rowBound.getLimit());
MetaObject boundSqlMeta = MetaObject.forObject(boundSql, new DefaultObjectFactory(),
new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
boundSqlMeta.setValue("sql", buildSql);
meta.setValue("delegate.rowBounds", RowBound.DEFAULT);//回复默认的分页信息
return invocation.proceed();
}
//生成目标对象的代理
@Override
public Object plugin(Object target) {
if(target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
if(properties.getProperty("dialect").equals("mysql")) {
dialect = new MysqlDialect();
}else if(properties.getProperty("dialect").equals("oracle")) {
//TODO
}
}
}
buildSql:
package com.ccq.framework.plugin;
import com.ccq.framework.exception.AppException;
public class MysqlDialect extends Dialect{
@Override
public String builderSql(String rawSql,int OFFSET,int LIMIT) {
if(rawSql.endsWith(";")) {
throw new AppException("Bad sql: grammer error on ; with limit");
}
StringBuffer sb = new StringBuffer(rawSql);
//SELECT * FROM table LIMIT [offset,] rows
sb.append(String.format(" limit %s,%s",new Object[]{OFFSET,LIMIT}));
return sb.toString();
}
}
只针对mysql的sql语句。
测试:采用了的mybatis的通用单表CRUD工具,后面给链接。