springboot+mybatis+mysql+sharding-jdbc如何实现分库分表
这篇文章主要介绍springboot + mybatis + MySQL + sharding-jdbc如何实现分库分表,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!
专注于为中小企业提供成都网站制作、网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业朔州免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了超过千家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
配置文件
mybatis.config-locations=classpath:mybatis/mybatis-config.xml #datasource spring.devtools.remote.restart.enabled=false #data source1 spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver spring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg1?serverTimezone=UTC spring.datasource.test1.username=root spring.datasource.test1.password=123456 #data source2 spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg2?serverTimezone=UTC spring.datasource.test2.username=root spring.datasource.test2.password=123456
在数据库添加表结构
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for t_order_0 -- ---------------------------- DROP TABLE IF EXISTS `t_order_0`; CREATE TABLE `t_order_0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` varchar(32) DEFAULT NULL COMMENT '顺序编号', `user_id` varchar(32) DEFAULT NULL COMMENT '用户编号', `userName` varchar(32) DEFAULT NULL COMMENT '用户名', `passWord` varchar(32) DEFAULT NULL COMMENT '密码', `nick_name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_order_1 -- ---------------------------- DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` varchar(32) DEFAULT NULL COMMENT '顺序编号', `user_id` varchar(32) DEFAULT NULL COMMENT '用户编号', `userName` varchar(32) DEFAULT NULL COMMENT '用户名', `passWord` varchar(32) DEFAULT NULL COMMENT '密码', `nick_name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
数据源配置和Mybatis配置和分库分表规则配置
@Configuration @MapperScan(basePackages = "com.example.shardingjdbc.mapper", sqlSessionTemplateRef = "test1SqlSessionTemplate") public class DataSourceConfig { /** * 配置数据源0,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ @Bean(name="dataSource0") @ConfigurationProperties(prefix = "spring.datasource.test1") public DataSource dataSource0(){ return DataSourceBuilder.create().build(); } /** * 配置数据源1,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ @Bean(name="dataSource1") @ConfigurationProperties(prefix = "spring.datasource.test2") public DataSource dataSource1(){ return DataSourceBuilder.create().build(); } /** * 配置数据源规则,即将多个数据源交给sharding-jdbc管理,并且可以设置默认的数据源, * 当表没有配置分库规则时会使用默认的数据源 * @param dataSource0 * @param dataSource1 * @return */ @Bean public DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0, @Qualifier("dataSource1") DataSource dataSource1){ MapdataSourceMap = new HashMap<>(); //设置分库映射 dataSourceMap.put("dataSource0", dataSource0); dataSourceMap.put("dataSource1", dataSource1); return new DataSourceRule(dataSourceMap, "dataSource0"); //设置默认库,两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一 } /** * 配置数据源策略和表策略,具体策略需要自己实现 * @param dataSourceRule * @return */ @Bean public ShardingRule shardingRule(DataSourceRule dataSourceRule){ //具体分库分表策略 TableRule orderTableRule = TableRule.builder("t_order") .actualTables(Arrays.asList("t_order_0", "t_order_1")) .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())) .dataSourceRule(dataSourceRule) .build(); //绑定表策略,在查询时会使用主表策略计算路由的数据源,因此需要约定绑定表策略的表的规则需要一致,可以一定程度提高效率 List bindingTableRules = new ArrayList (); bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule))); return ShardingRule.builder() .dataSourceRule(dataSourceRule) .tableRules(Arrays.asList(orderTableRule)) .bindingTableRules(bindingTableRules) .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm())) .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())) .build(); } /** * 创建sharding-jdbc的数据源DataSource,MybatisAutoConfiguration会使用此数据源 * @param shardingRule * @return * @throws SQLException */ @Bean(name="dataSource") public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException { return ShardingDataSourceFactory.createDataSource(shardingRule); } /** * 需要手动配置事务管理器 * @param dataSource * @return */ @Bean public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){ return new DataSourceTransactionManager(dataSource); } @Bean(name = "test1SqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml")); return bean.getObject(); } @Bean(name = "test1SqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
分库规则
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm{ @Override public String doEqualSharding(Collection databaseNames, ShardingValue shardingValue) { for (String each : databaseNames) { if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) { return each; } } throw new IllegalArgumentException(); } @Override public Collection doInSharding(Collection databaseNames, ShardingValue shardingValue) { Collection result = new LinkedHashSet<>(databaseNames.size()); for (Long value : shardingValue.getValues()) { for (String tableName : databaseNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } return result; } @Override public Collection doBetweenSharding(Collection databaseNames, ShardingValue shardingValue) { Collection result = new LinkedHashSet<>(databaseNames.size()); Range range = (Range ) shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : databaseNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } }
分表规则
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm{ @Override public String doEqualSharding(Collection tableNames, ShardingValue shardingValue) { for (String each : tableNames) { if (each.endsWith(shardingValue.getValue() % 2 + "")) { return each; } } throw new IllegalArgumentException(); } @Override public Collection doInSharding(Collection tableNames, ShardingValue shardingValue) { Collection result = new LinkedHashSet<>(tableNames.size()); for (Long value : shardingValue.getValues()) { for (String tableName : tableNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } return result; } @Override public Collection doBetweenSharding(Collection tableNames, ShardingValue shardingValue) { Collection result = new LinkedHashSet<>(tableNames.size()); Range range = (Range ) shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : tableNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } }
取消自动配置数据源,使用我们这里定义的数据源配置
@SpringBootApplication @EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion @EnableTransactionManagement(proxyTargetClass = true) //开启事物管理功能 public class ShardingJdbcApplication { public static void main(String[] args) { SpringApplication.run(ShardingJdbcApplication.class, args); } }
使用测试
@Service @RestController public class UserController { @Autowired private UserService userService; /** * 测试新增 * @param id * @param user_id * @param order_id * @param nickName * @param passWord * @param userName * @return * http://localhost:8080/update1?id=1&user_id=1&order_id=1&nickName=%E5%BC%A0%E4%B8%89&passWord=123456&userName=%E7%94%A8%E6%88%B71 */ @RequestMapping(value="update1") public String updateTransactional(@RequestParam(value = "id") Long id, @RequestParam(value = "user_id") Long user_id, @RequestParam(value = "order_id") Long order_id, @RequestParam(value = "nickName") String nickName, @RequestParam(value = "passWord") String passWord, @RequestParam(value = "userName") String userName ) { User user2 = new User(); user2.setId(id); user2.setUser_id(user_id); user2.setOrder_id(order_id); user2.setNick_name(nickName); user2.setPassWord(passWord); user2.setUserName(userName); userService.insert(user2); return "success"; } }
以上是“springboot + mybatis + mysql + sharding-jdbc如何实现分库分表”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注创新互联行业资讯频道!
网页名称:springboot+mybatis+mysql+sharding-jdbc如何实现分库分表
新闻来源:http://pwwzsj.com/article/jjdpsi.html