SpringBoot整合Sharding-JDBC分库分表(简单使用)

案例说明

订单案例，有飞机和火箭两类商品，对商品类型进行分表处理，根据订单号进行分库存储。

数据库结构

CREATE DATABASE IF NOT EXISTS `sharedb1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `sharedb1`;

DROP TABLE IF EXISTS `order0`;
CREATE TABLE `order0` (
  `oid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `order_num` int(11) DEFAULT NULL,
  `order_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `order1`;
CREATE TABLE `order1` (
  `oid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `order_num` int(11) DEFAULT NULL,
  `order_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE DATABASE  IF NOT EXISTS `sharedb0` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `sharedb0`;

DROP TABLE IF EXISTS `order0`;
CREATE TABLE `order0` (
  `oid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `order_num` int(11) DEFAULT NULL,
  `order_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `order1`;
CREATE TABLE `order1` (
  `oid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `order_num` int(11) DEFAULT NULL,
  `order_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

项目结构

导入依赖pom

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

application.yml

server:
  port: 8088

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.hexu.sharedb.entity

spring:
  application:
    name: sharedb
  main:
    # 只使用注册一个bean 会有多个dataSource
    allow-bean-definition-overriding: true

sharding:
  jdbc:
    datasource:
      names: sharedb0,sharedb1
      #sharedb0
      sharedb0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharedb0?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456
      #share1
      sharedb1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharedb1?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456

    config:
      sharding:
        # 分库策略
        default-database-strategy:
          inline:
            # 根据字段拆分
            sharding-column: order_num
            # 策略
            algorithm-expression: sharedb$->{order_num % 2}
        # 分表策略
        tables:
          order:
            actual-data-nodes: sharedb$->{0..1}.order$->{0..1}
            table-strategy:
              inline:
                sharding-column: order_type
                algorithm-expression: order$->{order_type % 2}
             # 自增列名称，缺省表示不使用自增主键生成器
#            key-generator-column-name: oid

      # 开启SQL打印
      props:
        sql.show: true

启动类

@MapperScan("com.hexu.sharedb.mapper")
@SpringBootApplication
public class SharedbApplication {

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

}

实体类Order

@Data
@TableName("order")
public class Order {

    @TableId(type = IdType.ASSIGN_UUID)
    private String oid;
    private Integer orderNum; // 订单号 分库字段
    private String orderName;
    private Integer orderType; // 类型1：飞机 2：火箭 分表字段
}

OrderMapper

public interface OrderMapper extends BaseMapper<Order> {
}

业务接口OrderService

public interface OrderService {

    List<Order> getOrderList();
    Order getOrder(Integer orderId);
    boolean save(Order book);
}

OrderServiceImpl

@Service
public class OrderServiceImpl  implements OrderService {

    @Resource
    OrderMapper orderMapper;

    @Override
    public List<Order> getOrderList() {
        return orderMapper.selectList(new QueryWrapper<>());
    }

    @Override
    public Order getOrder(Integer orderNum) {
        return orderMapper.selectOne(new QueryWrapper<Order>().eq("order_num", orderNum));
    }

    @Override
    public boolean save(Order order) {
        return orderMapper.insert(order) > 0 ? true : false;
    }
}

OrderController

@RestController
public class OrderController {

    @Autowired
    private OrderService orderService;

    @RequestMapping(value = "/order", method = RequestMethod.GET)
    public List<Order> getItems() {
        return orderService.getOrderList();
    }

    @RequestMapping(value = "/order/{orderNum}", method = RequestMethod.GET)
    public Order getItem(@PathVariable Integer orderNum) {
        return orderService.getOrder(orderNum);
    }

    @RequestMapping(value = "/order", method = RequestMethod.POST)
    public Boolean saveItem(@RequestBody Order order) {
        return orderService.save(order);
    }
}

测试

localhost:8088/order  # Get请求查询全部订单
localhost:8088/order  # Post请求，插入订单，请求体参数如下json
	{
    "orderNum":1001,
    "orderName":"火箭第1单",
    "orderType":2
	}
localhost:8088/order/1001 # 根据订单号orderNum查询订单

最重要的一点还是分库分表策略，本文只是入门案例，具体的策略需要深入研究，我也在继续深入学习，干巴得！