sharding-jdbc使用教程

概念

分库分表

分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。
分表：从单张表拆分成多张表的过程，将数据散落在多张表内。
本文主要针对水平拆分。

Sharding-JDBC

Sharding-JDBC是一套开源的分布式数据库中间件解决方案，定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

逻辑表

水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为2张表，分别是biz_order_1到biz_order_1，他们的逻辑表名为biz_order。

真实表

在分片的数据库中真实存在的物理表。即上个示例中的biz_order_1到t_order_2。

数据节点

数据分片的最小单元。由数据源名称和数据表组成，例：m_1.biz_order_1。

环境要求

jdk版本：1.8
mysql版本：5.7
spring-boot版本：2.2.1.RELEASE
sharding-jdbc版本：4.1.1

Sharding-JDBC配置

行表达式

行表达式标识符可以使用${...}或$->{...}，但前者与Spring本身的属性文件占位符冲突，因此在Spring环境中使用行表达式标识符建议使用$->{...}。

代码

项目依赖

pom.xml

<modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sharding</groupId>
    <artifactId>sharding-demo</artifactId>
    <version>1</version>
    <name>sharding-demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

配置

application.properties分片配置。

#定义两个数据源m1,m2
spring.shardingsphere.datasource.names=m1,m2
#配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
#m1数据源配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.1.1:3306/edu_db_1?characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

#m2数据源配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.1.2:3306/edu_db_2?characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456

#指定数据库分布情况，数据库里面表分布情况
# m1 m2 biz_order_1 biz_order_2
spring.shardingsphere.sharding.tables.biz_order.actual-data-nodes=m$->{1..2}.biz_order_$->{1..2}
# 指定 biz_order 表里面主键 order_id 生成策略 SNOWFLAKE（雪花算法）
spring.shardingsphere.sharding.tables.biz_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.biz_order.key-generator.type=SNOWFLAKE
# 指定数据库分片策略 是偶数添加 m1，是奇数添加 m2
spring.shardingsphere.sharding.tables.biz_order.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.biz_order.database-strategy.inline.algorithm-expression=m$->{order_id % 2 + 1}
#指定数据表分片策略 按order_user_id进行分表 偶数到表1 奇数到2
spring.shardingsphere.sharding.tables.biz_order.table-strategy.inline.sharding-column=order_user_id
spring.shardingsphere.sharding.tables.biz_order.table-strategy.inline.algorithm-expression=biz_order_$->{order_user_id%2+1}

#订单item
spring.shardingsphere.sharding.tables.biz_order_item.actual-data-nodes=m$->{1..2}.biz_order_item_$->{1..2}
# 指定 biz_order_item 表里面主键 cid 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.biz_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.biz_order_item.key-generator.type=SNOWFLAKE
# 指定数据库分片策略 是偶数添加 m1，是奇数添加 m2
spring.shardingsphere.sharding.tables.biz_order_item.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.biz_order_item.database-strategy.inline.algorithm-expression=m$->{order_id % 2 + 1}
#指定数据表分片策略 按order_user_id进行分表 偶数到表1 奇数到2
spring.shardingsphere.sharding.tables.biz_order_item.table-strategy.inline.sharding-column=order_user_id
spring.shardingsphere.sharding.tables.biz_order_item.table-strategy.inline.algorithm-expression=biz_order_item_$->{order_user_id%2+1}

#设置绑定关系
spring.shardingsphere.sharding.binding-tables=biz_order,biz_order_item

#默认数据源，即未配置分表规则的表数据存储表
spring.shardingsphere.sharding.default-data-source-name=m1

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
# mybatis配置
mybatis-plus.mapper-locations=classpath:mapper/*.xml

数据库初始化

首先准备两个数据库，例如本文中的为192.168.1.1:3306/edu_db_1,192.168.1.2:3306/edu_db_2.
初始化表：
在这里插入图片描述
在这里插入图片描述
biz_order表：

CREATE TABLE `biz_order_1` (
  `order_id` bigint(20) NOT NULL,
  `order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  `order_money` decimal(20,2) DEFAULT NULL,
  `order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `biz_order_2` (
  `order_id` bigint(20) NOT NULL,
  `order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  `order_money` decimal(20,2) DEFAULT NULL,
  `order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

biz_order_item表：

CREATE TABLE `biz_order_item_1` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `goods_num` int(11) DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `goods_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `biz_order_item_2` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `goods_num` int(11) DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `goods_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

源码片段

代码结构

在这里插入图片描述

代码清单

启动类：

@SpringBootApplication
@MapperScan(value = "com.sharding.shardingdemo.dao")
public class ShardingDemoApplication {

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

}

实体类BizOrder：

	@TableId
    private Long orderId;
    private String orderNo;
    private Date orderTime;
    private BigDecimal orderMoney;
    private String orderStatus;
    private Integer orderUserId;

实体类BizOrderItem：

    @TableId
    private Long orderItemId;
    private Long orderId;
    private String goodsName;
    private int goodsNum;
    private Integer orderUserId;
    private BigDecimal goodsPrice;

联表查询结果集OrderAndItemModel：

 	private Long orderId;
    private String orderNo;
    private Date orderTime;
    private BigDecimal orderMoney;
    private String orderStatus;
    private Integer orderUserId;
    private List<BizOrderItem> items;

dao层mapper:

@Repository
public interface BizOrderMapper extends BaseMapper<BizOrder> {
}
@Repository
public interface BizOrderItemMapper extends BaseMapper<BizOrderItem> {
    List<OrderAndItemModel> selectOrderAndItem(@Param("orderId") Long orderId, @Param("userId")Integer userId);
}

mapper.xml

<mapper namespace="com.sharding.shardingdemo.dao.BizOrderItemMapper">

    <resultMap id="orderMap" type="com.sharding.shardingdemo.model.OrderAndItemModel">
        <id column="order_id" property="orderId" />
        <result column="order_no" property="orderNo" />
        <result column="order_time" property="orderTime" />
        <result column="order_money" property="orderMoney" />
        <result column="order_status" property="orderStatus" />
        <result column="order_user_id" property="orderUserId" />
        <collection property="items" ofType="com.sharding.shardingdemo.entity.BizOrderItem">
            <id column="order_item_id" property="orderItemId" />
            <result column="goods_name" property="goodsName" />
            <result column="goods_num" property="goodsNum" />
            <result column="goods_price" property="goodsPrice" />
        </collection>
    </resultMap>
    <select id="selectOrderAndItem" resultMap="orderMap">
        SELECT
            ord.order_id order_id,
            ord.order_no order_no,
            ord.order_time order_time,
            ord.order_money order_money,
            ord.order_status order_status,
            ord.order_user_id order_user_id,
            item.order_item_id order_item_id,
            item.goods_name goods_name,
            item.goods_num goods_num,
            item.goods_price goods_price
            FROM biz_order ord
            LEFT JOIN biz_order_item item ON ord.order_id = item.order_id
            <where>
                <if test="orderId!=null">
                    and ord.order_id  = #{orderId}
                </if>
                <if test="userId!=null">
                    and  ord.order_user_id  = #{userId}
                </if>
            </where>
    </select>
</mapper>

测试类

测试类：

import com.alibaba.fastjson.JSONObject;
import com.sharding.shardingdemo.dao.BizOrderItemMapper;
import com.sharding.shardingdemo.dao.BizOrderMapper;
import com.sharding.shardingdemo.entity.BizOrder;
import com.sharding.shardingdemo.entity.BizOrderItem;
import com.sharding.shardingdemo.model.OrderAndItemModel;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@SpringBootTest
@RunWith(SpringRunner.class)
class ShardingDemoApplicationTests {

    @Autowired
    private BizOrderMapper orderMapper;

    @Autowired
    private BizOrderItemMapper orderItemMapper;

    @Test
    void addOrder(){
        // 插入订单
        BizOrder order = new BizOrder();
        order.setOrderMoney(new BigDecimal("100"));
        order.setOrderNo(System.currentTimeMillis()/1000+"");
        order.setOrderStatus("0");
        order.setOrderUserId(1);
        order.setOrderTime(new Date());
        orderMapper.insert(order);
        Long orderId = order.getOrderId();
        System.out.println(orderId);
        // 插入订单item
        BizOrderItem item1 = new BizOrderItem();
        BizOrderItem item2 = new BizOrderItem();
        item1.setGoodsName("APPLE手机");
        item1.setGoodsNum(3);
        item1.setGoodsPrice(new BigDecimal("1200"));
        item1.setOrderId(orderId);
        item1.setOrderUserId(order.getOrderUserId());
        orderItemMapper.insert(item1);

        item2.setGoodsName("HONOR手机");
        item2.setGoodsNum(4);
        item2.setGoodsPrice(new BigDecimal("3300"));
        item2.setOrderId(orderId);
        item2.setOrderUserId(order.getOrderUserId());
        orderItemMapper.insert(item2);



    }

    @Test
    void selectOrder(){
        // 关联查询订单和订单item
        List<OrderAndItemModel> orderAndItemModels = orderItemMapper.selectOrderAndItem(orderId,orderUserId);
        System.out.println(JSONObject.toJSONString(orderAndItemModels));
    }
}

运行结果

打印日志：

[ main] ShardingSphere-SQL: Logic SQL: （逻辑SQL）
INSERT INTO biz_order  ( order_id,order_no,order_time,order_money,order_status,order_user_id )  VALUES  ( ?,?,?,?,?,? )
[ main] ShardingSphere-SQL: Actual SQL: （实际入库SQL）
m1 ::: INSERT INTO biz_order_2  ( order_id,order_no,order_time,order_money,order_status,order_user_id )  
VALUES  (?, ?, ?, ?, ?, ?) ::: [1461169036709548034, 1637204820, 2021-11-18 11:07:00.098, 100, 0, 1]


[main] ShardingSphere-SQL: Logic SQL: 
INSERT INTO biz_order_item  ( order_item_id,order_id,goods_name,goods_num,order_user_id,goods_price )  VALUES  ( ?,?,?,?,?,? )
[ main] ShardingSphere-SQL: Actual SQL: m1 ::: 
INSERT INTO biz_order_item_2  ( order_item_id,order_id,goods_name,goods_num,order_user_id,goods_price )  VALUES  (?, ?, ?, ?, ?, ?) 
::: [1461169040706719745, 1461169036709548034, APPLE手机2, 3, 1, 1200]
[ main] ShardingSphere-SQL : Logic SQL: 
INSERT INTO biz_order_item  ( order_item_id,order_id,goods_name,goods_num,order_user_id,goods_price )  VALUES  ( ?,?,?,?,?,? )
 [main] ShardingSphere-SQL: Actual SQL: m1 :::
 INSERT INTO biz_order_item_2  ( order_item_id,order_id,goods_name,goods_num,order_user_id,goods_price )  VALUES  (?, ?, ?, ?, ?, ?) 
 ::: [1461169041067429890, 1461169036709548034, HONOR手机2, 4, 1, 3300]

结果分析：

	日志打印显示sharding-jdbc会根据配置对入库数据进行分片计算，然后将数据插入对应数据库和对应表。
	执行ShardingDemoApplicationTests.addOrder()方法后，可以看到订单数据和订单详情分别会根据order_id分布到两个数据库中，是偶数添加到 m1，是奇数添加到m2；根据order_user_id进行分表 偶数到表1 奇数到表2。
	执行selectOrder()方法，会根据传入的orderId和orderUserId自动查询对应分片规则下的库表，若都不指定，则会按m{1,2} * order{1,2}的笛卡尔集进行查询所有组合方式的库表。
# java # 分布式
Logo
GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

cover

GitTalk | 使用面向业务的狮偶编程语言提升开发效率

avatar GitCode 开源社区
cover

GitTalk | DevUI Suits 场景解决方案

avatar GitCode 开源社区
cover

GitTalk | DevUI Admin 前端项目构建

avatar GitCode 开源社区