SpringBoot+JPA整合ShardingShpere实现分表

ShardingShpere 简介

Apache ShardingSphere 是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

ShardingShpere 分表

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

ShardingShpere 源码

版本依赖

• JDK 8
• SpringBoot 2.4.3
• ShardingShpere 5.0

创建一个数据库和两张表，

CREATE DATABASE `shop` 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;

添加maven依赖

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

        <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>

        <!-- ShardingSphere -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-alpha</version>
        </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.18</version>
        </dependency>

创建application.yml

server:
  port: 8080
spring:
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
        dialect: org.hibernate.dialect.MySQL5Dialect
        show_sql: true
  shardingsphere:
    datasource:  #数据源配置
      names: ds0
      common:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
      ds0:
        jdbc-url: jdbc:mysql://172.28.128.3:3306/shop?serverTimezone=UTC&useSSL=false
        username: root
        password: Test@122
    rules:
      sharding:
        sharding-algorithms:  #分片算法配置
          table-inline:
            type: INLINE
            props: #在本库中分表策略，按照ID分表，通过ID除以2取模，如果为1就存到t_user1,如果为2就存到t_user2
              algorithm-expression: user_$->{ id % 2 }
        key-generators:  #主键生成策略
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1
        tables:  #分表策略
          user:
            actual-data-nodes: ds0.user_$->{0..1}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline
    props:
      sql-show: true

User实体类

@Entity
public class User {
    @Id
//    @GeneratedValue(strategy= GenerationType.AUTO)
    private Integer id;

    private String name;

    private String email;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

UserRepository 类

public interface UserRepository extends CrudRepository<User, Integer> {
  	
}

添加测试类

@SpringBootTest
public class ApplicationTests {
    @Autowired
    UserRepository userRepository;

    @Test
    void saveUserTest(){
        for(int i = 1; i < 10 ;i++){
            User user=new User();
            user.setId(i);
            user.setName("name"+i);
            user.setEmail("someemai"+i+"l@someemailprovider.com");
            userRepository.save(user);
        }
    }
}

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

参考

https://shardingsphere.apache.org/document/current/cn/overview/

https://github.com/apache/shardingsphere/tree/master/examples

问题：Factory method ‘shardingSphereDataSource’ threw exception; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException:

数据源文件配置有误，把注释删除。

问题：om.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Error thrown while acquiring connection from data source

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

java.sql.SQLSyntaxErrorException: Table ‘shop1.user_2’ doesn’t exist

数据库表不存在，在库中创建表。

问题：org.springframework.orm.jpa.JpaSystemException: could not read a hi value - you need to populate the table: hibernate_sequence; nested exception is org.hibernate.ids

解决：在实体类属性ID上去掉 @GeneratedValue(strategy= GenerationType.AUTO)

主键增长方式由数据库自动选择，当数据库选择AUTO方式时就会自动生成hibernate_sequence表。