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

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

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运行保存方法

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

4.5.2查看表数据

emp0表

emp1表

5、分库分表请看下文

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