mybatis使用拦截器实现物理分页功能
困扰我很久，为什么在使用mybatis的rowbounds不启作用了，以前做的项目都可以，后来发现原来是因为数据库换了，拦截器无法配置 导致的定义抽象类
package com.odbpo.mybatis;
/**
* @Title: Dialect.java
*/
public abstract class Dialect
{
public static enum Type {
MYSQL, ORACLE
}
public abstract String getLimitString(String sql, int skipResults, int maxResults);
}
定义抽象类的实现类
package com.odbpo.mybatis;
import org.apache.log4j.Logger;
/**
* @Title: OracleDialect.java
* @Description:
*/
public class OracleDialect extends Dialect
{
protected static Logger log = Logger.getLogger(OracleDialect.class);
@Override
public String getLimitString(String sql, int offset, int limit)
{
sql = sql.trim();
StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(sql);
pagingSelect.append(" ) row_ ) where rownum_ > ").append(offset).append(" and rownum_ <= ")
.append(offset + limit);
log.debug(pagingSelect.toString());
return pagingSelect.toString();
}
}
定义mybatis拦截器，--关键
package com.odbpo.mybatis;
/**
* @Title: PageInterceptor.java
* @Description: myBatis 分页工具
*/
import java.sql.Connection;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.DefaultParameterHandler;
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.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.apache.log4j.Logger;
@Intercepts(
{ @Signature(type = StatementHandler.class, method = "prepare", args =
{ Connection.class }) })
public class PaginationInterceptor implements Interceptor
{
protected static Logger log = Logger.getLogger(PaginationInterceptor.class);
/* (non-Javadoc)
* @see org.apache.ibatis.plugin.Interceptor#intercept(org.apache.ibatis.plugin.Invocation)
*/
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable
{
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);
RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds");
if (rowBounds == null || rowBounds == RowBounds.DEFAULT)
{
return invocation.proceed();
}
DefaultParameterHandler defaultParameterHandler = (DefaultParameterHandler) metaStatementHandler
.getValue("delegate.parameterHandler");
Map parameterMap = (Map) defaultParameterHandler.getParameterObject();
Object sidx = parameterMap.get("_sidx");
Object sord = parameterMap.get("_sord");
String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
if (sidx != null && sord != null)
{
originalSql = originalSql + " order by " + sidx + " " + sord;
}
Configuration configuration = (Configuration) metaStatementHandler
.getValue("delegate.configuration");
Dialect.Type databaseType = null;
try
{
databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect")
.toUpperCase());
}
catch (Exception e)
{
log.error(e);
}
if (databaseType == null)
{
throw new RuntimeException(
"the value of the dialect property in configuration.xml is not defined : "
+ configuration.getVariables().getProperty("dialect"));
}
Dialect dialect = null;
switch (databaseType)
{
case ORACLE:
dialect = new OracleDialect();
break;
case MYSQL://需要实现MySQL的分页逻辑
break;
}
metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitString(originalSql,
rowBounds.getOffset(), rowBounds.getLimit()));
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
if (log.isDebugEnabled())
{
BoundSql boundSql = statementHandler.getBoundSql();
log.debug("生成分页SQL : " + boundSql.getSql());
}
return invocation.proceed();
}
/* (non-Javadoc)
* @see org.apache.ibatis.plugin.Interceptor#plugin(java.lang.Object)
*/
@Override
public Object plugin(Object target)
{
return Plugin.wrap(target, this);
}
/* (non-Javadoc)
* @see org.apache.ibatis.plugin.Interceptor#setProperties(java.util.Properties)
*/
@Override
public void setProperties(Properties arg0)
{
}
}
定义分页的BEAN
package com.odbpo.mybatis;
import java.util.Collections;
import java.util.List;
@SuppressWarnings("unchecked")
public class Page
{
private int page;
private int limit;
private int total;
private List rows = Collections.emptyList();
public int getPage()
{
return page;
}
public void setPage(int page)
{
this.page = page;
}
public int getLimit()
{
return limit;
}
public void setLimit(int limit)
{
this.limit = limit;
}
public int getTotal()
{
return total;
}
public void setTotal(int total)
{
this.total = total;
}
public List getRows()
{
return rows;
}
public void setRows(List rows)
{
this.rows = rows;
}
}
页:
[1]