spring boot+mybatis-plus怎樣使用shardingsphere分庫分表,相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策,為此本文總結(jié)了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個(gè)問題。
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),錫林浩特企業(yè)網(wǎng)站建設(shè),錫林浩特品牌網(wǎng)站建設(shè),網(wǎng)站定制,錫林浩特網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,錫林浩特網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè),幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力??沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿,時(shí)刻以成就客戶成長(zhǎng)自我,堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己,讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
一.關(guān)于
效果預(yù)覽:
ShardingSphere是一個(gè)數(shù)據(jù)庫中間件,可以實(shí)現(xiàn)分庫分表、讀寫分離等,詳見官網(wǎng): https://shardingsphere.apache.org/
這里使用spring boot+mybatis-plus的方式來搭建demo。
二.項(xiàng)目搭建
使用vscode(java開發(fā)環(huán)境可以百度一下)新建一個(gè)spring boot項(xiàng)目,引入lombok、mybatis-plus等:
11 4.1.1 org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.3 org.springframework.boot spring-boot-devtools runtime true MySQL mysql-connector-java runtime com.baomidou mybatis-plus-boot-starter 3.3.1 org.projectlombok lombok org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${sharding-sphere.version} org.apache.shardingsphere sharding-jdbc-spring-namespace ${sharding-sphere.version} com.alibaba druid 1.1.22 org.antlr antlr4-runtime 4.7.2
項(xiàng)目結(jié)構(gòu):
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: true
db:分別建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{ /** * */ 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{ 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{ }
OrderItemMapper.java
public interface OrderItemMapper extends BaseMapper{ }
service:
OrderService.java
public interface OrderService extends IService{ boolean save(Order order,List items); }
OrderServiceImpl.java
@Service public class OrderServiceImpl extends ServiceImplimplements OrderService { @Autowired private OrderItemService orderItemService; @Transactional(rollbackFor = Exception.class) @Override public boolean save(Order order, List items) { save(order); orderItemService.saveBatch(items); return true; } }
OrderItemService.java
public interface OrderItemService extends IService{ }
OrderItemServiceImpl.java
@Service public class OrderItemServiceimpl extends ServiceImplimplements OrderItemService { }
spring boot啟動(dòng)類:
@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(); // 設(shè)置請(qǐng)求的頁面大于最大頁后操作, true調(diào)回到首頁,false 繼續(xù)請(qǐng)求 默認(rèn)false // paginationInterceptor.setOverflow(false); // 設(shè)置最大單頁限制數(shù)量,默認(rèn) 500 條,-1 不受限制 // paginationInterceptor.setLimit(500); // 開啟 count 的 join 優(yōu)化,只針對(duì)部分 left join paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true)); return paginationInterceptor; } }
三.測(cè)試效果
建一個(gè)測(cè)試類:OrderServiceImplTest.java 并編寫測(cè)試語句
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(); 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 (); 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 (); 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 (); 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 (); 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 (1,4); var queryWrapper=new QueryWrapper (); queryWrapper.orderByAsc("order_id"); var itemPage=orderItemService.page(page,queryWrapper); assertEquals(4, itemPage.getRecords().size()); assertEquals("蘋果", itemPage.getRecords().get(0).getProductName()); } }
測(cè)試通過,看一下數(shù)據(jù)庫的數(shù)據(jù):
看完上述內(nèi)容,你們掌握spring boot+mybatis-plus怎樣使用shardingsphere分庫分表的方法了嗎?如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝各位的閱讀!