springboot+druid连接池+mybatisplus+shardingsphere5.0alpha实现分库分表实战案例

最新推荐文章于 2023-09-23 15:52:31 发布

东岂

最新推荐文章于 2023-09-23 15:52:31 发布

阅读量907

点赞数

分类专栏： java 文章标签： spring boot sharding mybatis

本文链接：https://blog.csdn.net/qq_40726812/article/details/120594172

版权

java 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

springboot+druid连接池+mybatisplus+shardingsphere5.0alpha实现分库分表实战案例

官网资料
- 官网
- github
集成mybatisplus
测试

官网资料

官网

shardingsphere官网地址https://shardingsphere.apache.org/index_zh.html,这里有各种配置说明和部分示例代码。
在这里插入图片描述
如果想要复杂的配置，可以参考官网实现。

github

源码：https://github.com/apache/shardingsphere
源码中的demo:
在这里插入图片描述
不过把这个项目下载下来比较大，如果只想下载examples的话，可以访问：
https://github.com/apache/shardingsphere-example

集成mybatisplus

问题

本来是想使用纯yml的配置方式实现，但是发现这个数据源无法集成到mybatisplus，最后改成数据源和shardingsphere的写一个独立可拔插的@Configuration配置Bean来实现。具体所有源码如下：

数据库表SQL

CREATE TABLE `t_order_1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `site_id` bigint(20) unsigned NOT NULL COMMENT '站点ID',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '订单名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `t_order_2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `site_id` bigint(20) unsigned NOT NULL COMMENT '站点ID',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '订单名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

项目结构

在这里插入图片描述

父工程POM

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.dongshan</groupId>
    <artifactId>study</artifactId>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>sdsp</module>
    </modules>
    <name>study</name>
    <description>学习</description>
    <packaging>pom</packaging>

    <!--统一管理jar包版本-->
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <spring.boot.version>2.3.2.RELEASE</spring.boot.version>
        <spring.cloud.version>Hoxton.SR9</spring.cloud.version>
        <spring.cloud.alibaba.version>2.2.6.RELEASE</spring.cloud.alibaba.version>
        <druid.version>1.1.24</druid.version>
        <mybatis.plus.boot.version>3.3.2</mybatis.plus.boot.version>
    </properties>

    <!--子模块继承之后，提供作用：锁定版本+子modlue不用写groupId和version -->
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring.boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring.cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba.cloud</groupId>
                <artifactId>spring-cloud-alibaba-dependencies</artifactId>
                <version>${spring.cloud.alibaba.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>

            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.version}</version>
            </dependency>

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

        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <skipTests>true</skipTests>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

模块pom

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>study</artifactId>
        <groupId>com.dongshan</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sdsp</artifactId>
    <description>shardingsphere分库分表学习</description>


    <dependencies>

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

        <!-- springmvc -->
        <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>
        </dependency>

        <!-- 数据库连接 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <!-- mysql数据库 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- jdbc驱动包 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

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

        <!-- 分库分表
       <dependency>
           <groupId>org.apache.shardingsphere</groupId>
           <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
           <version>4.1.1</version>
       </dependency>
               <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>
        -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.0.0-alpha</version>
        </dependency>


    </dependencies>

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

</project>

配置文件application.yml

# 开发环境配置
server:
  # 服务器的HTTP端口，默认为80
  port: 80

spring:
  application:
    name: sdsp #应用名称
  datasource: #数据源配置
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://IP:3306/dycrm_coll?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root

启动类Main

package com.dongshan.sdsp;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

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

分片规则配置MyShardingRuleConfiguration

package com.dongshan.sdsp.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.RuleConfiguration;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
 * 分片规则配置，去除该文件，则按照原来没有分库的逻辑进行
 * 该文件可拔插
 */
@Configuration
public class MyShardingRuleConfiguration {

    // 数据源属性配置，这样可以直接从配置文件的spring.datasource中获取配置，保持原配置文件不变
    @Autowired
    private DataSourceProperties dataSourceProperties;

    /**
     * 使用经过ShardingSphere拦截的数据源，这里创建数据源后，druid就会使用该数据源
     * 因为DruidDataSourceAutoConfigure类中的dataSource()有@ConditionalOnMissingBean
     *
     * @return
     */
    @Bean
    public DataSource dataSource() throws SQLException {
        // 配置Druid数据源
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName(dataSourceProperties.getDriverClassName());
        ds.setUrl(dataSourceProperties.getUrl());
        ds.setUsername(dataSourceProperties.getUsername());
        ds.setPassword(dataSourceProperties.getPassword());

        // 数据源映射
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds", ds);

        // 分配规则
        Collection<RuleConfiguration> rules = Collections.singleton(shardingRuleConfiguration());

        // 经过ShardingSphere拦截的数据源
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, rules, new Properties());
    }

    /**
     * 分库分表配置
     *
     * @return
     */
    private ShardingRuleConfiguration shardingRuleConfiguration() {
        // 配置t_order表规则  按照站点分表，站点ID只能确定为100个左右，此处为了兼容性更强，配置10000个表
        ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("t_order", "ds.t_order_$->{0..9999}");
        // 配置分库策略，不做分库，只做分表
        //orderTableRuleConfig.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("column_id", "dbShardingAlgorithm"));
        // 配置分库算法
        // Properties orderDbShardingAlgorithmrProps = new Properties();
        // dbShardingAlgorithmrProps.setProperty("algorithm-expression", "ds${column_id % 2}");

        // 配置分表策略
        orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("site_id", "tableShardingAlgorithm"));
        // 配置分表算法
        Properties orderTableShardingAlgorithmrProps = new Properties();
        orderTableShardingAlgorithmrProps.setProperty("algorithm-expression", "t_order_${site_id % 10000}");


        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTables().add(orderTableRuleConfig);
        // shardingRuleConfig.getShardingAlgorithms().put("dbShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", dbShardingAlgorithmrProps));
        shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", orderTableShardingAlgorithmrProps));


        return shardingRuleConfig;
    }

}

mybatis全局配置MybatisPlusConfig

package com.dongshan.sdsp.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@MapperScan("com.dongshan.sdsp.mapper")
@Configuration
public class MybatisPlusConfig {

    /**
     * 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

}

domain service mapper

这些是通过idea的Mybatisx插件生成
在这里插入图片描述

最后写个测试TestController

package com.dongshan.sdsp.controller;



import com.dongshan.sdsp.domain.TOrder;
import com.dongshan.sdsp.service.TOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/order")
public class TestController {

    @Autowired
    TOrderService tOrderService ;

    /**
     * 测试类，通过输入的siteID来测试是否插入目标表
     * @param siteId
     * @return
     */
    @GetMapping("/save/{siteId}")
    public Boolean save(@PathVariable("siteId") Long siteId) {
        TOrder order = new TOrder() ;
        order.setSiteId(siteId);
        order.setName("订单分表测试"+siteId);
        boolean ok = tOrderService.save(order) ;
        return ok ;
    }

}

测试

浏览器访问http://localhost/order/save/1
查看数据库

东岂

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
springboot+druid连接池+mybatisplus+shardingsphere5.0alpha实现分库分表实战案例

springboot+druid连接池+mybatisplus+shardingsphere5.0alpha实现分库分表实战案例官网资料官网github集成mybatisplus问题数据库表SQL项目结构父工程POM模块pom配置文件application.yml启动类Main分片规则配置MyShardingRuleConfigurationmybatis全局配置MybatisPlusConfigdomain service mapper最后写个测试TestController测试官网资料官网sh.
复制链接

扫一扫