devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SpringBoot集成ShardingSphere（手工配置）_springboot创建shardingspere

作者：知新_RL | 2024-03-08 15:15:50

赞

踩

springboot创建shardingspere

在中大型项目开发过程中，如何存储大量的数据是我们不可回避的难题，对这个问题的处理直接关系到系统的稳定性和可用性，我曾经负责过一个公司重点的老项目（交接过来的，哈哈~~），由于开发的时候没能正确预估数据的增长量，两三年之后有若干张表的单表数据量已经达到了五千万以上（未分库分表），我们都知道出于性能考虑mysql的单表数据量不要超过一千万，而且这个系统的数据库还有其他问题，比如和其他系统数据共库等等，最终导致系统所在库的表达到了1500多张，若干表的单表数据量也非常大。尽管后来采取了查询限流，强制索引，限定查询条件，归档数据，读写分离等等措施，这个系统的可维护性依然非常低。对于这样大数据量的系统而言必须采取分表和分库结合的策略。

目前市面上比较常用且开源的分库分表中间件有很多，之前有接触过MyCat，一种基于代理模式的数据库中间件。最近了解了下ShardingSphere，它是基于客户端分表分库的数据库中间件，当然也有代理模式的，只是没有广泛采用，详情见官网上的文档：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/overview/。下面记录下ShardingSphere和SpringBoot整合实现分库分表的大致步骤。分两篇博客展开，分别介绍基于sharding-jdbc-core依赖的手工配置的集成方法和基于sharding-jdbc-spring-boot-starter依赖的自动配置的方法，除了集成了SpringBoot还使用了常用的MyBatis+Druid等技术。这一篇介绍下手工配置的方法，这种方法的优点是灵活。

  • 首先创建SpringBoot项目，我是用的是2.1.6版本，引入sharding-jdbc-core依赖和druid、MyBatis等依赖，开发过程中还发现了一个好用的工具依赖hutool，此时的pom.xml文件如下：
  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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  4.     <modelVersion>4.0.0</modelVersion>
  5.     <parent>
  6.         <groupId>org.springframework.boot</groupId>
  7.         <artifactId>spring-boot-starter-parent</artifactId>
  8.         <version>2.1.6.RELEASE</version>
  9.         <relativePath/>
  10.     </parent>
  11.     <groupId>com.hyc</groupId>
  12.     <artifactId>shard3-manual</artifactId>
  13.     <version>0.0.1-SNAPSHOT</version>
  14.     <name>shard3-manual</name>
  15.     <description>Spring Boot集成shardingsphere3.x</description>
  16.  
  17.     <properties>
  18.         <java.version>1.8</java.version>
  19.     </properties>
  20.  
  21.     <dependencies>
  22.         <dependency>
  23.             <groupId>org.springframework.boot</groupId>
  24.             <artifactId>spring-boot-starter-actuator</artifactId>
  25.         </dependency>
  26.         <dependency>
  27.             <groupId>org.springframework.boot</groupId>
  28.             <artifactId>spring-boot-starter-web</artifactId>
  29.         </dependency>
  30.         <dependency>
  31.             <groupId>org.mybatis.spring.boot</groupId>
  32.             <artifactId>mybatis-spring-boot-starter</artifactId>
  33.             <version>2.0.1</version>
  34.         </dependency>
  35.  
  36.         <dependency>
  37.             <groupId>org.springframework.boot</groupId>
  38.             <artifactId>spring-boot-devtools</artifactId>
  39.             <scope>runtime</scope>
  40.             <optional>true</optional>
  41.         </dependency>
  42.         <dependency>
  43.             <groupId>mysql</groupId>
  44.             <artifactId>mysql-connector-java</artifactId>
  45.             <scope>runtime</scope>
  46.         </dependency>
  47.         <dependency>
  48.             <groupId>org.springframework.boot</groupId>
  49.             <artifactId>spring-boot-configuration-processor</artifactId>
  50.             <optional>true</optional>
  51.         </dependency>
  52.         <dependency>
  53.             <groupId>org.projectlombok</groupId>
  54.             <artifactId>lombok</artifactId>
  55.             <optional>true</optional>
  56.         </dependency>
  57.         <dependency>
  58.             <groupId>org.springframework.boot</groupId>
  59.             <artifactId>spring-boot-starter-test</artifactId>
  60.             <scope>test</scope>
  61.         </dependency>
  62.         <!-- shardingsphere start -->
  63.         <dependency>
  64.             <groupId>io.shardingsphere</groupId>
  65.             <artifactId>sharding-jdbc-core</artifactId>
  66.             <version>3.1.0</version>
  67.         </dependency>
  68.         <dependency>
  69.             <groupId>org.apache.commons</groupId>
  70.             <artifactId>commons-lang3</artifactId>
  71.             <version>3.9</version>
  72.         </dependency>
  73.         <dependency>
  74.             <groupId>cn.hutool</groupId>
  75.             <artifactId>hutool-all</artifactId>
  76.             <version>4.5.13</version>
  77.         </dependency>
  78.         <dependency>
  79.             <groupId>com.alibaba</groupId>
  80.             <artifactId>fastjson</artifactId>
  81.             <version>1.2.58</version>
  82.         </dependency>
  83.         <dependency>
  84.             <groupId>com.alibaba</groupId>
  85.             <artifactId>druid</artifactId>
  86.             <version>1.1.17</version>
  87.         </dependency>
  88.  
  89.     </dependencies>
  90.  
  91.     <build>
  92.         <plugins>
  93.             <plugin>
  94.                 <groupId>org.springframework.boot</groupId>
  95.                 <artifactId>spring-boot-maven-plugin</artifactId>
  96.             </plugin>
  97.         </plugins>
  98.     </build>
  99.  
  100. </project>
  • 本地建test和test2两个分库，在每个分库下建立一样的分表，用户表，用户地址表，订单表，订单明细表和商品表。设想用户地址表依赖于用户表，订单明细表依赖于订单表，同一个用户的用户表数据和订单数据在一个库中不在一套表中，即后缀一致（最好能在一套表中），以上的表，除了商品表不需要分表以外，其他表都需要分表（商品的数据在我们公司大约几十万条不需要分表，因此作为全局表放在每个库里）以提高查询性能。建表语句如下：
  1. SET NAMES utf8mb4;
  2.  
  3. -- ----------------------------
  4. -- Table structure for t_order_0
  5. -- ----------------------------
  6. DROP TABLE IF EXISTS `t_order_0`;
  7. CREATE TABLE `t_order_0`  (
  8.   `order_id` bigint(32) NOT NULL COMMENT '主键',
  9.   `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  10.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  11.   `order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  12.   `order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',
  13.   `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',
  14.   PRIMARY KEY (`order_id`),
  15.   INDEX `idx_order_user_id`(`user_id`),
  16.   INDEX `idx_order_order_no`(`order_no`) 
  17. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';
  18.  
  19. -- ----------------------------
  20. -- Table structure for t_order_1
  21. -- ----------------------------
  22. DROP TABLE IF EXISTS `t_order_1`;
  23. CREATE TABLE `t_order_1`  (
  24.   `order_id` bigint(32) NOT NULL COMMENT '主键',
  25.   `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  26.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  27.   `order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  28.   `order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',
  29.   `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',
  30.   PRIMARY KEY (`order_id`),
  31.   INDEX `idx_order_user_id`(`user_id`),
  32.   INDEX `idx_order_order_no`(`order_no`) 
  33. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';
  34.  
  35. -- ----------------------------
  36. -- Table structure for t_order_item_0
  37. -- ----------------------------
  38. DROP TABLE IF EXISTS `t_order_item_0`;
  39. CREATE TABLE `t_order_item_0`  (
  40.   `order_item_id` bigint(32) NOT NULL COMMENT '主键',
  41.   `order_id` bigint(32) NOT NULL COMMENT '订单id',
  42.   `product_id` bigint(32) NOT NULL COMMENT '商品id',
  43.   `item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',
  44.   `total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',
  45.   `total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  46.   `order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  47.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  48.   PRIMARY KEY (`order_item_id`),
  49.   INDEX `idx_order_item_order_id`(`order_id`),
  50.   INDEX `idx_order_item_user_id`(`user_id`),
  51.   INDEX `idx_order_item_product_id`(`product_id`),
  52.   INDEX `idx_order_item_order_time`(`order_time`) 
  53. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';
  54.  
  55. -- ----------------------------
  56. -- Table structure for t_order_item_1
  57. -- ----------------------------
  58. DROP TABLE IF EXISTS `t_order_item_1`;
  59. CREATE TABLE `t_order_item_1`  (
  60.   `order_item_id` bigint(32) NOT NULL COMMENT '主键',
  61.   `order_id` bigint(32) NOT NULL COMMENT '订单id',
  62.   `product_id` bigint(32) NOT NULL COMMENT '商品id',
  63.   `item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',
  64.   `total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',
  65.   `total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  66.   `order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  67.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  68.   PRIMARY KEY (`order_item_id`),
  69.   INDEX `idx_order_item_order_id`(`order_id`),
  70.   INDEX `idx_order_item_user_id`(`user_id`),
  71.   INDEX `idx_order_item_product_id`(`product_id`),
  72.   INDEX `idx_order_item_order_time`(`order_time`) 
  73. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';
  74.  
  75. -- ----------------------------
  76. -- Table structure for t_product
  77. -- ----------------------------
  78. DROP TABLE IF EXISTS `t_product`;
  79. CREATE TABLE `t_product`  (
  80.   `product_id` bigint(32) NOT NULL COMMENT '主键',
  81.   `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品编码',
  82.   `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
  83.   `desc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品描述',
  84.   PRIMARY KEY (`product_id`),
  85.   INDEX `idx_user_product_code`(`code`) 
  86. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '商品表';
  87.  
  88. -- ----------------------------
  89. -- Table structure for t_user_0
  90. -- ----------------------------
  91. DROP TABLE IF EXISTS `t_user_0`;
  92. CREATE TABLE `t_user_0`  (
  93.   `user_id` bigint(32) NOT NULL COMMENT '主键',
  94.   `id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',
  95.   `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  96.   `age` int(4) DEFAULT NULL COMMENT '年龄',
  97.   `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  98.   `birth_date` date DEFAULT NULL COMMENT '出生日期',
  99.   PRIMARY KEY (`user_id`) 
  100. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';
  101.  
  102. -- ----------------------------
  103. -- Table structure for t_user_1
  104. -- ----------------------------
  105. DROP TABLE IF EXISTS `t_user_1`;
  106. CREATE TABLE `t_user_1`  (
  107.   `user_id` bigint(32) NOT NULL COMMENT '主键',
  108.   `id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',
  109.   `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  110.   `age` int(4) DEFAULT NULL COMMENT '年龄',
  111.   `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  112.   `birth_date` date DEFAULT NULL COMMENT '出生日期',
  113.   PRIMARY KEY (`user_id`) 
  114. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';
  115.  
  116. -- ----------------------------
  117. -- Table structure for t_user_address_0
  118. -- ----------------------------
  119. DROP TABLE IF EXISTS `t_user_address_0`;
  120. CREATE TABLE `t_user_address_0`  (
  121.   `address_id` bigint(32) NOT NULL COMMENT '主键',
  122.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  123.   `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',
  124.   `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',
  125.   `district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
  126.   `detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',
  127.   `sort` int(4) DEFAULT 1 COMMENT '排序',
  128.   `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  129.   PRIMARY KEY (`address_id`),
  130.   INDEX `idx_user_address_user_id`(`user_id`) 
  131. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';
  132.  
  133. -- ----------------------------
  134. -- Table structure for t_user_address_1
  135. -- ----------------------------
  136. DROP TABLE IF EXISTS `t_user_address_1`;
  137. CREATE TABLE `t_user_address_1`  (
  138.   `address_id` bigint(32) NOT NULL COMMENT '主键',
  139.   `user_id` bigint(32) NOT NULL COMMENT '用户id',
  140.   `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',
  141.   `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',
  142.   `district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
  143.   `detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',
  144.   `sort` int(4) DEFAULT 1 COMMENT '排序',
  145.   `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  146.   PRIMARY KEY (`address_id`),
  147.   INDEX `idx_user_address_user_id`(`user_id`) 
  148. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';
  • 使用Mybatis-Generator生成相关代码，这里只用用户表来举例，其他的表类似，限于篇幅不再一一列举
  1. package com.hyc.entity;
  2.  
  3. import lombok.Data;
  4. import java.util.Date;
  5.  
  6. @Data
  7. public class User {
  8.     private Long userId;
  9.     private String idNumber;
  10.     private String name;
  11.     private Integer age;
  12.     private Integer gender;
  13.     private Date birthDate;
  14. }
  1. package com.hyc.dao;
  2.  
  3. import com.hyc.entity.User;
  4. import com.hyc.vo.ListUserVo;
  5. import org.apache.ibatis.annotations.Param;
  6.  
  7. import java.util.List;
  8.  
  9. public interface UserMapper {
  10.     int deleteByPrimaryKey(Long userId);
  11.  
  12.     int insert(User record);
  13.  
  14.     int insertSelective(User record);
  15.  
  16.     User selectByPrimaryKey(Long userId);
  17.  
  18.     int updateByPrimaryKeySelective(User record);
  19.  
  20.     int updateByPrimaryKey(User record);
  21.  
  22.     List<User> listByCondition(@Param("condition") ListUserVo query);
  23.  
  24.     int count(@Param("condition") ListUserVo query);
  25. }
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  3. <mapper namespace="com.hyc.dao.UserMapper" >
  4.   <resultMap id="BaseResultMap" type="com.hyc.entity.User" >
  5.     <id column="user_id" property="userId" jdbcType="BIGINT" />
  6.     <result column="id_number" property="idNumber" jdbcType="VARCHAR" />
  7.     <result column="name" property="name" jdbcType="VARCHAR" />
  8.     <result column="age" property="age" jdbcType="INTEGER" />
  9.     <result column="gender" property="gender" jdbcType="INTEGER" />
  10.     <result column="birth_date" property="birthDate" jdbcType="DATE" />
  11.   </resultMap>
  12.  
  13.   <sql id="Base_Column_List" >
  14.     user_id, id_number, name, age, gender, birth_date
  15.   </sql>
  16.  
  17.   <sql id="queryListConditon">
  18.     <if test="condition.userId != null">
  19.       and user_id = #{condition.userId, jdbcType=BIGINT}
  20.     </if>
  21.     <if test="condition.code != null and condition.code !=''">
  22.       and code = #{condition.code, jdbcType=VARCHAR}
  23.     </if>
  24.     <if test="condition.name != null and condition.name !=''">
  25.       and name = #{condition.name, jdbcType=VARCHAR}
  26.     </if>
  27.     <if test="condition.age != null">
  28.       and age = #{condition.age, jdbcType=INTEGER}
  29.     </if>
  30.     <if test="condition.gender != null">
  31.       and gender = #{condition.gender, jdbcType=INTEGER}
  32.     </if>
  33.     <if test="condition.joinDateStart != null">
  34.       and <![CDATA[ join_date >= #{condition.joinDateStart, jdbcType=TIMESTAMP} ]]>
  35.     </if>
  36.     <if test="condition.joinDateEnd != null">
  37.       and <![CDATA[ join_date <= #{condition.joinDateEnd, jdbcType=TIMESTAMP} ]]>
  38.     </if>
  39.   </sql>
  40.  
  41.   <select id="listByCondition" resultMap="BaseResultMap" parameterType="java.util.Map" >
  42.     select
  43.     <include refid="Base_Column_List" />
  44.     from t_user
  45.     <where>
  46.       <include refid="queryListConditon"/>
  47.     </where>
  48.     order by id desc
  49.     <if test="condition.start != null and condition.pageSize != null ">
  50.       limit #{condition.start},#{condition.pageSize}
  51.     </if>
  52.   </select>
  53.  
  54.   <select id="count" resultType="java.lang.Integer" parameterType="java.util.Map" >
  55.     select
  56.     count(1)
  57.     from t_user
  58.     <where>
  59.       <include refid="queryListConditon"/>
  60.     </where>
  61.   </select>
  62.  
  63.   <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
  64.     select 
  65.     <include refid="Base_Column_List" />
  66.     from t_user
  67.     where user_id = #{userId,jdbcType=BIGINT}
  68.   </select>
  69.   <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
  70.     delete from t_user
  71.     where user_id = #{userId,jdbcType=BIGINT}
  72.   </delete>
  73.   <insert id="insert" parameterType="com.hyc.entity.User" useGeneratedKeys="true" keyProperty="userId">
  74.     insert into t_user (user_id, id_number, name, 
  75.       age, gender, birth_date
  76.       )
  77.     values (#{userId,jdbcType=BIGINT}, #{idNumber,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
  78.       #{age,jdbcType=INTEGER}, #{gender,jdbcType=INTEGER}, #{birthDate,jdbcType=DATE}
  79.       )
  80.   </insert>
  81.   <insert id="insertSelective" parameterType="com.hyc.entity.User" useGeneratedKeys="true" keyProperty="userId">
  82.     insert into t_user
  83.     <trim prefix="(" suffix=")" suffixOverrides="," >
  84.       <if test="userId != null" >
  85.         user_id,
  86.       </if>
  87.       <if test="idNumber != null" >
  88.         id_number,
  89.       </if>
  90.       <if test="name != null" >
  91.         name,
  92.       </if>
  93.       <if test="age != null" >
  94.         age,
  95.       </if>
  96.       <if test="gender != null" >
  97.         gender,
  98.       </if>
  99.       <if test="birthDate != null" >
  100.         birth_date,
  101.       </if>
  102.     </trim>
  103.     <trim prefix="values (" suffix=")" suffixOverrides="," >
  104.       <if test="userId != null" >
  105.         #{userId,jdbcType=BIGINT},
  106.       </if>
  107.       <if test="idNumber != null" >
  108.         #{idNumber,jdbcType=VARCHAR},
  109.       </if>
  110.       <if test="name != null" >
  111.         #{name,jdbcType=VARCHAR},
  112.       </if>
  113.       <if test="age != null" >
  114.         #{age,jdbcType=INTEGER},
  115.       </if>
  116.       <if test="gender != null" >
  117.         #{gender,jdbcType=INTEGER},
  118.       </if>
  119.       <if test="birthDate != null" >
  120.         #{birthDate,jdbcType=DATE},
  121.       </if>
  122.     </trim>
  123.   </insert>
  124.   <update id="updateByPrimaryKeySelective" parameterType="com.hyc.entity.User" >
  125.     update t_user
  126.     <set >
  127.       <if test="idNumber != null" >
  128.         id_number = #{idNumber,jdbcType=VARCHAR},
  129.       </if>
  130.       <if test="name != null" >
  131.         name = #{name,jdbcType=VARCHAR},
  132.       </if>
  133.       <if test="age != null" >
  134.         age = #{age,jdbcType=INTEGER},
  135.       </if>
  136.       <if test="gender != null" >
  137.         gender = #{gender,jdbcType=INTEGER},
  138.       </if>
  139.       <if test="birthDate != null" >
  140.         birth_date = #{birthDate,jdbcType=DATE},
  141.       </if>
  142.     </set>
  143.     where user_id = #{userId,jdbcType=BIGINT}
  144.   </update>
  145.   <update id="updateByPrimaryKey" parameterType="com.hyc.entity.User" >
  146.     update t_user
  147.     set id_number = #{idNumber,jdbcType=VARCHAR},
  148.       name = #{name,jdbcType=VARCHAR},
  149.       age = #{age,jdbcType=INTEGER},
  150.       gender = #{gender,jdbcType=INTEGER},
  151.       birth_date = #{birthDate,jdbcType=DATE}
  152.     where user_id = #{userId,jdbcType=BIGINT}
  153.   </update>
  154. </mapper>
  • 写分库和分表的算法，分库分表时必然会根据表里的字段设计分表分库的算法，对于用户表和用户地址表用user_id取模来分库，使用gender来分表。对于订单和订单明细表，使用user_id取模来分库，order_id取模来分表。因此需要用到两个分片算法，根据性别分片和根据id分片。
  1. package com.hyc.dbstrategy;
  2.  
  3. import com.google.common.collect.Range;
  4. import com.hyc.enums.GenderEnum;
  5. import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
  6. import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
  7. import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
  8. import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
  9.  
  10. import java.util.Collection;
  11. import java.util.LinkedHashSet;
  12.  
  13. public class GenderShardingAlgorithm implements PreciseShardingAlgorithm<Integer>, RangeShardingAlgorithm<Integer> {
  14.     /**
  15.      * Sharding.
  16.      *
  17.      * @param availableTargetNames available data sources or tables's names
  18.      * @param shardingValue        sharding value
  19.      * @return sharding result for data source or table's name
  20.      */
  21.     @Override
  22.     public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
  23.         String databaseName = availableTargetNames.stream().findFirst().get();
  24.  
  25.         for (String dbName : availableTargetNames) {
  26.             if (dbName.endsWith(genderToTableSuffix(shardingValue.getValue()))) {
  27.                 databaseName = dbName;
  28.             }
  29.         }
  30.  
  31.         return databaseName;
  32.     }
  33.  
  34.     /**
  35.      * Sharding.
  36.      *
  37.      * @param availableTargetNames available data sources or tables's names
  38.      * @param shardingValue        sharding value
  39.      * @return sharding results for data sources or tables's names
  40.      */
  41.     @Override
  42.     public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Integer> shardingValue) {
  43.         Collection<String> dbs = new LinkedHashSet<>(availableTargetNames.size());
  44.  
  45.         Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
  46.         for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
  47.             for (String dbName : availableTargetNames) {
  48.                 if (dbName.endsWith(genderToTableSuffix(i))) {
  49.                     dbs.add(dbName);
  50.                 }
  51.             }
  52.         }
  53.         return dbs;
  54.     }
  55.  
  56.     /**
  57.      * 字段与分库的映射关系
  58.      *
  59.      * @param gender
  60.      * @return
  61.      */
  62.     private String genderToTableSuffix(Integer gender) {
  63.         return gender.equals(GenderEnum.MALE.getCode()) ? "0" : "1";
  64.     }
  65. }
  1. package com.hyc.dbstrategy;
  2.  
  3. import com.google.common.collect.Range;
  4. import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
  5. import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
  6. import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
  7. import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
  8.  
  9. import java.util.Collection;
  10. import java.util.LinkedHashSet;
  11.  
  12. public class IdShardingAlgorithm implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
  13.     /**
  14.      * Sharding.
  15.      *
  16.      * @param availableTargetNames available data sources or tables's names
  17.      * @param shardingValue        sharding value
  18.      * @return sharding result for data source or table's name
  19.      */
  20.     @Override
  21.     public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
  22.         String table = availableTargetNames.stream().findFirst().get();
  23.  
  24.         for (String tableName : availableTargetNames) {
  25.             if (tableName.endsWith(idToTableSuffix(shardingValue.getValue()))) {
  26.                 table = tableName;
  27.             }
  28.         }
  29.  
  30.         return table;
  31.     }
  32.  
  33.     /**
  34.      * Sharding.
  35.      *
  36.      * @param availableTargetNames available data sources or tables's names
  37.      * @param shardingValue        sharding value
  38.      * @return sharding results for data sources or tables's names
  39.      */
  40.     @Override
  41.     public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
  42.         Collection<String> dbs = new LinkedHashSet<>(availableTargetNames.size());
  43.  
  44.         Range<Long> range = (Range<Long>) shardingValue.getValueRange();
  45.         for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
  46.             for (String dbName : availableTargetNames) {
  47.                 if (dbName.endsWith(idToTableSuffix(i))) {
  48.                     dbs.add(dbName);
  49.                 }
  50.             }
  51.         }
  52.  
  53.         return dbs;
  54.     }
  55.  
  56.     /**
  57.      * 字段与分表的映射关系
  58.      *
  59.      * @param id
  60.      * @return 表后缀（201906、201907等）
  61.      */
  62.     private String idToTableSuffix(Long id) {
  63.         return String.valueOf(id % 2);
  64.     }
  65. }
  • 编写ID生成算法，在数据分片的场景中使用MySQL主键自增就不太合适了，因此我使用了snowflake算法来生成主键，代码是从网上搜的，ShardingSphere支持我们制定主键生成，只需要实现KeyGenerator接口即可
  1. package com.hyc.keygen;
  2.  
  3. import io.shardingsphere.core.keygen.KeyGenerator;
  4.  
  5. import java.util.Random;
  6.  
  7. public final class SnowflakeShardingKeyGenerator implements KeyGenerator {
  8.     /**
  9.      * 开始时间截 (2015-01-01)
  10.      */
  11.     private final long twepoch = 1420041600000L;
  12.  
  13.     /**
  14.      * 机器id所占的位数
  15.      */
  16.     private final long workerIdBits = 5L;
  17.  
  18.     /**
  19.      * 数据标识id所占的位数
  20.      */
  21.     private final long dataCenterIdBits = 5L;
  22.  
  23.     /**
  24.      * 支持的最大机器id，结果是31 (这个移位算法可以很快的计算出几位二进制数所能表示的最大十进制数)
  25.      */
  26.     private final long maxWorkerId = -1L ^ (-1L << workerIdBits);
  27.  
  28.     /**
  29.      * 支持的最大数据标识id，结果是31
  30.      */
  31.     private final long maxDataCenterId = -1L ^ (-1L << dataCenterIdBits);
  32.  
  33.     /**
  34.      * 序列在id中占的位数
  35.      */
  36.     private final long sequenceBits = 12L;
  37.  
  38.     /**
  39.      * 机器ID向左移12位
  40.      */
  41.     private final long workerIdShift = sequenceBits;
  42.  
  43.     /**
  44.      * 数据标识id向左移17位(12+5)
  45.      */
  46.     private final long datacenterIdShift = sequenceBits + workerIdBits;
  47.  
  48.     /**
  49.      * 时间截向左移22位(5+5+12)
  50.      */
  51.     private final long timestampLeftShift = sequenceBits + workerIdBits + dataCenterIdBits;
  52.  
  53.     /**
  54.      * 生成序列的掩码，这里为4095 (0b111111111111=0xfff=4095)
  55.      */
  56.     private final long sequenceMask = -1L ^ (-1L << sequenceBits);
  57.  
  58.     /**
  59.      * 工作机器ID(0~31)
  60.      */
  61.     private long workerId;
  62.  
  63.     /**
  64.      * 数据中心ID(0~31)
  65.      */
  66.     private long dataCenterId;
  67.  
  68.     /**
  69.      * 毫秒内序列(0~4095)
  70.      */
  71.     private long sequence = 0L;
  72.  
  73.     /**
  74.      * 上次生成ID的时间截
  75.      */
  76.     private long lastTimestamp = -1L;
  77.  
  78.     private Random random = new Random();
  79.  
  80.     /**
  81.      * 构造函数
  82.      *
  83.      * @param workerId     工作ID (0~31)
  84.      * @param dataCenterId 数据中心ID (0~31)
  85.      */
  86.     public SnowflakeShardingKeyGenerator(long workerId, long dataCenterId) {
  87.         if (workerId > maxWorkerId || workerId < 0) {
  88.             throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
  89.         }
  90.         if (dataCenterId > maxDataCenterId || dataCenterId < 0) {
  91.             throw new IllegalArgumentException(String.format("datacenter Id can't be greater than %d or less than 0", maxDataCenterId));
  92.         }
  93.         this.workerId = workerId;
  94.         this.dataCenterId = dataCenterId;
  95.     }
  96.  
  97.     /**
  98.      * 阻塞到下一个毫秒，直到获得新的时间戳
  99.      *
  100.      * @param lastTimestamp 上次生成ID的时间截
  101.      * @return 当前时间戳
  102.      */
  103.     private long tilNextMillis(long lastTimestamp) {
  104.         long timestamp = timeGen();
  105.         while (timestamp <= lastTimestamp) {
  106.             timestamp = timeGen();
  107.         }
  108.         return timestamp;
  109.     }
  110.  
  111.     /**
  112.      * 返回以毫秒为单位的当前时间
  113.      *
  114.      * @return 当前时间(毫秒)
  115.      */
  116.     private long timeGen() {
  117.         return System.currentTimeMillis();
  118.     }
  119.  
  120.     /**
  121.      * Generate key.
  122.      *
  123.      * @return generated key
  124.      */
  125.     @Override
  126.     public Number generateKey() {
  127.         long timestamp = timeGen();
  128.  
  129.         //如果当前时间小于上一次ID生成的时间戳，说明系统时钟回退过这个时候应当抛出异常
  130.         if (timestamp < lastTimestamp) {
  131.             throw new RuntimeException(
  132.                     String.format("Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
  133.         }
  134.  
  135.         //如果是同一时间生成的，则进行毫秒内序列
  136.         if (lastTimestamp == timestamp) {
  137.             sequence = (sequence + 1) & sequenceMask;
  138.             //毫秒内序列溢出
  139.             if (sequence == 0) {
  140.                 //阻塞到下一个毫秒,获得新的时间戳
  141.                 timestamp = tilNextMillis(lastTimestamp);
  142.             }
  143.         }
  144.         //时间戳改变，毫秒内序列重置
  145.         else {
  146.             sequence = 0L;
  147.         }
  148.  
  149.         //上次生成ID的时间截
  150.         lastTimestamp = timestamp;
  151.  
  152.         long result = ((timestamp - twepoch) << timestampLeftShift)
  153.                 | (dataCenterId << datacenterIdShift)
  154.                 | (workerId << workerIdShift)
  155.                 | sequence;
  156.         int randomNum = random.nextInt(10);
  157.         //移位并通过或运算拼到一起组成64位的ID
  158.         return result + randomNum;
  159.     }
  160.  
  161.     public static void main(String[] args) {
  162.         SnowflakeShardingKeyGenerator generator = new SnowflakeShardingKeyGenerator(0,0);
  163.         for (int i = 0; i < 20; i++) {
  164.             System.out.println(generator.generateKey());
  165.         }
  166.     }
  167. }
  • 配置数据库连接、MyBatis集成和编写属性文件等等
  1. #data source0
  2. sharding.ds0.type=com.alibaba.druid.pool.DruidDataSource
  3. sharding.ds0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1?serverTimezone=GMT%2B8&useSSL=false
  4. sharding.ds0.username=root
  5. sharding.ds0.password=123456
  6.  
  7. #data source1
  8. sharding.ds1.type=com.alibaba.druid.pool.DruidDataSource
  9. sharding.ds1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test2?serverTimezone=GMT%2B8&useSSL=false
  10. sharding.ds1.username=root
  11. sharding.ds1.password=123456
  12.  
  13. #sql日志
  14. logging.level.com.hyc.dao=debug
  15.  
  16. #actuator端口
  17. management.server.port=9001
  18. #开放所有页面节点  默认只开启了health、info两个节点
  19. management.endpoints.web.exposure.include=*
  20. #显示健康具体信息  默认不会显示详细信息
  21. management.endpoint.health.show-details=always
  22.  
  23. snow.work.id=1
  24. snow.datacenter.id=2
  25.  
  26. mybatis.configuration.map-underscore-to-camel-case=true
  27. mybatis.type-aliases-package=com.hyc.entity
  28. mybatis.mapper-locations=classpath:mappers/*.xml

 

  1. package com.hyc.props;
  2.  
  3. import lombok.Data;
  4. import org.springframework.boot.context.properties.ConfigurationProperties;
  5.  
  6. @Data
  7. @ConfigurationProperties(prefix = "sharding.ds0")
  8. public class FirstDsProp {
  9.     private String jdbcUrl;
  10.     private String username;
  11.     private String password;
  12.     private String type;
  13. }
  1. package com.hyc.props;
  2.  
  3. import lombok.Data;
  4. import org.springframework.boot.context.properties.ConfigurationProperties;
  5.  
  6. @Data
  7. @ConfigurationProperties(prefix = "sharding.ds1")
  8. public class SecondDsProp {
  9.     private String jdbcUrl;
  10.     private String username;
  11.     private String password;
  12.     private String type;
  13. }

 

  • 下面就是最核心的部分了，数据分片相关配置，需要配置的东西很多，包括：各个表的配置规则TableRuleConfiguration、数据源DataSource，这部分可以参考官方文档说明来配置
  1. package com.hyc.config;
  2.  
  3. import com.alibaba.druid.filter.Filter;
  4. import com.alibaba.druid.filter.stat.StatFilter;
  5. import com.alibaba.druid.pool.DruidDataSource;
  6. import com.alibaba.druid.support.http.StatViewServlet;
  7. import com.google.common.collect.Lists;
  8. import com.hyc.dbstrategy.GenderShardingAlgorithm;
  9. import com.hyc.dbstrategy.IdShardingAlgorithm;
  10. import com.hyc.keygen.SnowflakeShardingKeyGenerator;
  11. import com.hyc.props.FirstDsProp;
  12. import com.hyc.props.SecondDsProp;
  13. import com.hyc.util.DataSourceUtil;
  14. import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
  15. import io.shardingsphere.api.config.rule.TableRuleConfiguration;
  16. import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
  17. import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
  18. import org.apache.ibatis.session.SqlSessionFactory;
  19. import org.mybatis.spring.SqlSessionFactoryBean;
  20. import org.mybatis.spring.SqlSessionTemplate;
  21. import org.mybatis.spring.annotation.MapperScan;
  22. import org.springframework.beans.factory.annotation.Autowired;
  23. import org.springframework.beans.factory.annotation.Qualifier;
  24. import org.springframework.beans.factory.annotation.Value;
  25. import org.springframework.boot.context.properties.EnableConfigurationProperties;
  26. import org.springframework.boot.web.servlet.ServletRegistrationBean;
  27. import org.springframework.context.annotation.Bean;
  28. import org.springframework.context.annotation.Configuration;
  29. import org.springframework.context.annotation.Primary;
  30. import org.springframework.core.env.Environment;
  31. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
  32. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  33. import org.springframework.transaction.annotation.EnableTransactionManagement;
  34.  
  35. import javax.sql.DataSource;
  36. import java.sql.SQLException;
  37. import java.util.HashMap;
  38. import java.util.Map;
  39. import java.util.Properties;
  40. import java.util.concurrent.ConcurrentHashMap;
  41.  
  42. @Configuration
  43. @EnableConfigurationProperties({FirstDsProp.class, SecondDsProp.class})
  44. @EnableTransactionManagement(proxyTargetClass = true)
  45. @MapperScan(basePackages = "com.hyc.dao", sqlSessionTemplateRef = "sqlSessionTemplate")
  46. public class DataSourceConfig {
  47.     @Value("${snow.work.id:0}")
  48.     private Long workId;
  49.  
  50.     @Value("${snow.datacenter.id:0}")
  51.     private Long datacenterId;
  52.  
  53.     @Autowired
  54.     private Environment env;
  55.  
  56.     /**
  57.      * druid数据源1
  58.      *
  59.      * @param firstDSProp
  60.      * @return
  61.      */
  62.     @Bean("ds0")
  63.     public DataSource ds0(FirstDsProp firstDSProp) {
  64.         Map<String, Object> dsMap = new HashMap<>();
  65.         dsMap.put("type", firstDSProp.getType());
  66.         dsMap.put("url", firstDSProp.getJdbcUrl());
  67.         dsMap.put("username", firstDSProp.getUsername());
  68.         dsMap.put("password", firstDSProp.getPassword());
  69.  
  70.         DruidDataSource ds = (DruidDataSource) DataSourceUtil.buildDataSource(dsMap);
  71.         ds.setProxyFilters(Lists.newArrayList(statFilter()));
  72.         // 每个分区最大的连接数
  73.         ds.setMaxActive(20);
  74.         // 每个分区最小的连接数
  75.         ds.setMinIdle(5);
  76.  
  77.         return ds;
  78.     }
  79.  
  80.     /**
  81.      * druid数据源2
  82.      *
  83.      * @param secondDsProp
  84.      * @return
  85.      */
  86.     @Bean("ds1")
  87.     public DataSource ds1(SecondDsProp secondDsProp) {
  88.         Map<String, Object> dsMap = new HashMap<>();
  89.         dsMap.put("type", secondDsProp.getType());
  90.         dsMap.put("url", secondDsProp.getJdbcUrl());
  91.         dsMap.put("username", secondDsProp.getUsername());
  92.         dsMap.put("password", secondDsProp.getPassword());
  93.  
  94.         DruidDataSource ds = (DruidDataSource) DataSourceUtil.buildDataSource(dsMap);
  95.         ds.setProxyFilters(Lists.newArrayList(statFilter()));
  96.         // 每个分区最大的连接数
  97.         ds.setMaxActive(20);
  98.         // 每个分区最小的连接数
  99.         ds.setMinIdle(5);
  100.  
  101.         return ds;
  102.     }
  103.  
  104.     @Bean
  105.     public Filter statFilter() {
  106.         StatFilter filter = new StatFilter();
  107.         filter.setSlowSqlMillis(5000);
  108.         filter.setLogSlowSql(true);
  109.         filter.setMergeSql(true);
  110.         return filter;
  111.     }
  112.  
  113.     @Bean
  114.     public ServletRegistrationBean statViewServlet() {
  115.         //创建servlet注册实体
  116.         ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
  117.         //设置ip白名单
  118.         servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
  119.         //设置控制台管理用户
  120.         servletRegistrationBean.addInitParameter("loginUsername", "admin");
  121.         servletRegistrationBean.addInitParameter("loginPassword", "123456");
  122.         //是否可以重置数据
  123.         servletRegistrationBean.addInitParameter("resetEnable", "false");
  124.         return servletRegistrationBean;
  125.     }
  126.  
  127.     /**
  128.      * shardingjdbc数据源
  129.      *
  130.      * @return
  131.      * @throws SQLException
  132.      */
  133.     @Bean("dataSource")
  134.     public DataSource dataSource(@Qualifier("ds0") DataSource ds0, @Qualifier("ds1") DataSource ds1) throws SQLException {
  135.         // 配置真实数据源
  136.         Map<String, DataSource> dataSourceMap = new HashMap<>();
  137.         dataSourceMap.put("ds0", ds0);
  138.         dataSourceMap.put("ds1", ds1);
  139.         // 配置分片规则
  140.         ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
  141.         shardingRuleConfig.getTableRuleConfigs().add(userRuleConfig());
  142.         shardingRuleConfig.getTableRuleConfigs().add(addressRuleConfig());
  143.         shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig());
  144.         shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfig());
  145.         shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  146.  
  147.         shardingRuleConfig.getBindingTableGroups().add("t_user, t_user_address");
  148.         shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
  149.  
  150.         shardingRuleConfig.getBroadcastTables().add("t_product");
  151.  
  152.         Properties p = new Properties();
  153.         p.setProperty("sql.show",Boolean.TRUE.toString());
  154.         // 获取数据源对象
  155.         DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), p);
  156.         return dataSource;
  157.     }
  158.  
  159.     /**
  160.      * 需要手动配置事务管理器
  161.      * @param dataSource
  162.      * @return
  163.      */
  164.     @Bean
  165.     public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){
  166.         return new DataSourceTransactionManager(dataSource);
  167.     }
  168.  
  169.     @Bean("sqlSessionFactory")
  170.     @Primary
  171.     public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
  172.         SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
  173.         bean.setDataSource(dataSource);
  174.         bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
  175.         return bean.getObject();
  176.     }
  177.  
  178.     @Bean("sqlSessionTemplate")
  179.     @Primary
  180.     public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
  181.         return new SqlSessionTemplate(sqlSessionFactory);
  182.     }
  183.  
  184.     private TableRuleConfiguration userRuleConfig() {
  185.         TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
  186.         tableRuleConfig.setLogicTable("t_user");
  187.         tableRuleConfig.setActualDataNodes("ds${0..1}.t_user_${0..1}");
  188.         tableRuleConfig.setKeyGeneratorColumnName("user_id");
  189.         tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
  190.         tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  191.         tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("gender", new GenderShardingAlgorithm(), new GenderShardingAlgorithm()));
  192.         return tableRuleConfig;
  193.     }
  194.  
  195.     private TableRuleConfiguration addressRuleConfig() {
  196.         TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
  197.         tableRuleConfig.setLogicTable("t_user_address");
  198.         tableRuleConfig.setActualDataNodes("ds${0..1}.t_user_address_${0..1}");
  199.         tableRuleConfig.setKeyGeneratorColumnName("address_id");
  200.         tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
  201.         tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  202.         tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("gender", new GenderShardingAlgorithm(), new GenderShardingAlgorithm()));
  203.         return tableRuleConfig;
  204.     }
  205.  
  206.     private TableRuleConfiguration orderRuleConfig() {
  207.         TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
  208.         tableRuleConfig.setLogicTable("t_order");
  209.         tableRuleConfig.setActualDataNodes("ds${0..1}.t_order_${0..1}");
  210.         tableRuleConfig.setKeyGeneratorColumnName("order_id");
  211.         tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
  212.         tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  213.         tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  214.         return tableRuleConfig;
  215.     }
  216.  
  217.     private TableRuleConfiguration orderItemRuleConfig() {
  218.         TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
  219.         tableRuleConfig.setLogicTable("t_order_item");
  220.         tableRuleConfig.setActualDataNodes("ds${0..1}.t_order_item_${0..1}");
  221.         tableRuleConfig.setKeyGeneratorColumnName("order_item_id");
  222.         tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
  223.         tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  224.         tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
  225.         return tableRuleConfig;
  226.     }
  227. }
  1. package com.hyc.util;
  2.  
  3. import lombok.extern.slf4j.Slf4j;
  4. import org.springframework.boot.jdbc.DataSourceBuilder;
  5.  
  6. import javax.sql.DataSource;
  7. import java.util.Map;
  8.  
  9. @Slf4j
  10. public class DataSourceUtil {
  11.     private static final String DATASOURCE_TYPE_DEFAULT = "com.zaxxer.hikari.HikariDataSource";
  12.  
  13.     public static DataSource buildDataSource(Map<String, Object> dataSourceMap) {
  14.         Object type = dataSourceMap.get("type");
  15.         if (type == null) {
  16.             type = DATASOURCE_TYPE_DEFAULT;
  17.         }
  18.         try {
  19.             Class<? extends DataSource> dataSourceType;
  20.             dataSourceType = (Class<? extends DataSource>) Class.forName((String) type);
  21.             //String driverClassName = dataSourceMap.get("driver").toString();
  22.             String url = dataSourceMap.get("url").toString();
  23.             String username = dataSourceMap.get("username").toString();
  24.             String password = dataSourceMap.get("password").toString();
  25.             // 自定义DataSource配置
  26.             DataSourceBuilder factory = DataSourceBuilder.create().url(url).username(username).password(password).type(dataSourceType);
  27.             return factory.build();
  28.         } catch (Exception e) {
  29.             log.error("构建数据源" + type + "出错", e);
  30.         }
  31.         return null;
  32.     }
  33. }

 

  • 启动类
  1. package com.hyc;
  2.  
  3. import org.springframework.boot.SpringApplication;
  4. import org.springframework.boot.autoconfigure.SpringBootApplication;
  5. import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
  6.  
  7. @SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
  8. public class Shard3ManualApplication {
  9.  
  10.     public static void main(String[] args) {
  11.         SpringApplication.run(Shard3ManualApplication.class, args);
  12.     }
  13. }
  • 编写controller和service
  1. package com.hyc.service;
  2.  
  3. import com.hyc.dao.*;
  4. import com.hyc.entity.*;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Service;
  7. import org.springframework.transaction.annotation.Transactional;
  8.  
  9. import java.util.HashMap;
  10. import java.util.Map;
  11.  
  12. @Service
  13. public class BussinessService {
  14.     @Autowired
  15.     private UserMapper userMapper;
  16.     @Autowired
  17.     private UserAddressMapper addressMapper;
  18.     @Autowired
  19.     private OrderMapper orderMapper;
  20.     @Autowired
  21.     private OrderItemMapper orderItemMapper;
  22.     @Autowired
  23.     private ProductMapper productMapper;
  24.  
  25.     @Transactional
  26.     public void saveAll(User user, UserAddress address, Order order, OrderItem orderItem) {
  27.         userMapper.insertSelective(user);
  28.  
  29.         address.setUserId(user.getUserId());
  30.         addressMapper.insertSelective(address);
  31.  
  32.         order.setUserId(user.getUserId());
  33.         orderMapper.insertSelective(order);
  34.  
  35.         orderItem.setOrderId(order.getOrderId());
  36.         orderItem.setUserId(user.getUserId());
  37.         orderItemMapper.insertSelective(orderItem);
  38.     }
  39.  
  40.     @Transactional
  41.     public void saveProduct(Product product) {
  42.         productMapper.insertSelective(product);
  43.     }
  44.  
  45.     public Map<String, Object> findAll() {
  46.         Map<String, Object> result = new HashMap<>();
  47.  
  48.         Long userId = 594099642262884355L;
  49.         User user = userMapper.selectByPrimaryKey(userId);
  50.         result.put("user", user);
  51.  
  52.         UserAddress address = addressMapper.selectByUserId(userId);
  53.         result.put("address", address);
  54.  
  55.         Order order = orderMapper.selectByUserId(userId);
  56.         result.put("order", order);
  57.  
  58.         OrderItem orderItem = orderItemMapper.selectByOrderId(order.getOrderId());
  59.         result.put("orderItem", orderItem);
  60.  
  61.         return result;
  62.     }
  63. }
  1. package com.hyc.controller;
  2.  
  3. import cn.hutool.core.date.DateUtil;
  4. import com.alibaba.fastjson.JSON;
  5. import com.hyc.entity.*;
  6. import com.hyc.enums.GenderEnum;
  7. import com.hyc.enums.OrderStatusEnum;
  8. import com.hyc.service.BussinessService;
  9. import com.hyc.util.SnowflakeIdGenerator;
  10. import lombok.extern.slf4j.Slf4j;
  11. import org.springframework.beans.factory.annotation.Autowired;
  12. import org.springframework.beans.propertyeditors.CustomDateEditor;
  13. import org.springframework.web.bind.WebDataBinder;
  14. import org.springframework.web.bind.annotation.GetMapping;
  15. import org.springframework.web.bind.annotation.InitBinder;
  16. import org.springframework.web.bind.annotation.RestController;
  17. import org.springframework.web.context.request.WebRequest;
  18.  
  19. import java.math.BigDecimal;
  20. import java.text.DateFormat;
  21. import java.text.SimpleDateFormat;
  22. import java.util.Date;
  23. import java.util.HashMap;
  24. import java.util.Map;
  25.  
  26. @Slf4j
  27. @RestController
  28. public class BussinessController {
  29.     @Autowired
  30.     private BussinessService bussinessService;
  31.     @Autowired
  32.     private SnowflakeIdGenerator snowflakeIdGenerator;
  33.  
  34.     @InitBinder
  35.     public void initBinder(WebDataBinder binder, WebRequest request) {
  36.         //转换日期
  37.         DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  38.         binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
  39.     }
  40.  
  41.     @GetMapping("/buss/createProduct")
  42.     public String createProduct() {
  43.         for (int i = 1; i < 10; i++) {
  44.             Product product = new Product();
  45.             product.setProductId(snowflakeIdGenerator.nextId());
  46.             product.setCode("P00" + i);
  47.             product.setName("商品" + i);
  48.             product.setDesc("商品描述" + i);
  49.             bussinessService.saveProduct(product);
  50.         }
  51.         return "成功";
  52.     }
  53.  
  54.     @GetMapping("/buss/create")
  55.     public String create() {
  56.         for (int i = 1; i <= 21; i++) {
  57.             User user = new User();
  58.             user.setName("王大毛" + i);
  59.             user.setGender(GenderEnum.MALE.getCode());
  60.             user.setAge(20 + i);
  61.             user.setBirthDate(DateUtil.parseDate("1989-08-16"));
  62.             user.setIdNumber("4101231989691" + i);
  63.  
  64.             UserAddress address = new UserAddress();
  65.             address.setCity("某某市");
  66.             address.setDetail("某某街道");
  67.             address.setDistrict("某某区");
  68.             address.setProvince("江苏省");
  69.             address.setSort(1);
  70.             address.setGender(user.getGender());
  71.  
  72.             Order order = new Order();
  73.             order.setOrderAmount(new BigDecimal(100));
  74.             order.setOrderNo("ORDER_00" + i);
  75.             order.setOrderStatus(OrderStatusEnum.PROCESSING.getCode());
  76.             order.setRemark("测试");
  77.  
  78.             OrderItem orderItem = new OrderItem();
  79.             orderItem.setItemPrice(new BigDecimal(5));
  80.             orderItem.setOrderTime(DateUtil.parse("2019-06-27 17:50:05"));
  81.             orderItem.setProductId(593860920283758592L);
  82.             orderItem.setTotalNum(20);
  83.             orderItem.setTotalPrice(new BigDecimal(100));
  84.  
  85.             bussinessService.saveAll(user, address, order, orderItem);
  86.         }
  87.  
  88.         for (int i = 1; i <= 21; i++) {
  89.             User user = new User();
  90.             user.setName("王大莉" + i);
  91.             user.setGender(GenderEnum.FEMALE.getCode());
  92.             user.setAge(20 + i);
  93.             user.setBirthDate(DateUtil.parseDate("1989-08-16"));
  94.             user.setIdNumber("1101231989691" + i);
  95.  
  96.             UserAddress address = new UserAddress();
  97.             address.setCity("某某市");
  98.             address.setDetail("某某街道");
  99.             address.setDistrict("某某区");
  100.             address.setProvince("江苏省");
  101.             address.setSort(1);
  102.             address.setGender(user.getGender());
  103.  
  104.             Order order = new Order();
  105.             order.setOrderAmount(new BigDecimal(100));
  106.             order.setOrderNo("ORDER_00" + i);
  107.             order.setOrderStatus(OrderStatusEnum.PROCESSING.getCode());
  108.             order.setRemark("测试");
  109.  
  110.             OrderItem orderItem = new OrderItem();
  111.             orderItem.setItemPrice(new BigDecimal(5));
  112.             orderItem.setOrderTime(DateUtil.parse("2019-06-27 17:50:05"));
  113.             orderItem.setProductId(593860924259958784L);
  114.             orderItem.setTotalNum(20);
  115.             orderItem.setTotalPrice(new BigDecimal(100));
  116.  
  117.             bussinessService.saveAll(user, address, order, orderItem);
  118.         }
  119.  
  120.         return "成功";
  121.     }
  122.  
  123.     @GetMapping("/buss/all")
  124.     public String findAll(){
  125.         Map<String,Object> result = new HashMap<>();
  126.         result = bussinessService.findAll();
  127.         return JSON.toJSONString(result);
  128.     }
  129. }
  • 测试

请求http://localhost:8080/buss/createProduct创建商品，可以发现商品表作为广播表在两个库中已经插入了两份一模一样的数据

请求http://localhost:8080/buss/create，创建用户、订单等，观察数据库，以用户表举例，用户表按照user_id的奇偶性分表保存在了两个库里

也按照性别的不同存在了不同的分表里面

 

按照我们的预想分库分表，还可以做查询和事务等相关测试，这里不一一列举了 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/210899
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号