java springboot mysql shardingsphere 分库分表上 (单库分表)

矫情吗；* 2023-10-04 18:48 9阅读 0赞

#### java springboot mysql shardingsphere 分库分表 ####

*   *   *  1、主要技术
         *  2、数据库脚本
         *  3、引入依赖
         *  4、单库分表
         *   *  4.1、yaml文件
             *  4.2、自定义主键生成策略
             *  4.3、spi机制
             *  4.4、新建实体类、mapper层、controller层
             *   *  4.4.1、实体类
                 *  4.4.2、mapper
                 *  4.4.3、controller
             *  4.5、测试运行
             *   *  4.5.1运行保存方法
                 *  4.5.2查看表数据
         *  5、分库分表请看下文

#### 1、主要技术 ####

> 本文主要采用SpringBoot + MyBatisPlus + sharding-jdbc技术  
> 实现：单库分表 、分库分表 水平拆分  
> 数据库：sharding0、sharding1  
> 表：emp0、emp1

#### 2、数据库脚本 ####

# 数据库自行创建
    CREATE TABLE `emp0` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `empname` varchar(20) NOT NULL DEFAULT '',
      `job` varchar(9) NOT NULL DEFAULT '',
      `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `hiredate` datetime NOT NULL,
      `sal` decimal(7,2) NOT NULL,
      `comn` decimal(7,2) NOT NULL,
      `depno` mediumint(8) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6000091 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `emp1` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `empname` varchar(20) NOT NULL DEFAULT '',
      `job` varchar(9) NOT NULL DEFAULT '',
      `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',
      `hiredate` datetime NOT NULL,
      `sal` decimal(7,2) NOT NULL,
      `comn` decimal(7,2) NOT NULL,
      `depno` mediumint(8) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6000091 DEFAULT CHARSET=utf8;

#### 3、引入依赖 ####

<dependencies>
        <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>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

#### 4、单库分表 ####

##### 4.1、yaml文件 #####

server:
      port: 7070
    spring:
      application:
        name: shadingsphere-name
      main:
        # 一个实体类对应两张表，覆盖 否则会报错
        allow-bean-definition-overriding: true
      shardingsphere:
        #配置数据源
        datasource:
          names: m1
          m1:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://localhost:3306/sharding0?serverTimezone=GMT%2B8
            username: root
            password: wuzhenyong1207
        sharding:
          tables:
            # 表名
            emp:
              # 配置表在哪个数据库里面 m1.emp0 ,m1.emp1
              actual-data-nodes: m1.emp$->{
        0..1}
              # 生成ID
              key-generator:
                column: id
                # 自定义主键生成类型  官网有两种：UUID， SNOWFLAKE 雪花算法
                type: SIMPLE
              # 指定分片策略
              table-strategy:
                inline:
                  sharding-column: id
                  algorithm-expression: emp$->{
        id %2}
        props:
          # 开启sql日志
          sql:
            show: true

##### 4.2、自定义主键生成策略 #####

> 说明：因为数据库是自增，所以采用自定义主键生成策略  
> 实际请项目请自行实现自增策略，否则会出现id重复问题，本文只是演示

/**
     * 自定义主键生成策略
     *
     * @author wuzhenyong
     * @date 2022/05/26
     */
    public class SimpleShardingKeyGenerator implements ShardingKeyGenerator {
    
        private AtomicLong atomic = new AtomicLong(0);
        @Getter
        @Setter
        private Properties properties = new Properties();
    
        @Override
        public Comparable<?> generateKey() {
            return atomic.incrementAndGet();
        }
    
        @Override
        public String getType() {
        	// 类型名称
            return "SIMPLE";
        }
    }

##### 4.3、spi机制 #####

> 在resources文件夹下新建  
> META-INF/services/  
> org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator 文件  
> 文件内容为：自定义主键生成策略所在包路径.SimpleShardingKeyGenerator

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XJscINPM-1654479995196)(C:\\Users\\lenovo\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220526135809717.png)\]][img-XJscINPM-1654479995196_C_Users_lenovo_AppData_Roaming_Typora_typora-user-images_image-20220526135809717.png]

##### 4.4、新建实体类、mapper层、controller层 #####

###### 4.4.1、实体类 ######

@TableName("emp")
    @Data
    public class EmpEntity {
        
    
        @TableId(type = IdType.AUTO)
        private Integer id;
        private Integer empno;
        private String empname;
        private String job;
        private String mgr;
        private LocalDateTime hiredate;
        private BigDecimal sal;
        private BigDecimal comn;
        private String depno;
    }

###### 4.4.2、mapper ######

@Mapper
    public interface EmpMapper extends BaseMapper<EmpEntity>{
        
       
    }

###### 4.4.3、controller ######

/**
     * @author wuzhenyong
     * ClassName:EmpController.java
     * date:2022-05-26 10:56
     * Description:
     */
    @RestController
    @RequestMapping("/emp")
    @AllArgsConstructor
    public class EmpController {
        
        private final EmpMapper empMapper;
    
        @GetMapping("/save")
        public String save() {
        
            for (int i = 0; i < 20; i++) {
        
                String value = String.valueOf(i);
                EmpEntity entity = new EmpEntity();
                entity.setEmpno(value);
                entity.setEmpname(value);
                entity.setJob(value);
                entity.setMgr(value);
                entity.setHiredate(LocalDateTime.now());
                entity.setSal(new BigDecimal(value));
                entity.setComn(new BigDecimal(value));
                entity.setDepno(value);
    
                empMapper.insert(entity);
            }
            return "添加成功";
        }
    
        @GetMapping("/list")
        public List<EmpEntity> list() {
        
            List<EmpEntity> empEntities = empMapper.selectList(new QueryWrapper<EmpEntity>()
                    .lambda()
                    .orderByDesc(EmpEntity::getId));
            return empEntities;
        }
    
        @GetMapping("/update/{id}")
        public String update(@PathVariable Integer id) {
        
            EmpEntity entity = empMapper.selectById(id);
            entity.setHiredate(LocalDateTime.now());
            empMapper.updateById(entity);
            return "更新成功";
        }
    
    }

##### 4.5、测试运行 #####

###### 4.5.1运行保存方法 ######

> 控制台日志，日志太长了显示不全

![在这里插入图片描述][2d3a016e4d1b47ec849f046517663b3e.png]

###### 4.5.2查看表数据 ######

emp0表

![在这里插入图片描述][c144d8debe984e57b5eee3be13afb76c.png]

emp1表

![在这里插入图片描述][3de1a3d1b9bf47718a93ff2c32dff0a2.png]

#### 5、分库分表请看下文 ####

[点我：java springboot mysql shardingsphere 分库分表 下 (分库分表)][java springboot mysql shardingsphere _ _]

[img-XJscINPM-1654479995196_C_Users_lenovo_AppData_Roaming_Typora_typora-user-images_image-20220526135809717.png]: https://img-blog.csdnimg.cn/28cf6f3984e04988a8571c705a751c72.png
[2d3a016e4d1b47ec849f046517663b3e.png]: https://img-blog.csdnimg.cn/2d3a016e4d1b47ec849f046517663b3e.png
[c144d8debe984e57b5eee3be13afb76c.png]: https://img-blog.csdnimg.cn/c144d8debe984e57b5eee3be13afb76c.png
[3de1a3d1b9bf47718a93ff2c32dff0a2.png]: https://img-blog.csdnimg.cn/3de1a3d1b9bf47718a93ff2c32dff0a2.png
[java springboot mysql shardingsphere _ _]: https://blog.csdn.net/A_yonga/article/details/125215530?spm=1001.2014.3001.5501