springBoot整合ShardingSphere实现分库分表

·  阅读 92

1:创建项目。搭建基础环境

创建一个新的SpringBoot项目,然后引入依赖

<?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.2.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.coco</groupId>
    <artifactId>sharding_boot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding_boot</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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <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.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.0</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>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
复制代码

创建二个新的数据库,ds0,ds1,然后分别创建以下几张表,二个数据库都要创建

CREATE TABLE `position` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `salary` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751105403344912385 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `position_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751105403453964289 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `city` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751371239271956481 DEFAULT CHARSET=utf8mb4;
复制代码

创建对应的实体类,dao层等,这里只展示position表的,其它二个表一样

@Entity
@Table(name = "position")
public class Position implements Serializable {
    private static final long serialVersionUID = 731054533134883488L;

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

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

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

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


    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 getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

}
复制代码
import com.coco.entity.Position;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface PositionRepository extends JpaRepository<Position,Long> {
}
复制代码

2: shardingSphere分库

1:创建一个配置文件:application-sharding-database.properties

#定义数据源
spring.shardingsphere.datasource.names=datasource0,datasource1

spring.shardingsphere.datasource.datasource0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.datasource0.username=root
spring.shardingsphere.datasource.datasource0.password=root


spring.shardingsphere.datasource.datasource1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.datasource1.username=root
spring.shardingsphere.datasource.datasource1.password=root

#切分策略
#对表position进行分片,根据表的id字段进行分库
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
#分库策略,我们现在只有二个库,所以直接对2进行取模
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=datasource$->{id % 2}


#指定主键生成策略,如果不加这二行配置就不启用
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#使用sharding自带的雪花算法生成主键
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
复制代码

2:创建一个配置文件:application.properties引入该配置文件

spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true
复制代码

3:测试,可以发现二个库中都有数据了

@org.junit.Test
public void addTest() {
    for(int i = 1; i <= 20; i ++) {
        Position position = new Position();
        position.setName("coco-" + i);
        position.setSalary("100000");
        position.setCity("南昌");
        positionRepository.save(position);
    }
}
复制代码

3: shardingSphere分库分表

1:在ds0和ds1数据库分别创建 t_order0 和 t_order1二张表

CREATE TABLE `t_order0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `compan_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751388348609724417 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `compan_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751388348609724417 DEFAULT CHARSET=utf8mb4;
复制代码

2:创建实体类和dao层

@Entity
@Table(name = "t_order")
public class Order implements Serializable {
    private static final long serialVersionUID = 718845255320843512L;

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

    @Column(name = "compan_id")
    private Long companId;

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


    public Long getId() {
        return id;
    }

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

    public Long getCId() {
        return companId;
    }

    public void setCId(Long companId) {
        this.companId = companId;
    }

    public String getName() {
        return name;
    }

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

}
复制代码
@Repository
public interface OrderRepository extends JpaRepository<Order,Long> {
}
复制代码

3:还是在 application-sharding-database.properties进行修改

#定义数据源
spring.shardingsphere.datasource.names=datasource0,datasource1

spring.shardingsphere.datasource.datasource0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.datasource0.username=root
spring.shardingsphere.datasource.datasource0.password=root


spring.shardingsphere.datasource.datasource1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.datasource1.username=root
spring.shardingsphere.datasource.datasource1.password=root

#切分策略
#对表position进行分片,根据表的id字段进行分库
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
#分库策略
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=datasource$->{id % 2}

#指定主键生成策略,如果不加这二行配置就不启用
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#使用sharding自带的雪花算法生成主键
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE


###################################################################################
#以下是新增的部分,上面的配置没有变化
#订单表分库分表
#我们使用compan_id字段进行分库,使用id字段来进行分表
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=compan_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=datasource$->{compan_id % 2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{id % 2}
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=datasource${0..1}.t_order${0..1}
#订单表主键生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
复制代码

4:测试

@org.junit.Test
public void test() {
    Random random = new Random();
    Order order = new Order();
    order.setName("nanchang");
    Integer l = random.nextInt(10);
    order.setCompanId(Long.valueOf(l.toString()));
    orderRepository.save(order);
}
复制代码

3: shardingSphere广播表

广播表的作用是指数据在每一个库中都存在一份,一般像字典表等都会在每一个库中都存在一份,而且每一个库中的数据都是一模一样的

1:还是在 application-sharding-database.properties进行修改

#定义数据源
spring.shardingsphere.datasource.names=datasource0,datasource1

spring.shardingsphere.datasource.datasource0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.datasource0.username=root
spring.shardingsphere.datasource.datasource0.password=root


spring.shardingsphere.datasource.datasource1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.datasource1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.datasource1.username=root
spring.shardingsphere.datasource.datasource1.password=root

#切分策略
#对表position进行分片,根据表的id字段进行分库
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
#分库策略
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=datasource$->{id % 2}

#指定主键生成策略,如果不加这二行配置就不启用
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#使用sharding自带的雪花算法生成主键
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE


###################################################################################
#以下是新增的部分,上面的配置没有变化
#订单表分库分表
#我们使用compan_id字段进行分库,使用id字段来进行分表
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=compan_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=datasource$->{compan_id % 2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{id % 2}
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=datasource${0..1}.t_order${0..1}
#订单表主键生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE



###################################################################################
#以下是新增的部分,上面的配置没有变化
#广播表配置
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
复制代码

2:测试,保存一条数据,二个库中的city表都会有

@org.junit.Test
public void test() {
    City city = new City();
    city.setName("南昌");
    cityRepository.save(city);
}
复制代码
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改