ShardingSphere4.1.1 | Sharding-JDBC广播表

前言

前文已经介绍了ShardingSphere及相关组件，Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。本文主要通过代码实例介绍如何使用Sharding-JDBC 。

第一篇文章介绍是官网对于sharding-jdbc的解释和介绍，其实说的直白一点，就是包含了分库分表功能的JDBC，因此我们可以直接把sharding-jdbc当做普通的jdbc来进行使用。

本系列文章项目基于SpringBoot构建，采用ShardingSphere v4.1.1，由于5.X版本还在公测，可能存在诸多Bug。

Sharding-JDBC 广播表

0、广播表（公共表）

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

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">
    <parent>
        <groupId>cn.zwqh</groupId>
        <artifactId>sharding-sphere-4.1.1</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-demo-4</artifactId>
    <version>${parent.version}</version>

    <packaging>jar</packaging>

    <name>sharding-sphere-demo-4</name>
    <description>Demo project for Spring Boot</description>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.zwqh.shardingspheredemo4.ShardingSphereDemo4Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

2、创建数据库ds0、ds1，分别在创建表t_order_type

CREATE TABLE `t_order_type` (
  `type_id` int NOT NULL COMMENT '订单类型id',
  `type_name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单类型名称',
  PRIMARY KEY (`type_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3、分片规则

公共表表示所有的库都具备相同的表。

4、创建实体类

@Data
public class OrderType {

    private Integer typeId;

    private String typeName;
}

5、创建Mapper类

@Mapper
public interface OrderTypeMapper {

    @Insert("insert into t_order_type(type_id,type_name) values(#{typeId},#{typeName})")
    void insertOrderType(OrderType orderType);

}

6、创建配置文件

serser.port=8080

spring.application.name=spring-boot-shardingsphere

#配置数据源的名称
spring.shardingsphere.datasource.names=ds0,ds1

#配置数据源的具体内容，
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# 广播表配置
spring.shardingsphere.sharding.broadcast-tables=t_order_type
spring.shardingsphere.sharding.tables.t_order_type.actual-data-nodes=ds$->{0..1}.t_order_type

#显示sql
spring.shardingsphere.props.sql.show=true

7、创建测试类

@SpringBootTest
public class OrderTypeTests {

    @Resource
    private OrderTypeMapper orderTypeMapper;

    @Test
    public void insertOrderType() {
        OrderType orderType1 = new OrderType();
        orderType1.setTypeId(1);
        orderType1.setTypeName("集采批发");
        OrderType orderType2 = new OrderType();
        orderType2.setTypeId(2);
        orderType2.setTypeName("集采代发");
        OrderType orderType3 = new OrderType();
        orderType3.setTypeId(3);
        orderType3.setTypeName("一件代发");

        orderTypeMapper.insertOrderType(orderType1);
        orderTypeMapper.insertOrderType(orderType2);
        orderTypeMapper.insertOrderType(orderType3);

    }
}

8、执行结果

新增的数据会同时插入到ds0和ds1的t_order_type表，且两表数据一致。

源码地址

github

码云

目录CONTENT