SpringBoot + ShardingJdbc + MyBatisPlus 完整使用案例

SpringBoot + ShardingJdbc + MyBatisPlus 完整使用案例

本文将提供一个完整的SpringBoot + ShardingJdbc + MyBatisPlus使用案例，实现分库分表功能。

一、环境准备

1. 数据库准备

创建分片数据库和表。这里我们准备两个数据库，每个数据库中创建两个表：

-- 创建数据库 db0
CREATE DATABASE IF NOT EXISTS `db0` DEFAULT CHARACTER SET utf8mb4;
USE `db0`;

-- 在 db0 中创建表 t_order_0 和 t_order_1
CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建数据库 db1
CREATE DATABASE IF NOT EXISTS `db1` DEFAULT CHARACTER SET utf8mb4;
USE `db1`;

-- 在 db1 中创建表 t_order_0 和 t_order_1
CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `order_amount` decimal(10,2) DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、项目搭建

1. 创建Spring Boot项目

使用Spring Initializr创建一个Spring Boot项目。

2. 添加依赖

在pom.xml中添加所需依赖：

<?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>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.12.RELEASE</version>
        <relativePath/>
    </parent>
    
    <groupId>com.example</groupId>
    <artifactId>sharding-jdbc-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
        <mybatis-plus.version>3.4.2</mybatis-plus.version>
    </properties>
    
    <dependencies>
        <!-- Spring Boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        
        <!-- Druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        
        <!-- Sharding-JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        
        <!-- MyBatis-Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

三、配置Sharding-JDBC

1. 配置application.yml

spring:
  shardingsphere:
    # 数据源配置
    datasource:
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456
    
    # 分片规则配置
    sharding:
      # 分库规则
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: db$->{user_id % 2}
      
      # 表分片规则
      tables:
        t_order:
          actual-data-nodes: db$->{0..1}.t_order_$->{0..1}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_$->{order_id % 2}
          key-generator:
            column: order_id
            type: SNOWFLAKE
    
    # 显示SQL
    props:
      sql:
        show: true

# MyBatis-Plus配置
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.shardingjdbcdemo.entity
  configuration:
    map-underscore-to-camel-case: true

四、代码实现

1. 实体类

package com.example.shardingjdbcdemo.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;

@Data
@TableName(value = "t_order") // 逻辑表名
public class Order {
    private Long orderId;      // 订单ID
    private Integer userId;    // 用户ID
    private Integer productId; // 产品ID
    private BigDecimal orderAmount; // 订单金额
    private Date orderTime;    // 订单时间
}

2. Mapper接口

package com.example.shardingjdbcdemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.shardingjdbcdemo.entity.Order;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

3. Service层

package com.example.shardingjdbcdemo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.shardingjdbcdemo.entity.Order;

public interface OrderService extends IService<Order> {
}

4. Service实现类

package com.example.shardingjdbcdemo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.shardingjdbcdemo.entity.Order;
import com.example.shardingjdbcdemo.mapper.OrderMapper;
import com.example.shardingjdbcdemo.service.OrderService;
import org.springframework.stereotype.Service;

@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
}

5. Controller层

package com.example.shardingjdbcdemo.controller;

import com.example.shardingjdbcdemo.entity.Order;
import com.example.shardingjdbcdemo.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

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

    @Autowired
    private OrderService orderService;

    // 创建订单
    @PostMapping
    public String createOrder(@RequestBody Order order) {
        order.setOrderTime(new Date());
        boolean result = orderService.save(order);
        return result ? "创建成功" : "创建失败";
    }

    // 查询订单列表
    @GetMapping
    public List<Order> getOrderList() {
        return orderService.list();
    }

    // 根据用户ID查询订单
    @GetMapping("/user/{userId}")
    public List<Order> getOrdersByUserId(@PathVariable Integer userId) {
        return orderService.lambdaQuery().eq(Order::getUserId, userId).list();
    }

    // 根据订单ID查询订单
    @GetMapping("/{orderId}")
    public Order getOrderByOrderId(@PathVariable Long orderId) {
        return orderService.getById(orderId);
    }

    // 批量创建测试数据
    @PostMapping("/batch")
    public String batchCreate() {
        for (int i = 1; i <= 10; i++) {
            Order order = new Order();
            order.setUserId(i % 4); // 生成不同用户ID，测试分库
            order.setProductId(i);
            order.setOrderAmount(new BigDecimal(i * 100));
            order.setOrderTime(new Date());
            orderService.save(order);
        }
        return "批量创建成功";
    }
}

五、启动类

package com.example.shardingjdbcdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.shardingjdbcdemo.mapper")
public class ShardingJdbcDemoApplication {

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

}

六、测试

1. 启动应用

启动Spring Boot应用。

2. 测试批量创建

使用Postman或其他工具发送POST请求：

POST http://localhost:8080/order/batch

3. 查看分片效果

查看数据库，可以看到数据已经根据配置的分片规则分布到不同的数据库和表中：

• user_id为偶数的订单会保存到db0
• user_id为奇数的订单会保存到db1
• order_id为偶数的订单会保存到t_order_0表
• order_id为奇数的订单会保存到t_order_1表

4. 查询测试

发送GET请求查询所有订单：

GET http://localhost:8080/order

根据用户ID查询订单：

GET http://localhost:8080/order/user/1

七、高级特性

1. 读写分离配置

如果需要实现读写分离，可以在application.yml中添加以下配置：

spring:
  shardingsphere:
    # 主从配置
    masterslave:
      name: ms
      master-data-source-name: db0
      slave-data-source-names: db0_slave
      load-balance-algorithm-type: round_robin
    
    # 禁用分片，启用读写分离
    sharding:
      tables:
        # 为空时表示全部表使用读写分离
        
    # 数据源配置中添加从库
    datasource:
      names: db0,db0_slave
      # 从库配置
      db0_slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3307/db0?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456

2. 自定义分片策略

对于复杂的分片需求，可以实现自定义分片策略：

package com.example.shardingjdbcdemo.config;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;

public class CustomDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        // 自定义分片逻辑
        Integer value = shardingValue.getValue();
        for (String databaseName : availableTargetNames) {
            if (databaseName.endsWith(String.valueOf(value % 2))) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}

然后在配置类中注册：

package com.example.shardingjdbcdemo.config;

import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
public class ShardingJdbcConfig {
    
    @Bean
    public DataSource dataSource() throws SQLException {
        // 数据源配置（省略具体实现）
        // ...
        
        // 分片规则配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "db$->{0..1}.t_order_$->{0..1}");
        
        // 使用自定义分片策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(
            new StandardShardingStrategyConfiguration("user_id", new CustomDatabaseShardingAlgorithm()));
        
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
        
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
    }
    
    // 创建数据源映射（省略具体实现）
    private Map<String, DataSource> createDataSourceMap() {
        // ...
        return new HashMap<>();
    }
}

八、常见问题

1. 版本兼容性：确保Spring Boot、Sharding-JDBC和MyBatis-Plus版本兼容
2. 分片键问题：查询时必须包含分片键，否则会全库表扫描
3. 主键生成：推荐使用Snowflake算法生成分布式ID
4. 性能优化：
   • 合理设计分片键
   • 避免跨库join查询
   • 使用读写分离分担压力

通过以上配置和代码实现，您可以成功搭建一个使用SpringBoot + ShardingJdbc + MyBatisPlus的分库分表项目。