【开发记录】pagehelper和sharding在一个项目中共同使用

1、解决办法：配置不同数据源，指定同一个数据库
pagehelper 使用单独的数据源
sharding 使用单独的数据源
2、目前项目中使用了sharding分表框架没有接入pagehelper
注意点：需要使用俩套代码模块
一套用来 使用 pagehelper-例如：mapper 路径为 xxx.pagemapper 映射路径为 classpath*:pagemapper/.xml
一套用来 使用 sharding - 例如：mapper 路径为 xxx.mapper 映射路径为 classpath:mapper/*.xml
分表框架配置如下

@Slf4j
@Data
@Configuration
@MapperScan()
@ConfigurationProperties()
@PropertySource()
public class MasterDataSourceConfig {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String filters;
    private String maxActive;
    private String initialSize;
    private String timeBetweenEvictionRunsMillis;
    private String minEvictableIdleTimeMillis;
    private String validationQuery;
    private String testWhileIdle;
    private String testOnReturn;
    private String testOnBorrow;
    private String maxWait;
    private String minIdle;
    private String poolPreparedStatements;
    private String maxOpenPreparedStatements;
    private String connectionProperties;

    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() throws SQLException {
        List<String> list = new ArrayList<String>();
        list.add("set names 'utf8mb4'");
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        dataSource.setFilters(filters);
        dataSource.setMaxActive(Integer.parseInt(maxActive));
        dataSource.setInitialSize(Integer.parseInt(initialSize));
        dataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(timeBetweenEvictionRunsMillis));
        dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(minEvictableIdleTimeMillis));
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));
        dataSource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));
        dataSource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));
        dataSource.setMaxWait(Long.parseLong(maxWait));
        dataSource.setMinIdle(Integer.parseInt(minIdle));
        dataSource.setPoolPreparedStatements(Boolean.parseBoolean(poolPreparedStatements));
        dataSource.setMaxOpenPreparedStatements(Integer.parseInt(maxOpenPreparedStatements));
        dataSource.setConnectionProperties(connectionProperties);
        dataSource.setConnectionInitSqls(list);
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds", dataSource);
        Map<String, Integer> yearMap = getLogicTable(dataSource);

        Properties properties = new Properties();

        // 配置 显示sql
        properties.setProperty("sql-show", "false");
        // 是否检查表 不检查启动速度增加
        properties.setProperty("check.table.metadata.enabled", "false");
        // 最大并发查询数量
        properties.setProperty("max.connections.size.per.query", "50");

        // 构建数据源
        DataSource dataSource1 = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singletonList(createShardingRuleConfiguration(yearMap)), properties);
        return dataSource1;
    }

    @Bean(name = "masterTransactionManager")
    public DataSourceTransactionManager masterTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig
                .MAPPER_LOCATION);

        for (Resource resource : resources) {
            log.info("mapper resources is {}", resource);
        }

        sessionFactory.setMapperLocations(resources);
        // mybatis 拦截插件
        // sessionFactory.setPlugins(encryptPlugin);
        return sessionFactory.getObject();
    }
    // sharding 配置
    private ShardingRuleConfiguration createShardingRuleConfiguration(Map<String, Integer> yearMap) {
        ShardingRuleConfiguration result = new ShardingRuleConfiguration();
        result.getTables().add(getMaterialTableRuleConfiguration(yearMap));
        Properties props = new Properties();
        result.getShardingAlgorithms().put("materialTableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("materialTableShardingAlgorithm", props));
        return result;
    }

    /*
     * 分表配置
     */
    private static ShardingTableRuleConfiguration getMaterialTableRuleConfiguration(Map<String, Integer> yearMap) {
        ShardingTableRuleConfiguration result = new ShardingTableRuleConfiguration(MaterialTableShardingAlgorithm.logicTable, StrUtil.format("ds.material_${{minYear}..{maxYear}}", yearMap));
        // 分片规则
        result.setTableShardingStrategy(new ComplexShardingStrategyConfiguration("information_id", "materialTableShardingAlgorithm"));
        return result;
    }

    /*
     * 雪花算法配置
     */
    private static Properties getProperties() {
        Properties result = new Properties();
        result.setProperty("worker-id", "123");
        return result;
    }

    private static Map<String, Integer> getLogicTable(DataSource dataSource) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Integer> result = new HashMap<>(2);
        int nowYear = DateUtil.year(new Date());
        result.put("maxYear", nowYear);
        result.put("minYear", nowYear);
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("select table_name from information_schema.tables WHERE table_name regexp 'material_[0-9]{4}'");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                String[] year = tableName.split("_");
                Integer tableYear = Integer.parseInt(year[1]);
                if (result.get("maxYear") <= tableYear) {
                    result.put("maxYear", tableYear);
                }
                if (result.get("minYear") >= tableYear) {
                    result.put("minYear", tableYear);
                }
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
            throw new EasybiiException("分表加载最大最小年分出错");
        } finally {
            CloseUtil.close(connection);
            CloseUtil.close(preparedStatement);
            CloseUtil.close(resultSet);
        }
        return result;
    }
}

pagehelper配置如下

@Slf4j
@Data
@Configuration
@MapperScan(basePackages = MasterDataSourceConfigTwo.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory2")
@ConfigurationProperties()
@PropertySource(e)
public class MasterDataSourceConfigTwo {


    static final String PACKAGE = "";
    static final String MAPPER_LOCATION = "";
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String filters;
    private String maxActive;
    private String initialSize;
    private String timeBetweenEvictionRunsMillis;
    private String minEvictableIdleTimeMillis;
    private String validationQuery;
    private String testWhileIdle;
    private String testOnReturn;
    private String testOnBorrow;
    private String maxWait;
    private String minIdle;
    private String poolPreparedStatements;
    private String maxOpenPreparedStatements;
    private String connectionProperties;

    @Bean(name = "masterDataSource2")
    public DataSource masterDataSource2() throws SQLException {
        List<String> list = new ArrayList<String>();
        list.add("set names 'utf8mb4'");
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        dataSource.setFilters(filters);
        dataSource.setMaxActive(Integer.parseInt(maxActive));
        dataSource.setInitialSize(Integer.parseInt(initialSize));
        dataSource.setTimeBetweenEvictionRunsMillis(Long.parseLong(timeBetweenEvictionRunsMillis));
        dataSource.setMinEvictableIdleTimeMillis(Long.parseLong(minEvictableIdleTimeMillis));
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));
        dataSource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));
        dataSource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));
        dataSource.setMaxWait(Long.parseLong(maxWait));
        dataSource.setMinIdle(Integer.parseInt(minIdle));
        dataSource.setPoolPreparedStatements(Boolean.parseBoolean(poolPreparedStatements));
        dataSource.setMaxOpenPreparedStatements(Integer.parseInt(maxOpenPreparedStatements));
        dataSource.setConnectionProperties(connectionProperties);
        dataSource.setConnectionInitSqls(list);
        return dataSource;
    }

    @Bean(name = "masterTransactionManager2")
    public DataSourceTransactionManager masterTransactionManager2() throws SQLException {
        return new DataSourceTransactionManager(masterDataSource2());
    }

    @Bean(name = "masterSqlSessionFactory2")
    public SqlSessionFactory masterSqlSessionFactory2(@Qualifier("masterDataSource2") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfigTwo
                .MAPPER_LOCATION);

        for (Resource resource : resources) {
            log.info("mapper resources is {}", resource);
        }
        sessionFactory.setMapperLocations(resources);

        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        // 设置合理分页参数等配置
        properties.setProperty("reasonable", "true");
        properties.setProperty("helperDialect", "mysql");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("params", "count=countSql");
        pageInterceptor.setProperties(properties);
        sessionFactory.setPlugins(new Interceptor[]{pageInterceptor});
        return sessionFactory.getObject();
    }

}