springboot集成sharding-jdbc(5.0.0)实现分表功能(附属项目还有更多惊喜)

2023-10-16 10:52:10

依赖

 <dependencies>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.ctrip.framework.apollo</groupId>
            <artifactId>apollo-client</artifactId>
            <version>${apollo.client.version}</version>
        </dependency>

        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0</version>
        </dependency>


         <!-- 下面是父项目的依赖，为了读者方便看 我就放一起了 -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>${knife4j-v}</version>
            <exclusions>
                <exclusion>
                    <artifactId>mapstruct</artifactId>
                    <groupId>org.mapstruct</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok-v}</version>
        </dependency>

        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson-v}</version>
        </dependency>

        <!--guava-->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava-v}</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool-v}</version>
        </dependency>

        <!-- mapstruct -->
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct</artifactId>
            <version>${org.mapstruct.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct-processor</artifactId>
            <version>${org.mapstruct.version}</version>
            <scope>compile</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.carrotsearch/java-sizeof -->
        <dependency>
            <groupId>com.carrotsearch</groupId>
            <artifactId>java-sizeof</artifactId>
            <version>${java-sizeof-v}</version>
        </dependency>

        <!-- 为了编码方便，并非apollo 必须的依赖 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3-v}</version>
        </dependency>


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

引用了sharding-jdbc 5.0.0版本

配置项目

application.properties


server.port: 8080

#mybatisPlus-config
mybatis-plus.global-config.db-config.id-type=auto


spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://${extraEnvHost}:3306/sharding?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&rewriteBatchedStatements=true
spring.shardingsphere.datasource.ds.username=${jdbc.username}
spring.shardingsphere.datasource.ds.password=${jdbc.pwd}

spring.shardingsphere.rules.sharding.binding-tables[0]=suser_sharding_month

spring.shardingsphere.rules.sharding.tables.suser_sharding_month.actual-data-nodes=ds.suser_sharding_month_$->{0..1}
spring.shardingsphere.rules.sharding.tables.suser_sharding_month.table-strategy.standard.sharding-column=sharding_index
spring.shardingsphere.rules.sharding.tables.suser_sharding_month.table-strategy.standard.sharding-algorithm-name=t-order-inline
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.algorithm-expression=suser_sharding_month_$->{sharding_index % 2}

spring.shardingsphere.props.sql-show=true

上图中的${}中的值替换成你自己项目的值，配置项的分表个数仅仅作为demo,可根据自己需求修改。

数据库表设计：

CREATE TABLE `suser_sharding_0` (
  `id` bigint NOT NULL,
  `sharding_index` bigint DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `suser_sharding_1` (
  `id` bigint NOT NULL,
  `sharding_index` bigint DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

swagger的配置项：

import com.github.xiaoymin.knife4j.spring.annotations.EnableKnife4j;
import io.swagger.annotations.ApiOperation;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;

@Configuration
@EnableKnife4j
public class SwaggerConfig {

    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.OAS_30)
                .enable(true)
                .apiInfo(apiInfo())
                .pathMapping("/")
                .select()
                .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
                .paths(PathSelectors.any())
                .build();
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("sharding-JDBC-table3-Use")
                .description("sharding-JDBC-table3-Use接口文档")
                .version("1.0")
                .build();
    }

}

实体类：

@Data
@TableName("suser_sharding_month")
public class SuserShardingMonthEntity {


    private Long id;

    private Long shardingIndex;

    private String name;

}

dao层：

@Repository
@Mapper
public interface SuserShardingMonthMapper extends BaseMapper<SuserShardingMonthEntity> {


}

controller:

import com.robben.entity.SuserShardingMonthEntity;
import com.robben.mapper.SuserShardingMonthMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * Description： TODO
 * Author: robben
 * Date: 2021/12/2 10:04
 */
@Api(tags = "分表用户服务2")
@RestController
@RequestMapping("/feature3")
public class SuserShardingMonthController {

    @Autowired
    private SuserShardingMonthMapper suserShardingMonthMapper;


    @ApiOperation("分表存储用户")
    @GetMapping("/user/save")
    public String save() {
        for (int i = 0; i <2 ; i++) {
            SuserShardingMonthEntity user = new SuserShardingMonthEntity();
            user.setName("test"+i);
            user.setShardingIndex(123l + i);
            suserShardingMonthMapper.insert(user);
        }
        return "success";
    }


    @ApiOperation("分表获取用户")
    @GetMapping("/user/get")
    public SuserShardingMonthEntity get(@RequestParam Long id) {
        return suserShardingMonthMapper.selectById(id);
    }


}

启动项目后成功后，访问：http://localhost:8080/doc.html#/home

注意新版的sharding-jdbc不在打印配置项中的分表策略了，所有没有显示。

执行插入：

可以看到执行成功，同时我在配置项中增加了debug日志，所以你可以看到逻辑sql和实际sql的执行过程。再查看数据库中可以看出已经插入了

在看看查询,依旧可以看出查询成功。

附上github: GitHub - robben009/springboot-agg

项目中有很多实际工作中的用到技巧案例，缓存使用、多数据源配置、全局异常处理、注解参数校验、还有一系列其他中间件的使用案例。

码农公寓

相关文章