SpringBoot+Mybatis-Plus整合Sharding-JDBC实现分库分表

小鱼儿 2024-03-22 12:28 27阅读 0赞

### 一、前言 ###

小编最近一直在研究关于分库分表的东西，前几天docker安装了mycat实现了分库分表，但是都在说mycat的bug很多。很多人还是倾向于`shardingsphere`，其实他是一个全家桶，有`JDBC、Proxy 和 Sidecar`组成，小编今天以最简单的`JDBC`来简单整合一下！ 现在最新版已经是`5.1.1`，经过一天的研究用于解决了所有问题，完成了单库分表！！

### 二、踩过的坑 ###

#### 1. 数据源问题 ####

不要使用`druid-spring-boot-starter`这个依赖，启动会有问题

<dependency>-->
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid-spring-boot-starter</artifactId>
    	<version>1.1.21</version>
    /dependency>
    复制代码

报错信息：

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userMapper' defined in file 
    [D:\jiawayun\demo\target\classes\com\example\demo\mapper\UserMapper.class]:
     Invocation of init method failed; nested exception is 
     java.lang.IllegalArgumentException: Property 'sqlSessionFactory' 
     or 'sqlSessionTemplate' are required
    复制代码

==解决方案：==

使用单独的`druid`

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.8</version>
    </dependency>
    复制代码

建议使用==默认的数据源==，sharding-jdbc也是使用的默认的数据源，小编使用的自带的，忘记`druid`后面会不会有问题了！！

type: com.zaxxer.hikari.HikariDataSource
    复制代码

#### 2. Insert 语句不支持分表路由到多个数据节点 ####

报错信息： `Insert statement does not support sharding table routing to multiple data nodes.`

### 三、导入maven依赖 ###

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.10</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    
    
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.1</version>
    </dependency>
    复制代码

### 四、新建表 ###

#### 1. 新建二张表 ####

命名为：`user_0`、`user_1`

CREATE TABLE `user_0`  (
      `cid` bigint(25) NOT NULL,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`cid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    SET FOREIGN_KEY_CHECKS = 1;
    复制代码

#### 2. 数据库结构 ####

![format_png][]

### 五、框架全局展示 ###

#### 1. User实体类 ####

@Data
    public class User implements Serializable {
        private static final long serialVersionUID = 337361630075002456L;
        
        private Long cid;
        
        private String name;
    
        private String gender;
    
        private String data;
    
    }
    复制代码

#### 2. controller ####

@RestController
    @RequestMapping("/test")
    public class UserController {
    
        @Autowired
        private UserMapper userMapper;
    
        @GetMapping("/insertTest")
        public void insertTest(){
            for (int i = 1 ; i < 10; i++) {
                User test = new User("王"+i,"男","数据" + i);
                userMapper.insert(test);
            }
        }
    }
    复制代码

#### 3. mapper ####

我们直接省略了service，简单一下哈！！

public interface UserMapper extends BaseMapper<User> {
    }
    复制代码

#### 4. application.yml配置 ####

server:
      port: 8089
    
    spring:
      shardingsphere:
        mode:
          type: memory
        # 是否开启
        datasource:
          # 数据源（逻辑名字）
          names: m1
          # 配置数据源
          m1:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://localhost:3306/test?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=UTC
            username: root
            password: root
        # 分片的配置
        rules:
          sharding:
            # 表的分片策略
            tables:
              # 逻辑表的名称
              user:
                # 数据节点配置，采用Groovy表达式
                actual-data-nodes: m1.user_$->{0..1}
                # 配置策略
                table-strategy:
                  # 用于单分片键的标准分片场景
                  standard:
                    sharding-column: cid
                    # 分片算法名字
                    sharding-algorithm-name: user_inline
                key-generate-strategy: # 主键生成策略
                  column: cid  # 主键列
                  key-generator-name: snowflake  # 策略算法名称(推荐使用雪花算法)
            key-generators:
              snowflake:
                type: SNOWFLAKE
            sharding-algorithms:
              user_inline:
                type: inline
                props:
                  algorithm-expression: user_$->{cid % 2}
        props:
          # 日志显示具体的SQL
          sql-show: true
    
    
    logging:
      level:
        com.wang.test.demo: DEBUG
    
    mybatis-plus:
      mapper-locations: classpath:mapper/*.xml
      type-aliases-package: com.example.demo.entity
      configuration:
        #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射 address_book ---> addressBook
        map-underscore-to-camel-case: true
    复制代码

#### 5. 启动类 ####

@MapperScan("com.example.demo.mapper")
    @SpringBootApplication
    public class DemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(DemoApplication.class, args);
        }
    
    }
    复制代码

### 六、测试插入九条数据 ###

==本次测试策略是：行表达式分片策略:`inline`==

#### 1. 插入数据 ####

输入 ：`localhost:8089/test/insertTest`

![format_png 1][]

==分片成功==

#### 2. 单个查询 ####

@GetMapping("/selectOneTest")
    public void selectOneTest(){
    
        User user = userMapper.selectOne(Wrappers.<User>lambdaQuery().eq(User::getCid,736989417020850176L));
        System.out.println(user);
    
    }
    复制代码

**这时他会根据cid去自动获取去那个表中获取数据**

![format_png 2][]

#### 3. 全查询 ####

@GetMapping("/selectListTest")
    public void selectListTest(){
    
        List<User> list = userMapper.selectList(null);
        System.out.println(list);
    
    }
    复制代码

**由于没有条件，他会去把两个表`UNION ALL`进行汇总**

![format_png 3][]

#### 4. 分页查询 ####

需要先配置mybatis-plus分页配置类：

@Configuration
    public class MybatisPlusConfig {
    
        @Bean
        public MybatisPlusInterceptor mybatisPlusInterceptor() {
            MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
            interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
            return interceptor;
        }
    }
    复制代码

@GetMapping("/selectListPage")
    public void selectListPage(){
        IPage<User> page = new Page(1,6);
        IPage<User> userIPage = userMapper.selectPage(page,null);
        List<User> records = userIPage.getRecords();
        System.out.println(records);
    }
    复制代码

`我们user_0有5条数据，user_1有4条数据`

==我们发现它会向所有的表中去进行一遍分页查询，第一个表数据不够就会加上另一个表分页拿到的值==

![format_png 4][]

==分页size为3时，一个user\_0就可以满足分页条件，就会忽略user\_1的分页数据。==

![format_png 5][]

#### 5. 非分片属性查询 ####

我们先把`user_0表`性别修改两个为女，然后进行查询！看看没有分片的字段是否能够只去`user_0`去查询

@GetMapping("/selectListByGender")
    public void selectListByGender(){
    
        List<User> list = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getGender, "女"));
        System.out.println(list);
    }
    复制代码

**有图可见：不是分片的字段查询，回去全连接表去查询一遍，效率和不分表一样了哈！！**

![format_png 6][]

#### 6. 分片属性来自一个表in查询 ####

@GetMapping("/selectInList")
    public void selectList(){
        List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().in(User::getCid,736989417020850176L,736989418119757824L));
        System.out.println(users);
    }
    复制代码

**我们可以发现，我们根据分片字段进行in查询，sharding-jdbc会识别出来来自于那个表进而提高效率，不会所有的表进行全连接。**

![format_png 7][]

### 七、总结 ###

这样就完成了最新版的`sharding-jdbc`的简单测试和一些坑的解决，总的来说配置很费劲，不能有一定的错误！

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/098daeca925c48f995a5e9c9831d8770.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/05318fa46ef745759f09826bcffea25b.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/21aa117fdbd04842af2c302d50fdf8a0.png
[format_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/546cf34f8f28425db651331b00101626.png
[format_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/768454da2e434dc9bc72297fac2ee27e.png
[format_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/beea8f7ebd8e439592c4a65e9b0a675e.png
[format_png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/40cf5eb5b9e44dae8b335af10e463305.png
[format_png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a39bd6c9821a437b9d1ac05effaf75ed.png