springboot整合shardingsphere JDBC，雪花算法

最新推荐文章于 2024-06-02 21:48:50 发布

tcoding

最新推荐文章于 2024-06-02 21:48:50 发布

阅读量891

点赞数 1

分类专栏： springboot Jpa 文章标签： spring boot java spring 数据库 shardingsphere

本文链接：https://blog.csdn.net/qq_23934475/article/details/126839389

版权

springboot 同时被 2 个专栏收录

19 篇文章 1 订阅

订阅专栏

Jpa

3 篇文章 0 订阅

订阅专栏

springBoot 版本 2.7.3
shardingsphere版本5.2.0

依赖

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.0</version>
        </dependency>
        <!--我这里使用的jpa-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

数据准备

两个数据库，每个库中一张表，也可以多张，这里演示一张的情况

create table hello_spring_boot.sys_user
(
    id             bigint unsigned                     not null
        primary key,
    user_name      varchar(20)                         not null,
    group_id       bigint unsigned                     not null,
    password       varchar(50)                         not null,
    phone_number   varchar(50)                         not null,
    c_time         timestamp default CURRENT_TIMESTAMP null,
    u_time         timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
);

create table hello_spring_boot_2.sys_user
(
    id             bigint unsigned                     not null
        primary key,
    user_name      varchar(20)                         not null,
    group_id       bigint unsigned                     not null,
    password       varchar(50)                         not null,
    phone_number   varchar(50)                         not null,
    c_time         timestamp default CURRENT_TIMESTAMP null,
    u_time         timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
);

application.yaml编写

# shardingsphere读写分离。分片篇日志文档 https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/
spring:
  shardingsphere:
    datasource: # 配置数据源
      names: ds_0, ds_1 # 分库的逻辑库名,可配置多个
      ds_0: # 逻辑库名
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/hello_spring_boot?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
      ds_1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/hello_spring_boot_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456
    props:
      sql:
        show: true # 开启sql日志输出
    rules:
      sharding:
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              # 分片算法的行表达式（算法自行定义，此处为方便演示效果）
              algorithm-expression: ds_$->{id % 2}
          table-inline:
            type: INLINE
            props:
              # 分片算法的行表达式
              algorithm-expression: sys_user
        default-key-generate-strategy:
          column: id
          key-generator-name: snowflake
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
        tables:
          sys_user:
            actual-data-nodes: ds_${0..1}.sys_user
            database-strategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: database-inline
                # 分表策略
            table-strategy:
              standard:
                # 分片列名称
                sharding-column: id
                # 分片算法名称
                sharding-algorithm-name: table-inline
            keyGeneratorColumnName: id
            keyGenerateStrategy: # 分布式序列策略
              column: id # 自增列名称，缺省表示不使用自增主键生成器
              keyGeneratorName: snowflake
              props:
                worker-id: 1

model

@Data
@Entity
@Table(name = "sys_user")
public class SysUser {

    @Id
    //要指定strategy = GenerationType.IDENTITY，不然雪花算法不生效
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_name", length = 20)
    private String userName;

    private Long groupId;

    private String password;

    private String phoneNumber;

    private Date cTime;

    private Date uTime;
}

repository

public interface SysUserRepository extends JpaRepository<SysUser, Long>, JpaSpecificationExecutor<SysUser> {

}

test

@SpringBootTest
class ShardingSphereApplicationTests {

    @Autowired
    SysUserRepository sysUserRepository;


    @Test
    void contextLoads() {
        for (long i = 0; i < 10L; i++) {
            SysUser user = new SysUser();
            user.setUserName("sharding");
            user.setGroupId(1L);
            user.setPassword("123456");
            user.setPhoneNumber("123456");
            sysUserRepository.save(user);
        }
    }
}