【Sharding-Sphere 整合SpringBoot】

最新推荐文章于 2024-07-17 08:58:21 发布

懒虫虫~

最新推荐文章于 2024-07-17 08:58:21 发布

阅读量1.4k

点赞数 25

分类专栏： SpringBoot 文章标签： spring boot java Sharding-JDBC Sharding-Sphere

本文链接：https://blog.csdn.net/jike11231/article/details/135446723

版权

SpringBoot 专栏收录该内容

45 篇文章 9 订阅

订阅专栏

Sharding-Jdbc在3.0后改名为Sharding-Sphere。Sharding-Sphere相关资料，请自行网上查阅，这里仅仅介绍了实战相关内容，算是抛砖引玉。

一、项目准备

创建12张order_info表
这里以创建order_info_01为例

CREATE TABLE `order_info_01` (
  `id` varchar(100) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、项目实战

1. pom.xml及application.yml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/>
    </parent>
    <groupId>com.bst</groupId>
    <artifactId>shard</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shard</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

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


        <!--apache提供的众多commons工具包-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8</version>
        </dependency>

        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>2.0.1</version>
        </dependency>

        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.2</version>
        </dependency>

        <!-- druid数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- 日志 -->
        <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>


        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- 热部署插件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.xiaoleilu</groupId>
            <artifactId>hutool-all</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
    </dependencies>

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

</project>

server:
  port: 8080
  tomcat:
    uri-encoding: UTF-8
    max-threads: 1000
    min-spare-threads: 10
logging:
  level:
    com.taguan: debug
spring:
  shardingsphere:
    datasource:
      names: db1
      db1: # 数据库
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useUnicode=true
        username: root
        password: root1234
    sharding:
      tables:
        order_info:  #
          # 配置表的分布，表的策略
          #actual-data-nodes:
          actual-data-nodes: db1.order_info_0$->{1..9},db1.order_info_1$->{0..2}
          # 指定tg_weld_after_rule_表 主键id 生成策略为 SNOWFLAKE
          key-generator:
            column: id
            type: SNOWFLAKE
          # 指定分片策略
          table-strategy:
            #            inline:
            ##              约定id值是偶数添加到tg_weld_after_rule_0表，如果id是奇数添加到tg_weld_after_rule_1表
            #              sharding-column: id
            #              algorithm-expression: tg_weld_after_rule_$->{id%2}
            standard:
              #根据年月份进行分表，分表规则自定义handler
              sharding-column: create_date
              precise-algorithm-class-name: com.huahua.shard.myShard.USerTablePreciseShardingAlgorithm
    props:
      # 打开sql输出日志
      sql:
        show: true

2. OrderInfoController

@RestController
public class OrderInfoController {

    @Autowired
    private OrderInfoService orderInfoService;

    @RequestMapping("add")
    public String add() throws ParseException {
        return orderInfoService.add();
    }


    @RequestMapping("get")
    @ResponseBody
    public Object get() {
        return orderInfoService.get();
    }
}

3. OrderInfoService

@Service
public class OrderInfoService {

    @Autowired
    private OrderInfoMapper orderInfoMapper;

    public String add() throws ParseException {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < 10; i++) {
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.setId(UUID.randomUUID().toString());
            int a = RandomUtils.nextInt(1, 9);
            String dateStr = "2024-09-1" + a;
            Date date = formatter.parse(dateStr);
            orderInfo.setCreateDate(date);
            orderInfoMapper.insert(orderInfo);

            orderInfo.setId(UUID.randomUUID().toString());
            String dateS = "2024-10-1" + a;
            orderInfo.setCreateDate(formatter.parse(dateS));
            orderInfoMapper.insert(orderInfo);

            orderInfo.setId(UUID.randomUUID().toString());
            String date2S = "2024-11-1" + a;
            orderInfo.setCreateDate(formatter.parse(date2S));
            orderInfoMapper.insert(orderInfo);

            orderInfo.setId(UUID.randomUUID().toString());
            String date22S = "2024-12-1" + a;
            orderInfo.setCreateDate(formatter.parse(date22S));
            orderInfoMapper.insert(orderInfo);

        }
        return "0";
    }

    public Object get() {
        QueryWrapper<OrderInfo> wrapper = new QueryWrapper<>();
        //wrapper.eq("create_date","2024-08-08");
        return orderInfoMapper.selectList(wrapper);
    }
}

4. OrderInfo

@Data
public class OrderInfo {
    private String id;
    private Date createDate;
}

5. OrderInfoMapper

public interface OrderInfoMapper extends BaseMapper<OrderInfo> {
}

6. USerTablePreciseShardingAlgorithm

自定义算法规则

public class USerTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM");

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        try {
            String tableName = preciseShardingValue.getLogicTableName();
            String dataTime;
            if (preciseShardingValue.getValue() != null) {
                dataTime = formatter.format(preciseShardingValue.getValue());
            } else {
                throw new IllegalArgumentException("没有匹配到库");
            }
            // 按照月份，拼接数据库表名
            return tableName.concat("_").concat(dataTime.substring(5, 7));
        } catch (Exception e) {
            throw new IllegalArgumentException("没有匹配到库:" + preciseShardingValue.getValue());
        }
    }
}