rName("买家Three");
System.out.println(buyerMapper.insert(buyer));
}
@Test
public void testBuyerUpdate (){
// 更新数据
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、查询数据
List<Seller> sellerList = sellerMapper.selectByExample(sellerExample) ;
// 3、构建分页实体对象
PageInfo<Seller> pageInfo = new PageInfo<>(sellerList) ;
System.out.println(pageInfo);
}
}
2、分库操作
在对tb_order
表执行增删改查时,会根据order_id
的字段值计算库表的路由节点,注意分页时会查询所有的分库和分表,然后汇总查询的结果;
public class ShardTest {
@Autowired
private OrderMapper orderMapper ;
/**
* 写入100条数据
*/
@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){
// 原数据:买家和卖家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、查询数据
List<Order> orderList = orderMapper.selectByExample(orderExample) ;
// 3、构建分页实体对象
PageInfo<Order> pageInfo = new PageInfo<>(orderList) ;
System.out.println(pageInfo);
}
}
3、综合查询
编写一个订单详情查询接口,同时使用三个库构建数据结构;如果是基于列表数据的检索,比较常规做法的是构建ES索引结构,如果没有搜索的需求,可以在订单表分页查询后去拼接其他结构;
@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 ;
}
}
查看SQL语句
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