本文目录导读：
我来为你提供一个完整的Java分库分表案例,使用最流行的ShardingSphere框架。
1 技术栈
- Spring Boot 2.7.x
- MyBatis Plus 3.5.x
- ShardingSphere-JDBC 5.2.x
- MySQL 8.0
- HikariCP 连接池
2 分片方案
- 分库策略：根据用户ID取模，分成2个库
- 分表策略：根据订单ID取模，每个库分成2张表
- 广播表：配置字典表，所有库同步
- 绑定表：订单表和订单详情表关联分片
项目结构
sharding-demo/
├── pom.xml
├── src/main/java/
│ └── com/example/sharding/
│ ├── ShardingApplication.java
│ ├── config/
│ │ └── ShardingConfig.java
│ ├── controller/
│ │ └── OrderController.java
│ ├── entity/
│ │ ├── Order.java
│ │ ├── OrderItem.java
│ │ └── Dict.java
│ ├── mapper/
│ │ ├── OrderMapper.java
│ │ ├── OrderItemMapper.java
│ │ └── DictMapper.java
│ └── service/
│ └── OrderService.java
└── src/main/resources/
└── application.yml核心代码实现
1 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.7.14</version>
</parent>
<groupId>com.example</groupId>
<artifactId>sharding-demo</artifactId>
<version>1.0.0</version>
<properties>
<java.version>1.8</java.version>
<sharding-jdbc.version>5.2.1</sharding-jdbc.version>
<mybatis-plus.version>3.5.3</mybatis-plus.version>
</properties>
<dependencies>
<!-- Spring Boot Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MyBatis Plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<!-- ShardingSphere JDBC -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
<!-- MySQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!-- Lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
</project>
2 application.yml
server:
port: 8080
spring:
shardingsphere:
datasource:
names: ds0,ds1
# 数据源0
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/sharding_db0?useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root123
max-pool-size: 50
min-pool-size: 5
# 数据源1
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/sharding_db1?useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root123
max-pool-size: 50
min-pool-size: 5
rules:
sharding:
# 分片算法配置
sharding-algorithms:
# 数据库分片算法
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
# 订单表分片算法
order-table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 2}
# 订单详情表分片算法
order-item-table-inline:
type: INLINE
props:
algorithm-expression: t_order_item_$->{order_id % 2}
# 绑定表配置
binding-tables:
- t_order,t_order_item
# 广播表配置
broadcast-tables:
- t_dict
# 表配置
tables:
# 订单表
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: order-table-inline
# 订单详情表
t_order_item:
actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..1}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: order-item-table-inline
# 字典表（广播表）
t_dict:
actual-data-nodes: ds$->{0..1}.t_dict
# 属性配置
props:
sql-show: true # 显示SQL
# MyBatis Plus 配置
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
logic-delete-field: deleted
logic-delete-value: 1
logic-not-delete-value: 0
3 实体类
// Order.java
@Data
@TableName("t_order")
public class Order {
@TableId(type = IdType.INPUT)
private Long orderId;
private Long userId;
private BigDecimal totalAmount;
private Integer status;
private LocalDateTime createTime;
private LocalDateTime updateTime;
@TableLogic
private Integer deleted;
}
// OrderItem.java
@Data
@TableName("t_order_item")
public class OrderItem {
@TableId(type = IdType.INPUT)
private Long itemId;
private Long orderId;
private Long userId;
private Long productId;
private String productName;
private Integer quantity;
private BigDecimal price;
private LocalDateTime createTime;
@TableLogic
private Integer deleted;
}
// Dict.java（广播表）
@Data
@TableName("t_dict")
public class Dict {
@TableId(type = IdType.AUTO)
private Long id;
private String dictType;
private String dictCode;
private String dictValue;
private Integer status;
}
4 Mapper层
// OrderMapper.java
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
// 自定义分页查询
IPage<Order> selectOrderPage(Page<Order> page, @Param("userId") Long userId);
// 统计用户订单数
@Select("SELECT COUNT(*) FROM t_order WHERE user_id = #{userId}")
Long countByUserId(@Param("userId") Long userId);
}
// OrderItemMapper.java
@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {
// 根据订单ID查询订单详情
@Select("SELECT * FROM t_order_item WHERE order_id = #{orderId}")
List<OrderItem> selectByOrderId(@Param("orderId") Long orderId);
}
// DictMapper.java
@Mapper
public interface DictMapper extends BaseMapper<Dict> {
// 根据类型查字典
@Select("SELECT * FROM t_dict WHERE dict_type = #{dictType} AND status = 1")
List<Dict> selectByType(@Param("dictType") String dictType);
}
5 Service层
@Service
public class OrderService {
@Resource
private OrderMapper orderMapper;
@Resource
private OrderItemMapper orderItemMapper;
/**
* 创建订单（包含订单详情）
*/
@Transactional(rollbackFor = Exception.class)
public Order createOrder(Order order, List<OrderItem> items) {
// 生成订单ID
Long orderId = IdGenerator.generateId();
order.setOrderId(orderId);
order.setStatus(1);
order.setCreateTime(LocalDateTime.now());
order.setUpdateTime(LocalDateTime.now());
order.setDeleted(0);
// 插入订单
orderMapper.insert(order);
// 插入订单详情
for (OrderItem item : items) {
item.setItemId(IdGenerator.generateId());
item.setOrderId(orderId);
item.setUserId(order.getUserId());
item.setCreateTime(LocalDateTime.now());
itemMapper.insert(item);
}
return order;
}
/**
* 查询用户订单列表（分页）
*/
public IPage<Order> getUserOrders(Long userId, int page, int size) {
Page<Order> orderPage = new Page<>(page, size);
IPage<Order> result = orderMapper.selectPage(orderPage,
new LambdaQueryWrapper<Order>()
.eq(Order::getUserId, userId)
.orderByDesc(Order::getCreateTime));
// 遍历订单，查询详情
if (result.getRecords() != null && !result.getRecords().isEmpty()) {
for (Order order : result.getRecords()) {
List<OrderItem> items = orderItemMapper.selectByOrderId(order.getOrderId());
order.setItems(items);
}
}
return result;
}
/**
* 订单统计
*/
public Map<String, Object> getOrderStatistics(Long userId) {
Map<String, Object> result = new HashMap<>();
// 订单总数
Long orderCount = orderMapper.countByUserId(userId);
result.put("orderCount", orderCount);
// 订单总金额
List<Order> orders = orderMapper.selectList(
new LambdaQueryWrapper<Order>()
.eq(Order::getUserId, userId)
.eq(Order::getStatus, 1)
);
BigDecimal totalAmount = orders.stream()
.map(Order::getTotalAmount)
.reduce(BigDecimal.ZERO, BigDecimal::add);
result.put("totalAmount", totalAmount);
return result;
}
/**
* ID生成器（简化版，生产环境建议使用雪花算法）
*/
public static class IdGenerator {
private static final AtomicLong ID = new AtomicLong(1);
public static Long generateId() {
return System.currentTimeMillis() * 1000000 + ID.getAndIncrement();
}
}
}
6 Controller层
@RestController
@RequestMapping("/api/order")
public class OrderController {
@Resource
private OrderService orderService;
/**
* 创建订单
*/
@PostMapping("/create")
public Result createOrder(@RequestBody CreateOrderRequest request) {
Order order = new Order();
order.setUserId(request.getUserId());
order.setTotalAmount(request.getTotalAmount());
List<OrderItem> items = request.getItems().stream().map(item -> {
OrderItem orderItem = new OrderItem();
orderItem.setProductId(item.getProductId());
orderItem.setProductName(item.getProductName());
orderItem.setQuantity(item.getQuantity());
orderItem.setPrice(item.getPrice());
return orderItem;
}).collect(Collectors.toList());
Order result = orderService.createOrder(order, items);
return Result.success("下单成功", result);
}
/**
* 查询用户订单
*/
@GetMapping("/user/{userId}")
public Result getUserOrders(@PathVariable Long userId,
@RequestParam(defaultValue = "1") int page,
@RequestParam(defaultValue = "10") int size) {
IPage<Order> orders = orderService.getUserOrders(userId, page, size);
return Result.success("查询成功", orders);
}
/**
* 订单统计
*/
@GetMapping("/statistics/{userId}")
public Result getStatistics(@PathVariable Long userId) {
Map<String, Object> statistics = orderService.getOrderStatistics(userId);
return Result.success("统计成功", statistics);
}
}
// 请求体类
@Data
public class CreateOrderRequest {
private Long userId;
private BigDecimal totalAmount;
private List<OrderItemRequest> items;
}
@Data
public class OrderItemRequest {
private Long productId;
private String productName;
private Integer quantity;
private BigDecimal price;
}
// 统一响应类
@Data
public class Result {
private Integer code;
private String message;
private Object data;
public static Result success(String message, Object data) {
Result result = new Result();
result.setCode(200);
result.setMessage(message);
result.setData(data);
return result;
}
}
数据库初始化脚本
-- 创建数据库
CREATE DATABASE IF NOT EXISTS sharding_db0 DEFAULT CHARACTER SET utf8mb4;
CREATE DATABASE IF NOT EXISTS sharding_db1 DEFAULT CHARACTER SET utf8mb4;
-- sharding_db0 表结构
USE sharding_db0;
-- 订单表
CREATE TABLE IF NOT EXISTS t_order_0 (
order_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status INT DEFAULT 1,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted TINYINT DEFAULT 0,
INDEX idx_user_id (user_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t_order_1 LIKE t_order_0;
-- 订单详情表
CREATE TABLE IF NOT EXISTS t_order_item_0 (
item_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
product_name VARCHAR(100),
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
deleted TINYINT DEFAULT 0,
INDEX idx_order_id (order_id),
INDEX idx_user_id (user_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t_order_item_1 LIKE t_order_item_0;
-- 字典表（广播表）
CREATE TABLE IF NOT EXISTS t_dict (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
dict_type VARCHAR(50) NOT NULL,
dict_code VARCHAR(50) NOT NULL,
dict_value VARCHAR(100) NOT NULL,
status TINYINT DEFAULT 1,
UNIQUE KEY uk_type_code (dict_type, dict_code)
) ENGINE=InnoDB;
-- 初始化字典数据
INSERT INTO t_dict (dict_type, dict_code, dict_value) VALUES
('ORDER_STATUS', '1', '待支付'),
('ORDER_STATUS', '2', '已支付'),
('ORDER_STATUS', '3', '已发货'),
('ORDER_STATUS', '4', '已完成');
-- sharding_db1 表结构（与 db0 相同）
USE sharding_db1;
CREATE TABLE IF NOT EXISTS t_order_0 LIKE sharding_db0.t_order_0;
CREATE TABLE IF NOT EXISTS t_order_1 LIKE sharding_db0.t_order_1;
CREATE TABLE IF NOT EXISTS t_order_item_0 LIKE sharding_db0.t_order_item_0;
CREATE TABLE IF NOT EXISTS t_order_item_1 LIKE sharding_db0.t_order_item_1;
-- 广播表需要手动创建并同步数据
CREATE TABLE IF NOT EXISTS t_dict (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
dict_type VARCHAR(50) NOT NULL,
dict_code VARCHAR(50) NOT NULL,
dict_value VARCHAR(100) NOT NULL,
status TINYINT DEFAULT 1,
UNIQUE KEY uk_type_code (dict_type, dict_code)
) ENGINE=InnoDB;
INSERT INTO t_dict (dict_type, dict_code, dict_value) VALUES
('ORDER_STATUS', '1', '待支付'),
('ORDER_STATUS', '2', '已支付'),
('ORDER_STATUS', '3', '已发货'),
('ORDER_STATUS', '4', '已完成');
测试用例
@SpringBootTest
@RunWith(SpringRunner.class)
public class OrderServiceTest {
@Resource
private OrderService orderService;
@Test
public void testCreateOrder() {
// 创建订单
Order order = new Order();
order.setUserId(1001L);
order.setTotalAmount(new BigDecimal("1999.00"));
List<OrderItem> items = new ArrayList<>();
OrderItem item1 = new OrderItem();
item1.setProductId(1L);
item1.setProductName("iPhone 15");
item1.setQuantity(1);
item1.setPrice(new BigDecimal("5999.00"));
items.add(item1);
OrderItem item2 = new OrderItem();
item2.setProductId(2L);
item2.setProductName("AirPods Pro");
item2.setQuantity(1);
item2.setPrice(new BigDecimal("1899.00"));
items.add(item2);
Order result = orderService.createOrder(order, items);
System.out.println("订单创建成功：" + result);
}
@Test
public void testQueryOrders() {
// 查询用户订单
IPage<Order> orders = orderService.getUserOrders(1001L, 1, 10);
System.out.println("订单总数：" + orders.getTotal());
orders.getRecords().forEach(order -> {
System.out.println("订单号：" + order.getOrderId() +
", 金额：" + order.getTotalAmount() +
", 商品数：" + order.getItems().size());
});
}
@Test
public void testDataDistribution() {
// 测试数据分布
List<Long> userIds = Arrays.asList(1L, 2L, 3L, 4L, 5L, 6L);
for (Long userId : userIds) {
System.out.println("用户 " + userId + " -> 数据库 " + (userId % 2));
}
}
}
分片测试数据验证
-- 验证分库效果 USE sharding_db0; SELECT 'db0' AS database_name, COUNT(*) AS order_count FROM t_order_0; SELECT 'db0' AS database_name, COUNT(*) AS order_count FROM t_order_1; USE sharding_db1; SELECT 'db1' AS database_name, COUNT(*) AS order_count FROM t_order_0; SELECT 'db1' AS database_name, COUNT(*) AS order_count FROM t_order_1; -- 查询某个用户的订单（会自动路由到对应分片） -- 用户ID=1001 应路由到 ds1 EXPLAIN SELECT * FROM t_order WHERE user_id = 1001 AND order_id = 123456;
注意事项
1 分片键选择
- 必须包含分片键的查询才能路由到正确分片
- 全表扫描会广播到所有分片
2 性能优化建议
- 合理设计分片数量（建议2的幂次方）
- 使用绑定表避免跨库join
- 避免分布式事务（可使用柔性事务）
3 常见问题
- 分布式ID：使用雪花算法或号段模式
- 数据迁移：使用elastic-job等工具
- 跨分片查询：使用ShardingSphere的联邦查询或汇总层
这个案例涵盖了分库分表的核心实现,你可以根据实际业务需求进行调整和扩展。
