如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离

这篇文章主要介绍"如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离"，在日常操作中，相信很多人在如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

项目搭建

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")@Slf4jpublic 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@Slf4jpublic 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 List findUserList() {        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: 8018spring:  application:    name: bj-sharding-jdbc  main:    allow-bean-definition-overriding: true  profiles:    # rw-读写分离配置  table-数据分表+读写分离   dt-分库分表+读写分离    active: dtmybatis:  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实现的分库分表、读写分离"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！