<!-- Mybatis組件 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- ShardingSphere分庫分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>

spring:
# 分庫分表配置
shardingsphere:
datasource:
# 默認數(shù)據(jù)源
sharding:
default-data-source-name: db_master
names: db_master,db_0,db_1
db_master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shard_db
username: root
password: 123456
db_0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shard_db_0
username: root
password: 123456
db_1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shard_db_1
username: root
password: 123456
rules:
sharding:
tables:
# tb_order邏輯
tb_order:
actual-data-nodes: db_${0..1}.tb_order_${0..2}
# tb_order庫路由
database-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: database_inline
# tb_order表路由
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table_inline
sharding-algorithms:
# tb_order庫路由算法
database_inline:
type: INLINE
props:
algorithm-expression: db_${order_id % 2}
# tb_order表路由算法
table_inline:
type: INLINE
props:
algorithm-expression: tb_order_${order_id % 3}
props:
sql-show: true
sql-comment-parse-enabled: true

public class MasterTest {
@Autowired
private BuyerMapper buyerMapper ;
@Autowired
private SellerMapper sellerMapper ;
@Test
public void testBuyerQuery (){
// 主鍵查詢
Buyer buyer = buyerMapper.selectByPrimaryKey(1) ;
System.out.println(buyer.getId()+";"+buyer.getBuyerName());
}
@Test
public void testBuyerInsert (){
// 新增數(shù)據(jù)
Buyer buyer = new Buyer() ;
buyer.setBuyerName("買家Three");
System.out.println(buyerMapper.insert(buyer));
}
@Test
public void testBuyerUpdate (){
// 更新數(shù)據(jù)
Buyer buyer = buyerMapper.selectByPrimaryKey(3) ;
if (buyer != null){
buyer.setBuyerName("Three買家");
System.out.println(buyerMapper.updateByPrimaryKey(buyer));
}
}
@Test
public void testSellerPage (){
// 1、設置分頁和查詢條件
PageHelper.startPage(2,2) ;
SellerExample sellerExample = new SellerExample() ;
sellerExample.setOrderByClause("id asc");
// 2、查詢數(shù)據(jù)
List<Seller> sellerList = sellerMapper.selectByExample(sellerExample) ;
// 3、構建分頁實體對象
PageInfo<Seller> pageInfo = new PageInfo<>(sellerList) ;
System.out.println(pageInfo);
}
}

public class ShardTest {
@Autowired
private OrderMapper orderMapper ;
/**
* 寫入100條數(shù)據(jù)
*/
@Test
public void testOrderInsert (){
for (int i=1 ; i<= 100 ; i++){
Order order = new Order(i,i%3+1,i%3+1) ;
// orderMapper.insert(order) ;
}
}
@Test
public void testOrderQuery (){
Order order = orderMapper.selectByPrimaryKey(5) ;
System.out.println(order);
}
@Test
public void testOrderUpdate (){
Order order = orderMapper.selectByPrimaryKey(100) ;
if (order != null){
// 原數(shù)據(jù)：買家和賣家ID都是2
order.setBuyerId(1);
order.setSellerId(3);
orderMapper.updateByPrimaryKey(order) ;
}
}
@Test
public void testOrderPage (){
// 1、設置分頁和查詢條件
PageHelper.startPage(1,10) ;
OrderExample orderExample = new OrderExample() ;
orderExample.createCriteria().andBuyerIdEqualTo(2).andSellerIdEqualTo(2);
orderExample.setOrderByClause("order_id desc");
// 2、查詢數(shù)據(jù)
List<Order> orderList = orderMapper.selectByExample(orderExample) ;
// 3、構建分頁實體對象
PageInfo<Order> pageInfo = new PageInfo<>(orderList) ;
System.out.println(pageInfo);
}
}

@RestController
public class OrderController {
@Resource
private BuyerMapper buyerMapper ;
@Resource
private SellerMapper sellerMapper ;
@Resource
private OrderMapper orderMapper ;
/**
* 查詢訂單詳情
*/
@GetMapping("/order/info/{orderId}")
public Map<String,Object> orderInfo (@PathVariable Integer orderId){
Map<String,Object> orderMap = new HashMap<>() ;
Order order = orderMapper.selectByPrimaryKey(orderId) ;
if (order != null){
orderMap.put("order",order) ;
orderMap.put("buyer",buyerMapper.selectByPrimaryKey(order.getBuyerId())) ;
orderMap.put("seller",sellerMapper.selectByPrimaryKey(order.getSellerId())) ;
}
return orderMap ;
}
}

db_master ::: select id, buyer_name from tb_buyer where id = ? ::: [1]
db_master ::: select id, seller_name from tb_seller where id = ? ::: [3]
db_0 ::: select order_id, seller_id, buyer_id from tb_order_1 where order_id = ? ::: [100]

文檔倉庫：
https://gitee.com/cicadasmile/butte-java-note
源碼倉庫：
https://gitee.com/cicadasmile/butte-spring-parent