前言
前文已经介绍了ShardingSphere及相关组件，Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。本文主要通过代码实例介绍如何使用Sharding-JDBC 。
第一篇文章介绍是官网对于sharding-jdbc的解释和介绍，其实说的直白一点，就是包含了分库分表功能的JDBC，因此我们可以直接把sharding-jdbc当做普通的jdbc来进行使用。
本系列文章项目基于SpringBoot构建，采用ShardingSphere v4.1.1，由于5.X版本还在公测，可能存在诸多Bug。
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-2</artifactId>
<version>${parent.version}</version>
<packaging>jar</packaging>
<name>sharding-sphere-demo-2</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.shardingspheredemo2.ShardingSphereDemo2Application</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
2、创建数据库ds0、ds1，分别创建数据表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、分片规则
库：如果user_id是偶数添加到ds0，如果是奇数添加到ds1
表：如果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);
@Select("select * from t_orders where order_id = #{orderId} and user_id=#{userId}")
@Results({
@Result(property = "orderId", column = "order_id",jdbcType = JdbcType.INTEGER),
@Result(property = "orderType", column = "order_type",jdbcType = JdbcType.INTEGER),
@Result(property = "userId", column = "user_id",jdbcType = JdbcType.INTEGER),
@Result(property = "orderAmount", column = "order_amount",jdbcType = JdbcType.DOUBLE)
})
Orders selectOneDB(Orders orders);
}
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.tables.t_orders.actual-data-nodes=ds$->{0..1}.t_orders_$->{0..1}
#指定orders表里主键order_id生成策略
spring.shardingsphere.sharding.tables.t_orders.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_orders.key-generator.type=SNOWFLAKE
#指定库分片策略。根据user_id的奇偶性来判断插入到哪个库
spring.shardingsphere.sharding.tables.t_orders.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_orders.database-strategy.inline.algorithm-expression=ds${user_id%2}
#指定表分片策略。根据order_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 addOrders2() {
for (int i = 1; i <= 100; i++) {
Orders orders = new Orders();
orders.setOrderId(i);
orders.setUserId(i % 5);
orders.setOrderType(i % 2);
orders.setOrderAmount(1000.0 * i);
ordersMapper.insert(orders);
}
}
@Test
public void selectOneDB(){
Orders orders=new Orders();
orders.setUserId(4);
orders.setOrderId(11);
System.out.println(ordersMapper.selectOneDB(orders));
}
}
8、执行结果
在查询过程中，sharding-jdbc会对参数拦截，根据参数和配置分片规则动态找到对应的数据库和表，进行查询。
9、SNOWFLAKE
把新增操作中的主键order_id去除，新增时会自动生成唯一ID插入。
评论区