SringBoot集成Sharding-Jdbc 实践

一、数据库规划

192.128.127.129和192.128.127.134

192.128.127.129 创建数据库，数据库名为sharding_test

 192.128.127.134 创建数据库，数据库名为sharding_test2

 如下图所示

                   

 在每个分库下建立一样的分表，用户表，用户地址表，订单表，订单明细表和商品表。同一个用户的用户表数据和订单数据在一个库中。除了商品表不需要分表以外，其他表都需要分表（商品表作为全局表放在每个库里）以提高查询性能。建表语句如下：

SET NAMES utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0`  (
  `order_id` bigint(32) NOT NULL COMMENT '主键',
  `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  `order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',
  `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',
  PRIMARY KEY (`order_id`),
  INDEX `idx_order_user_id`(`user_id`),
  INDEX `idx_order_order_no`(`order_no`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';
 
-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` bigint(32) NOT NULL COMMENT '主键',
  `order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  `order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',
  `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',
  PRIMARY KEY (`order_id`),
  INDEX `idx_order_user_id`(`user_id`),
  INDEX `idx_order_order_no`(`order_no`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';
 
-- ----------------------------
-- Table structure for t_order_item_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_0`;
CREATE TABLE `t_order_item_0`  (
  `order_item_id` bigint(32) NOT NULL COMMENT '主键',
  `order_id` bigint(32) NOT NULL COMMENT '订单id',
  `product_id` bigint(32) NOT NULL COMMENT '商品id',
  `item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',
  `total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',
  `total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  `order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  PRIMARY KEY (`order_item_id`),
  INDEX `idx_order_item_order_id`(`order_id`),
  INDEX `idx_order_item_user_id`(`user_id`),
  INDEX `idx_order_item_product_id`(`product_id`),
  INDEX `idx_order_item_order_time`(`order_time`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';
 
-- ----------------------------
-- Table structure for t_order_item_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_1`;
CREATE TABLE `t_order_item_1`  (
  `order_item_id` bigint(32) NOT NULL COMMENT '主键',
  `order_id` bigint(32) NOT NULL COMMENT '订单id',
  `product_id` bigint(32) NOT NULL COMMENT '商品id',
  `item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',
  `total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',
  `total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  `order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  PRIMARY KEY (`order_item_id`),
  INDEX `idx_order_item_order_id`(`order_id`),
  INDEX `idx_order_item_user_id`(`user_id`),
  INDEX `idx_order_item_product_id`(`product_id`),
  INDEX `idx_order_item_order_time`(`order_time`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';
 
-- ----------------------------
-- Table structure for t_product
-- ----------------------------
DROP TABLE IF EXISTS `t_product`;
CREATE TABLE `t_product`  (
  `product_id` bigint(32) NOT NULL COMMENT '主键',
  `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品编码',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
  `desc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品描述',
  PRIMARY KEY (`product_id`),
  INDEX `idx_user_product_code`(`code`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '商品表';
 
-- ----------------------------
-- Table structure for t_user_0
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0`;
CREATE TABLE `t_user_0`  (
  `user_id` bigint(32) NOT NULL COMMENT '主键',
  `id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  `birth_date` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`user_id`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';
 
-- ----------------------------
-- Table structure for t_user_1
-- ----------------------------
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1`  (
  `user_id` bigint(32) NOT NULL COMMENT '主键',
  `id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  `birth_date` date DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`user_id`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';
 
-- ----------------------------
-- Table structure for t_user_address_0
-- ----------------------------
DROP TABLE IF EXISTS `t_user_address_0`;
CREATE TABLE `t_user_address_0`  (
  `address_id` bigint(32) NOT NULL COMMENT '主键',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',
  `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',
  `district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
  `detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',
  `sort` int(4) DEFAULT 1 COMMENT '排序',
  `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  PRIMARY KEY (`address_id`),
  INDEX `idx_user_address_user_id`(`user_id`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';
 
-- ----------------------------
-- Table structure for t_user_address_1
-- ----------------------------
DROP TABLE IF EXISTS `t_user_address_1`;
CREATE TABLE `t_user_address_1`  (
  `address_id` bigint(32) NOT NULL COMMENT '主键',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',
  `city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',
  `district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
  `detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',
  `sort` int(4) DEFAULT 1 COMMENT '排序',
  `gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',
  PRIMARY KEY (`address_id`),
  INDEX `idx_user_address_user_id`(`user_id`) 
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';

　　

二、工程创建

整个工程结构如下下图所示

1、创建Spring Boot工程

工程名为my-sharding-jdbc-demo

Spring Boot 版本为2.2.11，选择的依赖如下图所示

2、MyBatis-Generator生成相关代码

(1) mapping文件

 OrderItemMapping.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.myshardingjdbcdemo.dao.OrderItemMapper" >
  <resultMap id="BaseResultMap" type="com.example.myshardingjdbcdemo.model.OrderItem" >
    <id column="order_item_id" property="orderItemId" jdbcType="BIGINT" />
    <result column="order_id" property="orderId" jdbcType="BIGINT" />
    <result column="product_id" property="productId" jdbcType="BIGINT" />
    <result column="item_price" property="itemPrice" jdbcType="DECIMAL" />
    <result column="total_num" property="totalNum" jdbcType="INTEGER" />
    <result column="total_price" property="totalPrice" jdbcType="DECIMAL" />
    <result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
    <result column="user_id" property="userId" jdbcType="BIGINT" />
  </resultMap>
  <sql id="Base_Column_List" >
    order_item_id, order_id, product_id, item_price, total_num, total_price, order_time, 
    user_id
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from t_order_item
    where order_item_id = #{orderItemId,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from t_order_item
    where order_item_id = #{orderItemId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.myshardingjdbcdemo.model.OrderItem" >
    insert into t_order_item (order_item_id, order_id, product_id, 
      item_price, total_num, total_price, 
      order_time, user_id)
    values (#{orderItemId,jdbcType=BIGINT}, #{orderId,jdbcType=BIGINT}, #{productId,jdbcType=BIGINT}, 
      #{itemPrice,jdbcType=DECIMAL}, #{totalNum,jdbcType=INTEGER}, #{totalPrice,jdbcType=DECIMAL}, 
      #{orderTime,jdbcType=TIMESTAMP}, #{userId,jdbcType=BIGINT})
  </insert>
  <insert id="insertSelective" parameterType="com.example.myshardingjdbcdemo.model.OrderItem" >
    insert into t_order_item
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="orderItemId != null" >
        order_item_id,
      </if>
      <if test="orderId != null" >
        order_id,
      </if>
      <if test="productId != null" >
        product_id,
      </if>
      <if test="itemPrice != null" >
        item_price,
      </if>
      <if test="totalNum != null" >
        total_num,
      </if>
      <if test="totalPrice != null" >
        total_price,
      </if>
      <if test="orderTime != null" >
        order_time,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="orderItemId != null" >
        #{orderItemId,jdbcType=BIGINT},
      </if>
      <if test="orderId != null" >
        #{orderId,jdbcType=BIGINT},
      </if>
      <if test="productId != null" >
        #{productId,jdbcType=BIGINT},
      </if>
      <if test="itemPrice != null" >
        #{itemPrice,jdbcType=DECIMAL},
      </if>
      <if test="totalNum != null" >
        #{totalNum,jdbcType=INTEGER},
      </if>
      <if test="totalPrice != null" >
        #{totalPrice,jdbcType=DECIMAL},
      </if>
      <if test="orderTime != null" >
        #{orderTime,jdbcType=TIMESTAMP},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.myshardingjdbcdemo.model.OrderItem" >
    update t_order_item
    <set >
      <if test="orderId != null" >
        order_id = #{orderId,jdbcType=BIGINT},
      </if>
      <if test="productId != null" >
        product_id = #{productId,jdbcType=BIGINT},
      </if>
      <if test="itemPrice != null" >
        item_price = #{itemPrice,jdbcType=DECIMAL},
      </if>
      <if test="totalNum != null" >
        total_num = #{totalNum,jdbcType=INTEGER},
      </if>
      <if test="totalPrice != null" >
        total_price = #{totalPrice,jdbcType=DECIMAL},
      </if>
      <if test="orderTime != null" >
        order_time = #{orderTime,jdbcType=TIMESTAMP},
      </if>
      <if test="userId != null" >
        user_id = #{userId,jdbcType=BIGINT},
      </if>
    </set>
    where order_item_id = #{orderItemId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.myshardingjdbcdemo.model.OrderItem" >
    update t_order_item
    set order_id = #{orderId,jdbcType=BIGINT},
      product_id = #{productId,jdbcType=BIGINT},
      item_price = #{itemPrice,jdbcType=DECIMAL},
      total_num = #{totalNum,jdbcType=INTEGER},
      total_price = #{totalPrice,jdbcType=DECIMAL},
      order_time = #{orderTime,jdbcType=TIMESTAMP},
      user_id = #{userId,jdbcType=BIGINT}
    where order_item_id = #{orderItemId,jdbcType=BIGINT}
  </update>
</mapper>

　　

OrderMapping.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.myshardingjdbcdemo.dao.OrderMapper" >
  <resultMap id="BaseResultMap" type="com.example.myshardingjdbcdemo.model.Order" >
    <id column="order_id" property="orderId" jdbcType="BIGINT" />
    <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
    <result column="user_id" property="userId" jdbcType="BIGINT" />
    <result column="order_amount" property="orderAmount" jdbcType="DECIMAL" />
    <result column="order_status" property="orderStatus" jdbcType="INTEGER" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    order_id, order_no, user_id, order_amount, order_status, remark
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from t_order
    where order_id = #{orderId,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from t_order
    where order_id = #{orderId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.myshardingjdbcdemo.model.Order" >
    insert into t_order (order_id, order_no, user_id, 
      order_amount, order_status, remark
      )
    values (#{orderId,jdbcType=BIGINT}, #{orderNo,jdbcType=VARCHAR}, #{userId,jdbcType=BIGINT}, 
      #{orderAmount,jdbcType=DECIMAL}, #{orderStatus,jdbcType=INTEGER}, #{remark,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.example.myshardingjdbcdemo.model.Order" >
    insert into t_order
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="orderId != null" >
        order_id,
      </if>
      <if test="orderNo != null" >
        order_no,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
      <if test="orderAmount != null" >
        order_amount,
      </if>
      <if test="orderStatus != null" >
        order_status,
      </if>
      <if test="remark != null" >
        remark,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="orderId != null" >
        #{orderId,jdbcType=BIGINT},
      </if>
      <if test="orderNo != null" >
        #{orderNo,jdbcType=VARCHAR},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=BIGINT},
      </if>
      <if test="orderAmount != null" >
        #{orderAmount,jdbcType=DECIMAL},
      </if>
      <if test="orderStatus != null" >
        #{orderStatus,jdbcType=INTEGER},
      </if>
      <if test="remark != null" >
        #{remark,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.myshardingjdbcdemo.model.Order" >
    update t_order
    <set >
      <if test="orderNo != null" >
        order_no = #{orderNo,jdbcType=VARCHAR},
      </if>
      <if test="userId != null" >
        user_id = #{userId,jdbcType=BIGINT},
      </if>
      <if test="orderAmount != null" >
        order_amount = #{orderAmount,jdbcType=DECIMAL},
      </if>
      <if test="orderStatus != null" >
        order_status = #{orderStatus,jdbcType=INTEGER},
      </if>
      <if test="remark != null" >
        remark = #{remark,jdbcType=VARCHAR},
      </if>
    </set>
    where order_id = #{orderId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.myshardingjdbcdemo.model.Order" >
    update t_order
    set order_no = #{orderNo,jdbcType=VARCHAR},
      user_id = #{userId,jdbcType=BIGINT},
      order_amount = #{orderAmount,jdbcType=DECIMAL},
      order_status = #{orderStatus,jdbcType=INTEGER},
      remark = #{remark,jdbcType=VARCHAR}
    where order_id = #{orderId,jdbcType=BIGINT}
  </update>
</mapper>

　　

ProductMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.myshardingjdbcdemo.dao.ProductMapper" >
  <resultMap id="BaseResultMap" type="com.example.myshardingjdbcdemo.model.Product" >
    <id column="product_id" property="productId" jdbcType="BIGINT" />
    <result column="code" property="code" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="remark" property="remark" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    product_id, code, name, remark
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from t_product
    where product_id = #{productId,jdbcType=BIGINT}
  </select>
  <select id="selectProductList" resultMap="BaseResultMap"  >
    select
    <include refid="Base_Column_List" />
    from t_product
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from t_product
    where product_id = #{productId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.myshardingjdbcdemo.model.Product" >
    insert into t_product (product_id, code, name, 
      remark)
    values (#{productId,jdbcType=BIGINT}, #{code,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{remark,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.example.myshardingjdbcdemo.model.Product" >
    insert into t_product
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="productId != null" >
        product_id,
      </if>
      <if test="code != null" >
        code,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="remark != null" >
        remark,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="productId != null" >
        #{productId,jdbcType=BIGINT},
      </if>
      <if test="code != null" >
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="remark != null" >
        #{remark,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.myshardingjdbcdemo.model.Product" >
    update t_product
    <set >
      <if test="code != null" >
        code = #{code,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="remark != null" >
        remark = #{remark,jdbcType=VARCHAR},
      </if>
    </set>
    where product_id = #{productId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.myshardingjdbcdemo.model.Product" >
    update t_product
    set code = #{code,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR},
      remark = #{remark,jdbcType=VARCHAR}
    where product_id = #{productId,jdbcType=BIGINT}
  </update>
</mapper>

　　

UserAddressMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.myshardingjdbcdemo.dao.UserAddressMapper" >
  <resultMap id="BaseResultMap" type="com.example.myshardingjdbcdemo.model.UserAddress" >
    <id column="address_id" property="addressId" jdbcType="BIGINT" />
    <result column="user_id" property="userId" jdbcType="BIGINT" />
    <result column="province" property="province" jdbcType="VARCHAR" />
    <result column="city" property="city" jdbcType="VARCHAR" />
    <result column="district" property="district" jdbcType="VARCHAR" />
    <result column="detail" property="detail" jdbcType="VARCHAR" />
    <result column="sort" property="sort" jdbcType="INTEGER" />
    <result column="gender" property="gender" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    address_id, user_id, province, city, district, detail, sort, gender
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from t_user_address
    where address_id = #{addressId,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from t_user_address
    where address_id = #{addressId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.myshardingjdbcdemo.model.UserAddress" >
    insert into t_user_address (address_id, user_id, province, 
      city, district, detail, 
      sort, gender)
    values (#{addressId,jdbcType=BIGINT}, #{userId,jdbcType=BIGINT}, #{province,jdbcType=VARCHAR}, 
      #{city,jdbcType=VARCHAR}, #{district,jdbcType=VARCHAR}, #{detail,jdbcType=VARCHAR}, 
      #{sort,jdbcType=INTEGER}, #{gender,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.example.myshardingjdbcdemo.model.UserAddress" >
    insert into t_user_address
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="addressId != null" >
        address_id,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
      <if test="province != null" >
        province,
      </if>
      <if test="city != null" >
        city,
      </if>
      <if test="district != null" >
        district,
      </if>
      <if test="detail != null" >
        detail,
      </if>
      <if test="sort != null" >
        sort,
      </if>
      <if test="gender != null" >
        gender,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="addressId != null" >
        #{addressId,jdbcType=BIGINT},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=BIGINT},
      </if>
      <if test="province != null" >
        #{province,jdbcType=VARCHAR},
      </if>
      <if test="city != null" >
        #{city,jdbcType=VARCHAR},
      </if>
      <if test="district != null" >
        #{district,jdbcType=VARCHAR},
      </if>
      <if test="detail != null" >
        #{detail,jdbcType=VARCHAR},
      </if>
      <if test="sort != null" >
        #{sort,jdbcType=INTEGER},
      </if>
      <if test="gender != null" >
        #{gender,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.myshardingjdbcdemo.model.UserAddress" >
    update t_user_address
    <set >
      <if test="userId != null" >
        user_id = #{userId,jdbcType=BIGINT},
      </if>
      <if test="province != null" >
        province = #{province,jdbcType=VARCHAR},
      </if>
      <if test="city != null" >
        city = #{city,jdbcType=VARCHAR},
      </if>
      <if test="district != null" >
        district = #{district,jdbcType=VARCHAR},
      </if>
      <if test="detail != null" >
        detail = #{detail,jdbcType=VARCHAR},
      </if>
      <if test="sort != null" >
        sort = #{sort,jdbcType=INTEGER},
      </if>
      <if test="gender != null" >
        gender = #{gender,jdbcType=INTEGER},
      </if>
    </set>
    where address_id = #{addressId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.myshardingjdbcdemo.model.UserAddress" >
    update t_user_address
    set user_id = #{userId,jdbcType=BIGINT},
      province = #{province,jdbcType=VARCHAR},
      city = #{city,jdbcType=VARCHAR},
      district = #{district,jdbcType=VARCHAR},
      detail = #{detail,jdbcType=VARCHAR},
      sort = #{sort,jdbcType=INTEGER},
      gender = #{gender,jdbcType=INTEGER}
    where address_id = #{addressId,jdbcType=BIGINT}
  </update>
</mapper>

　　

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.myshardingjdbcdemo.dao.UserMapper" >
  <resultMap id="BaseResultMap" type="com.example.myshardingjdbcdemo.model.User" >
    <id column="user_id" property="userId" jdbcType="BIGINT" />
    <result column="id_number" property="idNumber" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="gender" property="gender" jdbcType="INTEGER" />
    <result column="birth_date" property="birthDate" jdbcType="DATE" />
  </resultMap>
  <sql id="Base_Column_List" >
    user_id, id_number, name, age, gender, birth_date
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    select 
    <include refid="Base_Column_List" />
    from t_user
    where user_id = #{userId,jdbcType=BIGINT}
  </select>
  <select id="selectByUserIdGender" resultMap="BaseResultMap"  >
    select
    <include refid="Base_Column_List" />
    from t_user
    where user_id = #{userId,jdbcType=BIGINT} and gender =  #{gender,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    delete from t_user
    where user_id = #{userId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.example.myshardingjdbcdemo.model.User" >
    insert into t_user (user_id, id_number, name, 
      age, gender, birth_date
      )
    values (#{userId,jdbcType=BIGINT}, #{idNumber,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{age,jdbcType=INTEGER}, #{gender,jdbcType=INTEGER}, #{birthDate,jdbcType=DATE}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.example.myshardingjdbcdemo.model.User" >
    insert into t_user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="userId != null" >
        user_id,
      </if>
      <if test="idNumber != null" >
        id_number,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="age != null" >
        age,
      </if>
      <if test="gender != null" >
        gender,
      </if>
      <if test="birthDate != null" >
        birth_date,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="userId != null" >
        #{userId,jdbcType=BIGINT},
      </if>
      <if test="idNumber != null" >
        #{idNumber,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
      <if test="gender != null" >
        #{gender,jdbcType=INTEGER},
      </if>
      <if test="birthDate != null" >
        #{birthDate,jdbcType=DATE},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.example.myshardingjdbcdemo.model.User" >
    update t_user
    <set >
      <if test="idNumber != null" >
        id_number = #{idNumber,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="gender != null" >
        gender = #{gender,jdbcType=INTEGER},
      </if>
      <if test="birthDate != null" >
        birth_date = #{birthDate,jdbcType=DATE},
      </if>
    </set>
    where user_id = #{userId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.example.myshardingjdbcdemo.model.User" >
    update t_user
    set id_number = #{idNumber,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER},
      gender = #{gender,jdbcType=INTEGER},
      birth_date = #{birthDate,jdbcType=DATE}
    where user_id = #{userId,jdbcType=BIGINT}
  </update>
</mapper>

　　

（2） dao层

（3） Mode层

3、写分库和分表的算法，分库分表时必然会根据表里的字段设计分表分库的算法，对于用户表和用户地址表用user_id取模来分库，使用gender来分表。对于订单和订单明细表，使用user_id取模来分库，order_id取模来分表。因此需要用到两个分片算法，根据性别分片和根据id分片

package com.example.myshardingjdbcdemo.shardingalgorithm;

import com.example.myshardingjdbcdemo.enums.GenderEnum;
import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;

import java.util.Collection;
import java.util.LinkedHashSet;

/**
 * @description: 按性别分表
 * @author: think
 * @create: 2020-11-16 10:08
 */
public class GenderShardingAlgorithm implements PreciseShardingAlgorithm<Integer>, RangeShardingAlgorithm<Integer> {
    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding result for data source or table's name
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        String databaseName = availableTargetNames.stream().findFirst().get();

        for (String dbName : availableTargetNames) {
            if (dbName.endsWith(genderToTableSuffix(shardingValue.getValue()))) {
                databaseName = dbName;
            }
        }

        return databaseName;
    }

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding results for data sources or tables's names
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Integer> shardingValue) {
        Collection<String> dbs = new LinkedHashSet<>(availableTargetNames.size());

        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String dbName : availableTargetNames) {
                if (dbName.endsWith(genderToTableSuffix(i))) {
                    dbs.add(dbName);
                }
            }
        }
        return dbs;
    }

    /**
     * 字段与分库的映射关系
     *
     * @param gender
     * @return
     */
    private String genderToTableSuffix(Integer gender) {
        return gender.equals(GenderEnum.MALE.getCode()) ? "0" : "1";
    }
}

　　

package com.example.myshardingjdbcdemo.shardingalgorithm;

import com.google.common.collect.Range;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;

import java.util.Collection;
import java.util.LinkedHashSet;

/**
 * @description: 根据分库分表
 * @author:
 * @create: 2020-11-16 10:16
 */
public class IdShardingAlgorithm  implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding result for data source or table's name
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String table = availableTargetNames.stream().findFirst().get();

        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(idToTableSuffix(shardingValue.getValue()))) {
                table = tableName;
            }
        }

        return table;
    }

    /**
     * Sharding.
     *
     * @param availableTargetNames available data sources or tables's names
     * @param shardingValue        sharding value
     * @return sharding results for data sources or tables's names
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        Collection<String> dbs = new LinkedHashSet<>(availableTargetNames.size());

        Range<Long> range = (Range<Long>) shardingValue.getValueRange();
        for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String dbName : availableTargetNames) {
                if (dbName.endsWith(idToTableSuffix(i))) {
                    dbs.add(dbName);
                }
            }
        }

        return dbs;
    }

    /**
     * 字段与分表的映射关系
     *
     * @param id
     * @return 表后缀（201906、201907等）
     */
    private String idToTableSuffix(Long id) {
        return String.valueOf(id % 2);
    }

}

　　

编写ID生成算法，在数据分片的场景中使用MySQL主键自增就不太合适了，因此我使用了snowflake算法来生成主键

@Data
@ConfigurationProperties(prefix = "sharding.ds1")
public class SecondDsProp {
    private String jdbcUrl;
    private String username;
    private String password;
    private String type;
}

　　

@Component
public class Sequence {

    private static final Log logger = LogFactory.getLog(Sequence.class);

    /* 时间起始标记点，作为基准，一般取系统的最近时间（一旦确定不能变动） */
    private final long twepoch = 1288834974657L;
    private final long workerIdBits = 5L;/* 机器标识位数 */
    private final long datacenterIdBits = 5L;
    private final long maxWorkerId = -1L ^ (-1L << workerIdBits);
    private final long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
    private final long sequenceBits = 12L;/* 毫秒内自增位 */
    private final long workerIdShift = sequenceBits;
    private final long datacenterIdShift = sequenceBits + workerIdBits;
    /* 时间戳左移动位 */
    private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
    private final long sequenceMask = -1L ^ (-1L << sequenceBits);

    private long workerId;

    /* 数据标识id部分 */
    private long datacenterId;
    private long sequence = 0L;/* 0，并发控制 */
    private long lastTimestamp = -1L;/* 上次生产id时间戳 */

    public Sequence() {
        this.datacenterId = getDatacenterId(maxDatacenterId);
        this.workerId = getMaxWorkerId(datacenterId, maxWorkerId);
    }

    /**
     * @param workerId     工作机器ID
     * @param datacenterId 序列号
     */
    public Sequence(long workerId, long datacenterId) {
        if (workerId > maxWorkerId || workerId < 0) {
            throw new IllegalArgumentException(String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
        }
        if (datacenterId > maxDatacenterId || datacenterId < 0) {
            throw new IllegalArgumentException(
                    String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
        }
        this.workerId = workerId;
        this.datacenterId = datacenterId;
    }

    /**
     * <p>
     * 获取 maxWorkerId
     * </p>
     */
    protected static long getMaxWorkerId(long datacenterId, long maxWorkerId) {
        StringBuilder mpid = new StringBuilder();
        mpid.append(datacenterId);
        String name = ManagementFactory.getRuntimeMXBean().getName();
        if (StringUtils.isNotEmpty(name)) {
            /*
             * GET jvmPid
             */
            mpid.append(name.split("@")[0]);
        }
        /*
         * MAC + PID 的 hashcode 获取16个低位
         */
        return (mpid.toString().hashCode() & 0xffff) % (maxWorkerId + 1);
    }

    /**
     * <p>
     * 数据标识id部分
     * </p>
     */
    protected static long getDatacenterId(long maxDatacenterId) {
        long id = 0L;
        try {
            InetAddress ip = InetAddress.getLocalHost();
            NetworkInterface network = NetworkInterface.getByInetAddress(ip);
            if (network == null) {
                id = 1L;
            } else {
                byte[] mac = network.getHardwareAddress();
                if (null != mac) {
                    id = ((0x000000FF & (long) mac[mac.length - 1]) | (0x0000FF00 & (((long) mac[mac.length - 2]) << 8))) >> 6;
                    id = id % (maxDatacenterId + 1);
                }
            }
        } catch (Exception e) {
            logger.warn(" getDatacenterId: " + e.getMessage());
        }
        return id;
    }

    /**
     * 获取下一个ID
     *
     * @return
     */
    public synchronized long nextId() {
        long timestamp = timeGen();
        if (timestamp < lastTimestamp) {//闰秒
            long offset = lastTimestamp - timestamp;
            if (offset <= 5) {
                try {
                    wait(offset << 1);
                    timestamp = timeGen();
                    if (timestamp < lastTimestamp) {
                        throw new RuntimeException(String.format("Clock moved backwards.  Refusing to generate id for %d milliseconds", offset));
                    }
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            } else {
                throw new RuntimeException(String.format("Clock moved backwards.  Refusing to generate id for %d milliseconds", offset));
            }
        }

        if (lastTimestamp == timestamp) {
            // 相同毫秒内，序列号自增
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) {
                // 同一毫秒的序列数已经达到最大
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            // 不同毫秒内，序列号置为 1 - 3 随机数
            sequence = ThreadLocalRandom.current().nextLong(1, 3);
        }

        lastTimestamp = timestamp;

        return ((timestamp - twepoch) << timestampLeftShift)    // 时间戳部分
                | (datacenterId << datacenterIdShift)           // 数据中心部分
                | (workerId << workerIdShift)                   // 机器标识部分
                | sequence;                                     // 序列号部分
    }

    protected long tilNextMillis(long lastTimestamp) {
        long timestamp = timeGen();
        while (timestamp <= lastTimestamp) {
            timestamp = timeGen();
        }
        return timestamp;
    }

    protected long timeGen() {
        return SystemClock.now();
    }

}

　　

4、配置数据库连接、MyBatis集成和编写属性文件等等

1） application.properties

#data source0
sharding.ds0.type=com.alibaba.druid.pool.DruidDataSource
sharding.ds0.jdbcUrl=jdbc:mysql://192.168.127.129:3306/sharding_test?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8
sharding.ds0.username=root
sharding.ds0.password=123456

  #data source1
sharding.ds1.type=com.alibaba.druid.pool.DruidDataSource
sharding.ds1.jdbcUrl=jdbc:mysql://192.168.127.134:3306/sharding_test2?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8
sharding.ds1.username=root
sharding.ds1.password=123456


snow.work.id=1
snow.datacenter.id=2

mybatis.configuration.map-underscore-to-camel-case=true
mybatis.type-aliases-package=com.example.myshardingjdbcdemo
mybatis.mapper-locations=classpath:mapping/*.xml

2)  FirstDsProp.java

@Data
@ConfigurationProperties(prefix = "sharding.ds0")
public class FirstDsProp {
    private String jdbcUrl;
    private String username;
    private String password;
    private String type;
}

　　

3) SecondDsProp.java

@Data
@ConfigurationProperties(prefix = "sharding.ds1")
public class SecondDsProp {
    private String jdbcUrl;
    private String username;
    private String password;
    private String type;
}

　　

4)数据分片相关配置，需要配置的东西很多，包括：各个表的配置规则TableRuleConfiguration、数据源DataSource

package com.example.myshardingjdbcdemo.config;


import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.example.myshardingjdbcdemo.shardingalgorithm.GenderShardingAlgorithm;
import com.example.myshardingjdbcdemo.shardingalgorithm.IdShardingAlgorithm;
import com.example.myshardingjdbcdemo.shardingalgorithm.SnowflakeShardingKeyGenerator;
import com.google.common.collect.Lists;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @description:
 * @author:
 * @create: 2020-11-16 10:26
 */
@Configuration
@EnableConfigurationProperties({FirstDsProp.class, SecondDsProp.class})
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan(basePackages = "com.example.myshardingjdbcdemo.dao", sqlSessionTemplateRef = "sqlSessionTemplate")
public class DataSourceConfig {

    @Value("${snow.work.id:0}")
    private Long workId;

    @Value("${snow.datacenter.id:0}")
    private Long datacenterId;

    @Autowired
    private Environment env;

    /**
     * druid数据源1
     *
     * @param firstDSProp
     * @return
     */
    @Bean("ds0")
    public DataSource ds0(FirstDsProp firstDSProp) {
        Map<String, Object> dsMap = new HashMap<>();
        dsMap.put("type", firstDSProp.getType());
        dsMap.put("url", firstDSProp.getJdbcUrl());
        dsMap.put("username", firstDSProp.getUsername());
        dsMap.put("password", firstDSProp.getPassword());

        DruidDataSource ds = (DruidDataSource) DataSourceUtil.buildDataSource(dsMap);
        ds.setProxyFilters(Lists.newArrayList(statFilter()));
        // 每个分区最大的连接数
        ds.setMaxActive(20);
        // 每个分区最小的连接数
        ds.setMinIdle(5);

        return ds;
    }

    /**
     * druid数据源2
     *
     * @param secondDsProp
     * @return
     */
    @Bean("ds1")
    public DataSource ds1(SecondDsProp secondDsProp) {
        Map<String, Object> dsMap = new HashMap<>();
        dsMap.put("type", secondDsProp.getType());
        dsMap.put("url", secondDsProp.getJdbcUrl());
        dsMap.put("username", secondDsProp.getUsername());
        dsMap.put("password", secondDsProp.getPassword());

        DruidDataSource ds = (DruidDataSource) DataSourceUtil.buildDataSource(dsMap);
        ds.setProxyFilters(Lists.newArrayList(statFilter()));
        // 每个分区最大的连接数
        ds.setMaxActive(20);
        // 每个分区最小的连接数
        ds.setMinIdle(5);

        return ds;
    }

    @Bean
    public Filter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(5000);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }

    @Bean
    public ServletRegistrationBean statViewServlet() {
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * shardingjdbc数据源
     *
     * @return
     * @throws SQLException
     */
    @Bean("dataSource")
    public DataSource dataSource(@Qualifier("ds0") DataSource ds0, @Qualifier("ds1") DataSource ds1) throws SQLException {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", ds0);
        dataSourceMap.put("ds1", ds1);
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(userRuleConfig());
        shardingRuleConfig.getTableRuleConfigs().add(addressRuleConfig());
        shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig());
        shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfig());
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
                new StandardShardingStrategyConfiguration("user_id",
                        new IdShardingAlgorithm(), new IdShardingAlgorithm()));

        shardingRuleConfig.getBindingTableGroups().add("t_user, t_user_address");
        shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");

        //t_product为全局表
        shardingRuleConfig.getBroadcastTables().add("t_product");

        Properties p = new Properties();
        p.setProperty("sql.show",Boolean.TRUE.toString());
        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), p);
        return dataSource;
    }

    /**
     * 需要手动配置事务管理器
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/*.xml"));
        return bean.getObject();
    }

    @Bean("sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 用户表切分。根据userId，切分到数据库0和数据库1，然后根据gender切分为t_user_1 和 t_user_2
     */
    private TableRuleConfiguration userRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        tableRuleConfig.setLogicTable("t_user");
        tableRuleConfig.setActualDataNodes("ds${0..1}.t_user_${0..1}");
        tableRuleConfig.setKeyGeneratorColumnName("user_id");
        tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
        tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("gender", new GenderShardingAlgorithm(), new GenderShardingAlgorithm()));
        return tableRuleConfig;
    }

    /**
     * 用户地址表切分。根据userId，切分到数据库0和数据库1，然后根据gender切分为t_user_address_1 和 t_user_address_2
     */
    private TableRuleConfiguration addressRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        tableRuleConfig.setLogicTable("t_user_address");
        tableRuleConfig.setActualDataNodes("ds${0..1}.t_user_address_${0..1}");
        tableRuleConfig.setKeyGeneratorColumnName("address_id");
        tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
        tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("gender", new GenderShardingAlgorithm(), new GenderShardingAlgorithm()));
        return tableRuleConfig;
    }

    /**
     * 订单表切分。根据userId，切分到数据库0和数据库1，然后根据order_id切分为t_order_0 和 t_order_1
     */
    private TableRuleConfiguration orderRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        tableRuleConfig.setLogicTable("t_order");
        tableRuleConfig.setActualDataNodes("ds${0..1}.t_order_${0..1}");
        tableRuleConfig.setKeyGeneratorColumnName("order_id");
        tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
        tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        return tableRuleConfig;
    }

    /**
     * 订单Item表切分。根据userId，切分到数据库0和数据库1，然后根据order_id切分为t_order_item_0 和 t_order_item_1
     */
    private TableRuleConfiguration orderItemRuleConfig() {
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        tableRuleConfig.setLogicTable("t_order_item");
        tableRuleConfig.setActualDataNodes("ds${0..1}.t_order_item_${0..1}");
        tableRuleConfig.setKeyGeneratorColumnName("order_item_id");
        tableRuleConfig.setKeyGenerator(new SnowflakeShardingKeyGenerator(workId, datacenterId));
        tableRuleConfig.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new IdShardingAlgorithm(), new IdShardingAlgorithm()));
        return tableRuleConfig;
    }


}

　　

DataSourceUtil 

package com.example.myshardingjdbcdemo.config;

/**
 * @description:
 * @author:
 * @create: 2020-11-16 10:30
 */
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.jdbc.DataSourceBuilder;

import javax.sql.DataSource;
import java.util.Map;

@Slf4j
public class DataSourceUtil {
    private static final String DATASOURCE_TYPE_DEFAULT = "com.zaxxer.hikari.HikariDataSource";

    public static DataSource buildDataSource(Map<String, Object> dataSourceMap) {
        Object type = dataSourceMap.get("type");
        if (type == null) {
            type = DATASOURCE_TYPE_DEFAULT;
        }
        try {
            Class<? extends DataSource> dataSourceType;
            dataSourceType = (Class<? extends DataSource>) Class.forName((String) type);
            //String driverClassName = dataSourceMap.get("driver").toString();
            String url = dataSourceMap.get("url").toString();
            String username = dataSourceMap.get("username").toString();
            String password = dataSourceMap.get("password").toString();
            // 自定义DataSource配置
            DataSourceBuilder factory = DataSourceBuilder.create().url(url).username(username).password(password).type(dataSourceType);
            return factory.build();
        } catch (Exception e) {
            log.error("构建数据源" + type + "出错", e);
        }
        return null;
    }
}

　　

5、启动类

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
public class MyShardingJdbcDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MyShardingJdbcDemoApplication.class, args);
    }

}

　　

6、service层

@Service
public class BussinessService {

    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UserAddressMapper addressMapper;
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private OrderItemMapper orderItemMapper;
    @Autowired
    private ProductMapper productMapper;

    /**
     *  创建用户
     */
    @Transactional
    public void saveUser(User user, UserAddress address) {
        userMapper.insertSelective(user);
        address.setUserId(user.getUserId());
        addressMapper.insertSelective(address);
    }

    /**
     * 查询用户信息
     */
    public User queryUser(Long userId) {
       return userMapper.selectByPrimaryKey(userId);
    }

    /**
     * 根据用户Id和性别查询
     */
    public User queryUser(Long userId, int gender) {
        User user = new User();
        user.setUserId( userId);
        user.setGender(gender);
        return userMapper.selectByUserIdGender(userId, gender);
    }


    @Transactional
    public void saveOrder(User user, Order order, OrderItem orderItem) {

        order.setUserId(user.getUserId());
        orderMapper.insertSelective(order);

        orderItem.setOrderId(order.getOrderId());
        orderItem.setUserId(user.getUserId());
        orderItemMapper.insertSelective(orderItem);
    }

    public Order queryOrder(Long orderId) {
        return orderMapper.selectByPrimaryKey(orderId);
    }

    @Transactional
    public void saveAll(User user, UserAddress address, Order order, OrderItem orderItem) {

        order.setUserId(user.getUserId());
        orderMapper.insertSelective(order);

        orderItem.setOrderId(order.getOrderId());
        orderItem.setUserId(user.getUserId());
        orderItemMapper.insertSelective(orderItem);
    }

    @Transactional
    public void saveProduct(Product product) {
        productMapper.insertSelective(product);
    }

    /**
     * 查询产品列表
     */
    public List<Product> queryProduct() {
        return productMapper.selectProductList();
    }



}

　　

7、Controller层

@Slf4j
@RestController
public class BussinessController {

    @Autowired
    private BussinessService bussinessService;
    //@Autowired
    //private SnowflakeIdGenerator snowflakeIdGenerator;

    @Autowired
    private Sequence sequence;

    @InitBinder
    public void initBinder(WebDataBinder binder, WebRequest request) {
        //转换日期
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
    }

    @GetMapping("/createProduct")
    public String createProduct() {
        for (int i = 1; i < 10; i++) {
            Product product = new Product();
            //product.setProductId(snowflakeIdGenerator.nextId());
            product.setProductId(sequence.nextId());
            product.setCode("P00" + i);
            product.setName("产品名称" + i);
            product.setRemark("产品介绍" + i);
            bussinessService.saveProduct(product);
        }
        return "成功";
    }

    @GetMapping("/queryProduct")
    public String queryProduct() {

       List<Product> list = bussinessService.queryProduct();
       if(list == null || list.isEmpty()){
           return "未查询到产品信息";
       }
        return JSON.toJSONString(list);
    }

    @GetMapping("/createUser")
    public String createUser() {
        for (int i = 1; i <= 21; i++) {
            User user = new User();
            user.setUserId(Long.parseLong(i + ""));
            user.setName("张无忌" + i);
            Random r = new Random();
            int gender = r.nextInt(100)  % 2 ;
            user.setGender(gender);
            user.setAge(20 + i);
            user.setBirthDate(DateUtil.parseDate("1989-08-16"));
            user.setIdNumber("4101231989691" + i);

            UserAddress address = new UserAddress();
            address.setCity("某某市");
            address.setDetail("某某街道");
            address.setDistrict("某某区");
            address.setProvince("浙江省");
            address.setSort(1);
            address.setGender(user.getGender());
            bussinessService.saveUser(user, address);
        }



        return "成功";
    }

    @GetMapping("/queryUser")
    public String queryUser() {
        Long userId = 2L;
        User user = bussinessService.queryUser(userId);
        if(user == null){
            return "未查询到相关用户信息";
        }
        return  JSON.toJSONString(user);
    }

    //http://localhost:8080/queryUserByUserIdGender?userId=2&gender=0
    @GetMapping("/queryUserByUserIdGender")
    public String  queryUserByUserIdGender(Long userId , int gender) {
        User user = bussinessService.queryUser(userId, gender);
        if(user == null){
            return "未查询到相关用户信息";
        }
        return  JSON.toJSONString(user);
    }


    //http://localhost:8080/queryOrder?orderId=1328224735909232641
    @GetMapping("/queryOrder")
    public String  queryOrder(Long orderId ) {
        Order order = bussinessService.queryOrder(orderId);
        if(order == null){
            return "未查询到相关信息";
        }
        return  JSON.toJSONString(order);
    }





    @GetMapping("/createOrder")
    public String createOrder() {
        for (int i = 1; i <= 21; i++) {
            User user = new User();
            user.setUserId(Long.parseLong(i + ""));

            Order order = new Order();
            order.setOrderId(sequence.nextId());
            order.setOrderAmount(new BigDecimal(100));
            order.setOrderNo("ORDER_00" + i);
            order.setOrderStatus(OrderStatusEnum.PROCESSING.getCode());
            order.setRemark("测试");

            OrderItem orderItem = new OrderItem();
            orderItem.setItemPrice(new BigDecimal(5));
            orderItem.setOrderTime(DateUtil.parseDate("2019-06-27 17:50:05"));
            orderItem.setProductId(1328207116648960001L);
            orderItem.setTotalNum(20);
            orderItem.setTotalPrice(new BigDecimal(100));

            bussinessService.saveOrder(user, order, orderItem);
        }


        return "成功";
    }



    @GetMapping("/buss/all")
    public String findAll(){
        Map<String,Object> result = new HashMap<>();
        result = bussinessService.findAll();
        return JSON.toJSONString(result);
    }

}

参考：https://blog.csdn.net/hyc2zbj/article/details/94005745