基于Springboot2，mybatisPlus 实现Sharding-jdbc按id分库按年月分表详解

最新推荐文章于 2024-05-21 08:17:41 发布

大梁子程序员

最新推荐文章于 2024-05-21 08:17:41 发布

阅读量1.5k

点赞数 1

分类专栏：技术文章标签： java

本文链接：https://blog.csdn.net/qq_34810124/article/details/119738269

版权

技术专栏收录该内容

5 篇文章 0 订阅

订阅专栏

环境:springboot2,mybatisPlus,mysql8，sharding-jdbc-server

pom：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <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>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.0</version>
    </dependency>
    <dependency>
        <groupId>com.minivision.maiot</groupId>
        <artifactId>maiot-common-base</artifactId>
        <version>1.3.2-SNAPSHOT</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>RELEASE</version>
        <scope>test</scope>
    </dependency>
</dependencies>

我是按照uuid取模分库，分了十个库，按照年月分库，根据年月可以不限多少个

创建的库：

创建的表:

properties配置：

# 配置Sharding-JDBC的分片策略
# 配置数据源，给数据源起名g1,g2...此处可配置多数据源
spring.shardingsphere.datasource.names=db0,db1,db2,db3,db4,db5,db6,db7,db8,db9

# 配置数据源具体内容————————包含  连接池，  驱动，             地址，   用户名，    密码
# 由于上面配置数据源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password
spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.jdbc-url=jdbc:mysql://172.16.9.177:3306/db1?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=root

spring.shardingsphere.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.jdbc-url=jdbc:mysql://172.16.9.177:3306/db2?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

spring.shardingsphere.datasource.db2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.jdbc-url=jdbc:mysql://172.16.9.177:3306/db3?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root

spring.shardingsphere.datasource.db3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db3.jdbc-url=jdbc:mysql://172.16.9.177:3306/db4?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db3.username=root
spring.shardingsphere.datasource.db3.password=root

spring.shardingsphere.datasource.db4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db4.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db4.jdbc-url=jdbc:mysql://172.16.9.177:3306/db5?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db4.username=root
spring.shardingsphere.datasource.db4.password=root

spring.shardingsphere.datasource.db5.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db5.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db5.jdbc-url=jdbc:mysql://172.16.9.177:3306/db6?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db5.username=root
spring.shardingsphere.datasource.db5.password=root

spring.shardingsphere.datasource.db6.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db6.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db6.jdbc-url=jdbc:mysql://172.16.9.177:3306/db7?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db6.username=root
spring.shardingsphere.datasource.db6.password=root

spring.shardingsphere.dataSource.db7.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db7.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db7.jdbc-url=jdbc:mysql://172.16.9.177:3306/db8?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db7.username=root
spring.shardingsphere.datasource.db7.password=root

spring.shardingsphere.datasource.db8.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db8.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db8.jdbc-url=jdbc:mysql://172.16.9.177:3306/db9?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db8.username=root
spring.shardingsphere.datasource.db8.password=root

spring.shardingsphere.datasource.db9.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db9.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db9.jdbc-url=jdbc:mysql://172.16.9.177:3306/db10?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.db9.username=root
spring.shardingsphere.datasource.db9.password=root
#分库策略
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=gid
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{gid % 10}

#这里俺采用的默认策略，没有使用表达式，也可以使用表达式，有兴趣的童鞋可以试试
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=gid
#这里俺使用的是业务代码的方式，指向某个指定的类，项目运行会主动加载该类，稍后俺会将代码贴出来
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.sharding.jdbc.common.ShardingTableAlgorithm
#绑定你创建的表
spring.shardingsphere.sharding.binding-tables=good,person
spring.shardingsphere.sharding.tables.good.actual-data-nodes= db$->{0..9}.good_0$->{1..9},db$->{0..9}.good_1$->{0..2}
spring.shardingsphere.sharding.tables.good.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.good.table-strategy.standard.precise-algorithm-class-name=com.sharding.jdbc.common.ShardingDataSourceAlgorithm
#如果between。。。and 查询必须使用一个range策略，俺也是自己写的
spring.shardingsphere.sharding.tables.good.table-strategy.standard.range-algorithm-class-name=com.sharding.jdbc.common.TimeRangeShardingAlgorithm
spring.shardingsphere.sharding.tables.good.key-generator.column=gid
spring.shardingsphere.sharding.tables.good.key-generator.type=SNOWFLAKE

#上面是按照月份分表，下面就是我衍生一下，可以按照年月来，其实都一样，不过策略需要修改一下
spring.shardingsphere.sharding.tables.person.actual-data-nodes= db$->{0..9}.person_$->{2021..2031}_0$->{1..9},db$->{0..9}.person_$->{2021..2031}_1$->{0..2},
spring.shardingsphere.sharding.tables.person.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.person.table-strategy.standard.precise-algorithm-class-name=com.sharding.jdbc.common.PersonShardingTableAlgorithm
spring.shardingsphere.sharding.tables.person.table-strategy.standard.range-algorithm-class-name=com.sharding.jdbc.common.PersonTimeRangeShardingAlgorithm
spring.shardingsphere.sharding.tables.person.key-generator.column=gid
spring.shardingsphere.sharding.tables.person.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.tables.good.key-generator.props.worker.id=123
#spring.shardingsphere.sharding.tables.good.key-generator.props.max.tolerate.time.difference.milliseconds=1000
spring.shardingsphere.props.sql.show=true

分库策略：

按月分表策略：

按照年月策略分表并查询

以上的代码是我写的demo。有兴趣的童鞋可以根据需求来制定

到这一步大家可以开始测试啦：

@Test
public void addGoods() {
    Good goods = new Good();
    goods.setCreateTime(new Date());
    goods.setGid(new IdWorkerUtils().nextId());
    goods.setGname("12323");
    goods.setGstatus("1");
    System.out.println(goods.getGid() % 10);
    System.out.println(goods);
    shardingMapper.insert(goods);
    //shardingMapper.add(goods);
}

下面就是我说的between。。and。。如果没有上面策略，会直接报策略不存在

@Test
public void select() {
    Date beginOfMonth = DateTimeUtil.beginOfMonth(new Date());
    Date endOfMonth = DateTimeUtil.endOfMonth(new Date());
    System.out.println(beginOfMonth);
    System.out.println(endOfMonth);
    Good good = new Good();
/*    good.setCreateStartTime(beginOfMonth);
    good.setCreateEndTime(endOfMonth);
    good.setCreateTime(new Date());*/
    good.setGid(1427100245101879296L);
    QueryWrapper<Good> objectQueryWrapper = new QueryWrapper<>();
    objectQueryWrapper.eq(null!=good.getGid(),"gid",good.getGid());
    objectQueryWrapper.between(true,"create_time",beginOfMonth,endOfMonth);
    List<Good> good1 = shardingMapper.selectList(objectQueryWrapper);
   /* List<Good> goods= shardingMapper.query(good);*/
    System.out.println(JSONObject.toJSONString(good1));
}

结束语:策略很重要有兴趣的可以了解了解。今天就到这儿，喜欢的给我点个赞！！

源码:https://github.com/zxl09214433/sharding-jdbc-server.git

大梁子程序员

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
1
评论
基于Springboot2，mybatisPlus 实现Sharding-jdbc按id分库按年月分表详解

环境:springboot2,mybatisPlus,mysql8，sharding-jdbc-serverpom：<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </depende
复制链接

扫一扫