环境搭建
主要分为以下几个步骤：
- 准备数据库环境
- 创建工程
- 配置依赖
- 配置yaml参数
- 创建实体类和资源操作类
- 测试环境
-
准备数据库环境
创建两个库，在库中创建两个相同的position表CREATE DATABASE sharding1; USE sharding1; CREATE TABLE `position` ( `id` BIGINT AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `city` VARCHAR(50) NOT NULL, `salary` INT, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE DATABASE sharding2; USE sharding2; CREATE TABLE `position` ( `id` BIGINT AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `city` VARCHAR(50) NOT NULL, `salary` INT, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -
创建一个新的maven父工程
-
创建子工程
sharding-jdbc-example -
修改父工程的pom文件，添加下面的内容
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
<shardingsphere.version>4.1.1</shardingsphere.version>
<springboot.version>2.2.9.RELEASE</springboot.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springboot.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<target>11</target>
<source>11</source>
<testSource>11</testSource>
<testTarget>11</testTarget>
</configuration>
</plugin>
</plugins>
</build>
- 修改子工程下的pom文件，引入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
</dependencies>
-
修改yaml配置文件
-
application.yaml
spring: profiles: active: sharding-database shardingsphere: props: sql: show: true -
application-sharding-database.yaml
#datasource spring: shardingsphere: datasource: names: ds0,ds1 ds0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://192.168.137.144:3306/sharding1 username: root password: 123456 ds1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://192.168.137.144:3306/sharding2 username: root password: 123456 sharding: tables: position: database-strategy: inline: sharding-column: id algorithm-expression: ds$->{ id % 2}
-
-
主启动类
@SpringBootApplication
public class RunBoot {
}
- 创建实体类，这里使用springdata jpa进行操作
@Entity
@Table(name = "position")
public class Position {
@Id
@Column(name = "id")
private Long id;
@Column(name = "name")
private String name;
@Column(name = "city")
private String city;
@Column(name = "salary")
private Integer salary;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public Integer getSalary() {
return salary;
}
public void setSalary(Integer salary) {
this.salary = salary;
}
}
- 资源操作类
public interface PositionRepository extends JpaRepository<Position, Long> {
}
- 测试类
@SpringBootTest
public class TestShardingDatabase {
@Autowired
PositionRepository positionRepository;
@Test
public void test() {
for (int i=1;i<=20;i++) {
Position position = new Position();
position.setId((long) i);
position.setName("lagou" + i);
position.setSalary(1000000);
position.setCity("hangzhou");
positionRepository.save(position);
}
}
}
主键生成器测试
yaml文件中新增主键生成器配置
sharding:
tables:
position:
database-strategy:
inline:
sharding-column: id
algorithm-expression: ds$->{
id % 2}
key-generator:
column: id
type: SNOWFLAKE
实体类中添加主键生成规则
修改测试类
自定义主键生成器
- 实现
ShardingKeyGenerator接口，getType()方法返回的就是该生成器的名称
public class MyKey implements ShardingKeyGenerator {
private SnowflakeShardingKeyGenerator snow = new SnowflakeShardingKeyGenerator();
@Override
public Comparable<?> generateKey() {
System.out.println("------执行了自定义主键生成器-------");
return snow.generateKey();
}
@Override
public String getType() {
return "MYSELFKEY";
}
@Override
public Properties getProperties() {
return null;
}
@Override
public void setProperties(Properties properties) {
}
}
- 使用SPI机制加载自定义生成器，resources目录下创建META-INF/services目录，创建
org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator文件，添加自定义生成器的全类名
com.elvis.id.MyKey
- 修改yaml文件
- 清空之前存入表中的数据
- 再次执行测试
拆表分库
上面使用的position表需要添加，详情描述，采用垂直拆分的方式，职位详情作为单独的一个表。
- 创建position_detail表
在上面的两个库中都进行创建 ，pid为上面position表中的主键也是它的分片键，position_detail中id为主键同时也作为表的分片键
CREATE TABLE `position_detail` (
`Id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`pid` BIGINT(11) NOT NULL DEFAULT '0',
`description` TEXT DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
- 创建实体类
@Entity
@Table(name = "position_detail")
public class PositionDetail {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "pid")
private Long pid;
@Column(name = "description")
private String description;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getPid() {
return pid;
}
public void setPid(Long pid) {
this.pid = pid;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}
*修改yaml配置文件添加position_detail相关的配置
position_detail:
database-strategy:
inline:
sharding-column: id
algorithm-expression: ds$->{
id % 2}
key-generator:
column: id
type: SNOWFLAKE
- 测试方法
@Test
public void test1() {
for (int i=1;i<=20;i++) {
Position position = new Position();
// position.setId((long) i);
position.setName("java" + i);
position.setSalary(1000000);
position.setCity("hangzhou");
positionRepository.save(position);
PositionDetail positionDetail = new PositionDetail();
positionDetail.setPid(position.getId());
positionDetail.setDescription("this is a message "+i);
positionDetailRepository.save(positionDetail);
}
}
- 查询数据测试
修改PositionRepository 增加查询接口
public interface PositionRepository extends JpaRepository<Position, Long> {
@Query(nativeQuery = true, value = "select p.id,p.name,p.salary,p.city,pd.description from position p join position_detail pd on(p.id=pd.pid) where p.id=:id")
public Object findPositionsById(@Param("id") long id);
}
- 增加测试方法
@Test
public void load() {
Object object = positionRepository.findPositionsById(626408061761748992L);
Object[] position = (Object[])object;
System.out.println(position[0]+" "+position[1]+" "+position[2]+" "+position[3]+" "+position[4]);
}
广播表
- 创建city表
USE sharding1;
CREATE TABLE `city` (
`Id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(256) DEFAULT NULL,
`province` VARCHAR(256) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
USE sharding2;
CREATE TABLE `city` (
`Id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(256) DEFAULT NULL,
`province` VARCHAR(256) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
-
配置广播表
-
实体类
@Entity
@Table(name = "city")
public class City implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "name")
private String name;
@Column(name = "province")
private String province;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
}
- CityRepository类
public interface CityRepository extends JpaRepository<City,Long> {
}
- 测试类
@Test
public void testBroadCast(){
City city = new City();
city.setName("hangzhou");
city.setProvince("zhejiang");
cityRepository.save(city);
}
两个表有相同的数据
分库分表
- 创建表
USE sharding1;
CREATE TABLE `b_order_0`(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`is_del` BIT(1) NOT NULL DEFAULT 0 COMMENT '是否被删除',
`company_id` INT(11) NOT NULL COMMENT '公司ID',
`position_id` BIGINT(11) NOT NULL COMMENT '职位ID',
`user_id` INT(11) NOT NULL COMMENT '用户id',
`publish_user_id` INT(11) NOT NULL COMMENT '职位发布者id',
`resume_type` INT(2) NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
`status` VARCHAR(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`operate_time` DATETIME NOT NULL COMMENT '操作时间',
`work_year` VARCHAR(100) DEFAULT NULL COMMENT '工作年限',
`name` VARCHAR(256) DEFAULT NULL COMMENT '投递简历人名字',
`position_name` VARCHAR(256) DEFAULT NULL COMMENT '职位名称',
`resume_id` INT(10) DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
PRIMARY KEY (`id`),
KEY `index_createTime` (`create_time`),
KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
CREATE TABLE `b_order_1`(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`is_del` BIT(1) NOT NULL DEFAULT 0 COMMENT '是否被删除',
`company_id` INT(11) NOT NULL COMMENT '公司ID',
`position_id` BIGINT(11) NOT NULL COMMENT '职位ID',
`user_id` INT(11) NOT NULL COMMENT '用户id',
`publish_user_id` INT(11) NOT NULL COMMENT '职位发布者id',
`resume_type` INT(2) NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
`status` VARCHAR(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`operate_time` DATETIME NOT NULL COMMENT '操作时间',
`work_year` VARCHAR(100) DEFAULT NULL COMMENT '工作年限',
`name` VARCHAR(256) DEFAULT NULL COMMENT '投递简历人名字',
`position_name` VARCHAR(256) DEFAULT NULL COMMENT '职位名称',
`resume_id` INT(10) DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
PRIMARY KEY (`id`),
KEY `index_createTime` (`create_time`),
KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
USE sharding2;
CREATE TABLE `b_order_0`(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`is_del` BIT(1) NOT NULL DEFAULT 0 COMMENT '是否被删除',
`company_id` INT(11) NOT NULL COMMENT '公司ID',
`position_id` BIGINT(11) NOT NULL COMMENT '职位ID',
`user_id` INT(11) NOT NULL COMMENT '用户id',
`publish_user_id` INT(11) NOT NULL COMMENT '职位发布者id',
`resume_type` INT(2) NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
`status` VARCHAR(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`operate_time` DATETIME NOT NULL COMMENT '操作时间',
`work_year` VARCHAR(100) DEFAULT NULL COMMENT '工作年限',
`name` VARCHAR(256) DEFAULT NULL COMMENT '投递简历人名字',
`position_name` VARCHAR(256) DEFAULT NULL COMMENT '职位名称',
`resume_id` INT(10) DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
PRIMARY KEY (`id`),
KEY `index_createTime` (`create_time`),
KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
CREATE TABLE `b_order_1`(
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`is_del` BIT(1) NOT NULL DEFAULT 0 COMMENT '是否被删除',
`company_id` INT(11) NOT NULL COMMENT '公司ID',
`position_id` BIGINT(11) NOT NULL COMMENT '职位ID',
`user_id` INT(11) NOT NULL COMMENT '用户id',
`publish_user_id` INT(11) NOT NULL COMMENT '职位发布者id',
`resume_type` INT(2) NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
`status` VARCHAR(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`operate_time` DATETIME NOT NULL COMMENT '操作时间',
`work_year` VARCHAR(100) DEFAULT NULL COMMENT '工作年限',
`name` VARCHAR(256) DEFAULT NULL COMMENT '投递简历人名字',
`position_name` VARCHAR(256) DEFAULT NULL COMMENT '职位名称',
`resume_id` INT(10) DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
PRIMARY KEY (`id`),
KEY `index_createTime` (`create_time`),
KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
- yaml配置文件
b_order:
database-strategy:
inline:
sharding-column: company_id
algorithm-expression: ds$->{
company_id % 2}
table-strategy:
inline:
sharding-column: id
algorithm-expression: b_order_$->{
id % 2}
actual-data-nodes: ds$->{
0..1}.b_order_$->{
0..1}
key-generator:
column: id
type: SNOWFLAKE
- 实体类
@Entity
@Table(name = "b_order")
public class BOrder implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "is_del")
private Boolean isDel;
@Column(name = "company_id")
private Integer companyId;
@Column(name = "position_id")
private long positionId;
@Column(name = "user_id")
private Integer userId;
@Column(name = "publish_user_id")
private Integer publishUserId;
@Column(name = "resume_type")
private Integer resumeType;
@Column(name = "status")
private String status;
@Column(name = "create_time")
private Date createTime;
@Column(name = "operate_time")
private Date operateTime;
@Column(name = "work_year")
private String workYear;
@Column(name = "name")
private String name;
@Column(name = "position_name")
private String positionName;
@Column(name = "resume_id")
private Integer resumeId;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public Boolean getDel() {
return isDel;
}
public void setDel(Boolean del) {
isDel = del;
}
public Integer getCompanyId() {
return companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
public long getPositionId() {
return positionId;
}
public void setPositionId(long positionId) {
this.positionId = positionId;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getPublishUserId() {
return publishUserId;
}
public void setPublishUserId(Integer publishUserId) {
this.publishUserId = publishUserId;
}
public Integer getResumeType() {
return resumeType;
}
public void setResumeType(Integer resumeType) {
this.resumeType = resumeType;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getOperateTime() {
return operateTime;
}
public void setOperateTime(Date operateTime) {
this.operateTime = operateTime;
}
public String getWorkYear() {
return workYear;
}
public void setWorkYear(String workYear) {
this.workYear = workYear;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPositionName() {
return positionName;
}
public void setPositionName(String positionName) {
this.positionName = positionName;
}
public Integer getResumeId() {
return resumeId;
}
public void setResumeId(Integer resumeId) {
this.resumeId = resumeId;
}
}
- jpa资源类
public interface BOrderRepository extends JpaRepository<BOrder,Long> {
}
- 测试方法
@Test
@RepeatedTest(100)
public void testShardingBOrder(){
Random random = new Random();
int companyId = random.nextInt(10);
BOrder order = new BOrder();
order.setDel(false);
order.setCompanyId(companyId);
order.setPositionId(3242342);
order.setUserId(2222);
order.setPublishUserId(1111);
order.setResumeType(1);
order.setStatus("AUTO");
order.setCreateTime(new Date());
order.setOperateTime(new Date());
order.setWorkYear("2");
order.setName("yanfa");
order.setPositionName("Java");
order.setResumeId(23233);
orderRepository.save(order);
}
原文链接: https://blog.csdn.net/Kiven_ch/article/details/119089079