# 项目初始化

作者：Ethan.Yang
博客：https://blog.ethanyang.cn (opens new window)
代码参考：[https://github.com/YangYingmeng/learning_shardingJDBC)

# 一、相关依赖以及配置

pom.xml 相关依赖

<!-- 建议直接用 Spring Boot parent，少踩坑 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <mybatisplus.boot.starter.version>3.4.2</mybatisplus.boot.starter.version>
    </properties>

    <dependencies>
        <!-- Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- MyBatis-Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>

        <!-- MySQL 驱动（关键依赖）-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>

        <!-- ShardingJDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>

        <!-- Lombok（可选） -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- 测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!-- Spring Boot 打包插件 -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

配置文件

server:
  port: 8080

spring:
  application:
    name: test-shardingJDBC

  main:
    allow-bean-definition-overriding: true

  shardingsphere:
    props:
      sql.show: true

    datasource:
      names: ds0, ds1
      # 物理库 1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: "jdbc:mysql://115.159.195.151:13306/sharding_order_0"
        username: root
        password: MySql123++
      # 物理库 2
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: "jdbc:mysql://115.159.195.151:13306/sharding_order_1"
        username: root
        password: MySql123++


    # 在业务代码使用时 只需要使用逻辑表名
    sharding:
      # 未指定分库策略, 则按照默认策略进行分库, ds0/1 用户id%2
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: ds$->{user_id % 2}
      # 表的配置
      tables:
        # 表名
        product_order_item:
          # 分表策略
          table-strategy:
            inline:
              sharding-column: product_order_id
              algorithm-expression: product_order_item_$->{product_order_id % 2}
          # 分库分表策略本质上只是算名的函数, 此处才是声明物理数据库的范围,在该范围内使用对应的分库分表策略
          actual-data-nodes: ds$->{0..1}.product_order_item_$->{0..1}
        product_order:
          table-strategy:
            inline:
              sharding-column: id    # 分表策略 按照用户id取模
              algorithm-expression: product_order_$->{id % 2}
          actual-data-nodes: ds$->{0..1}.product_order_$->{0..1}
          key-generator:  # 自定义主键生成策略
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 1

        ad_config:
          key-generator:
            type: SNOWFLAKE
            column: id
      # 广播表
      broadcast-tables: ad_config
      # 绑定表
      binding-tables:
        - product_order, product_order_item

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sharding_order_0
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

CREATE DATABASE IF NOT EXISTS sharding_order_1
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;


USE sharding_order_0;

-- ========================
-- 订单主表 product_order_0
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_0` (
                                                 `id` BIGINT NOT NULL COMMENT '主键id',
                                                 `out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
    `state` VARCHAR(11) DEFAULT NULL COMMENT 'NEW未支付,PAY已支付,CANCEL超时取消',
    `create_time` DATETIME DEFAULT NULL COMMENT '订单生成时间',
    `pay_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
    `nickname` VARCHAR(64) DEFAULT NULL COMMENT '昵称',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单主表 product_order_1
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_1` (
                                                 `id` BIGINT NOT NULL COMMENT '主键id',
                                                 `out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
    `state` VARCHAR(11) DEFAULT NULL COMMENT 'NEW未支付,PAY已支付,CANCEL超时取消',
    `create_time` DATETIME DEFAULT NULL COMMENT '订单生成时间',
    `pay_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
    `nickname` VARCHAR(64) DEFAULT NULL COMMENT '昵称',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 广播表 ad_config
-- ========================
CREATE TABLE IF NOT EXISTS `ad_config` (
                                           `id` BIGINT NOT NULL COMMENT '主键id',
                                           `config_key` VARCHAR(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
    `config_value` VARCHAR(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
    `type` VARCHAR(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单项表 product_order_item_0
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_item_0` (
                                                      `id` BIGINT NOT NULL COMMENT '主键id',
                                                      `product_order_id` BIGINT DEFAULT NULL COMMENT '订单号',
                                                      `product_id` BIGINT DEFAULT NULL COMMENT '产品id',
                                                      `product_name` VARCHAR(128) DEFAULT NULL COMMENT '商品名称',
    `buy_num` INT DEFAULT NULL COMMENT '购买数量',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单项表 product_order_item_1
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_item_1` (
                                                      `id` BIGINT NOT NULL COMMENT '主键id',
                                                      `product_order_id` BIGINT DEFAULT NULL COMMENT '订单号',
                                                      `product_id` BIGINT DEFAULT NULL COMMENT '产品id',
                                                      `product_name` VARCHAR(128) DEFAULT NULL COMMENT '商品名称',
    `buy_num` INT DEFAULT NULL COMMENT '购买数量',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;



USE sharding_order_1;

-- ========================
-- 订单主表 product_order_0
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_0` (
                                                 `id` BIGINT NOT NULL COMMENT '主键id',
                                                 `out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
    `state` VARCHAR(11) DEFAULT NULL COMMENT 'NEW未支付,PAY已支付,CANCEL超时取消',
    `create_time` DATETIME DEFAULT NULL COMMENT '订单生成时间',
    `pay_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
    `nickname` VARCHAR(64) DEFAULT NULL COMMENT '昵称',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单主表 product_order_1
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_1` (
                                                 `id` BIGINT NOT NULL COMMENT '主键id',
                                                 `out_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '订单唯一标识',
    `state` VARCHAR(11) DEFAULT NULL COMMENT 'NEW未支付,PAY已支付,CANCEL超时取消',
    `create_time` DATETIME DEFAULT NULL COMMENT '订单生成时间',
    `pay_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
    `nickname` VARCHAR(64) DEFAULT NULL COMMENT '昵称',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 广播表 ad_config
-- ========================
CREATE TABLE IF NOT EXISTS `ad_config` (
                                           `id` BIGINT NOT NULL COMMENT '主键id',
                                           `config_key` VARCHAR(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置key',
    `config_value` VARCHAR(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置value',
    `type` VARCHAR(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '类型',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单项表 product_order_item_0
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_item_0` (
                                                      `id` BIGINT NOT NULL COMMENT '主键id',
                                                      `product_order_id` BIGINT DEFAULT NULL COMMENT '订单号',
                                                      `product_id` BIGINT DEFAULT NULL COMMENT '产品id',
                                                      `product_name` VARCHAR(128) DEFAULT NULL COMMENT '商品名称',
    `buy_num` INT DEFAULT NULL COMMENT '购买数量',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ========================
-- 订单项表 product_order_item_1
-- ========================
CREATE TABLE IF NOT EXISTS `product_order_item_1` (
                                                      `id` BIGINT NOT NULL COMMENT '主键id',
                                                      `product_order_id` BIGINT DEFAULT NULL COMMENT '订单号',
                                                      `product_id` BIGINT DEFAULT NULL COMMENT '产品id',
                                                      `product_name` VARCHAR(128) DEFAULT NULL COMMENT '商品名称',
    `buy_num` INT DEFAULT NULL COMMENT '购买数量',
    `user_id` BIGINT DEFAULT NULL COMMENT '用户id',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

# 二、业务代码

mapper

public interface AdConfigMapper extends BaseMapper<AdConfigDO> {
}

1
2

public interface ProductOrderItemMapper extends BaseMapper<ProductOrderItemDO> {
}

1
2

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {

    @Select("select * from product_order o left join product_order_item i on o.id=i.product_order_id")
    List<Object> listProductOrderDetail();
}

1
2
3
4
5

@Data
@TableName("product_order") // 业务上使用逻辑表明
@EqualsAndHashCode(callSuper = false)
public class ProductOrderDO {

    @TableId(value = "id", type = IdType.NONE) // 使用shardingJDBC的雪花算法生成主键
    private Long id;

    private String outTradeNo;

    private String state;

    private Date createTime;

    private Double payAmount;

    private String nickname;

    private Long userId;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("ad_config")
public class AdConfigDO {

    private Long id;

    private String configKey;

    private String configValue;

    private String type;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

@Data
@TableName("product_order_item")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderItemDO {

    private Long id;

    private Long productOrderId;

    private Long productId;

    private String productName;

    private Integer buyNum;

    private Long userId;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

← 分库分表基础配置 →