SpringBoot+JPA整合ShardingShpere实现分库分表

ShardingShpere 分库分表

​ ShardingShpere 提供来了根据某个字段分表分库的功能。

ShardingShpere 源码

版本依赖

• JDK 8
• SpringBoot 2.4.3
• ShardingShpere 5.0

创建两个数据库 ds0、ds1，每个库包含两张表 user_0、user_1、user_2。

CREATE DATABASE `ds0` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `ds1` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `user_0` (
  `id` int(11)  NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `user_1` (
  `id` int(11)  NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `user_2` (
  `id` int(11)  NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

添加 application.yml

server:
  port: 8080
spring:
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
        dialect: org.hibernate.dialect.MySQL5Dialect
        show_sql: true
  shardingsphere:
    datasource:
      names: ds0,ds1
      common:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
      ds0:
        jdbc-url: jdbc:mysql://172.28.128.3:3306/ds0?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@122
      ds1:
        jdbc-url: jdbc:mysql://172.28.128.3:3306/ds1?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@122
    rules:
      sharding:
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{ id % 2 }
          table-inline:
            type: INLINE
            props:
              algorithm-expression: user_$->{ id % 3 }
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
        tables:
          user:
            actual-data-nodes: ds$->{0..1}.user_$->{0..2}
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline
    props:
      sql-show: true

运行测试类，登陆Mysql服务器，查看结果：