使用mybatis实现拦截参数自动分页

前言

之前说过我们的项目使用的是达梦数据库, 我一开始带了pageHelper进去, 发现他实现不了分页. 然后我们组长就通过mybatis的拦截器自己实现了分页. 今天正巧刷到一篇文章, 也是教自动分页的, 所以整理实现了一下

实现过程

我对比了下他的实现步骤和我们项目几乎是一样的, 毕竟天下代码一大抄. 但是他的实现的效果是指定方法分页, 而我们项目的效果是dao层方法里有Page参数的分页, 所以这里我要进行下改动~

拦截器

import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

// @Intercepts({
//         @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}),
//         @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
// })
@Intercepts({
        @Signature(method = "query", type = Executor.class, args = {
                MappedStatement.class, Object.class, RowBounds.class,
                ResultHandler.class}),
        @Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})
})
@Component
public class PageInterceptor implements Interceptor
{
    // private static final String SELECT_ID = "page";

    //插件运行的代码，它将代替原有的方法
    @Override
    public Object intercept(Invocation invocation) throws Throwable
    {
        if (invocation.getTarget() instanceof StatementHandler)
        {
            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
            // MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
            // String selectId = mappedStatement.getId();

            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
            Object params = boundSql.getParameterObject();
            Page page = null;
            if (params instanceof Page)
            {
                page = (Page) params;
            } else if (params instanceof MapperMethod.ParamMap)
            {
                MapperMethod.ParamMap map = (MapperMethod.ParamMap) params;
                for (Object o : map.keySet())
                {
                    if ("page".equals(o.toString()))
                    {
                        page = (Page) map.get(o);
                        break;
                    }
                }
            }
            if (page != null)
            {
                // 分页参数作为参数对象parameterObject的一个属性
                String sql = boundSql.getSql();
                //Page co = (Page) (boundSql.getParameterObject());

                // 重写sql
                String countSql = concatCountSql(sql);
                String pageSql = concatPageSql(sql, page);

                System.out.println("重写的 count  sql		:" + countSql);
                System.out.println("重写的 select sql		:" + pageSql);

                Connection connection = (Connection) invocation.getArgs()[0];

                PreparedStatement countStmt = null;
                ResultSet rs = null;
                int totalCount = 0;
                try
                {
                    countStmt = connection.prepareStatement(countSql);
                    rs = countStmt.executeQuery();
                    if (rs.next())
                    {
                        totalCount = rs.getInt(1);
                    }

                } catch (SQLException e)
                {
                    System.out.println("Ignore this exception" + e);
                } finally
                {
                    try
                    {
                        rs.close();
                        countStmt.close();
                    } catch (SQLException e)
                    {
                        System.out.println("Ignore this exception" + e);
                    }
                }

                metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);

                //绑定count
                page.setCount(totalCount);
            }
        }
        return invocation.proceed();
    }

    /**
     * 拦截类型StatementHandler
     */
    @Override
    public Object plugin(Object target)
    {
        if (target instanceof StatementHandler)
        {
            return Plugin.wrap(target, this);
        } else
        {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties)
    {

    }


    public String concatCountSql(String sql)
    {
        StringBuffer sb = new StringBuffer("select count(*) from ");
        sql = sql.toLowerCase();

        if (sql.lastIndexOf("order") > sql.lastIndexOf(")"))
        {
            sb.append(sql.substring(sql.indexOf("from") + 4, sql.lastIndexOf("order")));
        } else
        {
            sb.append(sql.substring(sql.indexOf("from") + 4));
        }
        return sb.toString();
    }

    public String concatPageSql(String sql, Page co)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        sb.append(" limit ").append(co.getPageBegin()).append(" , ").append(co.getPageSize());
        return sb.toString();
    }

    public void setPageCount()
    {

    }
}

分页实体

public class Page
{
	private int pageSize = 5;
	private int pageId;
	private int pageBegin = 1;
	private int count;

	public int getPageSize()
	{
		return pageSize;
	}

	public void setPageSize(int pageSize)
	{
		this.pageSize = pageSize;
	}

	public int getPageId()
	{
		return pageId;
	}

	public void setPageId(int pageId)
	{
		this.pageId = pageId;
	}

	public int getPageBegin()
	{
		return pageBegin;
	}

	public void setPageBegin(int pageBegin)
	{
		this.pageBegin = pageBegin;
	}

	public int getCount()
	{
		return count;
	}

	public void setCount(int count)
	{
		this.count = count;
	}
}