想自定义一个分页功能,但企图不侵入源码实现时遇到点问题

[changeAtLater]

想法:
面对一对多分页的情况,可以将countSql和pageSql写在一起, 用标签区分sql.自定义自定义方言实现类,以达到在拦截器做count查询和分页查询时传入不同sql的目的.

先看一份sql:

<select id="selectCatPage" resultMap="CatResult">
    select p.CAT_ID, p.CAT_NAME, t.TOY_ID, t.TOY_NAME
    from (<![CDATA[ <p> ]]>
       select distinct c.CAT_ID, c.CAT_NAME
       from CAT c
       left join TOY t on t.CAT_ID = c.CAT_ID
    <where>
        <if test="catName != null and catName != ''">and CAT_NAME like concat('%', #{catName},'%')</if>
        <if test="toyName != null and toyName != ''">and instr(t.TOY_NAME, #{toyName})</if>
    </where>
    <![CDATA[ </p> ]]>) p
    left join TOY t on t.CAT_ID = p.CAT_ID
    <where>
        <if test="toyName != null and toyName != ''">and instr(t.TOY_NAME, #{toyName})</if>
    </where>
</select>

countSql:

select distinct c.CAT_ID, c.CAT_NAME
from CAT c
left join TOY t on t.CAT_ID = c.CAT_ID
where CAT_NAME like concat('%', ?,'%') and instr(t.TOY_NAME, ?)

pageSql:

select p.CAT_ID, p.CAT_NAME, t.TOY_ID, t.TOY_NAME
from (
    select distinct c.CAT_ID, c.CAT_NAME
    from CAT c
    left join TOY t on t.CAT_ID = c.CAT_ID
    where CAT_NAME like concat('%', ?,'%') and instr(t.TOY_NAME, ?)
) p
left join TOY t on t.CAT_ID = p.CAT_ID
where instr(t.TOY_NAME, ?)

自定义方言:

public class MyMysqlHelperDialect extends MySqlDialect {

    @Override
    public String getCountSql(MappedStatement ms, BoundSql boundSql, 
                                              Object parameterObject, RowBounds rowBounds, CacheKey countKey) {
        Page<Object> page = getLocalPage();
        String countColumn = page.getCountColumn();
        String rawSql = boundSql.getSql();
        //根据<p></p>标签获取到countSql
        String sql = getDeclaredCountSql(rawSql);

        if (StringUtil.isNotEmpty(countColumn)) {
            return countSqlParser.getSmartCountSql(sql, countColumn);
        }
        return countSqlParser.getSmartCountSql(sql);
    }

    @Override
    public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, 
                                                                Page page, BoundSql boundSql, CacheKey pageKey) {
        paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
        paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
        //处理pageKey
        pageKey.update(page.getStartRow());
        pageKey.update(page.getPageSize());
        //处理参数配置
        if (boundSql.getParameterMappings() != null) {
            //统计</p>前的"?",就是参数的数量.因为limit是加在中间的,所以limit的参数也不能无脑加在最后
            int number = countQuestionMarkBeforePEnd(boundSql.getSql());
            List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
            if (page.getStartRow() == 0) {
                if (number > 0) {
                    newParameterMappings.add(number,
                            new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
                } else {
                    newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
                }
            } else {
                if (number > 0) {
                    newParameterMappings.add(number,
                            new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
                    newParameterMappings.add(number,
                            new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, long.class).build());
                } else {
                    newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, long.class).build());
                    newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
                }

            }
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("parameterMappings", newParameterMappings);
        }
        return paramMap;
    }

    @Override
    public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
        String rawSql = boundSql.getSql();
        //根据<p></p>标签获取到countSql
        String sql = getDeclaredCountSql(rawSql);

        Page page = getLocalPage();
        //支持 order by
        String orderBy = page.getOrderBy();
        if (StringUtil.isNotEmpty(orderBy)) {
            pageKey.update(orderBy);
            sql = OrderByParser.converToOrderBySql(sql, orderBy, jSqlParser);
        }
        if (page.isOrderByOnly()) {
            return rawSql.substring(0, rawSql.indexOf("<p>")) + sql + rawSql.substring(rawSql.indexOf("</p>") + 4);
        }

        String pageSql = getPageSql(sql, page, pageKey);
        if (rawSql.equals(sql)) {
            return pageSql;
        } else {
            return rawSql.substring(0, rawSql.indexOf("<p>")) + pageSql + rawSql.substring(rawSql.indexOf("</p>") + 4);
        }
    }
}

侵入修改部分(ExecutorUtil):

public abstract class ExecutorUtil {
...
public static Long executeAutoCount(Dialect dialect, Executor executor, MappedStatement countMs,
                                        Object parameter, BoundSql boundSql,
                                        RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
        Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
        //创建 count 查询的缓存 key
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        //调用方言获取 count sql
        String countSql = dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey);
        //countKey.update(countSql);
        //由于countSql是从原sql中提取出来的,参数数量或许会与原sql不一致,因此这里需要提取有效参数
        List<ParameterMapping> parameterMappings = substringParameter(boundSql);
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, parameterMappings, parameter);
        ...
    }
...
}

光从代码看executeAutoCount()方法相比于pageQuery()方法在方言中少一个可以自定义实现processParameterObject()方法.
但processParameterObject()方法中实质上会修改原始boundSql对象.
因此,我的想法是,可以将executeAutoCount()和pageQuery()方法中的new BoundSql()方法交由方言实现.

如果有其他的一对多分页方案,也可以告诉我,谢谢.