springboot、JPA整合shardingsphere实现读写分离和分表

提前搭建好主从数据库

单机Centos7搭建mysql5.7主备/主从（docker）

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>Sharding-jdbc-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Sharding-jdbc-demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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>

        <!--shardingsphere 读写分离-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>-->
<!--&lt;!&ndash;            <version>4.0.0-RC1</version>&ndash;&gt;-->
<!--        </dependency>-->

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.1.1</version>
        </dependency>


        <!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>shardingsphere-jdbc-core</artifactId>-->
<!--            <version>5.1.2</version>-->
<!--        </dependency>-->
<!--        <dependency>-->
<!--            <groupId>org.apache.shardingsphere</groupId>-->
<!--            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>-->
<!--            <version>5.0.0-alpha</version>-->
<!--        </dependency>-->
                <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

创建表users1、users2

DROP TABLE IF EXISTS `users1`;
CREATE TABLE `users1`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 749335416569597953 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `users2`;
CREATE TABLE `users2`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 749335416569597953 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

创建实体（逻辑表是users，实际是users1和users2）

@Table(name = "users")
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UsersEntity{

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name")
    private String name;
}

public interface UsersRepo extends BaseRepo<UsersEntity, Integer> {
    UsersEntity findByName(String name);
}

创建测试接口

@RestController
@RequestMapping("/api/test")
public class TestController {
    
    @Autowired
    UsersRepo usersRepo;


    @GetMapping("/add")
    public String addUser(@RequestParam String username){
        UsersEntity usersEntity = new UsersEntity();
        usersEntity.setName(username);
        usersRepo.save(usersEntity);
        return "success";
    }

    @GetMapping("/search")
    public UsersEntity searchUser(@RequestParam String username){
        UsersEntity usersEntity = usersRepo.findByName(username);
        return usersEntity;
    }

}

配置文件（核心）

spring:
  main:
    allow-bean-definition-overriding: true #当遇到同样名字的时候，是否允许覆盖注册
  shardingsphere:
    datasource:
      #主从库别名
      names: master, slave
      master:
        jdbc-url: jdbc:mysql://127.0.0.1:3307/slave-test?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: "123456"
      slave:
        jdbc-url: jdbc:mysql://127.0.0.1:3308/slave-test?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        username: root
        password: "123456"
    #数据分表规则
    #指定所需分的表
    sharding:
      master-slave-rules: #主从规则
        master: #数据库名称或者别名
          master-data-source-name: master
          slave-data-source-names: slave
      tables: #分表规则
        users: #需要分片的逻辑表
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 1
          actual-data-nodes: master.users$->{1..2} #数据库实际表
          table-strategy:
              inline:
                sharding-column: id
                algorithm-expression: users$->{id % 2 + 1}
#              sharding-column: id
#              sharding-algorithm-name: table_inline
#              sharding-algorithms:
#                table-inline:
#                  type: INLINE
#                  props:
#                   algorithm-expression: users$->{id % 2}



#    masterslave:
#      name: ds_m_s
#      #主从规则轮询
#      load-balance-algorithm-type: round_robin
#      master-data-source-name: master
#      slave-data-source-names: slave

    props:
      sql.show: true #开启sql日志

  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
    database: mysql
    show-sql: true
    properties:
      enable: true
      bootstrap-mode: default
      hibernate:
        format_sql: true
        enable_lazy_load_no_trans: true

启动项目，展示效果