mysql分库分表按时间_Sharding-JDBC 按日期时间分库分表

importjava.sql.SQLException;importjava.util.HashMap;importjava.util.Map;importjava.util.Properties;importjava.util.concurrent.ConcurrentHashMap;importjavax.sql.DataSource;importorg.apache.commons.dbcp.BasicDataSource;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.springframework.beans.factory.annotation.Value;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;importorg.springframework.core.io.support.PathMatchingResourcePatternResolver;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importio.shardingjdbc.core.api.config.ShardingRuleConfiguration;importio.shardingjdbc.core.api.config.TableRuleConfiguration;importio.shardingjdbc.core.api.config.strategy.StandardShardingStrategyConfiguration;importio.shardingjdbc.core.jdbc.core.datasource.ShardingDataSource;

@Configurationpublic classShardingDataSourceConfiguration {

@Value("${spring.datasource.username:root}")privateString username;

@Value("${spring.datasource.password:123456}")privateString password;

@Value("${spring.datasource.url}")privateString jdbcUrl;

@Value("${spring.datasource.driver-class-name}")privateString driverClassName;

@Value("${shardingjdbc.sql.show:true}")privateString sqlShow;

@Value("${mybatis.mapper-locations:mappper/**/*.xml}")privateString mapperLocations;//配置sharding-jdbc的DataSource，给上层应用使用，这个DataSource包含所有的逻辑库和逻辑表，应用增删改查时，修改对应sql//然后选择合适的数据库继续操作。因此这个DataSource创建很重要。

@Bean

@Primary

@ConfigurationProperties(prefix= "spring.datasource")public DataSource shardingDataSource() throwsSQLException {

ShardingRuleConfiguration shardingRuleConfig= newShardingRuleConfiguration();//订单表配置，可以累计添加多个配置

shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());//shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());//打印SQL

Properties props = newProperties();

props.put("sql.show", sqlShow);return newShardingDataSource(shardingRuleConfig.build(createDataSourceMap()),new ConcurrentHashMap(), props);

}//创建用户表规则

@Bean

TableRuleConfiguration getOrderTableRuleConfiguration() {

TableRuleConfiguration orderTableRuleConfig= newTableRuleConfiguration();

orderTableRuleConfig.setLogicTable("t_order");

orderTableRuleConfig.setLogicIndex("sys_time");//设置数据库策略，传入的是sys_time

orderTableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("sys_time", DatabaseShardingAlgorithm.class.getName()));//设置数据表策略，传入的是sys_time

orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("sys_time", TableShardingAlgorithm.class.getName()));//设置数据节点，格式为dbxx.tablexx。这里的名称要和map的别名一致。下面两种方式都可以//orderTableRuleConfig.setActualDataNodes("db_${0..1}.t_order_${0..1}");

orderTableRuleConfig.setActualDataNodes("db_201906.t_order_20190614,db_201906.t_order_20190615,db_201907.t_order_20190714,db_201907.t_order_20190715");//设置纵列名称//orderTableRuleConfig.setKeyGeneratorColumnName("ID");

returnorderTableRuleConfig;

}//下面函数是获取数据源，即包含有多少个数据库，读入到系统中存放于map中

private MapcreateDataSourceMap() {

Map result = new HashMap<>();

result.put("db_201906",

createDataSource("jdbc:mysql://localhost:3306/db_201906?characterEncoding=utf8&useSSL=false"));

result.put("db_201907",

createDataSource("jdbc:mysql://localhost:3306/db_201907?characterEncoding=utf8&useSSL=false"));returnresult;

}private DataSource createDataSource(finalString jdbcUrl) {//使用默认连接池

BasicDataSource result = newBasicDataSource();//指定driver的类名，默认从jdbc url中自动探测

result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());//设置数据库路径

result.setUrl(jdbcUrl);//设置数据库用户名

result.setUsername(username);//设置数据密码

result.setPassword(password);returnresult;

}

@Bean("sqlSessionFactory")

@Primarypublic SqlSessionFactory sqlSessionFactory(DataSource shardingDataSource) throwsException {

SqlSessionFactoryBean sessionFactory= newSqlSessionFactoryBean();

sessionFactory.setDataSource(shardingDataSource);

PathMatchingResourcePatternResolver resolver= newPathMatchingResourcePatternResolver();

sessionFactory.setMapperLocations(resolver.getResources(mapperLocations));returnsessionFactory.getObject();

}/*** - 需要手动配置事务管理器*/@BeanpublicDataSourceTransactionManager transactitonManager(DataSource shardingDataSource) {return newDataSourceTransactionManager(shardingDataSource);

}

@BeanpublicSqlSessionTemplate sqlSessionTmplate(SqlSessionFactory sqlSessionFactory) {

SqlSessionTemplate sqlSessionTmplate= newSqlSessionTemplate(sqlSessionFactory);returnsqlSessionTmplate;

}

}