shardingjdbc和pagehelper分页问题处理

• 问题简介
  使用shardingJdbc和PageHelper插件进行数据分页操作的时候，到第二页会报错的问题

• 问题定位
  第一页不会报错，第二页会报错，是因为Pagehelper的分页数据有一个时 long 类型，但是shardingJdbc时 Integer 类型，数据转换失败导致。
  现在覆盖PageHelper的策略逻辑，以 mysql 为例

源码

覆盖代码

import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.AbstractHelperDialect;
import com.github.pagehelper.page.PageAutoDialect;
import com.github.pagehelper.util.MetaObjectUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/*
 * 
 * 自定义方言,覆盖mysql
 *
 * @author pan jianghong
 * @version 1.0.0
 * @createdate 2023/1/17 14:51
 * @description 自定义方言,覆盖mysql
 */
@Component
public class CustomDialect extends AbstractHelperDialect implements ApplicationListener<ApplicationReadyEvent> {

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        PageAutoDialect.registerDialectAlias("mysql", CustomDialect.class);
    }

    @Override
    public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
        paramMap.put(com.github.pagehelper.Constant.PAGEPARAMETER_FIRST, Integer.parseInt(Long.toString(page.getStartRow())));
        paramMap.put(com.github.pagehelper.Constant.PAGEPARAMETER_SECOND, page.getPageSize());
        //处理pageKey
        pageKey.update(page.getStartRow());
        pageKey.update(page.getPageSize());
        //处理参数配置
        if (boundSql.getParameterMappings() != null) {
            List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
            if (page.getStartRow() == 0) {
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), com.github.pagehelper.Constant.PAGEPARAMETER_SECOND, int.class).build());
            } else {
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), com.github.pagehelper.Constant.PAGEPARAMETER_FIRST, int.class).build());
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), com.github.pagehelper.Constant.PAGEPARAMETER_SECOND, int.class).build());
            }
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("parameterMappings", newParameterMappings);
        }
        return paramMap;
    }

    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        if (page.getStartRow() == 0) {
            sqlBuilder.append("\n LIMIT ? ");
        } else {
            sqlBuilder.append("\n LIMIT ?, ? ");
        }
        return sqlBuilder.toString();
    }

}

其中 paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow()); 修改为paramMap.put(com.github.pagehelper.Constant.PAGEPARAMETER_FIRST, Integer.parseInt(Long.toString(page.getStartRow())));

newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, long.class).build()); 修改为 newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, int.class).build());