Spring Boot MySQL 分库分表

1.首先需要在 pom.xml 中配置相关依赖

<properties><java.version>17</java.version><shardingsphere.version>5.0.0</shardingsphere.version><sharding-jdbc-spring-boot-starter.version>4.1.1</sharding-jdbc-spring-boot-starter.version><mybatis-plus-boot-starter.version>3.5.7</mybatis-plus-boot-starter.version><mybatis-spring.version>3.0.3</mybatis-spring.version></properties><dependencies><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><scope>test</scope></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc</artifactId><version>5.5.0</version><exclusions><exclusion><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-test-util</artifactId></exclusion></exclusions></dependency><dependency><groupId>com.sun.xml.bind</groupId><artifactId>jaxb-impl</artifactId><version>2.3.3</version></dependency><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>2.2</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatis-plus-boot-starter.version}</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>${mybatis-spring.version}</version></dependency><!-- 数据库驱动依赖 --><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><!-- HikariCP for DataSource Pooling --><dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId></dependency></dependencies>

2.数据库配置 (application.yml)

server:port: 8082spring:datasource:driverClassName: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:config.ymlname: EncryptHikariCPhikari:minimumIdle: 10maximumPoolSize: 200autoCommit: trueidleTimeout: 30000poolName: BaseHikariCPmaxLifetime: 1800000connectionTimeout: 30000connectionTestQuery: SELECT 1thymeleaf:prefix: classpath:/templates/suffix: .htmlmode: HTMLcache: false  # 开发阶段关闭缓存
mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplglobal-config:db-config:id-type: autopagination:enable: true

3.config.yml配置

dataSources:ds_0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf8&serverTimezone=UTCusername: rootpassword: 123456ds_1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&serverTimezone=UTCusername: rootpassword: 123456rules:- !SHARDINGtables:orders:actualDataNodes: ds_${0..1}.orders_${0..1}tableStrategy:standard:shardingColumn: order_idshardingAlgorithmName: t_order_inlinekeyGenerateStrategy:column: order_idkeyGeneratorName: snowflakedefaultShardingColumn: user_idbindingTables:- ordersdefaultDatabaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: database_inlinedefaultTableStrategy:none:shardingAlgorithms:database_inline:type: INLINEprops:algorithm-expression: ds_${user_id % 2}t_order_inline:type: INLINEprops:algorithm-expression: orders_${order_id % 2}keyGenerators:snowflake:type: SNOWFLAKEauditors:sharding_key_required_auditor:type: DML_SHARDING_CONDITIONSprops:sql-show: true

4.两个库创建表

CREATE TABLE `orders_0` (`order_id` bigint(20) NOT NULL,`user_id` bigint(20) NOT NULL,`amount` decimal(10,2) DEFAULT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `orders_1` (`order_id` bigint(20) NOT NULL,`user_id` bigint(20) NOT NULL,`amount` decimal(10,2) DEFAULT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

5.创建实体类

import java.math.BigDecimal;
import java.time.LocalDateTime;import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;@Data
@TableName("orders")
public class Order {@TableIdprivate Long orderId;private Long userId;private BigDecimal amount;private LocalDateTime createTime;}

6.创建 OrderMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Order;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

7.创建 OrderService

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.entity.Order;public interface OrderService {public void initOrders();public Page<Order> getOrdersByPage(int pageNumber, int pageSize);
}

8.实现 OrderServiceImpl

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.icoderoad.orderpage.entity.Order;
import com.icoderoad.orderpage.mapper.OrderMapper;
import com.icoderoad.orderpage.service.OrderService;@Service
public class OrderServiceImpl implements OrderService {@Autowiredprivate OrderMapper orderMapper;@Overridepublic void initOrders() {List<Order> orders = new ArrayList<>();Random random = new Random();// 初始化100条订单数据for (int i = 1; i <= 100; i++) {Order order = new Order();order.setOrderId((long) i);order.setUserId((long) (100 + i));  // 假设用户ID从100开始order.setAmount(BigDecimal.valueOf(random.nextDouble() * 1000));order.setCreateTime(LocalDateTime.now());orders.add(order);}// 批量插入订单for (Order order : orders) {orderMapper.insert(order);}}@Overridepublic Page<Order> getOrdersByPage(int pageNumber, int pageSize) {// 创建分页对象Page<Order> page = new Page<>(pageNumber, pageSize);// 构造查询条件，按创建时间降序排列QueryWrapper<Order> queryWrapper = new QueryWrapper<>();queryWrapper.orderByDesc("create_time");// 执行分页查询return orderMapper.selectPage(page, queryWrapper);}
}

9.创建 OrderController

import java.util.HashMap;
import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.icoderoad.orderpage.entity.Order;
import com.icoderoad.orderpage.service.OrderService;@RestController
public class OrderController {@Autowiredprivate OrderService orderService;// 初始化订单数据的接口@GetMapping("/init-orders")public Map<String, String> initOrders() {orderService.initOrders();return Map.of("message", "订单数据初始化成功");}// 分页查询订单的接口@GetMapping("/orders")public Map<String, Object> getOrdersByPage(@RequestParam(defaultValue = "1") int pageNumber,@RequestParam(defaultValue = "10") int pageSize) {Page<Order> orderPage = orderService.getOrdersByPage(pageNumber, pageSize);Map<String, Object> result = new HashMap<>();result.put("total", orderPage.getTotal());result.put("pages", orderPage.getPages());result.put("current", orderPage.getCurrent());result.put("orders", orderPage.getRecords());return result;}
}