一、数据库规划
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
作者：Work Hard Work Smart
出处：http://www.cnblogs.com/linlf03/
欢迎任何形式的转载，未经作者同意，请保留此段声明！
