溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
spring boot+mybatis-plus怎樣使用shardingsphere分庫分表，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
一.關于
效果預覽：
ShardingSphere是一個數據庫中間件，可以實現分庫分表、讀寫分離等，詳見官網： https://shardingsphere.apache.org/
這里使用spring boot+mybatis-plus的方式來搭建demo。
二.項目搭建
使用vscode(java開發環境可以百度一下)新建一個spring boot項目，引入lombok、mybatis-plus等：
<properties> <java.version>11</java.version> <sharding-sphere.version>4.1.1</sharding-sphere.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--Mybatis-Plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.1</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <!-- for spring boot --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency> <!-- for spring namespace --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>${sharding-sphere.version}</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.22</version> </dependency> <dependency> <groupId>org.antlr</groupId> <artifactId>antlr4-runtime</artifactId> <version>4.7.2</version> </dependency>
項目結構：
application.yml配置：
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: 123456
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: 123456
sharding:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order_${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds${0..1}.t_order_item_${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
binding-tables: t_order,t_order_item
broadcast-tables: t_config
defaultDataSourceName: ds0
defaultTableStrategy:
none:
defaultKeyGenerator:
type: SNOWFLAKE
column: order_id
props:
sql.shwo: truedb：分別建ds0和ds1庫，并分別建t_order_0、t_order_1和t_order_item_0、t_order_item_1
# Host: localhost (Version 5.7.17) # Date: 2020-08-23 12:31:22 # Generator: MySQL-Front 6.0 (Build 2.29) # # Structure for table "t_order_0" # CREATE TABLE `t_order_0` ( `order_id` bigint(11) NOT NULL DEFAULT '0', `user_id` bigint(1) DEFAULT NULL, `amount` decimal(18,2) DEFAULT NULL, `discount` decimal(10,2) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; # # Structure for table "t_order_item_0" # CREATE TABLE `t_order_item_0` ( `order_id` bigint(11) NOT NULL DEFAULT '0', `product_id` bigint(11) DEFAULT NULL, `product_name` varchar(255) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `discount` decimal(10,2) DEFAULT NULL, `count` int(11) DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
entity：
Order.java
@Data
@TableName("t_order")
public class Order extends Model<Order> {
/**
*
*/
private static final long serialVersionUID = 1L;
private Long orderId;
private Long userId;
private BigDecimal amount;
private BigDecimal discount;
private LocalDateTime createTime;
public Order(){}
public Order(Long orderId,Long userId,BigDecimal amount,BigDecimal discount,LocalDateTime createTime){
this.orderId=orderId;
this.userId=userId;
this.amount=amount;
this.discount=discount;
this.createTime=createTime;
}
}OrderItem.java
@Data
@TableName("t_order_item")
public class OrderItem extends Model<OrderItem> {
private Long orderId;
private Long productId;
private String productName;
private BigDecimal price;
private BigDecimal discount;
@TableField("`count`")
private int count;
public OrderItem(){}
public OrderItem(Long orderId,Long productId,String productName,BigDecimal price,BigDecimal discount,int count){
this.orderId=orderId;
this.productId=productId;
this.productName=productName;
this.price=price;
this.discount=discount;
this.count=count;
}
}mapper:
OrderMapper.java
public interface OrderMapper extends BaseMapper<Order> {
}OrderItemMapper.java
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}service:
OrderService.java
public interface OrderService extends IService<Order> {
boolean save(Order order,List<OrderItem> items);
}OrderServiceImpl.java
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
@Autowired
private OrderItemService orderItemService;
@Transactional(rollbackFor = Exception.class)
@Override
public boolean save(Order order, List<OrderItem> items) {
save(order);
orderItemService.saveBatch(items);
return true;
}
}OrderItemService.java
public interface OrderItemService extends IService<OrderItem> {
}OrderItemServiceImpl.java
@Service
public class OrderItemServiceimpl extends ServiceImpl<OrderItemMapper,OrderItem> implements OrderItemService {
}spring boot啟動類：
@MapperScan("com.example.sharding.mapper")
@SpringBootApplication
public class ShardingDemoApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingDemoApplication.class, args);
}
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 設置請求的頁面大于最大頁后操作， true調回到首頁，false 繼續請求 默認false
// paginationInterceptor.setOverflow(false);
// 設置最大單頁限制數量，默認 500 條，-1 不受限制
// paginationInterceptor.setLimit(500);
// 開啟 count 的 join 優化,只針對部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
}三.測試效果
建一個測試類：OrderServiceImplTest.java 并編寫測試語句
package com.example.sharding.service.impl;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.sharding.entity.Order;
import com.example.sharding.entity.OrderItem;
import com.example.sharding.service.OrderItemService;
import com.example.sharding.service.OrderService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class OrderServiceImplTest {
@Autowired
private OrderService orderService;
@Autowired
private OrderItemService orderItemService;
@Test
public void testSave(){
var order1=new Order(1L,1L,new BigDecimal(22.5),new BigDecimal(1),LocalDateTime.now());
var orderItemList1=new ArrayList<OrderItem>();
orderItemList1.add(new OrderItem(1L,1L,"蘋果",new BigDecimal(6),new BigDecimal(1),2));
orderItemList1.add(new OrderItem(1L,2L,"香蕉",new BigDecimal(3.5),new BigDecimal(1),3));
var order2=new Order(2L,1L,new BigDecimal(799),new BigDecimal(1),LocalDateTime.now());
var orderItemList2=new ArrayList<OrderItem>();
orderItemList2.add(new OrderItem(2L,3L,"鞋子1",new BigDecimal(600),new BigDecimal(1),1));
orderItemList2.add(new OrderItem(2L,4L,"襯衣1",new BigDecimal(199),new BigDecimal(1),1));
var order3=new Order(3L,1L,new BigDecimal(399),new BigDecimal(1),LocalDateTime.now());
var orderItemList3=new ArrayList<OrderItem>();
orderItemList3.add(new OrderItem(3L,5L,"鞋子2",new BigDecimal(200),new BigDecimal(1),1));
orderItemList3.add(new OrderItem(3L,6L,"襯衣2",new BigDecimal(199),new BigDecimal(1),1));
var order4=new Order(4L,1L,new BigDecimal(499),new BigDecimal(1),LocalDateTime.now());
var orderItemList4=new ArrayList<OrderItem>();
orderItemList4.add(new OrderItem(4L,7L,"鞋子3",new BigDecimal(300),new BigDecimal(1),1));
orderItemList4.add(new OrderItem(4L,8L,"襯衣3",new BigDecimal(199),new BigDecimal(1),1));
var order5=new Order(5L,1L,new BigDecimal(899),new BigDecimal(1),LocalDateTime.now());
var orderItemList5=new ArrayList<OrderItem>();
orderItemList5.add(new OrderItem(5L,9L,"鞋子4",new BigDecimal(600),new BigDecimal(1),1));
orderItemList5.add(new OrderItem(5L,10L,"襯衣4",new BigDecimal(299),new BigDecimal(1),1));
assertTrue(orderService.save(order1,orderItemList1));
assertTrue(orderService.save(order2,orderItemList2));
assertTrue(orderService.save(order3,orderItemList3));
assertTrue(orderService.save(order4,orderItemList4));
assertTrue(orderService.save(order5,orderItemList5));
}
@Test
public void testQuery(){
var page=new Page<OrderItem>(1,4);
var queryWrapper=new QueryWrapper<OrderItem>();
queryWrapper.orderByAsc("order_id");
var itemPage=orderItemService.page(page,queryWrapper);
assertEquals(4, itemPage.getRecords().size());
assertEquals("蘋果", itemPage.getRecords().get(0).getProductName());
}
}測試通過，看一下數據庫的數據：
看完上述內容，你們掌握spring boot+mybatis-plus怎樣使用shardingsphere分庫分表的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
