大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
这篇文章主要介绍“如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”,在日常操作中,相信很多人在如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
巴南网站建设公司创新互联建站,巴南网站设计制作,有大型网站制作公司丰富经验。已为巴南1000+提供企业网站建设服务。企业网站搭建\成都外贸网站制作要多少钱,请找那个售后服务好的巴南做网站的公司定做!
1、新建一个spring boot工程,添加依赖
top.qrainly bj_core 0.0.1-SNAPSHOT org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2 com.alibaba druid-spring-boot-starter 1.1.10 io.shardingsphere sharding-jdbc-spring-boot-starter 3.1.0.M1 com.github.jsonzou jmockdata 4.1.2
这里友情推荐一下,依赖里用到了朋友开源的一个工具插件JMockData,此乃开发测试之利器,强烈推荐!!!
2、在master库执行sql脚本,创建用户表
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_0`; CREATE TABLE `user_0` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_3`; CREATE TABLE `user_3` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_4`; CREATE TABLE `user_4` ( `id` INT(12) NOT NULL AUTO_INCREMENT, `username` VARCHAR(12) NOT NULL, `password` VARCHAR(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
配置主从复制的内容请参考windows上MySQL的主从配置
3、配置生成dao/domain文件mybatis-generator配置
4、提供一个查询和添加的接口
controller
/** * @author v_liuwen * @date 2019-07-10 */ @RestController @RequestMapping(value = "/user") @Slf4j public class UserController { @Autowired private IUserService iUserService; @GetMapping("/list") public JsonResult> list() { List
userList = iUserService.findUserList(); return JsonResult.okJsonResultWithData(userList); } @GetMapping("/add") public JsonResult add(@RequestParam(name = "user",required = false) User user) { log.info("新增用户信息-->{}", JSONObject.toJSONString(user)); boolean result = iUserService.addUser(); return JsonResult.okJsonResultWithData(result); } @GetMapping("/batchAdd") public JsonResult batchAdd() { boolean result = iUserService.batchAddUser(); return JsonResult.okJsonResultWithData(result); } }
service
/** * @author v_liuwen * @date 2019-07-10 */ @Service @Slf4j public class IUserServiceImpl implements IUserService { private AtomicInteger num = new AtomicInteger(1); @Resource private UserDAO userDAO; @Override public boolean addUser() { User user = JMockData.mock(User.class); int i = userDAO.insertSelective(user); if(i == 1){ return true; } return false; } @Override public ListfindUserList() { List userList = userDAO.findUserList(); return userList; } @Override public boolean batchAddUser() { try{ for (int i =100;i<150;i++){ User user = JMockData.mock(User.class); user.setId(num.getAndIncrement()); userDAO.insertSelective(user); } return true; }catch (Exception e){ log.error("批量插入失败 失败原因-->{}",e.getMessage()); return false; } } }
5、配置文件(重点在这里)
基础配置-->application.yml
server: port: 8018 spring: application: name: bj-sharding-jdbc main: allow-bean-definition-overriding: true profiles: # rw-读写分离配置 table-数据分表+读写分离 dt-分库分表+读写分离 active: dt mybatis: mapper-locations: classpath:/top/qrainly/**/dao/**/*.xml
读写分离配置-->application-rw.yml
sharding: jdbc: dataSource: names: db-test0,db-test1 db-test0: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 db-test1: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 config: # 仅配置读写分离时打开此配置 masterslave: # 配置从库选择策略,提供轮询与随机,这里选择用轮询//random 随机 //round_robin 轮询 load-balance-algorithm-type: round_robin name: db1s2 master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!! show: true
数据分表+读写分离配置-->application-table.yml
sharding: jdbc: dataSource: names: db-test0,db-test1 db-test0: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 db-test1: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 config: # 配置数据分表 sharding: tables: user: table-strategy: standard: sharding-column: id precise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm # 读取ds_0数据源的user_0、user_1、user_2、user_3 actual-data-nodes: ds_0.user_$->{0..3} master-slave-rules: ds_0: master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志!!! show: true
分库分表+读写分离配置-->application-dt.yml
--- sharding: jdbc: datasource: names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-1-slave-0 # 主库0 ds-master-0: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root # 主库0-从库0 ds-master-0-slave-0: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root # 主库1 ds-master-1: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root # 主库1-从库0 ds-master-1-slave-0: password: 123456 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root config: sharding: tables: user: table-strategy: inline: sharding-column: id algorithm-expression: user_$->{id % 5} key-generator-column-name: id actual-data-nodes: ds_$->{0..1}.user_$->{0..4} default-database-strategy: inline: # 置的分库的字段,本案例是根据id进行分 sharding-column: id # 置的分库的逻辑,根据id%2进行分 algorithm-expression: ds_$->{id % 2} master-slave-rules: ds_1: slave-data-source-names: ds-master-1-slave-0 master-data-source-name: ds-master-1 ds_0: slave-data-source-names: ds-master-0-slave-0 master-data-source-name: ds-master-0
注:分库分表配置下需要在@SpringBootApplication上添加参数exclude={DataSourceAutoConfiguration.class}
ok,切换spring.profiles.active在不同配置模式下耍吧!
到此,关于“如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!