mysql 分页查询拦截器_mysql 分页拦截器，类似hibernate方言

package com.epdc.common.dialect;

/**

*

* @author lsh

*

*/

public class OracleDialect extends Dialect {

public String getLimitString(String sql, int offset, int limit) {

sql = sql.trim();

boolean isForUpdate = false;

if (sql.toLowerCase().endsWith(" for update")) {

sql = sql.substring(0, sql.length() - 11);

isForUpdate = true;

}

StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);

pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");

pagingSelect.append(sql);

pagingSelect.append(" ) row_ ) where rownum_ > "+offset+" and rownum_ <= "+(offset + limit));

if (isForUpdate) {

pagingSelect.append(" for update");

}

return pagingSelect.toString();

}

}

package com.epdc.common.intercept;

import java.sql.Connection;

import java.util.Properties;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

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 com.epdc.common.dialect.Dialect;

import com.epdc.common.dialect.OracleDialect;

@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})

public class PaginationInterceptor implements Interceptor{

private final static Log log = LogFactory.getLog(PaginationInterceptor.class);

@Override

public Object intercept(Invocation invocation) throws Throwable {

StatementHandler statementHandler = (StatementHandler)invocation.getTarget();

BoundSql boundSql = statementHandler.getBoundSql();

MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);

RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds");

if(rowBounds == null || rowBounds == RowBounds.DEFAULT){

return invocation.proceed();

}

Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration");

Dialect.Type databaseType = null;

try{

databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());

} catch(Exception e){

//ignore

}

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 MYSQL:

//dialect = new MySql5Dialect();

case ORACLE:

dialect = new OracleDialect();

}

String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql");

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()){

log.debug("SQL : " + boundSql.getSql());

}

return invocation.proceed();

}

@Override

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

@Override

public void setProperties(Properties properties) {

}

}