ibatis基于拦截器的分页实现

ibatis基于拦截器的分页实现

1、在spring配置文件（applicationContext.xml）中添加拦截器配置:

<!-- 分页插件，根据方言自动添加分页信息，默认要求 -->
<bean id="pagePlugin" class="com.ifdoo.core.mybatis.plugin.PagePlugin">
    <property name="properties">
        <props>
            <prop key="dialect">com.ifdoo.core.mybatis.dialet.MySQLDialect</prop>
            <prop key="pageSqlId">.*query.*</prop>
        </props>
    </property>
</bean>

2、PagePlugin.java



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.Set;

import javax.xml.bind.PropertyException;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.scripting.defaults.DefaultParameterHandler;

import com.ifdoo.core.exception.SystemException;
import com.ifdoo.core.mybatis.dialet.Dialect;
import com.ifdoo.core.utils.page.PageView;

/**
 * Mybatis的分页查询插件，通过拦截StatementHandler的prepare方法来实现。
 * 只有在参数列表中包括Page类型的参数时才进行分页查询。 在多参数的情况下，只对第一个Page类型的参数生效。
 * 另外，在参数列表中，Page类型的参数无需用@Param来标注
 * 
 * @version 1.0
 */
@SuppressWarnings("unchecked")
@Intercepts( { @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PagePlugin implements Interceptor {

    private static Dialect  dialectObject   = null; // 数据库方言
    private static String   pageSqlId       = "";   // mybaits的数据库xml映射文件中需要拦截的ID(正则匹配)

    @SuppressWarnings("rawtypes")
    public Object intercept(Invocation ivk) throws Throwable {
        if (ivk.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk.getTarget();
            BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate");
            MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement");
            /**
             * 方法1：通过ＩＤ来区分是否需要分页．.*query.* 方法2：传入的参数是否有page参数，如果有，则分页，
             */
            // if (mappedStatement.getId().matches(pageSqlId)) { // 拦截需要分页的SQL
            BoundSql boundSql = delegate.getBoundSql();
            Object parameterObject = boundSql.getParameterObject();// 分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空
            if (parameterObject == null) {
                // throw new
                // NullPointerException("boundSql.getParameterObject() is null!");
                return ivk.proceed();
            } else {

                PageView pageView = null;
                if (parameterObject instanceof PageView) { // 参数就是Pages实体
                    pageView = (PageView) parameterObject;
                } else if (parameterObject instanceof Map) {
                    for (Entry entry : (Set<Entry>) ((Map) parameterObject).entrySet()) {
                        if (entry.getValue() instanceof PageView) {
                            pageView = (PageView) entry.getValue();
                            break;
                        }
                    }
                } else { // 参数为某个实体，该实体拥有Pages属性
                    pageView = ReflectHelper.getValueByFieldType(parameterObject, PageView.class);
                    if (pageView == null) {
                        return ivk.proceed();
                    }
                }

                if (pageView == null) {
                    return ivk.proceed();
                }

                String sql = boundSql.getSql();
                PreparedStatement countStmt = null;
                ResultSet rs = null;
                try {
                    Connection connection = (Connection) ivk.getArgs()[0];
                     String countSql = "select count(1) from (" + sql
                     + ") tmp_count"; // 记录统计
//                   String countSql = "select count(1) from (" + sql +
//                   ") tmp_count"; // 记录统计
//                   ") tmp_count"; // 记录统计

//                  String countSql = "";// "select count(1) from (" + sql +
//                  // 拼装count SQL
//                  int from_index = 0;
//                  if ((from_index = StringUtils.indexOfIgnoreCase(sql, "from")) != -1) {
//                      countSql = "select count(1) " + sql.substring(from_index, sql.length());
//                  }
//                  int order_index = 0;
//                  if ((order_index = StringUtils.lastIndexOfIgnoreCase(countSql, "order")) != -1) {
//                      countSql = countSql.substring(0, order_index);
//                  }

                    countStmt = connection.prepareStatement(countSql);
                    ReflectHelper.setValueByFieldName(boundSql, "sql", countSql);
                    DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
                    parameterHandler.setParameters(countStmt);
                    rs = countStmt.executeQuery();
                    int count = 0;
                    if (rs.next()) {
                        count = ((Number) rs.getObject(1)).intValue();
                    }

                    if (null != pageView) {
                        pageView.setRowCount(count);
                    }
                } finally {
                    try {
                        rs.close();
                    } catch (Exception e) {
                    }
                    try {
                        countStmt.close();
                    } catch (Exception e) {
                    }
                }
                String pageSql = generatePagesSql(sql, pageView);
                ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); // 将分页sql语句反射回BoundSql.
            }
            // }
        }
        return ivk.proceed();
    }

    /**
     * 根据数据库方言，生成特定的分页sql
     * 
     * @param sql
     * @param page
     * @return
     */
    @SuppressWarnings("rawtypes")
    private String generatePagesSql(String sql, PageView page) {
        if (page != null && dialectObject != null) {
            // pageNow默认是从1，而已数据库是从0开始计算的．所以(page.getPageNow()-1)
            int pageNow = page.getPageNow();
            return dialectObject.getLimitString(sql, (pageNow <= 0 ? 0 : pageNow - 1) * page.getPageSize(), page.getPageSize());
        }
        return sql;
    }

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties p) {
        String dialect = ""; // 数据库方言
        dialect = p.getProperty("dialect");
        if (StringUtils.isBlank(dialect)) {
            try {
                throw new PropertyException("dialect property is not found!");
            } catch (PropertyException e) {
                throw new SystemException(e);
            }
        } else {
            try {
                dialectObject = (Dialect) Class.forName(dialect).getDeclaredConstructor().newInstance();
            } catch (Exception e) {
                throw new RuntimeException(dialect + ", init fail!\n" + e);
            }
        }
        pageSqlId = p.getProperty("pageSqlId");// 根据id来区分是否需要分页
        if (StringUtils.isBlank(pageSqlId)) {
            try {
                throw new PropertyException("pageSqlId property is not found!");
            } catch (PropertyException e) {
                throw new SystemException(e);
            }
        }
    }
}

3、Dialect.java


/**
 * 类似hibernate的Dialect,但只精简出分页部分
 */
public class Dialect {

    public boolean supportsLimit(){
        return false;
    }

    public boolean supportsLimitOffset() {
        return supportsLimit();
    }

    /**
     * 将sql变成分页sql语句,直接使用offset,limit的值作为占位符.</br>
     * 源代码为: getLimitString(sql,offset,String.valueOf(offset),limit,String.valueOf(limit))
     */
    public String getLimitString(String sql, int offset, int limit) {
        return getLimitString(sql,offset,Integer.toString(offset),limit,Integer.toString(limit));
    }

    /**
     * 将sql变成分页sql语句,提供将offset及limit使用占位符(placeholder)替换.
     * <pre>
     * 如mysql
     * dialect.getLimitString("select * from user", 12, ":offset",0,":limit") 将返回
     * select * from user limit :offset,:limit
     * </pre>
     * @return 包含占位符的分页sql
     */
    public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit,String limitPlaceholder) {
        throw new UnsupportedOperationException("paged queries not supported");
    }

}

4、MySQLDialect.java

public class MySQLDialect extends Dialect{

    public boolean supportsLimitOffset(){
        return true;
    }

    public boolean supportsLimit() {   
        return true;   
    }  

    public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder) {
        if (offset > 0) {   
            return sql + " limit "+offsetPlaceholder+","+limitPlaceholder; 
        } else {   
            return sql + " limit "+limitPlaceholder;
        }  
    }   
}

针对于其他数据库的分页方言处理在后面博客中添加，谢谢！