- 依赖
com.github.xiaoymin knife4j-spring-boot-starter3.0.2 org.springframework.boot spring-boot-starter-weborg.springframework.boot spring-boot-starter-testmysql mysql-connector-javacom.ctrip.framework.apollo apollo-client${apollo.client.version} com.baomidou mybatis-plus-boot-starter3.4.1 org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter5.0.0 com.github.xiaoymin knife4j-spring-boot-starter${knife4j-v} mapstruct org.mapstruct org.projectlombok lombok${lombok-v} com.alibaba fastjson${fastjson-v} com.google.guava guava${guava-v} cn.hutool hutool-all${hutool-v} org.mapstruct mapstruct${org.mapstruct.version} compile org.mapstruct mapstruct-processor${org.mapstruct.version} compile com.carrotsearch java-sizeof${java-sizeof-v} org.apache.commons commons-lang3${commons-lang3-v} org.springframework.boot spring-boot-starter-test
引用了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{ }
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;
@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
项目中有很多实际工作中的用到技巧案例，缓存使用、多数据源配置、全局异常处理、注解参数校验、还有一系列其他中间件的使用案例。
