模拟场景,公司表(不分表), 员工表与人员表根据租户标志分为多个表
项目主要根据官方文档编写
官方文档
建议大家根据官方文档配置其他的分库,读写分离等
项目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
测试
- 数据插入
- 没有租户的查询
- 有租户字段的查询
- 分页查询
- 数据删除
- 数据更新