前言
前文已经介绍了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表,且两表数据一致。
评论区