持久层

# 实体类

# ShortLinkDO

@Data
@Builder
@TableName("t_link")
@NoArgsConstructor
@AllArgsConstructor
public class ShortLinkDO extends BaseDO {
    /**
     * id
     */
    private Long id;

    /**
     * 域名
     */
    private String domain;

    /**
     * 短链接
     */
    private String shortUri;

    /**
     * 完整短链接
     */
    private String fullShortUrl;

    /**
     * 原始链接
     */
    private String originUrl;

    /**
     * 点击量
     */
    private Integer clickNum;

    /**
     * 分组标识
     */
    private String gid;

    /**
     * 启用标识 0：启用 1：未启用
     */
    private Integer enableStatus;

    /**
     * 创建类型 0：接口创建 1：控制台创建
     */
    private Integer createdType;

    /**
     * 有效期类型 0：永久有效 1：自定义
     */
    private Integer validDateType;

    /**
     * 有效期
     */
    private Date validDate;

    /**
     * 描述
     */
    @TableField("`describe`")
    private String describe;

    /**
     * 网站标识
     */
    private String favicon;

    /**
     * 历史PV
     */
    private Integer totalPv;

    /**
     * 历史UV
     */
    private Integer totalUv;

    /**
     * 历史UIP
     */
    private Integer totalUip;

    /**
     * 今日PV
     */
    @TableField(exist = false)
    private Integer todayPv;

    /**
     * 今日UV
     */
    @TableField(exist = false)
    private Integer todayUv;

    /**
     * 今日UIP
     */
    @TableField(exist = false)
    private Integer todayUip;

    /**
     * 删除时间
     */
    private Long delTime;
}

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

# ShortLinkPageReqDTO

@Data
public class ShortLinkPageReqDTO extends Page<ShortLinkDO> {
    /**
     * 分组标识
     */
    private String gid;

    /**
     * 排序标识
     */
    private String orderTag;
}

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

# ShortLinkPageRespDTO

@Data
public class ShortLinkPageRespDTO {
    /**
     * id
     */
    private Long id;

    /**
     * 域名
     */
    private String domain;

    /**
     * 短链接
     */
    private String shortUri;

    /**
     * 完整短链接
     */
    private String fullShortUrl;

    /**
     * 原始链接
     */
    private String originUrl;

    /**
     * 分组标识
     */
    private String gid;

    /**
     * 有效期类型 0：永久有效 1：自定义
     */
    private Integer validDateType;

    /**
     * 有效期
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date validDate;

    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    /**
     * 描述
     */
    private String describe;

    /**
     * 网站标识
     */
    private String favicon;

    /**
     * 历史PV
     */
    private Integer totalPv;

    /**
     * 今日PV
     */
    private Integer todayPv;

    /**
     * 历史UV
     */
    private Integer totalUv;

    /**
     * 今日UV
     */
    private Integer todayUv;

    /**
     * 历史UIP
     */
    private Integer totalUip;

    /**
     * 今日UIP
     */
    private Integer todayUip;
}

# ShortLinkGroupCountQueryRespDTO

@Data
public class ShortLinkGroupCountQueryRespDTO {
    /**
     * 分组标识
     */
    private String gid;

    /**
     * 短链接数量
     */
    private Integer shortLinkCount;
}

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

# ShortLinkUpdateReqDTO

@Data
public class ShortLinkUpdateReqDTO {
    /**
     * 原始链接
     */
    private String originUrl;

    /**
     * 完整短链接
     */
    private String fullShortUrl;

    /**
     * 分组标识
     */
    private String gid;

    /**
     * 有效期类型 0：永久有效 1：自定义
     */
    private Integer validDateType;

    /**
     * 有效期
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date validDate;

    /**
     * 描述
     */
    private String describe;
}

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

# ShortLinkGotoDO

@Data
@Builder
@TableName("t_link_goto")
@NoArgsConstructor
@AllArgsConstructor
public class ShortLinkGotoDO {
    /**
     * ID
     */
    private Long id;

    /**
     * 分组标识
     */
    private String gid;

    /**
     * 完整短链接
     */
    private String fullShortUrl;
}

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

# LinkStatsTodayDO

@TableName("t_link_stats_today")
@Data
public class LinkStatsTodayDO extends BaseDO {
    /**
     * id
     */
    private Long id;

    /**
     * 分组标识
     */
    private String gid;

    /**
     * 短链接
     */
    private String fullShortUrl;

    /**
     * 日期
     */
    private Date date;

    /**
     * 今日pv
     */
    private Integer todayPv;

    /**
     * 今日uv
     */
    private Integer todayUv;

    /**
     * 今日ip数
     */
    private Integer todayIpCount;
}

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

# 分库分表

# Link表

为什么用gid分片，考虑到用户分组查询短链接

public class LinkTableShardingTest {
    public static final String SQL = "CREATE TABLE `t_link_%d`\n" +
            "(\n" +
            "    `id`              bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',\n" +
            "    `domain`          varchar(128)                                   DEFAULT NULL COMMENT '域名',\n" +
            "    `short_uri`       varchar(8) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '短链接',\n" +
            "    `full_short_url`  varchar(128)                                   DEFAULT NULL COMMENT '完整短链接',\n" +
            "    `origin_url`      varchar(1024)                                  DEFAULT NULL COMMENT '原始链接',\n" +
            "    `click_num`       int(11) DEFAULT '0' COMMENT '点击量',\n" +
            "    `gid`             varchar(32)                                    DEFAULT 'default' COMMENT '分组标识',\n" +
            "    `favicon`         varchar(256)                                   DEFAULT NULL COMMENT '网站图标',\n" +
            "    `enable_status`   tinyint(1) DEFAULT NULL COMMENT '启用标识 0：启用 1：未启用',\n" +
            "    `created_type`    tinyint(1) DEFAULT NULL COMMENT '创建类型 0：接口创建 1：控制台创建',\n" +
            "    `valid_date_type` tinyint(1) DEFAULT NULL COMMENT '有效期类型 0：永久有效 1：自定义',\n" +
            "    `valid_date`      datetime                                       DEFAULT NULL COMMENT '有效期',\n" +
            "    `describe`        varchar(1024)                                  DEFAULT NULL COMMENT '描述',\n" +
            "    `total_pv`        int(11) DEFAULT NULL COMMENT '历史PV',\n" +
            "    `total_uv`        int(11) DEFAULT NULL COMMENT '历史UV',\n" +
            "    `total_uip`       int(11) DEFAULT NULL COMMENT '历史UIP',\n" +
            "    `create_time`     datetime                                       DEFAULT NULL COMMENT '创建时间',\n" +
            "    `update_time`     datetime                                       DEFAULT NULL COMMENT '修改时间',\n" +
            "    `del_time`        bigint(20) DEFAULT '0' COMMENT '删除时间戳',\n" +
            "    `del_flag`        tinyint(1) DEFAULT NULL COMMENT '删除标识 0：未删除 1：已删除',\n" +
            "    PRIMARY KEY (`id`),\n" +
            "    UNIQUE KEY `idx_unique_full-short-url` (`full_short_url`,`del_time`) USING BTREE\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    public static void main(String[] args) {
        for (int i = 0; i < 16; i++) {
            System.out.printf((SQL) + "%n", i);
        }
    }
}

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

配置类：

application.yml

server:
  port: 8001

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-config-${database.env:dev}.yaml
  data:
    redis:
      host: 127.0.0.1
      port: 6379

1
2
3
4
5
6
7
8
9
10
11

shardingsphere-config-dev.yml

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/link?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: 123456

rules:
  - !SHARDING
    tables:
      t_link:
        actualDataNodes: ds_0.t_link_${0..15}
        tableStrategy:
          standard:
            shardingColumn: gid
            shardingAlgorithmName: link_table_hash_mod
    shardingAlgorithms:
      link_table_hash_mod:
        type: HASH_MOD
        props:
          sharding-count: 16
props:
  sql-show: true

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

link_goto分库分表：

public class LinkGotoShardingTest {
    public static final String SQL = "CREATE TABLE `t_link_goto_%d`\n" +
            "(\n" +
            "    `id`             bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',\n" +
            "    `gid`            varchar(32)  DEFAULT 'default' COMMENT '分组标识',\n" +
            "    `full_short_url` varchar(128) DEFAULT NULL COMMENT '完整短链接',\n" +
            "    PRIMARY KEY (`id`)\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    public static void main(String[] args) {
        for (int i = 0; i < 16; i++) {
            System.out.printf((SQL) + "%n", i);
        }
    }
}

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

LinkStatsToday分库分表：

public class LinkStatsTodayShardingTest {
    public static final String SQL = "CREATE TABLE `t_link_stats_today_%d`\n" +
            "(\n" +
            "    `id`             bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',\n" +
            "    `gid`            varchar(32)  DEFAULT 'default' COMMENT '分组标识',\n" +
            "    `full_short_url` varchar(128) DEFAULT NULL COMMENT '短链接',\n" +
            "    `date`           date         DEFAULT NULL COMMENT '日期',\n" +
            "    `today_pv`       int(11) DEFAULT '0' COMMENT '今日PV',\n" +
            "    `today_uv`       int(11) DEFAULT '0' COMMENT '今日UV',\n" +
            "    `today_uip`      int(11) DEFAULT '0' COMMENT '今日IP数',\n" +
            "    `create_time`    datetime     DEFAULT NULL COMMENT '创建时间',\n" +
            "    `update_time`    datetime     DEFAULT NULL COMMENT '修改时间',\n" +
            "    `del_flag`       tinyint(1) DEFAULT NULL COMMENT '删除标识 0：未删除 1：已删除',\n" +
            "    PRIMARY KEY (`id`),\n" +
            "    UNIQUE KEY `idx_unique_today_stats` (`full_short_url`,`gid`,`date`) USING BTREE\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    public static void main(String[] args) {
        for (int i = 0; i < 16; i++) {
            System.out.printf((SQL) + "%n", i);
        }
    }
}

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

← 分组CRUD 短链接管理CRUD→