如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離

這篇文章主要介紹“如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”，在日常操作中，相信很多人在如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

項目搭建

1、新建一個spring boot工程，添加依賴

    <dependencies>
        <!--核心服務(wù)-->
        <dependency>
            <groupId>top.qrainly</groupId>
            <artifactId>bj_core</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--sharding-jdbc-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <!--jmockdata-->
        <dependency>
            <groupId>com.github.jsonzou</groupId>
            <artifactId>jmockdata</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>

這里友情推薦一下，依賴里用到了朋友開源的一個工具插件JMockData，此乃開發(fā)測試之利器，強烈推薦?。?！

2、在master庫執(zhí)行sql腳本，創(chuàng)建用戶表

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;

配置主從復(fù)制的內(nèi)容請參考windows上mysql的主從配置

3、配置生成dao/domain文件 mybatis-generator配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

    <context id="DB2Tables"    targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--數(shù)據(jù)庫鏈接地址賬號密碼-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;allowMultiQueries=true" userId="root" password="123456">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!--生成Model類存放位置-->
        <javaModelGenerator targetPackage="top.qrainly.sharding.jdbc.domain" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--生成映射文件存放位置-->
        <sqlMapGenerator targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--生成Dao類存放位置-->
        <!-- 客戶端代碼，生成易于使用的針對Model對象和XML配置文件 的代碼
                type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper對象
                type="MIXEDMAPPER",生成基于注解的Java Model 和相應(yīng)的Mapper對象
                type="XMLMAPPER",生成SQLMap XML文件和獨立的Mapper接口
        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!--生成對應(yīng)表及類名-->
        <table tableName="user" domainObjectName="User" mapperName="UserDAO" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"></table>
    </context>
</generatorConfiguration>

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<User>> list() {
        List<User> userList = iUserService.findUserList();
        return JsonResult.okJsonResultWithData(userList);
    }

    @GetMapping("/add")
    public JsonResult<Boolean> 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<Boolean> 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 List<User> findUserList() {
        List<User> 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、配置文件(重點在這里)

基礎(chǔ)配置-->application.yml

server:
  port: 8018

spring:
  application:
    name: bj-sharding-jdbc
  main:
    allow-bean-definition-overriding: true
  profiles:
    # rw-讀寫分離配置  table-數(shù)據(jù)分表+讀寫分離   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

數(shù)據(jù)分表+讀寫分離配置-->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:
      # 配置數(shù)據(jù)分表
      sharding:
        tables:
          user:
            table-strategy:
              standard:
                sharding-column: id
                precise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm
            # 讀取ds_0數(shù)據(jù)源的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:
            # 置的分庫的字段，本案例是根據(jù)id進行分
            sharding-column: id
            # 置的分庫的邏輯，根據(jù)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上添加參數(shù)exclude={DataSourceAutoConfiguration.class}

ok，切換spring.profiles.active在不同配置模式下耍吧！

到此，關(guān)于“如何用springboot+mybatis+Sharding jdbc實現(xiàn)的分庫分表、讀寫分離”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>