02 07 2021

前言

前文已经介绍了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项目,引入如下依赖:

  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-3</artifactId>
  12. <version>${parent.version}</version>
  13. <packaging>jar</packaging>
  14. <name>sharding-sphere-demo-3</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.shardingspheredemo3.ShardingSphereDemo3Application</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、ds1,在ds0创建数据表t_order,在ds1创建数据表t_user

  1. CREATE TABLE `t_order` (
  2. `order_id` bigint NOT NULL COMMENT '订单id',
  3. `order_type` tinyint(1) DEFAULT NULL COMMENT '订单类型',
  4. `user_id` int DEFAULT NULL COMMENT '用户id',
  5. `order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',
  6. PRIMARY KEY (`order_id`) USING BTREE
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  8. CREATE TABLE `t_user` (
  9. `user_id` int NOT NULL COMMENT '用户id',
  10. `user_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
  11. PRIMARY KEY (`user_id`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3、分片规则

将不同表的数据插入到不同的库中。

4、创建实体类

  1. @Data
  2. public class Orders {
  3. private Integer orderId;
  4. private Integer orderType;
  5. private Integer userId;
  6. private Double orderAmount;
  7. }
  8. @Data
  9. public class UserEntity {
  10. private Integer userId;
  11. private String userName;
  12. }

5、创建Mapper类

  1. @Mapper
  2. public interface OrdersMapper {
  3. @Insert("insert into orders(order_id,order_type,user_id,order_amount) values(#{orderId},#{orderType},#{userId},#{orderAmount})")
  4. void insertOrder(Orders orders);
  5. }
  6. @Mapper
  7. public interface UserMapper {
  8. @Insert("insert into users(user_id,user_name) values(#{userId},#{userName})")
  9. void insertUser(UserEntity userEntity);
  10. }

6、创建配置文件

  1. serser.port=8080
  2. spring.application.name=spring-boot-shardingsphere
  3. #配置数据源的名称
  4. spring.shardingsphere.datasource.names=ds0,ds1
  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. spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
  12. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
  13. spring.shardingsphere.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC
  14. spring.shardingsphere.datasource.ds1.username=root
  15. spring.shardingsphere.datasource.ds1.password=123456
  16. #配置order表所在的数据节点
  17. spring.shardingsphere.sharding.tables.orders.actual-data-nodes=ds0.t_order
  18. #user表的主键生成策略
  19. spring.shardingsphere.sharding.tables.orders.key-generator.column=order_id
  20. spring.shardingsphere.sharding.tables.orders.key-generator.type=SNOWFLAKE
  21. #指定user表分片的策略
  22. spring.shardingsphere.sharding.tables.orders.table-strategy.inline.sharding-column=order_id
  23. spring.shardingsphere.sharding.tables.orders.table-strategy.inline.algorithm-expression=t_order
  24. #配置user表所在的数据节点
  25. spring.shardingsphere.sharding.tables.users.actual-data-nodes=ds1.t_user
  26. #user表的主键生成策略
  27. spring.shardingsphere.sharding.tables.users.key-generator.column=user_id
  28. spring.shardingsphere.sharding.tables.users.key-generator.type=SNOWFLAKE
  29. #指定user表分片的策略
  30. spring.shardingsphere.sharding.tables.users.table-strategy.inline.sharding-column=user_id
  31. spring.shardingsphere.sharding.tables.users.table-strategy.inline.algorithm-expression=t_user
  32. #显示sql
  33. spring.shardingsphere.props.sql.show=true

7、创建测试类

  1. @SpringBootTest
  2. public class OrderTests {
  3. @Resource
  4. private OrdersMapper ordersMapper;
  5. @Test
  6. public void addOrder() {
  7. for (int i = 1; i <= 10; i++) {
  8. Orders orders = new Orders();
  9. orders.setOrderId(i);
  10. orders.setUserId(i);
  11. orders.setOrderType(i % 2);
  12. orders.setOrderAmount(1000.0 * i);
  13. ordersMapper.insertOrder(orders);
  14. }
  15. }
  16. }
  17. @SpringBootTest
  18. public class UserTests {
  19. @Resource
  20. private UserMapper userMapper;
  21. @Test
  22. public void insertUser() {
  23. for (int i = 1; i <= 10; i++) {
  24. UserEntity userEntity = new UserEntity();
  25. userEntity.setUserId(i);
  26. userEntity.setUserName("user" + i);
  27. userMapper.insertUser(userEntity);
  28. }
  29. }
  30. }

8、执行结果

和预期一样,orders数据插入到了ds0.t_order,users数据插入到了ds1.t_user。

9、SNOWFLAKE

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

源码地址

github

码云

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