springboot集成sharding-jdbc

1、springboot+mybatis-plus集成sharding-jdbc分库分表

2、引入pom.xml

     org.springframework.boot
     spring-boot-starter-parent
     2.6.4
      



	1.8
	3.5.1


	
		org.springframework.boot
		spring-boot-starter-web
	
	
	
		org.projectlombok
		lombok
		true
	

	
	
		mysql
		mysql-connector-java
		runtime
	
	
	
		com.alibaba
		druid-spring-boot-starter
		1.1.18
	
	
	
	
		com.baomidou
		mybatis-plus-boot-starter
		${mybatis-plus.version}
	
	
	
		com.baomidou
		mybatis-plus-generator
		${mybatis-plus.version}
		test
	
	
	
		com.baomidou
		mybatis-plus-boot-starter-test
		${mybatis-plus.version}
		test
	
	
	
	
		org.apache.shardingsphere
		sharding-jdbc-spring-boot-starter
		4.0.0-RC1
	

	
		org.apache.commons
		commons-dbcp2
		${commons-dbcp2.version}
	
	
	
		org.apache.tomcat
		tomcat-dbcp
		10.0.16	
	
	
	
	
		org.springframework.boot
		spring-boot-starter-test
		test
	
	
	
		junit
		junit
		test
	
	

3、sharding-jdbc配置，yaml

server:
  port: 8080
  servlet:
    context-path: /
spring:
  main:
    allow-circular-references: true
    allow-bean-definition-overriding: true
  datasource:
    # 数据源druid配置
    druid:
      initial-size: 10
      min-idle: 10
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 30000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: true
      test-on-return: false

  devtools:
    restart:
      enabled: true
  #兼容springboot2.6以后的版本
  mvc:
    pathmatch:
      matching-strategy: ant_path_matcher 

  #sharding-jdbc
  shardingsphere:
    # 打印sql
    props:
      sql:
        show: true
    # 数据源配置
    datasource:
      names: ds #数据源，可配置多个
      ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: 
        username: 
        password: 
    sharding:
      # 表策略配置
      tables:
        # order 是逻辑表
        order:
          # 分表节点 比如 order_0 ,order_1
          actualDataNodes: ds.order_$->{0..1} #数据源名称+表名
          tableStrategy:
            inline:
              # 根据表些列分表数据
              shardingColumn: id
              # 分表算法->如按奇偶数算法：order_0保存偶数,order_1保存奇数
              algorithmExpression: order_$->{id % 2}
          # 分表后，sharding-jdbc的全局id生成策略
          keyGenerator:
            type: SNOWFLAKE
            column: id

mybatis-plus:
  global-config:
    banner: false
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4、配置mybatis-plus

@Configuration
@MapperScan("com.xd.modules.**.mapper")
public class MybatisPlusConfig {
   
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor();
        paginationInterceptor.setDbType(DbType.MYSQL);

        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(paginationInterceptor);
        return interceptor;
    }
}

5、创建数据库及数据库表

CREATE TABLE `order`  (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `order_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单编码',
  `status` tinyint(1) NOT NULL COMMENT '订单状态：0=待支付,1=已支付,2=已取消,3=退回',
  `user_id` bigint NOT NULL COMMENT '用户ID',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '地址',
  `order_time` datetime NOT NULL COMMENT '下单时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `order_num`(`order_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1547142983831769100 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

6、测试添加数据

@RestController
@RequestMapping(value = "/order")
public class OrderController {

    @Autowired
    private OrderService orderService;

    @PostMapping
    public void insert() throws Exception {
        List list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setId(IdWorker.getId());
            order.setOrderCode(IdWorker.getIdStr());
            order.setStatus(0);
            order.setOrderTime(new Date());
            order.setAddress("address-->" + i);
            order.setUserId(IdWorker.getId());
            list.add(order);
        }
        orderService.saveBatch(list);
    }
}

ID为偶数的数据会保存在order_0表中，ID为奇数的数据会保存在order_1表中；order_0和order_1是order表的分表，表结构完全一致。