Mybatis分页插件实现

Mybatis分页插件

Pageable

/**
 * @author hanbing
 * @date 2019-06-20
 * @since 1.0.0
 */
public abstract class Pageable extends RowBounds {

    //查询的起始位置
    private int offset;
    //查询多少行记录
    private int limit;

    protected Pageable() {
        this.offset = NO_ROW_OFFSET;
        this.limit = NO_ROW_LIMIT;
    }

    protected void setOffsetLimit(int offset, int limit) {
        this.offset = offset;
        this.limit = limit;
    }

    public abstract int getPageNo();

    public abstract int getPageSize();

    public abstract long getTotalCount();

    public abstract void setTotalCount(long totalCount);

    public int getOffset() {
        return offset;
    }

    public int getLimit() {
        return limit;
    }

    public Optional<Pageable> toOptional() {
        return Optional.ofNullable(this);
    }

}

PageRequest

/**
 * @author hanbing
 * @date 2019-06-20
 * @since 1.0.0
 */
public class PageRequest extends Pageable implements Serializable {


    private static final long serialVersionUID = 1232825578694716871L;

    private int pageNo;

    private int pageSize;

    private long totalCount;

    private PageRequest(int page, int size) {
        if (page < 0) {
            throw new IllegalArgumentException("Page index must not be less than zero!");
        } else if (size < 1) {
            throw new IllegalArgumentException("Page size must not be less than one!");
        } else {
            this.pageNo = page;
            this.pageSize = size;
        }
        int offset = (pageNo -1) * pageSize;
        int limit = pageSize;
        this.setOffsetLimit(offset,limit);
    }

    public static PageRequest of(int page, int size) {
        return new PageRequest(page, size);
    }

    @Override
    public int getPageNo() {
        return pageNo;
    }

    @Override
    public int getPageSize() {
        return pageSize;
    }

    @Override
    public long getTotalCount() {
        return totalCount;
    }

    @Override
    public void setTotalCount(long totalCount) {
        this.totalCount = totalCount;
    }

    @Override
    public int hashCode() {
        return 31 * super.hashCode();
    }

    @Override
    public String toString() {
        return String.format("Page request [page: %d, size %d]", this.getPageNo(), this.getPageSize());
    }


}

PageHelper

/**
 * Pageable 分页组件
 *
 * @author hanbing
 * @date 2019-06-18
 * @since 1.0.0
 */
@Component
public class PageHelper extends Paging {

    /**
     * getPage
     *
     * @return
     */
    public static PageRequest getPage() {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        Assert.notNull(servletRequestAttributes, "servletRequestAttributes must not be null");
        HttpServletRequest request = servletRequestAttributes.getRequest();
        return getPage(request);
    }

    /**
     * getPage
     *
     * @param request
     * @return
     */
    public static PageRequest getPage(HttpServletRequest request) {
        Assert.notNull(request, "request must not be null");
        int page = getPageNo(request);
        int size = getPageSize(request);
        return PageHelper.getPage(page, size);
    }

    /**
     * getPage
     *
     * @param page
     * @return
     */
    public static PageRequest getPage(int page) {
        return PageRequest.of(page, defaultValue().getPageSize());
    }

    /**
     *  getPage
     *
     * @param page
     * @param size
     * @return
     */
    public static PageRequest getPage(int page, int size) {
        return PageRequest.of(page, size);
    }

    private static int getPageNo(HttpServletRequest request) {
        int pageNo;
        String no = request.getParameter(paramName().getPageNo());
        if (StringUtils.isNumeric(no)) {
            pageNo = Integer.parseInt(no);
            if (pageNo < 1) {
                pageNo = 1;
            }
        } else {
            pageNo = defaultValue().getPageNo();
        }
        return pageNo;
    }

    private static int getPageSize(HttpServletRequest request) {
        int pageSize;
        String size = request.getParameter(paramName().getPageSize());
        if (StringUtils.isNumeric(size)) {
            pageSize = Integer.parseInt(size);
        } else {
            pageSize = defaultValue().getPageSize();
        }
        return pageSize;
    }

}

dialect

/**
 * @author hanbing
 * @ClassName: DbDialect
 * @Description: 数据库Dialect接口
 */
public interface DbDialect {

    String buildCountSql(String sql);

    String buildSortSql(String sql, String sort);

    String buildPaginationSql(String sql, long offset, long limit);
}

/**
 * @author hanbing
 * @ClassName: DialectFactory
 * @Description:
 */
public class DialectFactory {

    public static DbDialect getDialect(String dialectName) throws Exception{

        if("mysql".equals(dialectName)){
            return new MysqlDialect();
        }
        else if("oracle".equals(dialectName)){
            return new OracleDialect();
        }else{
            throw new Exception("Cannot get the dialect : " + dialectName);
        }

    }
}

/**
 * @author hanbing
 * @ClassName: MysqlDialect
 * @Description: Dialect的Mysql实现
 */
public class MysqlDialect implements DbDialect {

    @Override
    public String buildCountSql(String sql) {
        return "select count(*) from (" + sql + ") as t_total";
    }

    @Override
    public String buildSortSql(String sql, String sort) {
        if (sort != null && !"".equals(sort)) {
            return sql + " order by " + sort;
        } else {
            return sql;
        }
    }

    @Override
    public String buildPaginationSql(String sql, long offset, long limit) {
        StringBuilder paginationSql = new StringBuilder();
        if (offset > 0) {
            paginationSql.append(sql).append(" limit ").append(offset).append(",").append(limit);
        } else {
            paginationSql.append(sql).append(" limit ").append(limit);
        }
        return paginationSql.toString();
    }
}

/**
 * @author hanbing
 * @ClassName: MysqlDialect
 * @Description: Dialect的Oracle实现
 * @Since 2018/07/12
 */
public class OracleDialect implements DbDialect {

    @Override
    public String buildCountSql(String sql) {
        return "select count(*) from (" + sql + ") t_total";
    }

    @Override
    public String buildSortSql(String sql, String sort) {
        if (sort != null && !"".equals(sort)) {
            return sql + " order by " + sort;
        } else {
            return sql;
        }
    }

    @Override
    public String buildPaginationSql(String sql, long offset, long limit) {
        StringBuilder paginationSql = new StringBuilder();
        offset++;
        paginationSql.append("select * from ( SELECT U.*, ROWNUM RNUM from ( ")
                .append(sql).append(") U where ROWNUM <= ")
                .append(offset + limit).append(") where RNUM > ").append(offset);
        return paginationSql.toString();
    }
}

MybatisPaginationPlugin

/**
 * MybatisPaginationPlugin
 *
 * @author hanbing
 * @since 1.0.0
 * @date 2019-06-18
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MybatisPaginationPlugin implements Interceptor {


    private static final Logger LOGGER = LoggerFactory.getLogger(MybatisPaginationPlugin.class);

    private DbDialect dialect;

    /**
     * 拦截器方法执行
     *
     * @param invocation
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        return this.processPage(invocation);
    }

    @Override
    public Object plugin(Object o) {
        return Plugin.wrap(o, this);
    }

    @Override
    public void setProperties(Properties properties) {
        try {
            this.dialect = DialectFactory.getDialect(properties.getProperty("dialect"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Mybatis拦截器方法，拦截Page,RowBounds参数
     *
     * @param invocation 拦截器入参
     * @return 返回执行结果
     * @throws Throwable 抛出异常
     */
    private Object processPage(Invocation invocation) throws Throwable {

        Object target = invocation.getTarget();
        if (target instanceof StatementHandler) {
            StatementHandler statementHandler = (StatementHandler) target;
            MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
            // 修复父类转子类时属性丢失问题
            MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
            BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
            Object bounds = metaStatementHandler.getValue("delegate.rowBounds");
            String originalSql = boundSql.getSql().trim();
            Object parameterObject = boundSql.getParameterObject();

            RowBounds rowBounds = (RowBounds) bounds;
            //判断是否默认分页：RowBounds类型参数

            if (bounds instanceof RowBounds) {
                // 不需要分页的场合
                if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {
                    return invocation.proceed();
                }
            }

            // 判断分页参数：Pageable类型参数
            if (bounds instanceof Pageable) {
                Pageable pageable = (Pageable) bounds;
                Connection connection = (Connection) invocation.getArgs()[0];
                final String totalCountSql = this.dialect.buildCountSql(originalSql);
                long totalCount = getTotalCount(totalCountSql, connection, mappedStatement, parameterObject, boundSql);
                pageable.setTotalCount(totalCount);

                String paginationSql = dialect.buildPaginationSql(originalSql, pageable.getOffset(), pageable.getLimit());
                metaStatementHandler.setValue("delegate.boundSql.sql", paginationSql);

            }

            // 查询分页数据
            LOGGER.debug("off={}", rowBounds.getOffset());
            LOGGER.debug("lim={}", rowBounds.getLimit());

            // 禁用内存分页
            metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);
            metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
        }
        return invocation.proceed();
    }

    /**
     * Title: buildMappedStatement
     * Description: 生成新的mappedStatement
     *
     * @param ms       原始ms
     * @param boundSql 原始boundsql
     * @param sql      分页最终sql
     * @return 新的mappedStatement
     */

    private MappedStatement buildMappedStatement(MappedStatement ms, BoundSql boundSql, String sql) {

        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(),
                new BoundSqlSqlSource(ms, boundSql, sql), ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.fetchSize(ms.getFetchSize());
        builder.timeout(ms.getTimeout());
        builder.statementType(ms.getStatementType());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        builder.keyGenerator(ms.getKeyGenerator());
        builder.keyProperty(delimitedArraytoString(ms.getKeyProperties()));
        builder.keyColumn(delimitedArraytoString(ms.getKeyColumns()));
        builder.databaseId(ms.getDatabaseId());
        return builder.build();
    }

    /**
     * 获取分页参数
     *
     * @param params RowBounds参数
     * @return 返回Page对象
     */
    public RowBounds getPageFromRowBounds(Object params) {
        RowBounds rowBounds = null;
        if (params instanceof RowBounds) {
            rowBounds = (RowBounds) params;
        }
        return rowBounds;
    }

    /**
     * 对象中获取分页参数
     *
     * @param params
     * @return
     */
    public Pageable getPageFromParam(Object params) {
        Pageable pageable = null;
        if (params instanceof Pageable) { // 只有一个参数的情况
            pageable = (Pageable) params;
        }
        return pageable;
    }

    /**
     * Title: delimitedArraytoString
     * Description: 将参数转string
     *
     * @param in properties
     * @return keyProperty
     */
    private static String delimitedArraytoString(String[] in) {
        if (in == null || in.length == 0) {
            return null;
        } else {
            StringBuffer answer = new StringBuffer();
            for (String str : in) {
                answer.append(str).append(",");
            }
            return answer.toString();
        }
    }

    /**
     * getTotalCount
     *
     * @param totalCountSql
     * @param connection
     * @param statement
     * @param parameterObj
     * @param boundSql
     * @return
     * @throws SQLException
     */
    public int getTotalCount(final String totalCountSql, final Connection connection, final MappedStatement statement, final Object parameterObj, final BoundSql boundSql) throws SQLException {
        PreparedStatement pStatement = null;
        ResultSet rs = null;
        try {
            pStatement = connection.prepareStatement(totalCountSql);
            setParameters(pStatement, this.buildMappedStatement(statement, boundSql, totalCountSql), boundSql, parameterObj);
            rs = pStatement.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            releaseResource(rs, pStatement);
        }
        return 0;
    }


    /**
     * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
     * //参考  org.apache.ibatis.scripting.defaults.DefaultParameterHandler
     *
     * @param ps
     * @param mappedStatement
     * @param boundSql
     * @param parameterObject
     * @throws SQLException
     */
    @SuppressWarnings("unchecked")
    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(ps);
    }

    /**
     * releaseResource
     *
     * @param rs
     * @param ps
     */
    public static void releaseResource(ResultSet rs, PreparedStatement ps) {
        try {
            if (null != rs) {
                rs.close();
                rs = null;
            }
            if (null != ps) {
                ps.close();
                ps = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Title: getMappedStatement
     * Description: 获取 MappedStatement
     *
     * @param invocation 调用
     * @return MappedStatement
     */
    private MappedStatement getMappedStatement(Invocation invocation) {
        return (MappedStatement) invocation.getArgs()[0];
    }

    /**
     * Title: setMappedStatement
     * Description: 设置MappedStatement
     *
     * @param invocation      调用
     * @param mappedStatement 新的MappedStatement
     */
    private void setMappedStatement(Invocation invocation, MappedStatement mappedStatement) {
        invocation.getArgs()[0] = mappedStatement;
    }

    /**
     * Title: getParameter
     * Description: 获取sql入参
     *
     * @param invocation 调用
     * @return sql入参
     */
    private Object getParameter(Invocation invocation) {
        return invocation.getArgs()[1];
    }

    /**
     * Title: getRowBounds
     * Description: 获取行数
     *
     * @param invocation 调用
     * @return 行数范围
     */
    private RowBounds getRowBounds(Invocation invocation) {
        return (RowBounds) invocation.getArgs()[2];
    }

    /**
     * Title: setRowBounds
     * Description: 设置行数范围
     *
     * @param invocation 调用
     * @param rowBounds  行数范围
     */
    private void setRowBounds(Invocation invocation, RowBounds rowBounds) {
        invocation.getArgs()[2] = rowBounds;
    }

    /**
     * ClassName: BoundSqlSqlSource
     * Description: boundsql处理类
     *
     * @author linyl linyuliang.85@gmail.com
     */
    public static class BoundSqlSqlSource implements SqlSource {

        /**
         * boundsql
         */
        private final BoundSql boundSql;

        /**
         * Title: 构造函数
         * Description:根据新的ms和boundsql生成sqlsource
         *
         * @param ms       新的ms
         * @param boundSql 新的boundsql
         * @param sql      执行sql
         */
        public BoundSqlSqlSource(MappedStatement ms, BoundSql boundSql, String sql) {
            this.boundSql = buildBoundSql(ms, boundSql, sql);
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }

        /**
         * Title: buildBoundSql
         * Description: 入参处理
         *
         * @param ms       新的ms
         * @param boundSql 新的boundsql
         * @param sql      执行sql
         * @return 最终boundsql
         */
        private BoundSql buildBoundSql(MappedStatement ms, BoundSql boundSql, String sql) {
            BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), sql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            for (ParameterMapping mapping : boundSql.getParameterMappings()) {
                String prop = mapping.getProperty();
                if (boundSql.hasAdditionalParameter(prop)) {
                    newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
                }
            }
            return newBoundSql;
        }
    }
}