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();
}
}