ShardingSphere5.1.0 +JPA的分表配置

模拟场景，公司表（不分表）， 员工表与人员表根据租户标志分为多个表

项目主要根据官方文档编写
官方文档
建议大家根据官方文档配置其他的分库，读写分离等

项目pom信息

<?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.6.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.aiwenoyo.demo</groupId>
    <artifactId>sharding</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding</name>
    <description>分库分表</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</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>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>5.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>

实体信息

ShareCompany

@Data
@Entity
public class ShareCompany {

    @Id
    @Column(length = 64)
    @GeneratedValue(generator = "uuidGenerator")
    @GenericGenerator(name="uuidGenerator", strategy="org.hibernate.id.UUIDGenerator")
    private String id;

    @Column(length = 100)
    private String companyName;

    @Column(length = 64, nullable = false)
    private String tenantId;

    public ShareCompany() {
    }

    public ShareCompany(String companyName, String tenantId) {
        this.companyName = companyName;
        this.tenantId = tenantId;
    }
}

ShareEmployee

package com.aiwenoyo.demo.sharding.entity;

import lombok.Data;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;

@Data
@Entity
public class ShareEmployee {

    @Id
    @Column(length = 64)
    private String id;

    @ManyToOne
    @JoinColumn(name = "company_id")
    private ShareCompany company;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private ShareUser user;

    @Column(length = 100)
    private String empName;

    @Column
    private Integer tenantId;

    public ShareEmployee() {
    }

    public ShareEmployee(ShareCompany company, ShareUser user, String empName, Integer tenantId) {
        this.company = company;
        this.user = user;
        this.empName = empName;
        this.tenantId = tenantId;
    }

    public ShareEmployee(String id, ShareCompany company, ShareUser user, String empName, Integer tenantId) {
        this.id = id;
        this.company = company;
        this.user = user;
        this.empName = empName;
        this.tenantId = tenantId;
    }
}

ShareUser

package com.aiwenoyo.demo.sharding.entity;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Data
@Entity
public class ShareUser {

    @Id
    @Column(length = 64)
    private String id;

    @Column(length = 20)
    private String userCode;

    @Column(length = 100)
    private String userName;

    @Column
    private Integer tenantId;

    public ShareUser() {
    }

    public ShareUser(String userCode, String userName, Integer tenantId) {
        this.userCode = userCode;
        this.userName = userName;
        this.tenantId = tenantId;
    }

    public ShareUser(String id, String userCode, String userName, Integer tenantId) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.tenantId = tenantId;
    }
}

数据操作DAO

package com.aiwenoyo.demo.sharding.dao;

import com.aiwenoyo.demo.sharding.entity.ShareCompany;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ShareCompanyDao extends JpaRepository<ShareCompany, String> {
}

package com.aiwenoyo.demo.sharding.dao;

import com.aiwenoyo.demo.sharding.entity.ShareEmployee;
import com.aiwenoyo.demo.sharding.model.ShareQueryModel;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ShareEmployeeDao extends JpaRepository<ShareEmployee, String> {

    @Query("select new com.aiwenoyo.demo.sharding.model.ShareQueryModel(c.id, c.companyName, e.id, e.empName, u.id, u.userCode, u.userName, e.tenantId)" +
            " from ShareCompany c" +
            " inner join ShareEmployee e on e.company = c" +
            " left join ShareUser u on e.user = u and e.tenantId = u.tenantId")
    List<ShareQueryModel> queryAllExt();

    @Query("select new com.aiwenoyo.demo.sharding.model.ShareQueryModel(c.id, c.companyName, e.id, e.empName, u.id, u.userCode, u.userName, e.tenantId)" +
            " from ShareCompany c" +
            " inner join ShareEmployee e on e.company = c" +
            " left join ShareUser u on e.user = u and e.tenantId = u.tenantId")
    Page<ShareQueryModel> queryAllExt(Pageable pageable);
}

package com.aiwenoyo.demo.sharding.dao;

import com.aiwenoyo.demo.sharding.entity.ShareUser;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ShareUserDao extends JpaRepository<ShareUser, String> {
}

查询模型

package com.aiwenoyo.demo.sharding.model;

import lombok.Data;

@Data
public class ShareQueryModel {

    private String companyId;

    private String companyName;

    private String employeeId;

    private String empName;

    private String userId;

    private String userCode;

    private String userName;

    private Integer tenantId;

    public ShareQueryModel() {
    }

    public ShareQueryModel(String companyId, String companyName,
                           String employeeId, String empName,
                           String userId, String userCode, String userName, Integer tenantId) {
        this.companyId = companyId;
        this.companyName = companyName;
        this.employeeId = employeeId;
        this.empName = empName;
        this.userId = userId;
        this.userCode = userCode;
        this.userName = userName;
        this.tenantId = tenantId;
    }
}

application.properties

server.port=6080

#thymeleaf start
spring.thymeleaf.mode=HTML
spring.thymeleaf.encoding=UTF-8
#开发时关闭缓存,不然没法看到实时页面
spring.thymeleaf.cache=false
#thymeleaf end


spring.shardingsphere.enabled=true
spring.shardingsphere.datasource.names=master
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/pumkin_study?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8&nullCatalogMeansCurrent=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=000000

spring.shardingsphere.rules.sharding.binding-tables[0]=share_employee,share_user

spring.shardingsphere.rules.sharding.tables.share_employee.actual-data-nodes=master.share_employee$->{1..5}
spring.shardingsphere.rules.sharding.tables.share_employee.table-strategy.standard.sharding-column=tenant_id
spring.shardingsphere.rules.sharding.tables.share_employee.table-strategy.standard.sharding-algorithm-name=share-employee-sharding
spring.shardingsphere.rules.sharding.sharding-algorithms.share-employee-sharding.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.share-employee-sharding.props.algorithm-expression=share_employee$->{tenant_id}
spring.shardingsphere.rules.sharding.tables.share_employee.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.share_employee.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.tables.share_user.actual-data-nodes=master.share_user$->{1..5}
spring.shardingsphere.rules.sharding.tables.share_user.table-strategy.standard.sharding-column=tenant_id
spring.shardingsphere.rules.sharding.tables.share_user.table-strategy.standard.sharding-algorithm-name=share-user-sharding
spring.shardingsphere.rules.sharding.sharding-algorithms.share-user-sharding.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.share-user-sharding.props.algorithm-expression=share_user$->{tenant_id}
spring.shardingsphere.rules.sharding.tables.share_user.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.share_user.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE

spring.shardingsphere.props.sql-show=true

#JPA 配置
spring.jpa.hibernate.ddl-auto=none
#在事务外也可以访问懒加载的数据
spring.jpa.open-in-view=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

#logging.level.root=DEBUG

测试

1. 数据插入
2. 没有租户的查询
3. 有租户字段的查询
4. 分页查询
5. 数据删除
6. 数据更新