<!-- 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: # 默认数据源 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: true2、配置原理
.表路由tb_order_${0..2}采用tb_order_${order_id%3}的算法;
public class MasterTest { @Autowired private BuyerMapper buyerMapper ; @Autowired private SellerMapper sellerMapper ; @Test public void testBuyerQuery (){ // 堆代码 duidaima.com // 主键查询 Buyer buyer = buyerMapper.selectByPrimaryKey(1) ; System.out.println(buyer.getId()+";"+buyer.getBuyerName()); } @Test public void testBuyerInsert (){ // 新增数据 Buyer buyer = new Buyer() ; buyer.setBuyerName("买家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、分库操作
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、综合查询
@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]五、参考源码