前言
前文已经介绍了ShardingSphere及相关组件,Sharding-JDBC是ShardingSphere的第一个产品,也是ShardingSphere的前身。本文主要通过代码实例介绍如何使用Sharding-JDBC 。
第一篇文章介绍是官网对于sharding-jdbc的解释和介绍,其实说的直白一点,就是包含了分库分表功能的JDBC,因此我们可以直接把sharding-jdbc当做普通的jdbc来进行使用。
本系列文章项目基于SpringBoot构建,采用ShardingSphere v4.1.1,由于5.X版本还在公测,可能存在诸多坑。
构建父项目
引入如下依赖:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.zwqh</groupId>
<artifactId>sharding-sphere-4.1.1</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>pom</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.7.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<modules>
<!-- 数据库分库示例 -->
<module>sharding-sphere-demo-1</module>
</modules>
<!-- 依赖版本管理 -->
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
<sharding-sphere.version>4.1.1</sharding-sphere.version>
<druid.version>1.1.16</druid.version>
<mybatis.version>2.1.3</mybatis.version>
<lombok.version>1.18.12</lombok.version>
<mysql.version>8.0.23</mysql.version>
<reg-zookeeper-curator.version>4.0.1</reg-zookeeper-curator.version>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>${spring-boot.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- 如果使用sp-distributed 服务治理环境,需引入该依赖,并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- 如果使用sp-distributed 服务治理环境,且使用zookeeper作为配置或注册中心,需引入该依赖,并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
<version>${reg-zookeeper-curator.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${spring-boot.version}</version>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
</dependencyManagement>
</project>
Sharding-JDBC 实现水平分表
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-1</artifactId>
<version>${parent.version}</version>
<packaging>jar</packaging>
<name>sharding-sphere-demo-1</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.shardingspheredemo1.ShardingSphereDemo1Application</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2、创建数据库ds0,创建数据表t_orders_0、t_orders_1
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_orders_0
-- ----------------------------
DROP TABLE IF EXISTS `t_orders_0`;
CREATE TABLE `t_orders_0` (
`order_id` int NOT NULL COMMENT '订单id',
`order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
`user_id` int DEFAULT NULL COMMENT '用户id',
`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Table structure for t_orders_1
-- ----------------------------
DROP TABLE IF EXISTS `t_orders_1`;
CREATE TABLE `t_orders_1` (
`order_id` int NOT NULL COMMENT '订单id',
`order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
`user_id` int DEFAULT NULL COMMENT '用户id',
`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SET FOREIGN_KEY_CHECKS = 1;
3、分片规则
如果order_id是偶数添加到t_orders_0,如果是奇数添加到t_orders_1。
4、创建实体类
@Data
public class Orders {
private Integer orderId;
private Integer orderType;
private Integer userId;
private Double orderAmount;
}
5、创建Mapper类
@Mapper
public interface OrdersMapper {
@Insert("insert into t_orders(order_id,order_type,user_id,order_amount) values(#{orderId},#{orderType},#{userId},#{orderAmount})")
void insert(Orders orders);
@Select("select * from t_orders where order_id = #{orderId}")
@Results({
@Result(property = "orderId", column = "order_id"),
@Result(property = "orderType", column = "order_type"),
@Result(property = "userId", column = "user_id"),
@Result(property = "orderAmount", column = "order_amount" )
})
Orders selectOne(Integer orderId);
}
6、创建配置文件
serser.port=8080
spring.application.name=spring-boot-shardingsphere
#配置数据源的名称
spring.shardingsphere.datasource.names=ds0
#配置数据源的具体内容,
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
#指定orders表的分布情况,配置表在哪个数据库中,表名称是什么
spring.shardingsphere.sharding.tables.t_orders.actual-data-nodes=ds0.t_orders_$->{0..1}
#指定orders表里主键id生成策略
spring.shardingsphere.sharding.tables.t_orders.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_orders.key-generator.type=SNOWFLAKE
#指定分片策略。根据id的奇偶性来判断插入到哪个表
spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.algorithm-expression=t_orders_${order_id%2}
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
7、创建测试类
@SpringBootTest
public class OrderTests {
@Resource
private OrdersMapper ordersMapper;
@Test
public void addOrders() {
for (int i = 1; i <= 10; i++) {
Orders orders = new Orders();
orders.setOrderId(i);
orders.setUserId(i % 3);
orders.setOrderType(i % 2);
orders.setOrderAmount(1000.0 * i);
ordersMapper.insert(orders);
}
}
@Test
public void queryOrders() {
Orders orders = ordersMapper.selectOne(1);
System.out.println(orders);
}
}
8、执行结果
可以看到预期的结果
9、SNOWFLAKE
把新增操作中的主键order_id去除,新增时会自动生成唯一ID插入。
评论区