02 07 2021

前言

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

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

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

构建父项目

引入如下依赖:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5. <modelVersion>4.0.0</modelVersion>
  6. <groupId>cn.zwqh</groupId>
  7. <artifactId>sharding-sphere-4.1.1</artifactId>
  8. <version>1.0-SNAPSHOT</version>
  9. <packaging>pom</packaging>
  10. <parent>
  11. <groupId>org.springframework.boot</groupId>
  12. <artifactId>spring-boot-starter-parent</artifactId>
  13. <version>2.3.7.RELEASE</version>
  14. <relativePath/> <!-- lookup parent from repository -->
  15. </parent>
  16. <modules>
  17. <!-- 数据库分库示例 -->
  18. <module>sharding-sphere-demo-1</module>
  19. </modules>
  20. <!-- 依赖版本管理 -->
  21. <properties>
  22. <java.version>1.8</java.version>
  23. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  24. <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  25. <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
  26. <sharding-sphere.version>4.1.1</sharding-sphere.version>
  27. <druid.version>1.1.16</druid.version>
  28. <mybatis.version>2.1.3</mybatis.version>
  29. <lombok.version>1.18.12</lombok.version>
  30. <mysql.version>8.0.23</mysql.version>
  31. <reg-zookeeper-curator.version>4.0.1</reg-zookeeper-curator.version>
  32. </properties>
  33. <dependencyManagement>
  34. <dependencies>
  35. <dependency>
  36. <groupId>org.springframework.boot</groupId>
  37. <artifactId>spring-boot-starter</artifactId>
  38. <version>${spring-boot.version}</version>
  39. </dependency>
  40. <dependency>
  41. <groupId>org.mybatis.spring.boot</groupId>
  42. <artifactId>mybatis-spring-boot-starter</artifactId>
  43. <version>${mybatis.version}</version>
  44. </dependency>
  45. <!-- lombok -->
  46. <dependency>
  47. <groupId>org.projectlombok</groupId>
  48. <artifactId>lombok</artifactId>
  49. <version>${lombok.version}</version>
  50. <scope>provided</scope>
  51. </dependency>
  52. <dependency>
  53. <groupId>mysql</groupId>
  54. <artifactId>mysql-connector-java</artifactId>
  55. <version>${mysql.version}</version>
  56. </dependency>
  57. <dependency>
  58. <groupId>com.alibaba</groupId>
  59. <artifactId>druid-spring-boot-starter</artifactId>
  60. <version>${druid.version}</version>
  61. </dependency>
  62. <dependency>
  63. <groupId>org.apache.shardingsphere</groupId>
  64. <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  65. <version>${sharding-sphere.version}</version>
  66. </dependency>
  67. <!-- 如果使用sp-distributed 服务治理环境,需引入该依赖,并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
  68. <dependency>
  69. <groupId>org.apache.shardingsphere</groupId>
  70. <artifactId>sharding-jdbc-orchestration-spring-boot-starter</artifactId>
  71. <version>${sharding-sphere.version}</version>
  72. </dependency>
  73. <!-- 如果使用sp-distributed 服务治理环境,且使用zookeeper作为配置或注册中心,需引入该依赖,并关闭上面sharding-jdbc-spring-boot-starter的依赖-->
  74. <dependency>
  75. <groupId>org.apache.shardingsphere</groupId>
  76. <artifactId>sharding-orchestration-center-zookeeper-curator</artifactId>
  77. <version>${sharding-sphere.version}</version>
  78. </dependency>
  79. <dependency>
  80. <groupId>org.apache.shardingsphere</groupId>
  81. <artifactId>sharding-orchestration-reg-zookeeper-curator</artifactId>
  82. <version>${reg-zookeeper-curator.version}</version>
  83. </dependency>
  84. <dependency>
  85. <groupId>org.springframework.boot</groupId>
  86. <artifactId>spring-boot-starter-test</artifactId>
  87. <version>${spring-boot.version}</version>
  88. <scope>test</scope>
  89. <exclusions>
  90. <exclusion>
  91. <groupId>org.junit.vintage</groupId>
  92. <artifactId>junit-vintage-engine</artifactId>
  93. </exclusion>
  94. </exclusions>
  95. </dependency>
  96. </dependencies>
  97. </dependencyManagement>
  98. </project>

Sharding-JDBC 实现水平分表

1、环境构建

创建一个SpringBoot项目,引入如下依赖:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <parent>
  5. <groupId>cn.zwqh</groupId>
  6. <artifactId>sharding-sphere-4.1.1</artifactId>
  7. <version>1.0-SNAPSHOT</version>
  8. </parent>
  9. <modelVersion>4.0.0</modelVersion>
  10. <groupId>cn.zwqh</groupId>
  11. <artifactId>sharding-sphere-demo-1</artifactId>
  12. <version>${parent.version}</version>
  13. <packaging>jar</packaging>
  14. <name>sharding-sphere-demo-1</name>
  15. <description>Demo project for Spring Boot</description>
  16. <dependencies>
  17. <dependency>
  18. <groupId>org.springframework.boot</groupId>
  19. <artifactId>spring-boot-starter</artifactId>
  20. </dependency>
  21. <dependency>
  22. <groupId>org.mybatis.spring.boot</groupId>
  23. <artifactId>mybatis-spring-boot-starter</artifactId>
  24. </dependency>
  25. <dependency>
  26. <groupId>org.projectlombok</groupId>
  27. <artifactId>lombok</artifactId>
  28. <scope>provided</scope>
  29. </dependency>
  30. <dependency>
  31. <groupId>mysql</groupId>
  32. <artifactId>mysql-connector-java</artifactId>
  33. </dependency>
  34. <dependency>
  35. <groupId>com.alibaba</groupId>
  36. <artifactId>druid-spring-boot-starter</artifactId>
  37. </dependency>
  38. <dependency>
  39. <groupId>org.apache.shardingsphere</groupId>
  40. <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  41. </dependency>
  42. <dependency>
  43. <groupId>org.springframework.boot</groupId>
  44. <artifactId>spring-boot-starter-test</artifactId>
  45. <scope>test</scope>
  46. <exclusions>
  47. <exclusion>
  48. <groupId>org.junit.vintage</groupId>
  49. <artifactId>junit-vintage-engine</artifactId>
  50. </exclusion>
  51. </exclusions>
  52. </dependency>
  53. </dependencies>
  54. <build>
  55. <plugins>
  56. <plugin>
  57. <groupId>org.apache.maven.plugins</groupId>
  58. <artifactId>maven-compiler-plugin</artifactId>
  59. <version>3.8.1</version>
  60. <configuration>
  61. <source>1.8</source>
  62. <target>1.8</target>
  63. <encoding>UTF-8</encoding>
  64. </configuration>
  65. </plugin>
  66. <plugin>
  67. <groupId>org.springframework.boot</groupId>
  68. <artifactId>spring-boot-maven-plugin</artifactId>
  69. <configuration>
  70. <mainClass>cn.zwqh.shardingspheredemo1.ShardingSphereDemo1Application</mainClass>
  71. </configuration>
  72. <executions>
  73. <execution>
  74. <id>repackage</id>
  75. <goals>
  76. <goal>repackage</goal>
  77. </goals>
  78. </execution>
  79. </executions>
  80. </plugin>
  81. </plugins>
  82. </build>
  83. </project>

2、创建数据库ds0,创建数据表t_orders_0、t_orders_1

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  3. -- ----------------------------
  4. -- Table structure for t_orders_0
  5. -- ----------------------------
  6. DROP TABLE IF EXISTS `t_orders_0`;
  7. CREATE TABLE `t_orders_0` (
  8. `order_id` int NOT NULL COMMENT '订单id',
  9. `order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
  10. `user_id` int DEFAULT NULL COMMENT '用户id',
  11. `order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  12. PRIMARY KEY (`order_id`) USING BTREE
  13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  14. -- ----------------------------
  15. -- Table structure for t_orders_1
  16. -- ----------------------------
  17. DROP TABLE IF EXISTS `t_orders_1`;
  18. CREATE TABLE `t_orders_1` (
  19. `order_id` int NOT NULL COMMENT '订单id',
  20. `order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
  21. `user_id` int DEFAULT NULL COMMENT '用户id',
  22. `order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  23. PRIMARY KEY (`order_id`) USING BTREE
  24. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  25. SET FOREIGN_KEY_CHECKS = 1;

3、分片规则

如果order_id是偶数添加到t_orders_0,如果是奇数添加到t_orders_1。

4、创建实体类

  1. @Data
  2. public class Orders {
  3. private Integer orderId;
  4. private Integer orderType;
  5. private Integer userId;
  6. private Double orderAmount;
  7. }

5、创建Mapper类

  1. @Mapper
  2. public interface OrdersMapper {
  3. @Insert("insert into t_orders(order_id,order_type,user_id,order_amount) values(#{orderId},#{orderType},#{userId},#{orderAmount})")
  4. void insert(Orders orders);
  5. @Select("select * from t_orders where order_id = #{orderId}")
  6. @Results({
  7. @Result(property = "orderId", column = "order_id"),
  8. @Result(property = "orderType", column = "order_type"),
  9. @Result(property = "userId", column = "user_id"),
  10. @Result(property = "orderAmount", column = "order_amount" )
  11. })
  12. Orders selectOne(Integer orderId);
  13. }

6、创建配置文件

  1. serser.port=8080
  2. spring.application.name=spring-boot-shardingsphere
  3. #配置数据源的名称
  4. spring.shardingsphere.datasource.names=ds0
  5. #配置数据源的具体内容,
  6. spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
  7. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
  8. spring.shardingsphere.datasource.ds0.url=jdbc:mysql://127.0.0.1:3306/ds0?serverTimezone=UTC
  9. spring.shardingsphere.datasource.ds0.username=root
  10. spring.shardingsphere.datasource.ds0.password=123456
  11. #指定orders表的分布情况,配置表在哪个数据库中,表名称是什么
  12. spring.shardingsphere.sharding.tables.t_orders.actual-data-nodes=ds0.t_orders_$->{0..1}
  13. #指定orders表里主键id生成策略
  14. spring.shardingsphere.sharding.tables.t_orders.key-generator.column=order_id
  15. spring.shardingsphere.sharding.tables.t_orders.key-generator.type=SNOWFLAKE
  16. #指定分片策略。根据id的奇偶性来判断插入到哪个表
  17. spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.sharding-column=order_id
  18. spring.shardingsphere.sharding.tables.t_orders.table-strategy.inline.algorithm-expression=t_orders_${order_id%2}
  19. #打开sql输出日志
  20. spring.shardingsphere.props.sql.show=true

7、创建测试类

  1. @SpringBootTest
  2. public class OrderTests {
  3. @Resource
  4. private OrdersMapper ordersMapper;
  5. @Test
  6. public void addOrders() {
  7. for (int i = 1; i <= 10; i++) {
  8. Orders orders = new Orders();
  9. orders.setOrderId(i);
  10. orders.setUserId(i % 3);
  11. orders.setOrderType(i % 2);
  12. orders.setOrderAmount(1000.0 * i);
  13. ordersMapper.insert(orders);
  14. }
  15. }
  16. @Test
  17. public void queryOrders() {
  18. Orders orders = ordersMapper.selectOne(1);
  19. System.out.println(orders);
  20. }
  21. }

8、执行结果

可以看到预期的结果

image-20210702095429156

image-20210702095515791

image-20210702095528958

9、SNOWFLAKE

把新增操作中的主键order_id去除,新增时会自动生成唯一ID插入。

源码地址

github

码云

延伸阅读
  1. MySQL 5.7 详细安装步骤
  2. Linux下MySQL的彻底卸载
  3. 一文读懂 MySQL 事务
  4. MySQL 怎么解决幻读问题
  5. MySQL join的使用和原理
发表评论